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

2025-06-11 10:55Oracle技術情報

　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リストを抽出し、各テーブルごとに今後のデータ増加量をチェックして、必要であれば、現在小さいサイズのテーブルでもインデックスをあらかじめ生成しておく必要があります。

次回ブログテーマ

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

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

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

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

人気記事ランキング

タグ一覧

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

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

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？MaxGaugeがサポートします

人気記事ランキング

タグ一覧

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

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

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします