SQLチューニング対象の選定方法 の4回目は（Case別）SQLチューニング対象の選定方法 の1回目 です。
「Case1. IO / CPU Top SQL関連性能の改善対象選定」について解説していきます。

それでは早速スタートしましょう！！

　2.3　Case別SQLチューニング対象選定方法

前回までのテーマでは、SQLチューニング対象選択の重要性と対象を選定するために活用ができるツールについて
説明して来ました。ここからは、先に説明したDictionary Viewを使って、実際にスクリプトを作成していきます。
そして、CASE別チューニング対象選定作業を進めながら、読者の皆さんの理解を助けていきたいと思います。

　2.3.1 Case1. IO / CPU Top SQL関連性能改善対象選定

■　ターゲット抽出スクリプト

Top SQLを抽出するための基準となる基本要素は、I/O発生量とCPU使用率です。
なぜならば、DBサーバーの性能問題を引き起こす要因ともなり得るからです。

I/O発生量とCPU使用率はV$SQLAREA、DBA_HIST_SQLSTATビューを照会する下記のようなスクリプトを作成して、
その性能指標に関連するSQLを抽出することができます。

[V$SQLAREA活用スクリプト］

SELECT ROWNUM cnt 、
       t1.*。
FROM (
        SELECT t1.parsing_schema_name 、
               t1.module 、
               t1.sql_id 、
               t1.hash_value 、
               t1.substr_sqltext 、
               t1.executions 、
               t1.buffer_gets 、
               t1.disk_reads 、
               t1.rows_processed 、
               t1.lio 、
               t1.elapsed_sec 、
               t1.cpu_sec 、
               ROUND( t1.cpu_time /t1.cpu_time_total*100 , 1 ) ratio_cpu 、
               ROUND( t1.elapsed_time /elapsed_time_total * 100 , 1 ) ratio_elapsed
        FROM (
                SELECT parsing_schema_name 、
                       MODULE 、
                       sql_id 、
                       hash_value 、
                       SUBSTR( sql_text , 1 , 100 ) substr_sqltext 、
                       実行 、
                       buffer_gets 、
                       disk_reads 、
                       rows_processed 、
                       cpu_time 、
                       elapsed_time 、
                       ROUND( buffer_gets / executions ) , 1 ) lio 、
                       ROUND( elapsed_time / executions ) /1000000 , 1 ) elapsed_sec 、
                       ROUND( cpu_time / executions ) /1000000 , 1 ) cpu_sec 、
                       SUM( cpu_time ) over( ) cpu_time_total 、
                       SUM( elapsed_time ) over( ) elapsed_time_total
                FROM v$sqlarea s
               ) t1
        WHERE t1.executions > 0
        AND t1.parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN' )
        ORDER BY ratio_cpu DESC
       ) t1
WHERE ROWNUM <= 100

[DBA_HIST_SQLSTATを活用したスクリプト ]

SELECT sql_id 、
       schema_name 、
       MODULE 、
       ela_ratio 、
       ela_tot 、
       cpu_ratio 、
       cpu_tot 、
       exec_ratio 、
       exec_tot 、
       lio_ratio 、
       lio_tot 、
       pio_ratio 、
       pio_tot 、
       rows_ratio 、
       rows_tot
