使用SQL TRACE和TKPROF观察SQL语句执行结果

SQL TRACE文件存放于
show parameter user_dump下

文件名为_ora_.trc。

默认情况下，用户SQL不会写入TRACE文件，可以在会话级别更改相关参数

ALTER SESSION SET SQL_TRACE=TRUE;

要查询当前会话的进程ID：

点击(此处)折叠或打开

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

SPID                            SID    SERIAL# USERNAME
------------------------ ---------- ---------- ------------------------------
PROGRAM
--------------------------------------------------------------------------------
1711                            145          2 oracle
oracle@ocp.demo.com (TNS V1-V3)

如上例，当前会话的TRACE文件名就应为：user_dump_dest目录下的OCP_ORCL_1711.trc。

还有一个更简便的方法：

点击(此处)折叠或打开

1. SQL> select value from v$diag_info where name='Default Trace File';
   
2. 
   
3. VALUE
   
4. --------------------------------------------------------------------------------
   
5. /u01/app/oracle/diag/rdbms/apr/apr/trace/apr_ora_4734.trc

---

TKPROF是用来将TRACE文件转换成可以阅读的格式：

SHELL$ tkprof ocp_ora_1711.trc mike.prf explain=user/password

读取mike.prf即可。

---

观察mike.prf的输出：

********************************************************************************


select count(id) 
from
 bom




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      1.67       1.63      72061      72093          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.68       1.64      72061      72095          0           1


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  (HK)


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=72093 pr=72061 pw=0 time=1639929 us)
33011000   INDEX FAST FULL SCAN BOM_PK_ID (cr=72093 pr=72061 pw=0 time=84 us)(object id 52507)




Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
33011000    INDEX   MODE: ANALYZED (FAST FULL SCAN) OF 'BOM_PK_ID' (INDEX 
               (UNIQUE))


********************************************************************************

Disk表示读取的磁盘统计，Query表示读取的内存统计。

以下是部分问题和解决措施的汇总：

The parsing numbers are high	The SHARED_POOL_SIZE may need to be increased
The disk reads are very high	Indexes are not being used or may not exist
The query and/or current (memory reads) are very high	Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.
The parse elapse time is high	There may be a problem with the number of open cursors
The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows.	This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.
The number of misses in the library cache during parse is greater than 1.	This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.

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

转载于:http://blog.itpub.net/22621861/viewspace-1279733/