TKProf Basic Overview (文档 ID 41634.1)
来源：互联网 发布：德国进出口数据 编辑：程序博客网 时间：2024/04/28 13:12
*** Checked for relevance on 19-Jul-2013*** This document refers to legacy versions. Although some information may still be relevant in later versions, changes may have been made that are not reflected in this document.TKPROF and Problem Solving==========================Purpose-------This document discusses the use of the following: * SQL TRACE * EXPLAIN PLAN * TKPROFScope & Application--------------------For users wanting to obtain a query plan and/or execution times for their queries.Related Documents------------------The majority of the material covered in this document can be found in the following manuals: * Oracle7 Server SQL Reference * Oracle7 Server Tuning Introduction------------Much information can be gathered about the performance of an Oracledatabase from the dynamic 'V$' views, as these are documented in theOracle7 Server Manual. Oracle also provides additional performancediagnostic tools to assist in monitoring and tuning the server.The reader should be familiar with the concepts of the Oracle Serverinitialization file and startup parameters. Most of the suggestionsdescribed in this document should be conducted by an experienced DBA.Overview Of SQL TRACE---------------------The diagnostic tool 'sql trace' provides performance information aboutindividual SQL statements and generates the following statistics foreach statement: * parse, execute, and fetch counts * CPU and elapsed times * physical reads and logical reads * number of rows processed * misses on the library cache This information is input to a trace (.trc) file and sql trace can beenabled/disabled for a session or an instance.Setting Initialization Parameters---------------------------------The following parameters need to be set up in the "init<sid>.ora" filefor the particular instance (SID) that you wish to use SQL Trace: * SQL_TRACE + Enable/Disable SQL Trace for the instance. Values ------ TRUE Enable statistics to be collected for all sessions. FALSE Disable statistics to be collected for all sessions. * TIMED_STATISTICS + Enable/Disable the collection of timed statistics, such as CPU and elapsed times. Values ------ TRUE Enable timing (we usually recommend this) FALSE Default value. * MAX_DUMP_FILE_SIZE + Specifies the maximum size of trace files operating system blocks. Values ------ The default value for this is 500 but if your trace file is truncated then increase this value. * USER_DUMP_DEST + Specifies the destination for the trace file. Values ------ The default value for this parameter is the default destination for system dumps on your operating system. Enabling SQL Trace------------------The SQL Trace facility can either be enabled/disabled for an individualsession or the instance. * To enable the SQL trace facility for your session issue the following SQL statement: ALTER SESSION SET SQL_TRACE = TRUE; * To disable the SQL trace facility for your session issue the following SQL statement: ALTER SESSION SET SQL_TRACE = FALSE;Enabling TIMED_STATISTICS-------------------------The parameter TIMED_STATISTICS can be enabled/disabled dynamically by using ALTER SYSTEM SET TIMED_STATISTICS = TRUE; or ALTER SESSION SET TIMED_STATISTICS = TRUE;To disable set TIMED_STATISTICS = FALSE:SQL Trace Facility for an Instance----------------------------------Note that if the initialization parameter SQL_TRACE=TRUE, then statisticswill be gathered for ALL sessions. If the facility has been enabled forthe instance, it may be disabled for an individual session by issuing bythe above SQL statement.Trace Files-----------Oracle will generate trace (.trc) files for every session where the valueof SQL_TRACE = TRUE and write them to the USER_DUMP_DEST destination.If tracing has been enabled for the instance then individual trace fileswill be generated for each session, unless otherwise disabled (see above).Note, that the generated files may be owned by an operating system userother than your own so you may have to get this user to grant you accessbefore you can use TKPROF to format them.Using TKPROF------------The TKPROF facility accepts as input an SQL trace file and produces aformatted output file. Note that TKPROF can be run on individual orappended trace files to produce the formatted output file. * For the full syntax of TKPROF see Oracle7 Server Tuning Manual. * Invoke TKPROF with no arguments and online help is displayed.Syntax of TKPROF----------------TKPROF command ::=>>-- TKPROF filename1 filename2 ----------------------------------------------> | | +- SORT = ---------------------------------+ | | +-- OPTION --+ | | | +---- , ----+ | | V | | |__( OPTION )__|>-----------------------------------------------------------------------------> | | | | | | +-- PRINT = integer --+ +-- INSERT = filname3 --+ +-- SYS = ---------+ | | +- YES -+ | | +- NO --+>-----------------------------------------------------------------------------> | | +---------------------------------------- EXPLAIN = user/password ------+ | | +---- TABLE = schema.table ----+>---------------------------------------------------------------------------->< | | +---- RECORD = filname ----+The Basics Of TKPROF--------------------For the purpose of this document we will only describe some of thefundamental arguments for TKPROF: * 'filename1' Specifies the input file, a trace file containing statistics produced by the SQL trace facility. This file can be either a trace file produced for a single session or a file produced by appending together together individual trace files from multiple sessions. * 'filename2' Specifies the file to which TKPROF writes its formatted output. * 'EXPLAIN' Determines the execution plan for each SQL statement in in the trace file and writes these execution plans to the output file. TKPROF determines execution plans by issuing the EXPLAIN PLAN command after connecting to Oracle with the user and password specified in this parameter. The specified user must have CREATE SESSION privileges. * 'TABLE' Specifies the schema and name of the table into which TKPROF temporarily places execution plans before writing them to the output file. If the specified table already exists, TKPROF deletes its rows then uses it for the EXPLAIN PLAN command and then deletes its rows. If this table does not exist, TKPROF creates, uses, then drops it. The specified user must be able to issue INSERT, SELECT, and DELETE statements against the table. If the table does not already exist, the user must also be able to issue CREATE TABLE and DROP TABLE statements. This option allows multiple individuals to run TKPROF concurrently with the same user in the EXPLAIN value. These individuals can specify different TABLE values and avoid destructively interfering with each other's processing on the temporary plan table. If you use the EXPLAIN parameter without the TABLE parameter, TKPROF uses the table PROF$PLAN_TABLE in the schema of the user specified by the EXPLAIN parameter. If you use the TABLE parameter without the EXPLAIN parameter, TKPROF ignores the TABLE parameter. * 'SYS' Enables and disables the listing of SQL statements issued by the user SYS, or recursive SQL statements into the output file. The default value of YES causes TKPROF to list these statements. The value of NO causes TKPROF to to omit them.Simple Example--------------This example shows TKPROF being run to format a trace file named"dsdb2_ora_18468.trc" and writing it to a formatted output filenamed "dsdb2_trace.out". TKPROF dsdb2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGERNote that if the command is likely to be longer than a single line on yourterminal screen you may have to use continuation characters.The Parameters In This Example: -------------------------------The EXPLAIN value causes TKPROF to connect as the user SCOTT and use the EXPLAIN PLAN command to generate the execution plan for each traced SQL statement. The SYS parameter with the value of NO causes TKPROF to omitrecursive SQL statements from the output file.Interpretting TKPROF Output===========================The EXPLAIN PLAN Command------------------------The EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements.A statement's execution plan is the sequence of operations that Oracle performs to execute the statement. By examining the execution plan, you can see exactly how Oracle executes your SQL statement. This information can help you determine whether the SQL statement you have written takes advantage of the indexes available. For the syntax of the EXPLAIN PLAN command, see the Oracle7 Server SQL Reference Manual. Creating the Output Table-------------------------Before you can issue an EXPLAIN PLAN statement, there must exist a table to hold its output, you do either of the following: * Run the SQL script "UTLXPLAN.SQL" to create a sample output table called PLAN_TABLE in your schema. * Issue a CREATE TABLE statement to create an output with any name you choose. You can then issue an EXPLAIN PLAN statement and direct its output to this table. Any table used to store the output of the EXPLAIN PLAN command must have the same column names and datatypes as the PLAN_TABLE.SQL Trace Facility Statistics-----------------------------TKPROF lists the statistics for a SQL statement returned by the SQL trace facility in rows and columns. Each row corresponds to one ofthree steps of SQL statement processing: * PARSE This step translates the SQL statement into an execution plan. This includes checks for proper security authorization and checks or the existence of tables, columns, and other referenced objects. * EXECUTE This step is the actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this step modifies the data. For SELECT statements, the step identifies the selected rows. * FETCH This step retrieves rows returned by a query. Fetches are only performed for SELECT statements.The step for which each row contains statistics is identified by the value ofthe call column. The other columns of the SQL trace facility output arecombined statistics for all parses, all executes, and all fetches of astatement: COUNT Number of times a statement was parsed, executed, or fetched. CPU Total CPU time in seconds for all parse, execute, or fetch calls for the statement. ELAPSED Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. DISK Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls. QUERY Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Buffers are usually retrieved in consistent mode for queries. CURRENT Total number of buffers retrieved in current mode. Buffers are often retrieved in current mode for INSERT, UPDATE, and DELETE statements. + The sum of QUERY & CURRENT is he total number of buffers accessed. ROWS Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.For SELECT statements, the number of rows returned appears for the fetch step.For UPDATE, DELETE, and INSERT statements, the number of rows processed appears for the execute step.Example Output (Partial) File-----------------------------TKPROF: Release 7.2.2.3.0 - Production on Wed Aug 7 11:29:43 1996Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.Trace file: <filename>.trcSort options: default********************************************************************************count = number of times OCI procedure was executedcpu = cpu time in seconds executingelapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = number of buffers gotten in current mode (usually for update)rows = number of rows processed by the fetch or execute call********************************************************************************DELETE FROM RM$HASH_ELMScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.00 0.00 0 0 0 0Execute 29 12.04 12.61 6786 6853 108 19Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 31 12.04 12.61 6786 6853 108 19Misses in library cache during parse: 0Optimizer hint: CHOOSEParsing user id: 9 (DES12A) (recursive depth: 3)Rows Execution Plan------- --------------------------------------------------- 0 DELETE STATEMENT HINT: CHOOSE 16 TABLE ACCESS (FULL) OF 'RM$HASH_ELMS'*****************************END-OF-EXAMPLE-FILE********************************Resolution of Statistics------------------------Since timing statistics have a resolution of one hundredth of a second, any operation on a cursor that takes a hundredth of a second or less maynot be timed accurately. Keep this in mind when interpreting statistics.In particular, be careful when interpreting the results from simple queriesthat execute very quickly.Recursive Calls---------------Sometimes to execute a SQL statement issued by a user, Oracle must issueadditional statements. Such statements are called 'recursive calls' or'recursive SQL statements'. For example, if you insert a row into a table that does not have enough space to hold that row, Oracle makes recursivecalls to allocate the space dynamically. Recursive calls are also generatedwhen data dictionary information is not available in the data dictionarycache and must be retrieved from disk.If recursive calls occur while the SQL trace facility is enabled, TKPROFproduces statistics for the recursive SQL statements and clearly marks themas recursive SQL statements in the output file. Note that the statistics fora recursive SQL statement are included in the listing for that statement,not in the listing for the SQL statement that caused the recursive call. Sowhen you are calculating the total resources required to process a SQLstatement, you should consider the statistics for that statement as well asthose for recursive calls caused by that statement. + Note that setting the TKPROF command line parameter to NO suppresses the listing of recursive calls in the output in the output file.Execution Plan--------------If you specify the EXPLAIN parameter on the TKPROF command line, TKPROFuses the EXPLAIN PLAN command to generate the execution plan of each SQL statement traced. TKPROF also displays the number of rows processed byeach step of the execution plan.------------------------------------------------------------------------------- Worldwide Customer Support Services
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Generic SQL Performance, SQL Execution, Query Optimizer
SQL_TRACE 0 0
- TKProf Basic Overview (文档 ID 41634.1)
- TKProf Interpretation (9i and above) (文档 ID 760786.1)
- Adaptive Cursor Sharing: Overview (文档 ID 740052.1)
- TKPROF官方文档
- overview basic execution environment(ia_32)
- Tkprof
- tkprof
- Tkprof
- Overview of ORA-1000 Maximum Number of Cursors Exceeded (文档 ID 1012266.6)
- ASM Concepts Quick Overview [ID 1086199.1]
- MySql C Connector 文档翻译 Overview
- 【翻译】Xibo官方文档7-Tour-OverView
- spark官方文档翻译_Cluster Mode Overview
- Unity3D说明文档翻译-Lighting Overview
- 白话React Native 文档(Basic)
- 文档 ID 359515.1
- 文档 ID 457926.1
- MOS 文档 ID 1212703.
- asp.net过滤器
- 一、Jmeter介绍+安装说明
- Android 4.0系统在sdcard不能创建目录
- 使用task来控制不进行重复加载
- 抽象类和接口的区别[好文章就是要收藏！
- TKProf Basic Overview (文档 ID 41634.1)
- 第二章 沟通的障碍
- Java dispose()
- Linux Shell Bash 带有特殊含义的退出码
- Red5服务器代码导入eclipse并测试它自带的demo
- Dmidecode命令详解
- Oracle存储过程中多层嵌套游标的用法
- 函数调用约定：__cdecl,__stdcall,__fastcall,__thiscall
- 安卓学习：设置按钮背景透明与半透明_图片背景透明
