SQLチューニング対象選定方法 の第6回目は（Case別）SQLチューニング対象選定方法の3回目 です。
「Case3. Literal SQL関連の性能改善対象選定」について解説していきます。

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

　2.3.3 Case3. Literal SQL関連の性能改善対象選定

■　Literal SQL関連の性能改善対象選定

I/OやCPU基準などでTop SQLを抽出する方法はCase1で紹介しました。
しかし、全ての環境で共通的に使えるわけではありません。
プログラムの特性上、Literal SQLが多く使われる環境であればそうなります。
Literal SQLが多い環境では、SQLをそれぞれ別のSQLとして規定すると、正常な結果を抽出することができません。

例えば、SELECT * FROM EMP WHERE EMPNO = 1234; と SELECT * FROM EMP WHERE EMPNO=1235; 
この二つのSQLが実行される場合、これらを別々に規定せず、一つのSQLで判断するようにスクリプトを実行しなければ、意味のあるデータを抽出することができません。

[ Literal SQL抽出スクリプト ］

SELECT ROWNUM rno 、
       t1.*。
FROM (
        SELECT MAX( substr_sqltext ) sql_text 、
               MAX( parsing_schema_name ) parsing_schema_name 、
               MAX( MODULE ) MODULE 、
               MAX( s.sql_id ) sql_id 、
               COUNT( s.exact_matching_signature ) literal_sql_cnt 、
               ROUND( SUM( buffer_gets ) /sum( s.executions ) , 2 ) buffer_avg 、
               ROUND( SUM( elapsed_time ) /sum( s.executions ) , 2 ) elapsed_avg 、
               ROUND( SUM( rows_processed ) /sum( s.executions ) , 2 ) rows_processed 、
               SUM( s.executions ) 実行回数 、
               ROUND( SUM( cpu_time ) /max( cpu_time_total ) *100 , 2 ) ratio_cpu 、
               ROUND( SUM( elapsed_time ) /max( elapsed_time_total ) *100 , 2 ) elapsed_cpu 、
               COUNT( DISTINCT s.plan_hash_value ) plan_cnt
        FROM (
                SELECT s.parsing_schema_name 、
                       s.module 、
                       s.sql_id 、
                       s.hash_value 、
                       s.plan_hash_value 、
                       s.address 、
                       SUBSTR( s.sql_text , 1 , 100 ) substr_sqltext 、
                       s.executions 、
                       s.buffer_gets 、
                       s.disk_reads 、
                       s.rows_processed 、
                       s.cpu_time 、
                       s.elapsed_time 、
                       s.force_matching_signature 、
                       s.exact_matching_signature 、
                       ROUND( s.buffer_gets / s.executions) , 1 ) lio 、
                       ROUND( s.elapsed_time / s.executions) /1000000 , 1 ) elapsed_sec 、
                       ROUND( s.cpu_time / s.executions) /1000000 , 1 ) cpu_sec 、
                       SUM( s.cpu_time ) over( ) cpu_time_total 、
                       SUM( s.elapsed_time ) over( ) elapsed_time_total
                FROM v$sqlarea s
               ) s
        WHERE s.executions > 0
        AND s.force_matching_signature <> exact_matching_signature
        AND s. parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN' )
        GROUP BY s.force_matching_signature
        HAVING COUNT( s.exact_matching_signature ) >= 2
        ORDER BY ratio_cpu DESC
       ) t1
WHERE ROWNUM <= 100

■　活用例

特定のDBサーバーの性能改善作業のために、Case 1(CPU_RATIO基準)で紹介したスクリプトを実行してみたところ、
下記の［表2-5］のような結果が抽出されました。

上記結果を見てみると、Top SQL ListのCPU_RATIO、つまりCPU使用率が全体的に低く、実行回数も少ないことを確認することができます。また、SQL Textが互いに似ていることが確認できます。
このような結果は、Literal SQLが頻繁に実行される環境においては多く発生する抽出結果です。
つまり、一般的なTop SQLを抽出するスクリプトを実行することに意味がありません。

［表2-6］は、条件の定数値だけが異なるSQLを１つのSQLとして規定して、性能関連データをSummaryした抽出結果です。
上の表にある結果を見てみると、上位3つのSQLがCPU使用率の大部分を占めることが分かります。

このようにLiteral SQLが多く実行されるシステムでは、Literal SQLに対する別の処理を行い、対象抽出スクリプトを実行
することで、正確な対象を照会できるようになります。
その結果、様々な形のSQLが実行されたような誤解をされる余地を未然に防ぐことが可能です。

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

次回ブログテーマ

SQLチューニングブログ 2nd Season（第10回）
「 Case.4 パッチプログラム関連性能改善対象の選定 」について

■　データベース運用のことなら日本エクセムにお任せください　■

当社の最新情報はSNSでの配信中（フォローお願いいたします）

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

私たちは、日本のITインフラにおける
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。

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