チューニング検討 体感的に遅い SQL を特定出来 Y N 一定期間の統計から抽出す N Y SQL トレース &TKPROF STATSPACK EXPLAIN PLAN コマンド 現在の状況から抽出する N Y AUTOTRACE 動的パフォーマンスヴュー V$SQL V$SQL_TEXT V$SQLL_PLAN 1
2) V$ 表 ( 動的パフォーマンスヴュー ) 1 使用するヴュー V$SQL 2 検証 SQL 後頁 5. 検証プログラム仕様 参照 3 抽出基準 STATSPACK 同様に以下を抽出基準とする 1. 物 1. 実行時間の長い SQL を抽出する 2. バッファ読込みブロックの多い SQL を抽出する 3. 実行回数の多い SQL の抽出 1. 実行時間の長い SQL を抽出する 実行時間の長い順に SQL を列挙し それぞれ構文解析 実行回数 バッファ読取バイト数 1 回実行あたりのバッファとディスク読込みバイト数等を検証する sql_text,parse_calls,executions,buffer_gets,disk_reads, trunc(buffer_gets/executions,2) buffer_per_run,trunc(disk_reads/executions,2) disk_per_run, cpu_time,elapsed_time from v$sql 実行時間の長い (ELAPSED_TIME) ものから順に出力している where executions > 0 and rownum <= 8 order by elapsed_time desc; SQL_TEXT, PARSE_CALLS EXECUTIONSBUFFER_GDISK_READS BUFFER_PERDISK_PER_EXE CPU_TIME ELAPSED_TIME lock table sys.mon_mods$ in exclusive mode nowait 5 5 28 1 5.6 0.2 12706 27545 count(*) from sys.job$ where (next_date > sysdate) and (next_date < 90 0 90 1 1 0.01 26940 26940 where NO =2order by NO 17 17 1428 0 84 0 26657 26657 lock table sys.col_usage$ in exclusive mode nowait 61 61 30 0 0.49 0 26590 26590 intcol#,type,flags,lo bcol,objcol,extracol, schemaoid, elemnum from opqtype$ where obj# = :1 order by 3 3 75 3 25 1 18464 25700 obj# from oid$ where user#=:1 and 2 1 41 3 20.5 1.5 14994 25275 file# from file$ where ts#=:1 2 2 17 2 8.5 1 5490 24800 where NO =6order by NO 16 16 1344 0 84 0 24755 24755 where NO =5order by NO 16 16 1344 0 84 0 24721 24721 検証用に用意した SQL の実行時間がワースト 8 に アスタリスクを使用して検索しているので解析効率が悪く実行時間が 2
( 赤枠のところ ) 登場している アスタリスクを使用し重複行の排除 (distinct) をしているので負荷と アスタリスクを使用して検索しているので解析効率が悪く実行時間が長くパフォーマンスのネックとなる事が判る 解析呼び出し (PARSE_CALL) を 90 回も繰り返しており構文解析のキャッシュ ( ライブラリキャッシュ ) を再利用出来ていないので効率が悪 2. バッファ読込みブロックの多いSQLを抽出する 上記 1. 同様にバッファ読込みブロックの多い順に抽出する sql_text,parse_calls,executions,buffer_gets,disk_reads, trunc(buffer_gets/executions,2) buffer_per_run,trunc(disk_reads/executions,2) disk_per_run, cpu_time,elapsed_time from v$sql バッファからの読込み回数の多い順 where buffer_gets > 0 and rownum <= 8 order by buffer_gets desc; SQL 文 PARSE_CALLS EXECUTIONSBUFFER_GDISK_READS BUFFER_PERDISK_PER_EXE CPU_TIME ELAPSED_TIME 134 134 11256 0 84 0 191963 206010 where NO =7order by NO 118 118 9947 0 84.29 0.7 185049 251486 where NO =2order by NO 17 17 1428 0 84 0 26657 26657 pos#,intcol#,col#,sp 214 55 1396 0 6.52 0 62015 62015 con#,type#,condlen 111 43 1363 1 12.27 0 68590 170949 16 16 1344 0 84 0 24755 24755 /*+ rule */ 16 16 1344 0 84 0 24721 24721 bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 439 12 1299 38 2.95 0.08 111887 901151 実行回数が多い分バッファからの読込み回数も多く負荷となっている事が判る ただし ディスクからの読込みは 0 であり これは 概ねディスク読込みは 0 となっており 最初の読込み以外はバッファキャッシュから読まれている事を意味しており最も負荷の高いディスクからの読込みがネックとはなっていない 3. 実行回数の多い SQL の抽出 sql_text,parse_calls,executions,buffer_gets,disk_reads, trunc(buffer_gets/executions,2) buffer_per_run,trunc(disk_reads/executions,2) disk_per_run, cpu_time,elapsed_time from v$sql 3
where executions > 0 and rownum <= 実行回数 8 (EXECUTIONS) が多いものを order by executions desc; SQL_TEXT, PARSE_CALLS EXECUTIONSBUFFER_GDISK_READS BUFFER_PERDISK_PER_EXE CPU_TIME ELAPSED_TIME where NO =1order by NO 48 48 4032 0 84 0 86770 88480 FROM menber where NO =1order 48 48 144 0 3 0 47576 47576 pos#,intcol#,col#,sp are1,bo#,spare2 from icol$ where 5 42 254 7 6.04 0.16 24457 67951 4
name,intcol#,segcol #,type#,length,nvl(p recision#,0),decode( type#,2,nvl(scale,- 127/*MAXSB1MIN AL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),n ull$,fixedstorage,nvl (deflength,0),default $,rowid,col#,propert y, nvl(charsetid,0),nvl( charsetform,0),spar e1,spare2,nvl(spare 3,0) from col$ 6 32 249 5 7.78 0.15 56608 142969 where NO =7order by NO 30 30 2520 0 84 0 48728 48728 i.obj#,i.ts#,i.file#,i.blo ck#,i.intcols,i.type#,i.flags,i.property,i.pct free$,i.initrans,i.max trans,i.blevel,i.leafcn t,i.distkey,i.lblkkey,i. dblkkey,i.clufac,i.col s,i.analyzetime,i.sam plesize,i.dataobj#,nv l(i.degree,1),nvl(i.ins tances,1),i.rowcnt,m od(i.pctthres$,256),i.indmethod#,i.trunc cnt,nvl(c.unicols,0), nvl(c.deferrable#+c. valid#,0),nvl(i.spare 1,i.intcols),i.spare4,i. spare2,i.spare6,dec ode(i.pctthres$,null, null,mod(trunc(i.pct thres$/256),256)),is t.cachedblk,ist.cach ehit,ist.logicalread from ind$ i, ind_stats$ ist, ( enabled, min(cols) unicols,min(to_numb er(bitand(defer,1))) deferrable#,min(to_ number(bitand(defe r,4))) valid# from 7 30 495 17 16.5 0.56 106761 277985 5
t.ts#,t.file#,t.block#, nvl(t.bobj#,0),nvl(t.t ab#,0),t.intcols,nvl(t. clucols,0),t.audit$,t.f lags,t.pctfree$,t.pct used$,t.initrans,t.ma xtrans,t.rowcnt,t.blk cnt,t.empcnt,t.avgs pc,t.chncnt,t.avgrln, t.analyzetime,t.sam plesize,t.cols,t.prop erty,nvl(t.degree,1), nvl(t.instances,1),t.a vgspc_flb,t.flbcnt,t.k ernelcols,nvl(t.trigfl ag, 0),nvl(t.spare1,0),nvl (t.spare2,0),t.spare4,t.spare6,ts.cachedb lk,ts.cachehit,ts.logi calread from tab$ t, tab_stats$ ts where 5 30 289 30 9.63 1 81880 422601 FROM menber where NO =7order 30 30 90 0 3 0 29166 67598 6