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

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

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

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

DBサーバーは、多くの業務データが処理されるシステムです。
会社の事業方針に変更があったり、既存の業務フローに変更があった場合、従来の処理で使用されているプログラムを
必要に応じて変更することが考えられます。

しかし、このようなプログラム変更は、システムに致命的な影響を及ぼす可能性があるため、DBサーバーの安定運用を
考慮すると、新たな運用リスクにもなり得ます。

そのため、多くのDB運用チームでは、新しく開発したプログラムをリリースする場合、性能問題が発生しないか？を
リスク回避を目的として事前点検後にリリースする手順を踏んでいますが、このような事前点検をプロセスの1つとして
実施するには、多くの人員リソースと時間を必要です。
そのため、性能点検をせずに開発したプログラムをそのままリリースすることもあります。

性能点検をせずにプログラムをリリースする場合、開発を担当したエンジニアがプログラムの性能チェックもあわせて
担当することになりますが、短期間で多くのプログラムを開発しなければならない場合が多く、性能面まで考慮した
完璧な開発を行うことは事実上難しいのが現状であると言えます。

通常、新規または既存のプログラムを変更する時、DBサーバーの性能面に大きな影響を与える部分の一つとして
考えられるのが、インデックス構成が正しく行われず、多くのSQLがTable Full Scanで実行されることです。

このような場合、前述のV$SQL_PLANビューを活用することで、Table Full Scanで実行されるSQLリストを簡単に
抽出することができます。

以下のスクリプトのWhere節の条件のように、V$SQL_PLANのカラムのうちOPERATION、OPTIONカラムを活用して
Table Full Scanで実行されたSQLを抽出して、V$SQLAREAビューとジョインして性能問題を引き起こす可能性がある
改善対象を抽出すればよいことになります。

[ Full Table Scanで実行されたSQL List抽出スクリプト ]

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 s.parsing_schema_name ,
                       s.module ,
                       s.sql_id ,
                       s.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 ,
                       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
               ) t1 ,
               (SELECT DISTINCT hash_value ,
                       address
                FROM   v$sql_plan
                WHERE  operation = 'TABLE ACCESS'
                AND    options = 'FULL' ) x
        WHERE  t1.executions > 0
        AND    x.hash_value = t1.hash_value
        AND    x.address = t1.address
        AND    t1.parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN' )
        ORDER  BY ratio_cpu DESC
       ) t1
WHERE  ROWNUM <= 100

■　活用例

Table Full Scanを実行するSQLを抽出することは、色々な場面で使うことができます。
ここでは、大きく下記の二つのケースで使われます。

１．新規テーブルにインデックス構成が正しく行われずに発生するTable Full Scan

２．リリース時点ではテーブルサイズが小さく、パフォーマンスの問題が生じることは無かったものの、
　　データ量が多くなったことで、パフォーマンス問題を引き起こしている Full Table Scan

以下の例を見てみましょう

●　データ量が少ない場合

TABLE NAME: BIG_TAB 
COLUMN_NAME   DATA_TYPE  LEN  SCAL N   DISTINCT    DENSITY   NUM_NULLS  BUCKET SAMPLE_SIZE LAST_ANAL
------------ ---------- ----- ---- - ---------- ------------ ---------- ------ ----------- ----------
C4           NUMBER        22      Y       5000  0.000200000       0      1     5000       2015-03-19
C5           VARCHAR2       2      Y         26  0.038461538       0      1     5000       2015-03-19
C6           NUMBER        22      Y       5000  0.000200000       0      1     5000       2015-03-19


［SQLと実行計画］

SELECT *
FROM   big_tab
WHERE  c4 = 100

---------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |      1 |00:00:00.01 |      14 |
|*  1 |  TABLE ACCESS FULL| BIG_TAB |      1 |      1 |      1 |00:00:00.01 |      14 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C4"=100)

特定システムをリリースする準備過程において
新規テーブルのデータ量増加についての点検を行わずに、システムリリースが行われたとします。

このケースでは、性能問題を引き起こしたテーブルが、上記のBIG_TABテーブルであると仮定した上で、
テーブルがオープンにされる前のデータ量は、上記結果にもあるように0.1 MBとかなり小さいため、
Table Full Scanで実行されるのが効率的であったと言えます。

ではリリース後、BIG_TABテーブルのデータ量が急激に増加したらどうでしょうか？

●　データ量が増加した場合

COLUMN_NAME DATA_TYPE 　LEN SCAL N 　DISTINCT 　DENSITY 　　　NUM_NULLS BUCKET SAMPLE_SIZE LAST_ANAL
---------- ---------- ----- ---- - ---------- ------------ ---------- ------ ----------- ----------
C4 　　　　　NUMBER      22        Y 10000000   0.000000100     0        1     10000000    2015-03-19
C5 　　　　　VARCHAR2     2        Y 26         0.038461538     0        1     10000000    2015-03-19
C6 　　　　　NUMBER      22        Y 10000000   0.000000100     0        1     10000000    2015-03-19

[SQLと実行計画］

SELECT *
FROM   big_tab
WHERE  c4 = 100

---------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |      1 |00:00:04.42 |   25986 |  25983 |
|*  1 |  TABLE ACCESS FULL| BIG_TAB |      1 |      1 |00:00:04.42 |   25986 |  25983 |

----------------------------------------------------------------------------[インデックス作成後 ]-----------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C4"=100)

データ量が増加した時点におけるBIG_TABテーブルのデータサイズは、約200MBまで増加したことが分かりました。
従って、このまま Table Full Scan で実行することは非効率的です。
この様なケースでは、SQL実行回数と抽出件数など様々な面で検討しなければならないはずです。BIG_TABテーブルの
サイズが今後も継続的に増加するため、そのサイズがどの程度になるのか？を予測できないからです。
従って、インデックスを作成することは当然ことなのです。

[ インデックス作成後 ]

COLUMN_NAME 　DATA_TYPE LEN　 SCAL N DISTINCT    DENSITY     NUM_NULLS  BUCKET SAMPLE_SIZE LAST_ANAL
----------- ---------- ----- ---- - ---------- ------------ ---------- ------ ----------- ----------
C4           NUMBER      22       Y 10000000   0.000000100    0         1      10000000   2015-03-19
C5           VARCHAR2     2       Y       26   0.038461538    0         1      10000000   2015-03-19
C6           NUMBER      22       Y 10000000   0.000000100    0         1      10000000   2015-03-19

INDEX_NAME TYPE U COLUMN LIST
-------------------------------- ---- - --------------
BIG_TAB_IDX_01 NORM N C4 ---> C4カラムにインデックス作成

[SQLと実行計画］

SELECT *
FROM   big_tab
WHERE  c4 = 100

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TAB        |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | BIG_TAB_IDX_01 |      1 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"=100)

インデックス生成後、SQLの効率が大幅に向上しました。
もし、当該テーブルがTable Full Scanで実行されていることをオープン前の時点であらかじめ把握し、インデックスを
あらかじめ生成しておけば、オープン後の時点でパフォーマンスの問題になることは未然に防ぐことができました。
このように、スクリプトを通じてTable Full Scanを実行するSQLとTableリストを抽出し、各テーブルごとに
今後のデータ増加量をチェックして、必要であれば、現在小さいサイズのテーブルでもインデックスをあらかじめ生成
しておく必要があります。

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

次回ブログテーマ

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

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

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

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

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

---

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

---

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

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

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