查询效率低下的sql的语句

       想要准确地测试sql语句的执行时间和统计数据的值（db block gets、consistents gets、physicas gets），也就是语句的每次执行都和第一次执行时处于基本相同的测试环境，需执行如下语句：

sql>alter system flush shared_pool;
sql>alter session set events 'immediate trace name flush_cache level 1';

从V$SQLAREA中查询最占用资源的查询
select b.username username,a.disk_reads reads,
    a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
    a.sql_text Statement
from  v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
 and a.disk_reads > 100000
order by a.disk_reads desc;

这里是用disk_reads的值来判断的，这里的disk_reads相当于在命令行里设置autotrace on执行结果的统计信息的physicas gets的值。

找出需要大量缓冲读取（逻辑读）操作的查询：
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
   dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
   from v$sql)
where buffer_gets_rank<=5;

这里是用buffer_gets 的值来判断的，这里的buffer_gets 相当于在命令行里设置autotrace on执行结果的统计信息的consistents  gets的值。

2、查找存储过程中效率地下的sql。

经常会遇到这种情况，一个存过运行非常慢，但里面的逻辑很复杂，sql语句非常多，很难得知道是哪些sql语句导致的性能低下，这时候可以用下面的方法跟踪：

sql>alter session set sql_trace=true;--开启sql_trace跟踪的功能。

sql> alter session set tracefile_identifier='yourName';--设置生成跟踪文件的文件标识符。

sql>exec procName;--执行存过。

sql>alter session set sql_trace=false;--关闭sql_trace跟踪功能。

获取生成的trc文件

sql>SELECT   d.VALUE

 2       || '/'

 3       || LOWER (RTRIM (i.INSTANCE, CHR (0)))

 4       || '_ora_'

 5       || p.spid

 6       || '.trc' as "trace_file_name"

 7   FROM (SELECT p.spid

 8           FROM v$mystat m, v$session s, v$process p

 9         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

 10       (SELECT t.INSTANCE

 11           FROM v$thread t, v$parameter v

 12         WHERE v.NAME = 'thread'

 13           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

 14       (SELECT VALUE

 15           FROM v$parameter

 16         WHERE NAME = 'user_dump_dest') d;

生产的trc文件名就是上面设置alter session set tracefile_identifier='yourName' 的标志名。

 

分析trc文件：

到user_dump_dest定义的路径下查找刚刚最近生成的trace文件，可以根据时间来排序，找最近的trace文件，也可以根据SID_ORA_SPID.TRC的规则，即ORCL_ORA_14483.TRC找到TRACE文件。

接着使用tkprof工具对此trace文件进行格式化分析，生成分析后的trace文件。
$tkprof orcl_ora_14483.trc allan.txt explain=system/manager aggregate=yes sys=no waits=yes sort=fchela

TKPROF: Release 11.2.0.1.0 - Development on 星期五 5月 28 16:48:49 2010

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

这里生成的allan.txt文件就是我们最终得到的格式化后的trace文件了，然后打开这个文件进行分析，在这个文件里就可以看到本次会话产生的所有的sql的统计信息，很方便的就可以找出性能低下的sql了。
最后总的统计：
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call    count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------   --------
Parse     20      0.01       0.02          0         58         0           0
Execute 13197     0.81       0.90         17       7436     6316          1484
Fetch   12944    22.86      22.10         20    2205941        0          8972
------- ------  -------- ---------- ---------- ---------- ---------- --------
total    26161     23.68     23.02         37    2213435     6316       10456

 

 

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

转载于:http://blog.itpub.net/20577218/viewspace-704015/