今回はSQLのアウトライン・ヒントを利用したチューニング方法をご紹介します。
事の発端は10.1のStandard Editionで性能問題が発生し、調査を依頼されたのですが、Standard Editionだと使えるチューニング手法がかなり限られます。
バージョン的にもやはり限りがあります。
勿論、Enterprise EditionでもTuningパックを購入していないとライセンス的に使える手段は限られますが。
10.1のStandard Editionでも使える手段が、アウトラインヒントの利用になります。
前述の通り、古いバージョンでStandard Editionだと、ヒント句をつけるくらいしかチューニング手段が無かったりします。でも複雑なSQLの複雑な実行計画だと、どこをどういじって良いのか分からず、途方に暮れる事がありますよね。
良い実行計画で実行された実績がある場合は、そこからヒント句を取り出して利用する事が可能です。
例えば、アップグレード前は早かったのに、アップグレード後に遅くなってしまったケース、同一SQLIDなのに実行計画が不安定で、性能にムラがあるケースなどです。
分かりやすくする為、単純なSQLの例を挙げます。
SQL>select count(*) from t3,t4 where t3.col1=t4.col1 and t3.col1 <= 500;
COUNT(*)
----------
2097156097
経過: 00:00:43.95
SQL>select count(*) from t3,t4 where t3.col1=t4.col1 and t3.col1 <= 500;
COUNT(*)
----------
2097156097
経過: 00:00:11.82
同じSQLなのに、処理時間に差が出ていますね。
以下のSQLで、速い時のsqlidとchild_numberを問い合わせます。
SQL>select sql_id,child_number,sql_text,elapsed_time from v$sql where sql_text like 'select count(*) from t3,t4 where t3.col1=t4.col1 and t3.col1 <= 500%';
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
-------------------------------------------------------------------------------------------------
ELAPSED_TIME
------------
4uhauy3aq834g 1
select count(*) from t3,t4 where t3.col1=t4.col1 and t3.col1 <= 500
196402
4uhauy3aq834g 2
select count(*) from t3,t4 where t3.col1=t4.col1 and t3.col1 <= 500
34837
child_number=2が速い時なので、そのアウトライン・ヒントを取得します。
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('4uhauy3aq834g',2,'OUTLINE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID 4uhauy3aq834g, child number 2
-------------------------------------
select count(*) from t3,t4 where t3.col1=t4.col1 and t3.col1 <= 500
Plan hash value: 3895210891
（略）
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_fix_control' '20424684:0')
ALL_ROWS
SHARED(8)
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T3"@"SEL$1")
FULL(@"SEL$1" "T4"@"SEL$1")
LEADING(@"SEL$1" "T3"@"SEL$1" "T4"@"SEL$1")
USE_HASH(@"SEL$1" "T4"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "T4"@"SEL$1" HASH HASH)
END_OUTLINE_DATA
*/
（略）
上記で取得したヒント句を埋め込んで、SQLを実行します。
SQL>select
2 /*+
3 BEGIN_OUTLINE_DATA
4 OPTIMIZER_FEATURES_ENABLE('19.1.0')
5 DB_VERSION('19.1.0')
6 OPT_PARAM('_optim_peek_user_binds' 'false')
7 OPT_PARAM('optimizer_dynamic_sampling' 0)
8 OPT_PARAM('_optimizer_use_feedback' 'false')
9 OPT_PARAM('_px_adaptive_dist_method' 'off')
10 OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
11 OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
12 OPT_PARAM('star_transformation_enabled' 'true')
13 OPT_PARAM('_fix_control' '20424684:0')
14 ALL_ROWS
15 SHARED(8)
16 OUTLINE_LEAF(@"SEL$1")
17 FULL(@"SEL$1" "T3"@"SEL$1")
18 FULL(@"SEL$1" "T4"@"SEL$1")
19 LEADING(@"SEL$1" "T3"@"SEL$1" "T4"@"SEL$1")
20 USE_HASH(@"SEL$1" "T4"@"SEL$1")
21 PQ_DISTRIBUTE(@"SEL$1" "T4"@"SEL$1" HASH HASH)
22 END_OUTLINE_DATA
23 */
24 count(*) from t3,t4 where t3.col1=t4.col1 and t3.col1 <= 500;
COUNT(*)
----------
2097156097
経過: 00:00:10.91
無事、速くなりました。