FROM (
        SELECT sql_id 、
               parsing_schema_name schema_name 、
               NVL( SUBSTR( b.module , 1 , 15 ) , '-' ) MODULE 、
               ROUND( RATIO_TO_REPORT(SUM(b.elapsed_time_delta)) over() * 100, 1 ) AS ela_ratio 、
               ROUND( SUM( b.elapsed_time_delta ) /1000000 , 0 ) AS ela_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.cpu_time_delta)) over() * 100, 1 ) AS cpu_ratio 、
               ROUND( SUM( b.cpu_time_delta ) /1000000 , 0 ) AS cpu_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.executions_delta)) over() * 100, 1 ) AS exec_ratio 、
               SUM( b.executions_delta ) AS exec_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.buffer_gets_delta)) over() * 100, 1 ) AS lio_ratio 、
               SUM( b.buffer_gets_delta ) AS lio_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.disk_reads_delta)) over() * 100, 1 ) AS pio_ratio 、
               SUM( b.disk_reads_delta ) AS pio_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.rows_processed_delta)) over() * 100, 1 ) AS rows_ratio、
               SUM( b.rows_processed_delta ) AS rows_tot
        FROM dba_hist_snapshot a 、
               dba_hist_sqlstat b
        WHERE a.instance_number=1
        AND a.begin_interval_time >= TO_DATE( :b1 , 'YYYY-MM-DD' )
        AND a.end_interval_time <= TO_DATE( :b2 , 'YYYY-MM-DD' ) +0.99999
        AND a.dbid=b.dbid
        AND b.parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN')
        AND a.instance_number = b.instance_number
        AND a.snap_id=b.snap_id
        GROUP BY b.sql_id 、
               b.parsing_schema_name 、
               b.モジュール
        ORDER BY cpu_ratio DESC
       )
WHERE rownum<=100 ；

上記の内容の中でDBA_HIST_SQLSTATを利用したスクリプトは、特定期間に実行したSQL実行履歴を
抽出したい場合によく使われます。

例えば、業務担当者から以下のような依頼を受けたとした場合に使用できるスクリプトです。

” 昨夜9時から今朝9時まで実行されたSQLのうち、CPU使用率が最も高いリストを抽出する ”

CPU使用率ではなく、I/O使用率や実行時間(Elapsed Time)を抽出したい場合には、ORDER BY節の内容だけ
クエリを変更することで必要となるリストを抽出することができます。

■　活用例

PROD DBシステムのCPU使用率の推移は以下の通りです。

「 図2.3.1-1 」PROD CPU使用率の時間別推移（3月23日）

［ 図2.3.1-1 ］は、当該システムの特定日のCPU使用率推移で、常に時間別CPU使用率は最大50%未満で推移しており、
安定して運用されていることがわかります。
ところが突然、［図2.3.1-2］のようにCPU使用率が大幅に高く検出されてしまいました。

「 図2.3.1-2 」PROD CPU使用率の時間別推移（3月24日）

このような状況においては、先に紹介したDBA_HIST_SQLSTATビューを使ったスクリプトを活用することで、
CPU使用率が高くなった原因を分析することができます。
先に紹介したスクリプトで普段のCPU使用率を維持する時のSQLリストとCPU使用率が高くなった時のSQLリストを
比較すると、どのプログラム(SQL)によって発生したのかを分析することができます。

「 表2.3.1-1 」PRODシステムのTop SQL List（3月23日）

「 表2.3.1-2 」PRODシステムのTop SQL List（3月24日）

3月23日に比べて3月24日のCPU使用率が高かったのは、PRODで実行されたTop SQL1,2が過去の時点では
実行されなかったことが明確な原因であるということが分かりました。
つまり、新しく追加されたプログラムの実行によりCPU使用率が増加した訳です。
この場合、当該SQLをチューニング対象に選定して改善の余地があるかどうかを点検する必要があります。

SQLチューニングブログ 2nd Season（第7回）　終

次回ブログテーマ

『 Case2. Table Full Scan関連性能改善対象選定 』

データベース運用でお困りなら

日本エクセムまで お気軽にご相談ください！！

私たちは、日本のITインフラにおける

『 プロジェクト運営 ～ システム運用 』の安定化と効率化を推進します。

日本エクセム X公式アカウント

最新情報を随時ポスト中！是非、フォローをお願いします。

---

掲載内容についてのお問い合せは

日本エクセム株式会社　営業推進部（担当：田中）まで
Mail：sales@ex-em.co.jp

●　こちらの問い合わせフォームもご利用ください　●