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

第2章　SQLチューニング対象選定方法（1/8）

いつもSQLチューニングブログをご愛読いただきまして、ありがとうございます。

ここからは、2nd Seasonも第2章へと突入していきます。
全8回のシリーズで SQLチューニング対象の選定方法 をお届けします。

今回のテーマは『 SQLチューニング対象を選択する重要性 』について解説していきたいと思います。

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

2. SQLチューニング対象選定方法

韓国には「始まりが半分だ」という諺（ことわざ）があります。

「始めるのは大変だけど、始めてしまえば半分はできたものと同じだ」とか
「どんなことでも、始めてしまえばすでに半分は成し遂げている」という意味です。

性能改善におけるSQLチーニング対象の選定とは、まさにこの諺通りのイメージになります。

チューニング対象となるSQLの選定は、その目的によって非常に多くのケースが考えられます。
その中でも特に注意しておきたいのは、作業の開始当初から選定先を間違えた状態でチューニングを進める事です。
そもそも性能改善に繋がらない可能性が高くなる上、性能への悪影響を与える結果を招く危険性があるからです。

★　POINT　★

では、合理的にSQLチューニング対象を選定するにはどうしたら良いのでしょうか？

選定を進めるには、まず対象のDBサーバーに関する業務環境がどのようなものか？・・・・
どんな性能問題が発生するのか？に対する理解を深めることが重要となるため、関連情報が収集できるツールの
導入と活用できる能力を身につけることが大切です。

そこで今回のブログでは、SQLチューニングの対象を選定することに対する重要性と、作業を進めるにあたって
活用することができるツールを紹介しながら、実際に性能改善の対象選定を進めていく例を挙げていきたいと思います。

一連の過程を理解していける良い機会になれば幸いです。

2.1　SQLチューニング対象選択の重要

業務上、特別な要件があったり、DBシステムの特別な状況に対処するために性能改善作業を行う場合、
SQLチューニング対象の選定は当然、その要件と状況に合わせて選定しなければなりません。

もし、特定のDBシステムの高いCPU使用率が問題である状況でチューニングを実施する場合、
性能改善作業の開始は、当該DBシステムのCPU占有時間が高い順のSQL情報を抽出するスクリプトを
実行することが正しい方法です。これこそが即ち、SQLチューニング対象の選定作業となります。

以下は、PROD DBシステムのCPU使用率が高く、これを下げてほしいという要請を受けた場合を 想定して説明した内容です。

[ 図2-1 ]　 PROD Database 時間別CPU使用率推移

[図2-1]は、PRODシステムの時間別CPU使用率の推移を示すグラフです。
この作業を行うチューナーは、まず自分が持っているスクリプトを活用た上でTop SQLの抽出を行い、
改善ポイントがあるSQLに対して、性能改善作業を実施していく・・・と言ったごくごく一般的な作業過程を
経ることになります。

この状況でチューナーが性能改善作業のために、対象SQLへ下記のスクリプトを実行したと仮定してみましょう。

性能改善対象SQL List抽出スクリプト（1）

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 , --SQL_FULLTEXT、
                       SUBSTR( sql_text , 1 , 100 ) substr_sqltext 、
                       実行 、
                       buffer_gets 、
                       disk_reads 、
                       rows_processed 、
                       cpu_time 、
                       elapsed_time 、
                       ROUND( buffer_gets / executions , 0 , 1 , 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 elapsed_sec DESC
       ) t1
WHERE ROWNUM <= 100

抽出された結果から、Top List 1~10までのSQLを点検後、改善の余地があると思われるSQLの性能改善作業を
実施した場合、CPU使用率にはどんな変化があるのでしょうか？

［図2-2］ PROD Database 時間別CPU使用率推移

［図2-1］ CPU使用率の推移と比較しても、性能改善作業後も大きな差が出ていないことがわかりました。
実際、何故このような結果が出たのか？・・・についての理由は明らかです。
その原因とは、SQLチューニング対象選択のスクリプトに問題があったからです。

