PK
`Aoa, mimetypeapplication/epub+zipPK `A iTunesMetadata.plistN
Oracle Database provides several tracing tools that can help you monitor and analyze applications running against an Oracle database.
End to End Application Tracing can identify the source of an excessive workload, such as a high load SQL statement, by client identifier, service, module, action, session, instance, or an entire database. This isolates the problem to a specific user, service, session, or application component.
Oracle Database provides the trcsess
command-line utility that consolidates tracing information based on specific criteria.
The SQL Trace facility and TKPROF
are two basic performance diagnostic tools that can help you monitor applications running against the Oracle database.
This chapter contains the following sections:
See Also: SQL*Plus User's Guide and Reference for information about the use of Autotrace to trace and tune SQL*Plus statements |
End to End Application Tracing simplifies the process of diagnosing performance problems in multitier environments. In these environments, a request from an end client is routed to different database sessions by the middle tier, making it difficult to track a client across database sessions. End to End Application Tracing uses a client ID to uniquely trace a specific end-client through all tiers to the database.
This feature could identify the source of an excessive workload, such as a high load SQL statement, and allow you to contact the specific user responsible. Also, a user having problems can contact you. You can then identify what this user's session is doing at the database level.
End to End Application Tracing also simplifies management of application workloads by tracking specific modules and actions in a service.
End to End Application Tracing can identify workload problems for:
Client identifier - specifies an end user based on the logon ID, such as HR.HR
Service - specifies a group of applications with common attributes, service level thresholds, and priorities; or a single application, such as ACCTG
for an accounting application
Module - specifies a functional block, such as Accounts Receivable or General Ledger, of an application
Action - specifies an action, such as an INSERT or UPDATE operation, in a module
Session - specifies a session based on a given database session identifier (SID), on the local instance
Instance - specifies a given instance based on the instance name
After tracing information is written to files, you can consolidate this information with the trcsess
utility and diagnose it with an analysis utility such as TKPROF
.
To to create services on single instance Oracle databases, you can use the CREATE_SERVICE
procedure in the DBMS_SERVICE
package or set the SERVICE_NAMES
initialization parameter.
The module and action names are set by the application developer. For example, you would use the SET_MODULE
and SET_ACTION
procedures in the DBMS_APPICATION_INFO
package to set these values in a PL/SQL program.
The recommended interface for End to End Application Tracing is Oracle Enterprise Manager. Using Enterprise Manager, you can view the top consumers for each consumer type, and enable or disable statistics gathering and SQL tracing for specific consumers. Whenever possible, you should use Enterprise Manager to manage End to End Application Tracing, as described in Oracle Database 2 Day + Performance Tuning Guide. If Oracle Enterprise Manager is unavailable, then you can manage this feature using the DBMS_MONITOR
APIs, as described in the following sections:
Enabling and Disabling Statistic Gathering for End to End Tracing
Viewing Gathered Statistics for End to End Application Tracing
See Also:
|
To gather the appropriate statistics using PL/SQL, you need to enable statistics gathering for client identifier, service, module, or action using procedures in the DBMS_MONITOR
package.
You can gather statistics by the following criteria:
The default level is the session-level statistics gathering. Statistics gathering is global for the database and continues after an instance is restarted.
The procedure CLIENT_ID_STAT_ENABLE
enables statistic gathering for a given client identifier. For example, to enable statistics gathering for a specific client identifier:
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE(client_id => 'OE.OE');
In the example, OE.OE
is the client identifier for which you want to collect statistics. You can view client identifiers in the CLIENT_IDENTIFIER
column in V$SESSION
.
The procedure CLIENT_ID_STAT_DISABLE
disables statistic gathering for a given client identifier. For example:
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_DISABLE(client_id => 'OE.OE');
The procedure SERV_MOD_ACT_STAT_ENABLE
enables statistic gathering for a combination of service, module, and action. For example:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'ACCTG', module_name => 'PAYROLL'); EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'ACCTG', module_name => 'GLEDGER', action_name => 'INSERT ITEM');
If both of the previous commands are executed, then statistics are gathered as follows:
For the ACCTG
service, because accumulation for each service name is the default
For all actions in the PAYROLL
module
For the INSERT
ITEM
action within the GLEDGER
module
The procedure SERV_MOD_ACT_STAT_DISABLE
disables statistic gathering for a combination of service, module, and action. For example:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(service_name => 'ACCTG', module_name => 'GLEDGER', action_name => 'INSERT ITEM');
Regarding statistics gathering, when you change the module or action using these procedures, the change takes effect when the next user call is executed in the session. For example, if a module is set to module1
in a session, and if the module is reset to module2
in a user call in the session, then the module remains module1
during this user call. The module is changed to module2
in the next user call in the session.
You can display the statistics that have been gathered with several dynamic views.
The accumulated global statistics for the currently enabled statistics can be displayed with the DBA_ENABLED_AGGREGATIONS
view.
The accumulated statistics for a specified client identifier can be displayed in the V$CLIENT_STATS
view.
The accumulated statistics for a specified service can be displayed in V$SERVICE_STATS
view.
The accumulated statistics for a combination of specified service, module, and action can be displayed in the V$SERV_MOD_ACT_STATS
view.
The accumulated statistics for elapsed time of database calls and for CPU use can be displayed in the V$SERVICEMETRIC
view.
To enable tracing for client identifier, service, module, action, session, instance or database, execute the appropriate procedures in the DBMS_MONITOR
package. You can enable tracing for specific diagnosis and workload management by the following criteria:
With the criteria that you provide, specific trace information is captured in a set of trace files and combined into a single output trace file.
The CLIENT_ID_TRACE_ENABLE
procedure enables tracing globally for the database for a given client identifier. For example:
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'OE.OE', waits => TRUE, binds => FALSE);
In this example, OE.OE
is the client identifier for which SQL tracing is to be enabled. The TRUE
argument specifies that wait information will be present in the trace. The FALSE
argument specifies that bind information will not be present in the trace.
The CLIENT_ID_TRACE_DISABLE
procedure disables tracing globally for the database for a given client identifier. To disable tracing, for the previous example:
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'OE.OE');
The SERV_MOD_ACT_TRACE_ENABLE
procedure enables SQL tracing for a given combination of service name, module, and action globally for a database, unless an instance name is specified in the procedure.
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'ACCTG', waits => TRUE, binds => FALSE, instance_name => 'inst1');
In this example, the service ACCTG
is specified. The module or action name is not specified. The TRUE
argument specifies that wait information will be present in the trace. The FALSE
argument specifies that bind information will not be present in the trace. The inst1
instance is specified to enable tracing only for that instance.
To enable tracing for all actions for a given combination of service and module:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'ACCTG', module_name => 'PAYROLL', waits => TRUE, binds => FALSE, instance_name => 'inst1');
The SERV_MOD_ACT_TRACE_DISABLE
procedure disables the trace at all enabled instances for a given combination of service name, module, and action name globally. For example, the following disables tracing for the first example in this section:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'ACCTG', instance_name => 'inst1');
This example disables tracing for the second example in this section:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'ACCTG', module_name => 'PAYROLL', instance_name => 'inst1');
The SESSION_TRACE_ENABLE
procedure enables the trace for a given database session identifier (SID), on the local instance.
To enable tracing for a specific session ID and serial number, determine the values for the session to trace:
SELECT SID, SERIAL#, USERNAME FROM V$SESSION; SID SERIAL# USERNAME ---------- ---------- ------------------------------ 27 60 OE ...
Use the appropriate values to enable tracing for a specific session:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60, waits => TRUE, binds => FALSE);
The TRUE
argument specifies that wait information will be present in the trace. The FALSE
argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE
procedure disables the trace for a given database session identifier (SID) and serial number. For example:
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60);
While the DBMS_MONITOR
package can only be invoked by a user with the DBA role, any user can also enable SQL tracing for their own session by using the DBMS_SESSION
package. A user can invoke the SESSION_TRACE_ENABLE
procedure to enable session-level SQL trace for the user's session. For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
The TRUE
argument specifies that wait information will be present in the trace. The FALSE
argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE
procedure disables the trace for the invoking session. For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();
The DATABASE_TRACE_ENABLE
procedure enables SQL tracing for a given instance or an entire database. Tracing is enabled for all current and future sessions. For example:
EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits => TRUE, binds => FALSE, instance_name => 'inst1');
In this example, the inst1
instance is specified to enable tracing for that instance. The TRUE
argument specifies that wait information will be present in the trace. The FALSE
argument specifies that bind information will not be present in the trace. This example results in SQL tracing of all SQL in the inst1
instance.
The DATABASE_TRACE_ENABLE
procedure overrides all other session-level traces, but will be complementary to the client identifier, service, module, and action traces. All new sessions will inherit the wait and bind information specified by this procedure until the DATABASE_TRACE_DISABLE
procedure is called. When this procedure is invoked with the instance_name
parameter specified, it will reset the session-level SQL trace for the named instance. If this procedure is invoked without the instance_name
parameter specified, then it will reset the session-level SQL trace for the entire database.
The DATABASE_TRACE_DISABLE
procedure disables the tracing for an entire instance or database. For example:
EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name => 'inst1');
In this example, all session-level SQL tracing will be disabled for the inst1
instance. To disable the session-level SQL tracing for an entire database, invoke the DATABASE_TRACE_DISABLE
procedure without specifying the instance_name
parameter:
EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE();
An Oracle Enterprise Manager report or the DBA_ENABLED_TRACES
view can display outstanding traces. In the DBA_ENABLED_TRACES
view, you can determine detailed information about how a trace was enabled, including the trace type. The trace type specifies whether the trace is enabled for client identifier, session, service, database, or a combination of service, module, and action.
The trcsess
utility consolidates trace output from selected trace files based on several criteria:
Session ID
Client ID
Service name
Action name
Module name
After trcsess
merges the trace information into a single output file, the output file could be processed by TKPROF
.
trcsess
is useful for consolidating the tracing of a particular session for performance or debugging purposes. Tracing a specific session is usually not a problem in the dedicated server model as a single dedicated process serves a session during its lifetime. You can see the trace information for the session from the trace file belonging to the dedicated server serving it. However, in a shared server configuration a user session is serviced by different processes from time to time. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.
The syntax for the trcsess
utility is:
trcsess [output=output_file_name] [session=session_id] [clientid=client_id] [service=service_name] [action=action_name] [module=module_name] [trace_files]
where
output
specifies the file where the output is generated. If this option is not specified, then the utility writes to standard output.
session
consolidates the trace information for the session specified. The session identifier is a combination of session index and session serial number, such as 21.2371
. You can locate these values in the V$SESSION
view.
clientid
consolidates the trace information given client ID.
service
consolidates the trace information for the given service name.
action
consolidates the trace information for the given action name.
module
consolidates the trace information for the given module name.
trace_files
is a list of all the trace file names, separated by spaces, in which trcsess
should look for trace information. You can use the wildcard character (*
) to specify the trace file names. If you do not specify trace files, then trcsess
takes all the files in the current directory as input.
You must specify one of the session
, clientid
, service
, action
, or module
options. If more then one of the session
, clientid
, service
, action
, or module
options is specified, then the trace files which satisfies all the criteria specified are consolidated into the output file.
This sample output of trcsess
shows the consolidation of traces for a particular session. In this example the session index and serial number equals 21.2371
.
You can invoke trcsess
with various options. In the following case, all files in current directory are taken as input:
trcsess session=21.2371
In this case, several trace files are specified:
trcsess session=21.2371 main_12359.trc main_12995.trc
The sample output is similar to the following:
[PROCESS ID = 12359] *** 2002-04-02 09:48:28.376 PARSING IN CURSOR #1 len=17 dep=0 uid=27 oct=3 lid=27 tim=868373970961 hv=887450622 ad='22683fb4' select * from cat END OF STMT PARSE #1:c=0,e=339,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=868373970944 EXEC #1:c=0,e=221,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=868373971411 FETCH #1:c=0,e=791,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=4,tim=868373972435 FETCH #1:c=0,e=1486,p=0,cr=20,cu=0,mis=0,r=6,dep=0,og=4,tim=868373986238 *** 2002-04-02 10:03:58.058 XCTEND rlbk=0, rd_only=1 STAT #1 id=1 cnt=7 pid=0 pos=1 obj=0 op='FILTER ' STAT #1 id=2 cnt=7 pid=1 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ ' STAT #1 id=3 cnt=7 pid=2 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 ' STAT #1 id=4 cnt=0 pid=1 pos=2 obj=4 op='TABLE ACCESS CLUSTER TAB$J2 ' STAT #1 id=5 cnt=6 pid=4 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# ' [PROCESS ID=12995] *** 2002-04-02 10:04:32.738 Archiving is disabled Archiving is disabled
The SQL Trace facility and TKPROF
let you accurately assess the efficiency of the SQL statements an application runs. For best results, use these tools with EXPLAIN
PLAN
rather than using EXPLAIN
PLAN
alone.
The SQL Trace facility provides performance information on individual SQL statements. It generates the following statistics for each statement:
Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each commit and rollback
Wait event data for each SQL statement, and a summary for each trace file
If the cursor for the SQL statement is closed, then SQL Trace also provides row source information that includes:
Row operations showing the actual execution plan of each SQL statement
Number of rows, number of consistent reads, number of physical reads, number of physical writes, and time elapsed for each operation on a row
Although it is possible to enable the SQL Trace facility for a session or for an instance, it is recommended that you use the DBMS_SESSION
or DBMS_MONITOR
packages instead. When the SQL Trace facility is enabled for a session or for an instance, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files. Using the SQL Trace facility can have a severe performance impact and may result in increased system overhead, excessive CPU usage, and inadequate disk space.
See Also: "Enabling and Disabling for End-to-End Tracing" to learn how to use theDBMS_SESSION or DBMS_MONITOR packages to enable SQL tracing for a session or an instance |
Oracle Database provides the trcsess
command-line utility that consolidates tracing information from several trace files based on specific criteria, such as session or client ID. See "Using the trcsess Utility".
You can run the TKPROF
program to format the contents of the trace file and place the output into a readable output file. TKPROF
can also:
Create a SQL script that stores the statistics in the database
Determine the execution plans of SQL statements
Note: If the cursor for a SQL statement is not closed, thenTKPROF output does not automatically include the actual execution plan of the SQL statement. In this situation, you can use the EXPLAIN option with TKPROF to generate an execution plan. |
TKPROF
reports each statement executed with the resources it has consumed, the number of times it was called, and the number of rows which it processed. This information lets you easily locate those statements that are using the greatest resource. With experience or with baselines available, you can assess whether the resources used are reasonable given the work done.
Follow these steps to use the SQL Trace facility and TKPROF
:
Set initialization parameters for trace file management.
See "Step 1: Setting Initialization Parameters for Trace File Management".
Enable the SQL Trace facility for the desired session, and run the application. This step produces a trace file containing statistics for the SQL statements issued by the application.
Run TKPROF
to translate the trace file created in Step 2 into a readable output file. This step can optionally create a SQL script that you can use to store the statistics in a database.
Interpret the output file created in Step 3.
Optionally, run the SQL script produced in Step 3 to store the statistics in the database.
The following sections discuss each step in depth.
When the SQL Trace facility is enabled for a session, Oracle Database generates a trace file containing statistics for traced SQL statements for that session. When the SQL Trace facility is enabled for an instance, Oracle Database creates a separate trace file for each process. Before enabling the SQL Trace facility:
Check the settings of the TIMED_STATISTICS
, MAX_DUMP_FILE_SIZE
, and USER_DUMP_DEST
initialization parameters. See Table 21-1.
Table 21-1 Initialization Parameters to Check Before Enabling SQL Trace
See Also:
|
Devise a way of recognizing the resulting trace file.
Be sure you know how to distinguish the trace files by name. Oracle Database writes them to the user dump destination specified by USER_DUMP_DEST
. However, this directory can soon contain many hundreds of files, usually with generated names. It might be difficult to match trace files back to the session or process that created them. You can tag trace files by including in your programs a statement like SELECT
'
program_name
'
FROM
DUAL
. You can then trace each file back to the process that created it.
You can also set the TRACEFILE_IDENTIFIER
initialization parameter to specify a custom identifier that becomes part of the trace file name. For example, you can add my_trace_id
to subsequent trace file names for easy identification with the following:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'my_trace_id';
See Also: Oracle Database Reference for information about theTRACEFILE_IDENTIFIER initialization parameter |
If the operating system retains multiple versions of files, then ensure that the version limit is high enough to accommodate the number of trace files you expect the SQL Trace facility to generate.
The generated trace files can be owned by an operating system user other than yourself. This user must make the trace files available to you before you can use TKPROF
to format them.
See Also:
|
Enable the SQL Trace facility for the session by using one of the following:
DBMS_MONITOR.DATABASE_TRACE_ENABLE
procedure (recommended)
DBMS_SESSION.SET_SQL_TRACE
procedure
ALTER SESSION SET SQL_TRACE = TRUE;
Caution: Because running the SQL Trace facility increases system overhead, enable it only when tuning SQL statements, and disable it when you are finished. Oracle recommends that you use theDBMS_SESSION or DBMS_MONITOR packages to enable SQL tracing for a session or an instance. To learn about these packages, see "Enabling and Disabling for End-to-End Tracing".
You may need to modify an application to contain the |
To disable the SQL Trace facility for the session, enter:
ALTER SESSION SET SQL_TRACE = FALSE;
The SQL Trace facility is automatically disabled for the session when the application disconnects from Oracle.
You can enable the SQL Trace facility for an instance by setting the value of the SQL_TRACE
initialization parameter to TRUE
in the initialization file.
SQL_TRACE = TRUE
After the instance has been restarted with the updated initialization parameter file, SQL Trace is enabled for the instance and statistics are collected for all sessions. If the SQL Trace facility has been enabled for the instance, then you can disable it for the instance by setting the value of the SQL_TRACE
parameter to FALSE
.
Note: SettingSQL_TRACE to TRUE can have a severe performance impact. For more information, see Oracle Database Reference. |
TKPROF
accepts as input a trace file produced by the SQL Trace facility, and it produces a formatted output file. TKPROF
can also be used to generate execution plans.
After the SQL Trace facility has generated trace files, you can:
Run TKPROF
on each individual trace file, producing several formatted output files, one for each session.
Concatenate the trace files, and then run TKPROF
on the result to produce a formatted output file for the entire instance.
Run the trcsess
command-line utility to consolidate tracing information from several trace files, then run TKPROF
on the result. See "Using the trcsess Utility".
TKPROF
does not report COMMITs
and ROLLBACKs
that are recorded in the trace file.
Sample output from TKPROF
is as follows:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno; call count cpu elapsed disk query current rows ---- ------- ------- --------- -------- -------- ------- ------ Parse 1 0.16 0.29 3 13 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.03 0.26 2 2 4 14 Misses in library cache during parse: 1 Parsing user id: (8) SCOTT Rows Execution Plan ------- ---------------------------------------------------
14 MERGE JOIN 4 SORT JOIN 4 TABLE ACCESS (FULL) OF 'DEPT' 14 SORT JOIN 14 TABLE ACCESS (FULL) OF 'EMP'
For this statement, TKPROF
output includes the following information:
The text of the SQL statement
The SQL Trace statistics in tabular form
The number of library cache misses for the parsing and execution of the statement.
The user initially parsing the statement.
TKPROF
also provides a summary of user level statements and recursive SQL calls for the trace file.
TKPROF
is run from the operating system prompt. The syntax is:
tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n] [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table] [explain=user/password] [record=filename4] [width=n]
The input and output files are the only required arguments. If you invoke TKPROF
without arguments, then the tool displays online help. Use the arguments in Table 21-2 with TKPROF
.
Table 21-2 TKPROF Arguments
Argument | Description |
---|---|
|
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 concatenating individual trace files from multiple sessions. |
|
Specifies the file to which |
|
Specifies whether to record summary for any wait events found in the trace file. Values are |
|
Sorts traced SQL statements in descending order of specified sort option before listing them into the output file. If multiple options are specified, then the output is sorted in descending order by the sum of the values specified in the sort options. If you omit this parameter, then |
|
Number of times parsed. |
|
CPU time spent parsing. |
|
Elapsed time spent parsing. |
|
Number of physical reads from disk during parse. |
|
Number of consistent mode block reads during parse. |
|
Number of current mode block reads during parse. |
|
Number of library cache misses during parse. |
|
Number of executes. |
|
CPU time spent executing. |
|
Elapsed time spent executing. |
|
Number of physical reads from disk during execute. |
|
Number of consistent mode block reads during execute. |
|
Number of current mode block reads during execute. |
|
Number of rows processed during execute. |
|
Number of library cache misses during execute. |
|
Number of fetches. |
|
CPU time spent fetching. |
|
Elapsed time spent fetching. |
|
Number of physical reads from disk during fetch. |
|
Number of consistent mode block reads during fetch. |
|
Number of current mode block reads during fetch. |
|
Number of rows fetched. |
|
Userid of user that parsed the cursor. |
|
Lists only the first integer sorted SQL statements from the output file. If you omit this parameter, then |
|
If you specify |
|
Creates a SQL script that stores the trace file statistics in the database. |
|
Enables and disables the listing of SQL statements issued by the user |
|
Specifies the schema and name of the table into which The specified user must be able to issue This option allows multiple individuals to run If you use the If no plan table exists, |
|
Determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file. |
|
Creates a SQL script with the specified |
|
An integer that controls the output line width of some TKPROF output, such as the explain plan. This parameter is useful for post-processing of TKPROF output. |
This section provides two brief examples of TKPROF
usage. For an complete example of TKPROF
output, see "Sample TKPROF Output".
If you are processing a large trace file using a combination of SORT
parameters and the PRINT
parameter, then you can produce a TKPROF
output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:
TKPROF ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10
This example runs TKPROF
, accepts a trace file named examp12_jane_fg_sqlplus_007
.trc
, and writes a formatted output file named outputa
.prf
:
TKPROF examp12_jane_fg_sqlplus_007.trc OUTPUTA.PRF EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO SORT=(EXECPU,FCHCPU)
This example is likely to be longer than a single line on the screen, and you might need to use continuation characters, depending on the operating system.
Note the other parameters in this example:
The EXPLAIN
value causes TKPROF
to connect as the user scott
and use the EXPLAIN
PLAN
statement to generate the execution plan for each traced SQL statement. You can use this to get access paths and row source counts.
Note: If the cursor for a SQL statement is not closed, thenTKPROF output does not automatically include the actual execution plan of the SQL statement. In this situation, you can use the EXPLAIN option with TKPROF to generate an execution plan. |
The TABLE
value causes TKPROF
to use the table temp_plan_table_a
in the schema scott
as a temporary plan table.
The INSERT
value causes TKPROF
to generate a SQL script named STOREA
.SQL
that stores statistics for all traced SQL statements in the database.
The SYS
parameter with the value of