Using Sql_trace to track SQL execution in Oracle

When you execute a SQL statement very slowly, do you want to ask Oracle how to execute this statement?
Oracle provides sql_trace tools to let you know exactly what SQL you are doing. The process of execution is
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 to the generated trace file is the $oracle_base/admin/sid/udump directory.
The above statement is to let the production trace file name include MySession, as in this example
D:\oracle\product\10.2.0\admin\orcl\udump generated the ORCL_ORA_5732_MYSESSION.TRC
Sql> alter session set Sql_trace=true;
Session altered.
Sql> select * from t where id=123;
ID object_name
123 I_access1
Sql> alter session set Sql_trace=false;
Session altered.
In general, the generated trace file is more difficult to read, and you can use TKPROF to generate a more readable file.
Note Tkprof is a command-line tool for the Oracle band, not the Sqlplus command.
Enter the D:\oracle\product\10.2.0\admin\orcl\udump directory on another command line
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, 2005, Oracle. All rights reserved.
Open the Orcl_ora_5732_mysession.txt file to see the execution SQL information:
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)
********************************************************************************
For each SQL statement, there are 3 steps:
The analysis phase of the parse:sql.
The implementation phase of the Execute:sql.
Fetch: The data extraction phase. (Fetch may be multiple times for a single SQL operation)
The horizontal header header is:
Count: The number of times the current operation was executed.
CPU: The CPU time (in seconds) that is consumed by the current operation.
Elapsed: The time consumed by the current operation (CPU time plus wait time).
Disk: The number of iOS disks.
Query: The number of data blocks for which the current operation is consistent read.
Current: The number of blocks of current read by current before operation (typically occurs during an update operation).
Rows: Number of record rows processed.

Misses in library cache during parse refers to whether the execution plan is reused, and if the same SQL statement executes the second time,
Its value is zero, here is 1, indicating that there is no execution plan for this SQL in the shared pool, and a hard parse occurred.
Optimizer: Optimizer mode.
Parsing User id: the parsed userid.
The Row Source Operation section contains the resources that are actually consumed.
CR: a data block of consistent reads, equivalent to a fetch query.
PR: Physical reading, equivalent to fetch disk.
PW: Physical Write.
Time: The execution times of the current operation.

At the same time we also find SQL for accessing system tables in the trace file, which is usually called recursive SQL.

You can also use the 10046 event to track SQL, which provides more detailed information than sql_trace. It has level 1,4,8,12 four levels.
Where level 1 is equivalent to Sql_trace. Here are 10046 examples of use
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.

Using Sql_trace to track SQL execution in Oracle