sql trace工具

sql trace工具

1）sql跟踪运行查询并输出所执行的一个oracle查询（或一系列查询）的统计信息
2）sql跟踪开发人员分析查询的每一个部分
通常情况下，oracle跟踪在跟踪文件中记录了数据库的所有活动（特别是查询），跟踪文件由oracle sql跟踪生成，需要使用tkprof把它转换成容易阅读的格式

对简单查询使用sql跟踪的步骤

create pfile='/home/oracle/pfile' from spfile;
alter system set  timed_statistics=true;
alter system set max_dump_file_size=unlimited;

timed_statistics：允许在系统中执行跟踪操作
max_dump_file_size：指定跟踪文件的大小，如果超过指定大小，则后面的跟踪记录将不被记录。
可以使用alter session 命令对会话单独开启sql跟踪，在11.2版本之后，使用参数diagnostic_dest参数的值指定

SQL> select value from v$diag_info where name='Diag Trace';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

案例：

1） 会话级别启用sql跟踪
SQL> alter session set sql_trace true;

2）运行要查询的语句
SQL> select * from scott.emp;

3）对会话停止跟踪
SQL> alter session set sql_trace false;

运行sql trace后输出这样的文件
-rw-r----- 1 oracle asmadmin 30939 Jun 22 03:29 orcl_ora_24172.trc

#要诀

1）在参数文件中加timed_statistics=true 可以启用收集时间统计

给予用户查看特定视图的查询权限

SQL> grant select on v_$process to scott;
SQL> grant select on v_$session to scott;
SQL> grant select on v_$mystat to scott;

set linesize 400
col program for a40
select spid,s.sid,s.serial#,p.username,p.program from v$process p,v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1);

SPID				SID    SERIAL# USERNAME        PROGRAM
------------------------ ---------- ---------- --------------- ----------------------------------------
5274				 22	 61076 grid	       oracle@ora2


[oracle@ora2 trace]$ ll
-rw-r----- 1 oracle asmadmin 7389 Jun 24 04:25 orcl_ora_5274.trc
-rw-r----- 1 oracle asmadmin 2268 Jun 24 04:25 orcl_ora_5274.trm

[oracle@ora2 trace]$ 
[oracle@ora2 trace]$ tkprof orcl_ora_5274.trc rich1.prf explain=system/oracle
TKPROF: Release 19.0.0.0.0 - Development on Sat Jun 24 04:28:36 2023
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

[oracle@ora2 trace]$ ll
-rw-r--r-- 1 oracle oinstall 7373 Jun 24 04:28 rich1.prf
[oracle@ora2 trace]$ cat rich1.prf 

TKPROF: Release 19.0.0.0.0 - Development on Sat Jun 24 04:28:36 2023

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl_ora_5274.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: ggqns3c1jz86c Plan Hash: 3956160932

select * 
from
 scott.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        2      0.00       0.00          0          7          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0          14

运行tkprof并列出消耗cpu前5的（数据提取+执行+解析）结果

[oracle@ora2 trace]$ tkprof orcl_ora_5274.trc rich2.prf explain=system/oracle sort=FCHCPU,EXECPU,PRSCPU print=5
[oracle@ora2 trace]$ cat rich2.prf 

TKPROF: Release 19.0.0.0.0 - Development on Sat Jun 24 05:02:29 2023

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl_ora_5274.trc
Sort options: fchcpu  execpu  prscpu  
********************************************************************************
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: g8307m98rjqpt Plan Hash: 529676139

select spid,s.sid,s.serial#,p.username,p.program 
from
 v$process p,v$session s where p.addr = s.paddr and s.sid=(select sid from 
  v$mystat where rownum=1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0          0          0           1

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
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  NESTED LOOPS  (cr=0 pr=0 pw=0 time=1058 us starts=1 cost=0 size=68 card=1)
         1          1          1   HASH JOIN  (cr=0