ITPub博客

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

原创 Oracle 作者:hooca 时间:2014-09-24 18:31:15 0 删除 编辑
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. VALUE
  3. --------------------------------------------------------------------------------
  4. /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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-29

  • 博文量
    206
  • 访问量
    367579