Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-01 |
|
The SQL trace facility and TKPROF
are two basic performance diagnostic tools that can help you monitor and tune applications running against the Oracle Server.
This chapter contains the following sections:
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:
You can enable the SQL trace facility for a session or for an instance. When the SQL trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files.
The additional overhead of running the SQL trace facility against an application with performance problems is normally insignificant compared with the inherent overhead caused by the application's inefficiency.
You can run the TKPROF
program to format the contents of the trace file and place the output into a readable output file. Optionally, TKPROF
can also:
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
:
See "Step 1: Setting Initialization Parameters for Trace File Management".
TKPROF
to translate the trace file created in Step 2 into a readable output file. This step can optionally create a SQL script that can be used to store the statistics in a database.
In the following sections, each of these steps is discussed in depth.
When the SQL trace facility is enabled for a session, Oracle generates a trace file containing statistics for traced SQL statements for that session. When the SQL trace facility is enabled for an instance, Oracle creates a separate trace file for each process.
Before enabling the SQL trace facility:
TIMED_STATISTICS
, MAX_DUMP_FILE_SIZE
, and USER_DUMP_DEST
initialization parameters.
Be sure you know how to distinguish the trace files by name. Oracle 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.
TKPROF
to format them.
See Also:
"Setting the Level of Statistics Collection" for information about |
Enable the SQL trace facility for the session by using one of the following:
DBMS_SESSION.SET_SQL_TRACE
procedureALTER 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. Setting |
Alternatively, you can enable the SQL trace facility for the session by using the DBMS_SESSION
.SET_SQL_TRACE
procedure.
You can enable SQL trace in another session by using the DBMS_SYSTEM
.SET_SQL_TRACE_IN_SESSION
procedure.
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.
To enable the SQL trace facility for the instance, set the value of the SQL_TRACE
initialization parameter to true
. Statistics are collected for all sessions.
ALTER SYSTEM SET SQL_TRACE = true;
After the SQL trace facility has been enabled for the instance, you can disable it for the instance by entering:
ALTER SYSTEM SET SQL_TRACE = false;
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 a number of trace files, you can:
TKPROF
on each individual trace file, producing a number of formatted output files, one for each session.TKPROF
on the result to produce a formatted output file for the entire instance.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:
EXPLAIN
PLAN
.TKPROF
also provides a summary of user level statements and recursive SQL calls for the trace file.
TKPROF::=
If you invoke TKPROF
without arguments, then online help is displayed. Use the following arguments with TKPROF
:
Argument | Meaning |
---|---|
|
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 more than one option is 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 TKPROF lists statements into the output file in order of first use. Sort options are listed as follows: |
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 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. |
|
|
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 filename with all of the nonrecursive SQL in the trace file. This can be used to replay the user events from the trace file. |
|
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 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 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:
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.TABLE
value causes TKPROF
to use the table temp_plan_table_a
in the schema scott
as a temporary plan table.INSERT
value causes TKPROF
to generate a SQL script named STOREA
.SQL
that stores statistics for all traced SQL statements in the database.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.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.This section provides pointers for interpreting TKPROF
output.
While TKPROF
provides a very useful analysis, the most accurate measure of efficiency is the actual performance of the application in question. At the end of the TKPROF
output is a summary of the work done in the database engine by the process during the period that the trace was running.
TKPROF
lists the statistics for a SQL statement returned by the SQL trace facility in rows and columns. Each row corresponds to one of three steps of SQL statement processing. Statistics are identified by the value of the CALL
column:
The other columns of the SQL trace facility output are combined statistics for all parses, all executes, and all fetches of a statement. The sum of query
and current
is the total number of buffers accessed, also called Logical I/Os (LIOs).
Statistics about the processed rows appear in the ROWS
column.
SQL Trace Statistic | Meaning |
---|---|
|
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.
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. Keep this in mind when interpreting statistics. In particular, be careful when interpreting the results from simple queries that execute very quickly.
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.
Note: Recursive SQL statistics are not included for SQL-level operations. However, recursive SQL statistics are included for operations done under the SQL level, such as triggers. For more information, see "Avoiding the Trigger Trap". |
TKPROF
also lists the number of library cache misses resulting from parse and execute steps for each SQL statement. These statistics appear on separate lines following the tabular statistics. If the statement resulted in no library cache misses, then TKPROF
does not list the statistic. In "Sample TKPROF Output", the statement resulted in one library cache miss for the parse step and no misses for the execute step.
The following SQL statements are truncated to 25 characters in the SQL trace file:
SET ROLE GRANT ALTER USER ALTER ROLE CREATE USER CREATE ROLE
TKPROF
also lists the user ID of the user issuing each SQL statement. If the SQL trace input file contained statistics from multiple users and the statement was issued by more than one user, then TKPROF
lists the ID of the last user to parse the statement. The user ID of all database users appears in the data dictionary in the column ALL_USERS
.USER_ID
.
If you specify the EXPLAIN
parameter on the TKPROF
statement line, then TKPROF
uses the EXPLAIN
PLAN
statement to generate the execution plan of each SQL statement traced. TKPROF
also displays the number of rows processed by each step of the execution plan.
See Also:
Chapter 9, "Using EXPLAIN PLAN" for more information on interpreting execution plans |
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.
See Also:
"Examples of TKPROF Statement" for examples of finding resource intensive statements |
With the exception of locking problems and inefficient PL/SQL loops, neither the CPU time nor the elapsed time is necessary to find problem statements. The key is the number of block visits, both query (that is, subject to read consistency) and current (that is, not subject to read consistency). Segment headers and blocks that are going to be updated are acquired in current mode, but all query and subquery processing requests the data in query mode. These are precisely the same measures as the instance statistics CONSISTENT
GETS
and DB
BLOCK
GETS
.
You can find high disk activity in the disk column.
The following listing shows TKPROF
output for one SQL statement as it appears in the output file:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno; call count cpu elapsed disk query current rows ---- ------- ------- --------- -------- -------- ------- ------ Parse 11 0.08 0.18 0 0 0 0 Execute 11 0.23 0.66 0 3 6 0 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.
You can also see that 10 unnecessary parse call were made (because there were 11 parse calls for this one statement) and that array fetch operations were performed. You know this because more rows were fetched than there were fetches performed. A large gap between CPU
and elapsed
timings indicates Physical I/Os (PIOs).
You might want to keep a history of the statistics generated by the SQL trace facility for an application, and compare them over time. TKPROF
can generate a SQL script that creates a table and inserts rows of statistics into it. This script contains:
CREATE
TABLE
statement that creates an output table named TKPROF_TABLE
.INSERT
statements that add rows of statistics, one for each traced SQL statement, to the TKPROF_TABLE
.After running TKPROF
, you can run this script to store the statistics in the database.
When you run TKPROF
, use the INSERT
parameter to specify the name of the generated SQL script. If you omit this parameter, then TKPROF
does not generate a script.
After TKPROF
has created the SQL script, you might want to edit the script before running it. If you have already created an output table for previously collected statistics and you want to add new statistics to this table, then remove the CREATE
TABLE
statement from the script. The script then inserts the new rows into the existing table.
If you have created multiple output tables, perhaps to store statistics from different databases in different tables, then edit the CREATE
TABLE
and INSERT
statements to change the name of the output table.
The following CREATE
TABLE
statement creates the TKPROF_TABLE
:
CREATE TABLE TKPROF_TABLE ( DATE_OF_INSERT DATE, CURSOR_NUM NUMBER, DEPTH NUMBER, USER_ID NUMBER, PARSE_CNT NUMBER, PARSE_CPU NUMBER, PARSE_ELAP NUMBER, PARSE_DISK NUMBER, PARSE_QUERY NUMBER, PARSE_CURRENT NUMBER, PARSE_MISS NUMBER, EXE_COUNT NUMBER, EXE_CPU NUMBER, EXE_ELAP NUMBER, EXE_DISK NUMBER, EXE_QUERY NUMBER, EXE_CURRENT NUMBER, EXE_MISS NUMBER, EXE_ROWS NUMBER, FETCH_COUNT NUMBER, FETCH_CPU NUMBER, FETCH_ELAP NUMBER, FETCH_DISK NUMBER, FETCH_QUERY NUMBER, FETCH_CURRENT NUMBER, FETCH_ROWS NUMBER, CLOCK_TICKS NUMBER, SQL_STATEMENT LONG);
Most output table columns correspond directly to the statistics that appear in the formatted output file. For example, the PARSE_CNT
column value corresponds to the count statistic for the parse step in the output file.
These columns help you identify a row of statistics:
The output table does not store the statement's execution plan. The following query returns the statistics from the output table. These statistics correspond to the formatted output shown in the section "Sample TKPROF Output".
SELECT * FROM TKPROF_TABLE;
Oracle responds with something similar to:
DATE_OF_INSERT CURSOR_NUM DEPTH USER_ID PARSE_CNT PARSE_CPU PARSE_ELAP -------------- ---------- ----- ------- --------- --------- ---------- 21-DEC-1998 1 0 8 1 16 22 PARSE_DISK PARSE_QUERY PARSE_CURRENT PARSE_MISS EXE_COUNT EXE_CPU ---------- ----------- ------------- ---------- --------- ------- 3 11 0 1 1 0 EXE_ELAP EXE_DISK EXE_QUERY EXE_CURRENT EXE_MISS EXE_ROWS FETCH_COUNT -------- -------- --------- ----------- -------- -------- ----------- 0 0 0 0 0 0 1 FETCH_CPU FETCH_ELAP FETCH_DISK FETCH_QUERY FETCH_CURRENT FETCH_ROWS --------- ---------- ---------- ----------- ------------- ---------- 2 20 2 2 4 10 SQL_STATEMENT --------------------------------------------------------------------- SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
This section describes some fine points of TKPROF
interpretation:
If you are not aware of the values being bound at run time, then it is possible to fall into the "argument trap". EXPLAIN
PLAN
cannot determine the type of a bind variable from the text of SQL statements, and it always assumes that the type is varchar
. If the bind variable is actually a number or a date, then TKPROF
can cause implicit data conversions, which can cause inefficient plans to be executed. To avoid this, experiment with different data types in the query.
To avoid this problem, perform the conversion yourself.
See Also:
"EXPLAIN PLAN Restrictions" for information about |
The next example illustrates the read consistency trap. Without knowing that an uncommitted transaction had made a series of updates to the NAME
column, it is very difficult to see why so many block visits would be incurred.
Cases like this are not normally repeatable: if the process were run again, it is unlikely that another transaction would interact with it in the same way.
SELECT name_id FROM cq_names WHERE name = 'FLOOR'; call count cpu elapsed disk query current rows ---- ----- --- ------- ---- ----- ------- ---- Parse 1 0.10 0.18 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.11 0.21 2 101 0 1 Misses in library cache during parse: 1 Parsing user id: 01 (USER1) Rows Execution Plan ---- --------- ---- 0 SELECT STATEMENT 1 TABLE ACCESS (BY ROWID) OF 'CQ_NAMES' 2 INDEX (RANGE SCAN) OF 'CQ_NAMES_NAME' (NON_UNIQUE)
This example shows an extreme (and thus easily detected) example of the schema trap. At first, it is difficult to see why such an apparently straightforward indexed query needs to look at so many database blocks, or why it should access any blocks at all in current mode.
SELECT name_id FROM cq_names WHERE name = 'FLOOR'; call count cpu elapsed disk query current rows -------- ------- -------- --------- ------- ------ ------- ---- Parse 1 0.06 0.10 0 0 0 0 Execute 1 0.02 0.02 0 0 0 0 Fetch 1 0.23 0.30 31 31 3 1 Misses in library cache during parse: 0 Parsing user id: 02 (USER2) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT 2340 TABLE ACCESS (BY ROWID) OF 'CQ_NAMES' 0 INDEX (RANGE SCAN) OF 'CQ_NAMES_NAME' (NON-UNIQUE)
Two statistics suggest that the query might have been executed with a full table scan. These statistics are the current mode block visits, plus the number of rows originating from the Table Access row source in the execution plan. The explanation is that the required index was built after the trace file had been produced, but before TKPROF
had been run.
Generating a new trace file gives the following data:
SELECT name_id
FROM cq_names
WHERE name = 'FLOOR';
call count cpu elapsed disk query current rows
----- ------ ------ -------- ----- ------ ------- -----
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Parsing user id: 02 (USER2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT
1 TABLE ACCESS (BY ROWID) OF 'CQ_NAMES'
2 INDEX (RANGE SCAN) OF 'CQ_NAMES_NAME' (NON-UNIQUE)
One of the marked features of this correct version is that the parse call took 10 milliseconds of CPU time and 20 milliseconds of elapsed time, but the query apparently took no time at all to execute and perform the fetch. These anomalies arise because the clock tick of 10 milliseconds is too long relative to the time taken to execute and fetch the data. In such cases, it is important to get lots of executions of the statements, so that you have statistically valid numbers.
Sometimes, as in the following example, you might wonder why a particular query has taken so long.
UPDATE cq_names SET ATTRIBUTES = lower(ATTRIBUTES) WHERE ATTRIBUTES = :att call count cpu elapsed disk query current rows -------- ------- -------- --------- -------- -------- ------- ---------- Parse 1 0.06 0.24 0 0 0 0 Execute 1 0.62 19.62 22 526 12 7 Fetch 0 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Parsing user id: 02 (USER2) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT 2519 TABLE ACCESS (FULL) OF 'CQ_NAMES'
Again, the answer is interference from another transaction. In this case, another transaction held a shared lock on the table cq_names
for several seconds before and after the update was issued. It takes a fair amount of experience to diagnose that interference effects are occurring. On the one hand, comparative data is essential when the interference is contributing only a short delay (or a small increase in block visits in the previous example). On the other hand, if the interference is contributing only a modest overhead, and the statement is essentially efficient, then its statistics might not need to be analyzed.
The resources reported for a statement include those for all of the SQL issued while the statement was being processed. Therefore, they include any resources used within a trigger, along with the resources used by any other recursive SQL (such as that used in space allocation). With the SQL trace facility enabled, TKPROF
reports these resources twice. Avoid trying to tune the DML statement if the resource is actually being consumed at a lower level of recursion.
If a DML statement appears to be consuming far more resources than you would expect, then check the tables involved in the statement for triggers and constraints that could be greatly increasing the resource usage.
This section provides an extensive example of TKPROF
output. Portions have been edited out for the sake of brevity.
Copyright (c) Oracle Corporation 1979, 1999. All rights reserved. Trace file: v80_ora_2758.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** The following statement encountered a error during parse: select deptno, avg(sal) from emp e group by deptno having exists (select deptno from dept where dept.deptno = e.deptno and dept.budget > avg(e.sal)) order by 1 Error encountered: ORA-00904 ********************************************************************************
ALTER SESSION SET SQL_TRACE = true call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.10 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.10 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: CHOOSE Parsing user id: 02 (USER02) ******************************************************************************** SELECT emp.ename, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.11 0.13 2 0 1 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 2 2 4 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.11 0.13 4 2 5 14 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 02 (USER02) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 14 MERGE JOIN 4 SORT (JOIN) 4 TABLE ACCESS (FULL) OF 'DEPT' 14 SORT (JOIN) 14 TABLE ACCESS (FULL) OF 'EMP' ******************************************************************************** SELECT a.ename name, b.ename manager FROM emp a, emp b WHERE a.mgr = b.empno(+) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.01 0.01 1 50 2 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.02 0.02 1 50 2 14 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 01 (USER01) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 13 NESTED LOOPS (OUTER) 14 TABLE ACCESS (FULL) OF 'EMP' 13 TABLE ACCESS (BY ROWID) OF 'EMP' 26 INDEX (RANGE SCAN) OF 'EMP_IND' (NON-UNIQUE) ******************************************************************************** SELECT ename, job, sal FROM emp WHERE sal = (SELECT max(sal) FROM emp) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 12 4 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 12 4 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 01 (USER01) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 14 FILTER 14 TABLE ACCESS (FULL) OF 'EMP' 14 SORT (AGGREGATE) 14 TABLE ACCESS (FULL) OF 'EMP' ******************************************************************************** SELECT deptno FROM emp WHERE job = 'clerk' GROUP BY deptno HAVING COUNT(*) >= 2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 1 1 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 1 0 Misses in library cache during parse: 13 Optimizer goal: CHOOSE Parsing user id: 01 (USER01) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 FILTER 0 SORT (GROUP BY) 14 TABLE ACCESS (FULL) OF 'EMP' ******************************************************************************** SELECT dept.deptno, dname, job, ename FROM dept,emp WHERE dept.deptno = emp.deptno(+) ORDER BY dept.deptno call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 3 3 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 3 3 10 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 01 (USER01) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 14 MERGE JOIN (OUTER) 4 SORT (JOIN) 4 TABLE ACCESS (FULL) OF 'DEPT' 14 SORT (JOIN) 14 TABLE ACCESS (FULL) OF 'EMP' ******************************************************************************** SELECT grade, job, ename, sal FROM emp, salgrade WHERE sal BETWEEN losal AND hisal ORDER BY grade, job call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.04 0.06 2 16 1 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.01 0.01 1 10 12 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.05 0.07 3 26 13 10 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 02 (USER02) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 14 SORT (ORDER BY) 14 NESTED LOOPS 5 TABLE ACCESS (FULL) OF 'SALGRADE' 70 TABLE ACCESS (FULL) OF 'EMP' ******************************************************************************** SELECT LPAD(' ',level*2)||ename org_chart, level, empno, mgr, job, deptno FROM emp CONNECT BY prior empno = mgr START WITH ename = 'clark' OR ename = 'blake' ORDER BY deptno call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.01 0.01 0 1 2 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.02 0.02 0 1 2 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 02 (USER02) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (ORDER BY) 0 CONNECT BY 14 TABLE ACCESS (FULL) OF 'EMP' 0 TABLE ACCESS (BY ROWID) OF 'EMP' 0 TABLE ACCESS (FULL) OF 'EMP' ******************************************************************************** CREATE TABLE TKOPTKP (a number, b number) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 1 0 1 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 1 0 1 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 02 (USER02) Rows Execution Plan ------- --------------------------------------------------- 0 CREATE TABLE STATEMENT GOAL: CHOOSE ******************************************************************************** INSERT INTO TKOPTKP VALUES (1,1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.07 0.09 0 0 0 0 Execute 1 0.01 0.20 2 2 3 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.08 0.29 2 2 3 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 02 (USER02) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE ******************************************************************************** INSERT INTO TKOPTKP SELECT * FROM TKOPTKP call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.02 0.02 0 2 3 11 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 0.02 0 2 3 11 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 02 (USER02) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE 12 TABLE ACCESS (FULL) OF 'TKOPTKP' ******************************************************************************** SELECT * FROM TKOPTKP WHERE a > 2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 1 2 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.01 0 1 2 10 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 02 (USER02) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 24 TABLE ACCESS (FULL) OF 'TKOPTKP' ********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 18 0.40 0.53 30 182 3 0 Execute 19 0.05 0.41 3 7 10 16 Fetch 12 0.05 0.06 4 105 66 78 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 49 0.50 1.00 37 294 79 94 Misses in library cache during parse: 18 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 69 0.49 0.60 9 12 8 0 Execute 103 0.13 0.54 0 0 0 0 Fetch 213 0.12 0.27 40 435 0 162 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 385 0.74 1.41 49 447 8 162 Misses in library cache during parse: 13 19 user SQL statements in session. 69 internal SQL statements in session. 88 SQL statements in session. 17 statements EXPLAINed in this session. ******************************************************************************** Trace file: v80_ora_2758.trc Trace file compatibility: 7.03.02 Sort options: default 1 session in tracefile. 19 user SQL statements in trace file. 69 internal SQL statements in trace file. 88 SQL statements in trace file. 41 unique SQL statements in trace file. 17 SQL statements EXPLAINed using schema: SCOTT.prof$plan_table Default table was used. Table was created. Table was dropped. 1017 lines in trace file.
|
![]() Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|