SQL_TRACE文件

---sql_trace文件分为两类
     会话级别：alter session set sql_trace=true;
     实例级：alter database set sql_trace=true;
----测试 生成trace文件

1. SQL> alter session set tracefile_identifier='my_trace';
   
2. 
   
3. Session altered.
   
4. 
   
5. SQL> alter session set sql_trace=true;
   
6. 
   
7. Session altered.
   
8. 
   
9. SQL> select count(*) from hr.employees where job_id='SA_REP';
   
10. 
    
11.   COUNT(*)
    
12. ----------
    
13.         30
    
14. 
    
15. SQL> alter session set sql_trace=false;
    
16. 
    
17. Session altered.

---查找trace文件

1. SQL> show parameter background_dump_dest
   
2. 
   
3. NAME TYPE VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. background_dump_dest string /u01/app/oracle/diag/rdbms/ora
   
6.                                                  /ORA/trace
   
7. SQL>

---查看生成的trace文件

1. [oracle@zh trace]$ ls *my_trace*trc
   
2. ORA_ora_21406_my_trace.trc
   
3. [oracle@zh trace]$

---需要用tkprof工具格式化trace文件

1. [oracle@zh trace]$ ls *my_trace*trc
   
2. ORA_ora_21406_my_trace.trc
   
3. [oracle@zh trace]$ tkprof ORA_ora_21406_my_trace.trc asdf.txt
   
4. 
   
5. TKPROF: Release 11.2.0.4.0 - Development on Wed Dec 21 11:15:12 2016
   
6. 
   
7. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

---查看格式化后的trace文件

1. [oracle@zh trace]$ ls asdf.txt
   
2. asdf.txt
   
3. [oracle@zh trace]$ more asdf.txt
   
4. ********************************************************************************
   
5. 
   
6. SQL ID: fa48b614fg9d1 Plan Hash: 128828553
   
7. 
   
8. select count(*)
   
9. from
   
10.  hr.employees where job_id='SA_REP'
    
11. 
    
12. 
    
13. call count cpu elapsed disk query current rows
    
14. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    
15. Parse 1 0.01 0.01 0 0 0 0
    
16. Execute 1 0.00 0.00 0 0 0 0
    
17. Fetch 2 0.00 0.00 1 1 0 1
    
18. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    
19. total 4 0.01 0.02 1 1 0 1
    
20. 
    
21. Misses in library cache during parse: 1
    
22. Optimizer mode: ALL_ROWS
    
23. Parsing user id: SYS
    
24. Number of plan statistics captured: 1
    
25. 
    
26. Rows (1st) Rows (avg) Rows (max) Row Source Operation
    
27. ---------- ---------- ---------- ------------------------------------------------
    
28. ---
    
29.          1 1 1 SORT AGGREGATE (cr=1 pr=1 pw=0 time=6561 us)
    
30.         30 30 30 INDEX RANGE SCAN EMP_JOB_IX (cr=1 pr=1 pw=0 tim
    
31. e=6531 us cost=1 size=270 card=30)(object id 88139)
    
32. 
    
33. ********************************************************************************

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31399171/viewspace-2131172/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31399171/viewspace-2131172/