パフォーマンス系

1実行当たりの実行時間が長いSQL

SET LINES 300
set pages 100
COL SQL_TXT        FORM A48
COL buffer_per_run  FORM 999,999,999,999
COL disk_per_run    FORM 999,999,999,999
COL cpu__per_run_S    FORM 999,999.999
COL elp_per_run_S     FORM 999,999.999
SELECT * FROM
(SELECT
substr(sql_text,1,48) SQL_TXT
,SQL_ID
,address,
hash_value
,parse_calls
,executions
,buffer_gets,disk_reads
,TRUNC(buffer_gets/executions,3) buffer_per_run
,TRUNC(disk_reads/executions,3) disk_per_run
,TRUNC((cpu_time/executions)/1000000,3)  cpu_per_run_S
,TRUNC((elapsed_time/executions)/1000000,3)  elps_per_run_S
FROM v$sql
WHERE executions>0
ORDER BY elps_per_run_S desc)
WHERE rownum <= 10
;

---

ディスク読み取りブロック数が多いSQL

 

---

バッファの読み取り数が極端に多いSQL

---

実行回数が極端に多いSQL

SET LINES 300
set pages 100
COL sql_text            FORM A48
COL buffer_per_run      FORM 999,999,999,999
COL disk_per_run        FORM 999,999,999,999
COL cpu__per_run        FORM 999,999.999
COL elp_per_run         FORM 999,999.999
SELECT * FROM
(SELECT
        substr(vs.sql_text,1,48) sql_text
       ,vs.SQL_ID 
       ,vs.address
       ,vs.hash_value
       ,vs.USERS_OPENING
       ,vsa.version_count
       ,vs.parse_calls
       ,vs.executions
       ,vs.buffer_gets
       ,vs.disk_reads
       ,trunc(vs.buffer_gets/vs.executions,4) buffer_per_run
       ,trunc(vs.disk_reads/vs.executions,4) disk_per_run
       ,trunc((vs.cpu_time/vs.executions)/100000,4) cpu_per_run
       ,trunc((vs.elapsed_time/vs.executions)/100000,4) elps_per_run
FROM v$sql vs,V$sqlarea vsa
WHERE vs.executions>0
AND   vs.SQL_ID = vsa.SQL_ID
ORDER BY vs.executions desc)
WHERE rownum <= 10

;

---

共有プールのチャンク数（各カテゴリ毎）

 

インスタンス実行直後との比較など期間で比較してチャンク数の増減を確認する。

 

select a.KSMCHCOM
          ,sum(a.chunk)   chunk
          ,sum(a.recr)    recr
          ,sum(a.freeabl) freeabl
          ,sum(a.sum)     sum
    from (
            select KSMCHCOM
                  ,count(KSMCHCOM) chunk
                  ,decode(KSMCHCLS,'recr',sum(KSMCHSIZ),null) recr
                 ,decode(KSMCHCLS,'freeabl',sum(KSMCHSIZ),null) freeabl
                 ,sum(KSMCHSIZ) sum
           from  x$ksmsp
           group by KSMCHCOM,KSMCHCLS) a
  group by a.KSMCHCOM
;