スクリプトを確認してみましょう。
ORDER BY節に降順で記述したカラムがElapsed_secで、このカラムの意味は1回実行あたりのElapsed Timeです。
結局、高いCPU使用率が問題になったDBサーバーで1回の実行当たりのElapsed Timeが長い順にSQL Listを抽出して
性能改善作業を行ったものです。
もちろん、1回の実行当たりのElapsed Timeが長かった順番でSQL Listを抽出し、これに対する性能改善作業を
行ったことが、CPU使用率の面で非常に影響力が無いものとは言えません。
何故ならば、Elapsed Timeが長かった分、CPUを占有していたと見てもいいからです。
厳密に言うのであれば、CPU使用率を下げるためのチューニング対象となるのはElapsed Timeでは無かった。
これが最終的な結果となります。

★　POINT　★

では、CPU使用率を下げるためのSQLチューニングの対象選定はどうすれば良いのでしょうか？

性能改善対象SQL List抽出スクリプト（2）

・　性能改善対象SQL List抽出スクリプト

SELECT *を選択
FROM (
        SELECT ROWNUM cnt 、
               t1.*。
        FROM (
                SELECT parsing_schema_name 、
                       MODULE 、
                       sql_id 、
                       hash_value 、
                       SUBSTR( sql_text , 1 , 100 ) substr_sqltext 、
                       実行 、
                       buffer_gets 、 
                       disk_reads 、
                       rows_processed 、
ROUND( buffer_gets/executions , 1 ) lio 、 
ROUND( elapsed_time/executions/1000000 , 1 ) elapsed_sec 、 
ROUND( cpu_time/executions/1000000 , 1 ) cpu_sec 、 
ROUND( elapsed_time/tot_elapsed*100 , 1 ) ratio_elapsed 、 
ROUND( cpu_time/tot_cpu*100 , 1 ) ratio_cpu 
                FROM v$sqlarea s 、
                       ( SELECT SUM( elapsed_time ) tot_elapsed 、
                               SUM( cpu_time ) tot_cpu
                        FROM v$sqlarea ) t
                WHERE s.executions > 0
                AND parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN')
                AND (( MODULE NOT LIKE 'TOAD%')
                        AND MODULE NOT LIKE 'SQL De%' のように
                        AND MODULE NOT LIKE 'Orange%' )
                        OR MODULE IS NULL
                    )
                ORDER BY ratio_cpu DESC
               ) t1
        WHERE ROWNUM <= 10
       )
WHERE cnt >= 1；

既存のスクリプトと比較すると目立つ変更点はORDER BY節のカラム名です。
Elapsed_secカラムではなく、RATIO_CPUカラムにソート対象が変わったのがわかります。
それでは早速、抽出された結果を見てみましょう。

［表 2-2］ 性能改善対象 SQL List

ソート対象を全体に対してのCPU使用率に変更すると、このような結果が抽出されました。
この結果をチューニング対象に対して性能改善作業を行うと結果して、以下のようなCPU使用率を示しました。

［図 2-3］ PROD Database 時間別のCPU使用率の推移

CPU使用率がほぼ80%に近いグラフから50%以内を記録するレベルにCPU使用率が減少したことがわかりました。
結局、［図2-3］の推移こそが、チューニングを依頼した顧客やチューナーが望む結果であると言えます。
SQLチューニング対象選択の重要性を説明するために仮定した状況ですが、実際に特定の目的に合った性能改善作業を
進めれば、このような劇的な結果を十分に得ることができます。

SQLチューニング対象の選定が最初から目的に合った情報を抽出できれば、より迅速で合理的な結果を得られます。

この回では『SQLチューニング対象を選択する重要性』 についての理解を深めてもらうために、具体的な例を挙げて
説明してきました。これを逆説的に言うのであれば、その重要性を理解するのに役立ったのではないか？思います。

SQLチューニング対象の選定作業は、その目的が明確であればあるほど、間違いを犯してしまう確率は減ります。
しかし、これを見落したままの間違えた状態で見切発車すると、思ったような改善結果が得られない可能性が高まります。だからこそ細心の注意を払うべき必要があるのです。

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

---

次回ブログテーマ

『　チューニング対象選択のための情報及び活用ツール（前編）　』

---

データベース運用でお困りなら
日本エクセムまで お気軽にご相談ください！！

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

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