本篇內(nèi)容介紹了“AWR TOP SQL實(shí)現(xiàn)方法是什么”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

為石峰等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及石峰網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、石峰網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎn)！

1 按解析次數(shù)排序

select a.*,
       to_char(substr(b.sql_text,1,4000))
from
    (select dhs.sql_id,
       sum(parse_calls_delta) parse,
       sum(executions_delta) exec_nums,
       dhs.MODULE
    from dba_hist_sqlstat dhs
    where
        snap_id  > 22438
        and snap_id <= 22440
    group by dhs.sql_id,MODULE) a,
    dba_hist_sqltext b
    where a.sql_id=b.sql_id order by a.parse desc;

2 按執(zhí)行時(shí)間排序

select a.*,
       to_char(substr(b.sql_text,1,4000))
from
    (select dhs.sql_id,
       round(sum(elapsed_time_delta)/1000/1000,2) "elapsed_time(s)",
       sum(executions_delta) execs,
       round(sum(elapsed_time_delta)/1000/1000/sum(executions_delta),2)  elapsed_time_per,
       dhs.MODULE
    from dba_hist_sqlstat dhs
    where
        snap_id  > 22438
        and snap_id <= 22440
    group by dhs.sql_id,MODULE) a,
    dba_hist_sqltext b
    where a.sql_id=b.sql_id order by a."elapsed_time(s)" desc;

3 按CPU時(shí)間排序

select a.*,
       to_char(substr(b.sql_text,1,4000))
from
    (select dhs.sql_id,
       round(sum(cpu_time_delta)/1000/1000,2) "cpu_time",
       sum(executions_delta) execs,
       round(sum(cpu_time_delta)/1000/1000/sum(executions_delta),2)  cpu_time_per,
        round(sum(elapsed_time_delta)/1000/1000,2) "elapsed_time(s)",
       dhs.MODULE
    from dba_hist_sqlstat dhs
    where
        snap_id  > 22438
        and snap_id <= 22440
    group by dhs.sql_id,MODULE) a,
    dba_hist_sqltext b
    where a.sql_id=b.sql_id order by a."cpu_time" desc;

4 按User I/O wait排序

select a.*,
       to_char(substr(b.sql_text,1,4000))
from
    (select dhs.sql_id,
       round(sum(iowait_delta)/1000/1000,2) "iowait_time(s)",
       sum(executions_delta) execs,
       round(sum(iowait_delta)/1000/1000/sum(executions_delta),2)  iowait_time_per,
        round(sum(elapsed_time_delta)/1000/1000,2) "elapsed_time(s)",
       dhs.MODULE
    from dba_hist_sqlstat dhs
    where
        snap_id  > 22438
        and snap_id <= 22440
    group by dhs.sql_id,MODULE) a,
    dba_hist_sqltext b
    where a.sql_id=b.sql_id order by a."iowait_time(s)" desc;

5 按邏輯讀(gets)排序

select a.*,
       to_char(substr(b.sql_text,1,4000))
from
    (select dhs.sql_id,
       round(sum(buffer_gets_delta),2) "buffer_ges",
       sum(executions_delta) execs,
       round(sum(buffer_gets_delta)/sum(executions_delta),2)  iowait_time_per,
        round(sum(elapsed_time_delta)/1000/1000,2) "elapsed_time(s)",
       dhs.MODULE
    from dba_hist_sqlstat dhs
    where
        snap_id  > 22438
        and snap_id <= 22440
    group by dhs.sql_id,MODULE) a,
    dba_hist_sqltext b
    where a.sql_id=b.sql_id order by a."buffer_ges" desc;

7 按物理讀(physical read)排序

select a.*,
       to_char(substr(b.sql_text,1,4000))
from
    (select dhs.sql_id,
       round(sum(DISK_READS_DELTA),2) "physical_read",
       sum(executions_delta) execs,
       round(sum(DISK_READS_DELTA)/sum(executions_delta),2)  iowait_time_per,
        round(sum(elapsed_time_delta)/1000/1000,2) "elapsed_time(s)",
       dhs.MODULE
    from dba_hist_sqlstat dhs
    where
        snap_id  > 22438
        and snap_id <= 22440
    group by dhs.sql_id,MODULE) a,
    dba_hist_sqltext b
    where a.sql_id=b.sql_id order by a."physical_read" desc;

8 按執(zhí)行次數(shù)排序

select a.*,
       to_char(substr(b.sql_text,1,4000))
from
    (select dhs.sql_id,
       round(sum(executions_delta),2) "exec_num",
       sum(ROWS_PROCESSED_DELTA) row_process,
       round(sum(ROWS_PROCESSED_DELTA)/sum(executions_delta),2)  rows_per_exec,
        round(sum(elapsed_time_delta)/1000/1000,2) "elapsed_time(s)",
       dhs.MODULE
    from dba_hist_sqlstat dhs
    where
        snap_id  > 22438
        and snap_id <= 22440
    group by dhs.sql_id,MODULE) a,
    dba_hist_sqltext b
    where a.sql_id=b.sql_id order by a."exec_num" desc;

“AWR TOP SQL實(shí)現(xiàn)方法是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)-成都網(wǎng)站建設(shè)公司網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

當(dāng)前名稱：AWRTOPSQL實(shí)現(xiàn)方法是什么-創(chuàng)新互聯(lián)
標(biāo)題鏈接：http://aaarwkj.com/article36/pgisg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護(hù)、關(guān)鍵詞優(yōu)化、軟件開發(fā)、網(wǎng)站改版、網(wǎng)頁設(shè)計(jì)公司、響應(yīng)式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)