查詢效率低下的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/，如需轉載，請註明出處，否則將追究法律責任。