胡恺的个人空间

好奇心和完美主义是DBA最大的两个敌人!

  • 博客访问: 340878
  • 博文数量: 206
  • 用 户 组: 普通用户
  • 注册时间: 2009-09-29 15:06
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(206)

文章存档

2016年(77)

2015年(52)

2014年(77)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

分类: Oracle

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.

阅读(491) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册