Use SQL _TRACE in Oracle to track SQL Execution

In Oracle, use SQL _TRACE to trace SQL Execution. When you execute 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 statements you execute are. the execution process is output to the trace file. the following example uses www.2cto.com 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, orcl_ora_5732_mysession.trcSQL> alter session set SQL _trace = true is generated by D: \ oracle \ product \ 10.2.0 \ admin \ orcl \ udump; session altered. www.2cto.com SQL> select * from t where id = 123; ID OBJECT_NAME123 I _ACCESS1SQL> 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 restart: 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 * www.2cto.com from t where id = 123 call count cpu elapsed disk query current rows ------- ------ -------- ---------- Parse 1 0.04 0.30 0 2 0 Execute 1 0.00 0 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 www.2cto.com 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, it contains three steps: Parse: SQL analysis stage. execute: SQL Execution stage. fetch: data extraction stage. (For an SQL operation, Fetch may be performed multiple times) the header of the horizontal title 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. www.2cto.com Misses in library cache during parse indicates whether the execution plan is reused. If the same SQL statement is executed for the second time, its value is zero. Here it is 1, it indicates 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 SQL> alter session set events '2017 trace name context forever, level 4'; Session altered. www.2cto.com .... some SQL statementsSQL> alter session set events '10046 trace name context off'; Session altered.