ORACLE sql trace跟踪

1.查看sql_trace开启状态

SQL> show parameter sql_trace
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
sql_trace			     boolean	 FALSE

2.开启sql_trace

SQL> alter session set sql_trace=true;
Session altered.

3.执行查询语句测试

SQL> select * from zhangyun.t1 where rownum<10;

OWNER			       OBJECT_NAME											       SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE	      CREATED	LAST_DDL_ TIMESTAMP	      STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS			       ICOL$
	20		2 TABLE 	      24-AUG-13 24-AUG-13 2013-08-24:11:37:35 VALID   N N N	     1

SYS			       I_USER1
	46	       46 INDEX 	      24-AUG-13 24-AUG-13 2013-08-24:11:37:35 VALID   N N N	     4

SYS			       CON$
	28	       28 TABLE 	      24-AUG-13 24-AUG-13 2013-08-24:11:37:35 VALID   N N N	     1

4.关闭sql_trace

SQL> alter session set sql_trace=false;
Session altered.

5.查看当前进程id

SQL> SELECT P.SPID, S.SERIAL#, P.USERNAME, P.PROGRAM
  FROM V$SESSION S, V$PROCESS P
 WHERE S.PADDR = P.ADDR
   AND S.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);  2    3    4  

SPID			    SERIAL# USERNAME	    PROGRAM
------------------------ ---------- --------------- ------------------------------------------------
4389				  5 oracle	    oracle@host01.example.com (TNS V1-V3)

6.查看trace log目录

SQL> select name,value from v$diag_info;

NAME		     VALUE
-------------------- ------------------------------------------------------------
Diag Enabled	     TRUE
ADR Base	     /u01/app/oracle
ADR Home	     /u01/app/oracle/diag/rdbms/db11g/DB11G
Diag Trace	     /u01/app/oracle/diag/rdbms/db11g/DB11G/trace
Diag Alert	     /u01/app/oracle/diag/rdbms/db11g/DB11G/alert
Diag Incident	     /u01/app/oracle/diag/rdbms/db11g/DB11G/incident
Diag Cdump	     /u01/app/oracle/diag/rdbms/db11g/DB11G/cdump
Health Monitor	     /u01/app/oracle/diag/rdbms/db11g/DB11G/hm
Default Trace File   /u01/app/oracle/diag/rdbms/db11g/DB11G/trace/DB11G_ora_4389.
		     trc

7.进入trace log目录，用tkprof工具转换

[oracle@host01 trace]$ ls -lrt
-rw-r----- 1 oracle oinstall    287 Feb 10 15:17 DB11G_ora_4389.trm
-rw-r----- 1 oracle oinstall  11650 Feb 10 15:17 DB11G_ora_4389.trc

[oracle@host01 trace]$ tkprof DB11G_ora_4389.trc 2023.prf explain=system/oracle
TKPROF: Release 11.2.0.4.0 - Development on Fri Feb 10 15:21:07 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

8.查看转换后的内容

可以看到刚刚查询测试的语句，走的物理读次数，查询以及行数等

[oracle@host01 trace]$ more 2023.prf 
TKPROF: Release 11.2.0.4.0 - Development on Fri Feb 10 15:21:07 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Trace file: DB11G_ora_4389.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
********************************************************************************
SQL ID: 2ktg0hvu8bk7z Plan Hash: 3836375644
select * 
from
 zhangyun.t1 where rownum<10
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00         28          5          0           9
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00         28          7          0           9

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         9          9          9  COUNT STOPKEY (cr=5 pr=28 pw=0 time=206 us)
         9          9          9   TABLE ACCESS FULL T1 (cr=5 pr=28 pw=0 time=204 us cost=3 size=23042619 card=111317)
********************************************************************************