Use SQL _TRACE in Oracle to track SQL Execution

When you are running an SQL statement very slowly, do you want to ask Oracle how to execute this statement? The SQL _TRACE tool provided by Oracle allows you to know exactly what the SQL statement you run is. The execution process will be output to the trace file. The following example is used to track the execution of an SQL statement: SQLcreatetabletasselectro

When you are running an SQL statement very slowly, do you want to ask Oracle how to execute this statement? The SQL _TRACE tool provided by Oracle allows you to know exactly what the SQL statement you run is. The execution process will be output to the trace file. The following example is used to track the execution of an SQL statement: SQL create table t as select ro

When you are running an SQL statement very slowly, do you want to ask Oracle how to execute this statement?
The SQL _TRACE tool provided by Oracle allows you to know exactly what the SQL statement you run is. The execution process will be output to the trace file.

The following example is used to track the execution of an SQL statement:

SQL> create table t as select rownum as id, object_name from dba_objects;


Table created.
SQL> create index t_ind on t (id );
Index created.
SQL> alter session set tracefile_identifier = 'mysession ';
Session altered.
The path of the generated trace file is the $ Oracle_BASE/admin/SID/udump directory.
The preceding statement allows the production trace file name to include mysession. In this example

D: \ Oracle \ product \ 10.2.0 \ admin \ orcl \ udump generates orcl_ora_5732_mysession.trc
SQL> alter session set SQL _trace = true;
Session altered.
SQL & gt; select * from t where id = 123;
ID OBJECT_NAME
123 I _ACCESS1
SQL> alter session set SQL _trace = false;
Session altered.

Generally, the generated trace file is hard to read. You can use tkprof to generate a readable file.
Note that tkprof is a command line tool in Oracle, not a SQLPLUS command.

In another command line, enter the D: \ Oracle \ product \ 10.2.0 \ admin \ orcl \ udump directory.
D: \ Oracle \ product \ 10.2.0 \ admin \ orcl \ udump> tkprof orcl_ora_5732_mysession.trc orcl_ora_5732_mysession.txt
TKPROF: Release 10.2.0.1.0-Production on Fri Sep 14 16:59:12 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.

Open the orcl_ora_5732_mysession.txt file and you can see the information for executing the SQL statement:
Select *
From
T where id = 123
Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.04 0.30 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
-----------------------------------------------------------------------
Total 4 0.04 0.30 0 6 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
----------------------------------------------------------
1 table access by index rowid t (cr = 4 pr = 0 pw = 0 time = 86 us)
1 index range scan T_IND (cr = 3 pr = 0 pw = 0 time = 67 us) (object id 57205)

Each SQL statement contains three steps:
Parse: SQL analysis stage.
Execute: SQL Execution stage.
Fetch: The data extraction phase. (For one SQL operation, Fetch may be performed multiple times)
The horizontal title header is:
Ount: the number of times the current operation is executed.
Cpu: the CPU time (in seconds) consumed by the current operation ).
Elapsed: the time consumed by the current operation (CPU time plus wait time ).
Disk: the number of disk I/O operations.
Query: the number of data blocks read consistently for the current operation.
Current: number of data blocks read by current in the previous operation (usually in the update operation ).
Rows: number of records processed.

Misses in library cache during parse indicates whether the execution plan is reused. If the same SQL statement is executed for the second time,
The value is zero. Here is 1, indicating that there is no execution plan for this SQL statement in the shared pool, and a hard parsing occurs.
Optimizer: Optimizer mode.
Parsing user id: analyzed user id.
The Row Source Operation part contains the actually consumed resources.
Cr: consistent read data block, equivalent to the query of Fetch.
Pr: Physical read, equivalent to the disk of Fetch.
Pw: Physical write.
Time: the execution time of the current operation.

At the same time, we will find SQL statements for accessing system tables in the trace file, which is commonly referred to as recursive SQL.

You can also use the 10046 event to track SQL, which provides more detailed information than SQL _TRACE. It has four levels: LEVEL 1, 4, 8, and 12.
Level 1 is equivalent to SQL _TRACE. The following is an example of 10046.
SQL> alter session set events '10046 trace name context forever, level 4 ';
Session altered.
... Some SQL statements
SQL> alter session set events '10046 trace name context off ';
Session altered.