The SQL Trace facility provides performance information on individual SQL statements. It generates the following statistics for each statement:
Each commit and rollback (this feature is exclusive for sql trace.not for tkprof)
also it tells you what access path is oracle using to access tables and it determines the execution plans of SQL statements
Initialization Parameters to Check Before Enabling SQL Trace
TIMED_STATISTICS, MAX_DUMP_FILE_SIZE, and USER_DUMP_DEST
Timed statistics are automatically collected for the database if the initialization parameter STATISTICS_LEVEL is set to TYPICAL or ALL. If STATISTICS_LEVEL is set to BASIC, then you must set TIMED_STATISTICS to TRUE to enable collection of timed statistics.
Enabling the SQL Trace Facility
DBMS_SESSION.SET_SQL_TRACE procedure
ALTER SESSION SET SQL_TRACE = TRUE;
You can enable SQL Trace in another session by using the DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure.
EX:
SQL>Exec DBMS_SYSTEM.set_sql_trace_in_session(sid,serial#,true);
SQL>alter session set sql_trace=true;
SQL>exec DBMS_SYSTEM.set_sql_trace(true)
Because running the SQL Trace facility increases system overhead, enable it only when tuning SQL statements, and disable it when you are finished.
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.
Sample TKPROF Output
sample output from TKPROF is as follows:
WHERE emp.deptno = dept.deptno;
call count cpu elapsed disk query current rows
---- ------- ------- --------- -------- -------- ------- ------
Misses in library cache during parse: 1
Misses in library cache during execute:10
Optimizer mode: ALL_ROWS
------- ---------------------------------------------------
4 TABLE ACCESS (FULL) OF 'DEPT'
14 TABLE ACCESS (FULL) OF 'EMP'
For this statement, TKPROF output includes the following information:
TKPROF also provides a summary of user level statements and recursive SQL calls for the trace file.like
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS and
OVERALL TOTALS FOR ALL NON RECURSIVE STATEMENTS
TKPROF Example 1
if you are processing a large trace file using a combination of SORT parameters and the PRINTTKPROF 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: parameter, then you can produce a
TKPROF ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10
PRSDSK
Number of physical reads from disk during parse.
EXEDSK
Number of physical reads from disk during execute.
FCHDSK
Number of physical reads from disk during fetch.
TKPROF Example 2
This example runs TKPROF, accepts a trace file named dlsun12_jane_fg_sqlplus_007.trc, and writes a formatted output file named outputa.prf:
TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
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.
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 NO causes TKPROF to omit recursive SQL statements from the output file. In this way, you can ignore internal Oracle statements such as temporary table operations.
The SORT value causes TKPROF to sort the SQL statements in order of the sum of the CPU time spent executing and the CPU time spent fetching rows before writing them to the output file. For greatest efficiency, always use SORT parameters
SQL Trace Statistics for Parses, Executes, and Fetches.
COUNT
Number of times a statement was parsed, executed, or fetched.
Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.
Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.
Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE
ROWS
Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.
NOTE:
The row source counts are displayed when a cursor is closed. In SQL*Plus, there is only one user cursor, so each statement executed causes the previous cursor to be closed; therefore, the row source counts are displayed. PL/SQL has its own cursor handling and does not close child cursors when the parent cursor is closed. Exiting (or reconnecting) causes the counts to be displayed.
Interpreting the Resolution of Statistics
Timing statistics have a resolution of one hundredth of a second; therefore, any operation on a cursor that takes a hundredth of a second or less might not be timed accurately.
Understanding Recursive Calls
Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional 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, then Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.
If recursive calls occur while the SQL Trace facility is enabled, then TKPROF produces statistics for the recursive SQL statements and marks them clearly as recursive SQL statements in the output file.
You can suppress the listing of Oracle internal recursive calls (for example, space management) in the output file by setting the SYS command-line parameter to NO. The statistics for a recursive SQL statement are included in the listing for that statement, not in the listing for the SQL statement that caused the recursive call. So, when you are calculating the total resources required to process a SQL statement, consider the statistics for that statement as well as those for recursive calls caused by that statement.
Library Cache Misses in TKPROF
TKPROF also lists the number of library cache misses resulting from parse and execute steps for each SQL statement.
Statement Truncation in SQL Trace
The following SQL statements are truncated to 25 characters in the SQL Trace file:
Deciding Which Statements to Tune
You need to find which SQL statements use the most CPU or disk resource. If the TIMED_STATISTICS parameter is on, then you can find high CPU activity in the CPU column. If TIMED_STATISTICS is not on, then check the QUERY and CURRENT columns.
The following listing shows TKPROF output for one SQL statement as it appears in the output file:
WHERE emp.deptno = dept.deptno;
call count cpu elapsed disk query current rows
---- ------- ------- --------- -------- -------- ------- ------
Fetch 35 6.70 6.83 100 12326 2 824
------------------------------------------------------------------
total 57 7.01 7.67 100 12329 8 826
Misses in library cache during parse: 0
If it is acceptable to have 7.01 CPU seconds and to retrieve 824 rows, then you need not look any further at this trace output. In fact, a major use of TKPROF reports in a tuning exercise is to eliminate processes from the detailed tuning phase.
0 comments:
Post a Comment