2022.05.13
Bind Peeking限界によるAdaptive Cursor Sharing登場
Bind Peeking限界によるAdaptive Cursor Sharing登場
(株)エクセンコンサルティング本部/DBコンサルティングチームキム・チョルファン
Bind Peeking の限界
SQLが最初に実行されると、3段階の過程を経るようになり、Parsing段階を経てExecuteしてFetchの過程を通じてデータをユーザーに転送するようになり、Parsing段階で実行計画が生成されます。
Bind変数を使用するSQLで実行計画が生成されるとき、実際に使用される変数値が分からないため、性能に問題を発生させる実行計画が立てられる可能性があります。
このようなBind変数の欠点を補うために10gでBind Peekingという機能が紹介されましたが、Bind変数を使用するSQLで最初に実行される時点の実際のBind値が何であるかによって実行計画が決定されることを言います。
これにより、実際に使用されるBind変数の値を利用して実行計画を立てることができ、より性能に有利な実行計画を立てることができると考えられますが、Bind Peekingも限界を持っています。
最初にBind変数の値によって実行計画が固定されるため、最初の値がSQLのパフォーマンスを決定するようになり、Table Full Scanで実行計画が生成された場合、このSQLは新しい実行計画が生成されるまでTable Full Scanが発生することになります。
このような問題で、この機能はまったく使用できない機能になってしまったのです。 結局、Bind変数の欠点を補うためにBind Peekingという機能が紹介されたが、依然としてこの機能も限界を持っています。
進化した Cursor Sharing が必要
このようなBind Peeking機能の限界を補完するために、Oracle 11gではAdaptive Cursor Sharing（適応カーソル共有）という機能が紹介されましたが、Adaptive Cursor Sharingという意味は状況に合わせて柔軟にCursor Shareするという意味です。 この機能を使用すると複数の実行計画を管理でき、最初に入力された値によって実行計画が決定されて、その後新しいBind
変数値が使用されると、これに合わせて適切な実行計画を選択してSQLを実行することになります。 すなわち、BindPeekingの限界を補完したのです。
Cursor Sharingの動作原理
この機能が動作するには、デフォルトで条件Columnにヒストグラムが作成されている必要があります。
その状態でBind変数を含むクエリを実行したとき、オプティマイザがヒストグラム分布図によって実行計画が大きく異なると判断する場合、該当カーソルをBind Sensitiveカーソルという状態にしておくことになります。
このようなBind Sensitiveカーソルに対応する構文が再度実行されたときに、特定の変数値で性能が大きく低下したと判断された場合、そのカーソルをBind Awareカーソル状態に変更します。
この状態になると、従来の性能低下と判断されたカーソルについては既存の実行計画を使用せず、子カーソルを生成して新しい実行計画を生成して保存するようになり、新しい実行計画を生成した後には選択度が似ていると判断されるCursorは同じ実行計画を使用します。
Adaptive Cursor Sharingの設定方法
Adaptive Cursor Sharing機能を設定する方法を学びましょう。.
Parameterを使用したAdaptive Cursor Sharing設定
Adaptive Cursor Sharing機能を使用するかどうかを指定し、デフォルトはTrueです。
Alter [ System |Session ] Set “_optimizer_adaptive_cursor_sharing” = TRUE;
統計情報を収集してHistogramを使用できるようにする必要があります。
Adaptive Cursor Sharing機能を使用するには、入力される変数の分布図を知る必要がありますが、その情報はHistogramを通して知ることができます。
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, TABLE , ESTIMATE_PERCENT=>100, METHOD_OPT=>’FOR COLUMNS SIZE ‘);
Adaptive Cursor Sharingに関する考慮事項
Adaptive Cursor Sharingを使用しない場合は、クエリが実行されるたびにキャッシュに格納されている実行計画を実行しますが、Adaptive Cursor Sharingを使用すると、Bind変数の値が変更されたときに実行計画を再作成する必要があるかどうかを判断します。
この機能をすべてのSQLが使用すると、システムに多くの過負荷が発生します。
したがって、すべてのSQLに対してこの機能を使用するのではなく、ParameterをFalse適用した後に、この機能が必要なSQLでセッション単位でParameterをTrueに変更して使用することをお勧めします。
Adaptive Cursor Sharing 活用方案
問合せするSQLで問合せ条件のColumnの重複値の分布が不均一で実行計画を分離する必要がある場合がありますが、同じSQLで入力値によってIndex ScanやFull Table Scanを排他的に実行しなければパフォーマンスに有利な場合があります。
このような場合、Adaptive Cursor Sharing機能を使用すると、入力値に応じて最適に実行計画を生成してSQLのパフォーマンスを向上させることができます。
テストデータの生成
ACCT_NO値がそれぞれ10万件と10件でデータを生成。
CREATE TABLE TB_DPS_TRSC_BASE AS
SELECT 1 ACCT_NO, ‘BANK’ CUST_NO , ROUND(DBMS_RANDOM.VALUE(10,100)) AMT FROM DUAL
CONNECT BY LEVEL <= 100000;
INSERT INTO TB_DPS_TRSC_BASE SELECT 99,’NAME’,ROUND(DBMS_RANDOM.VALUE(10,100) FROM ALL_OBJECTS WHERE ROWNUM <= 10;
COMMIT;
インデックスの作成
CREATE INDEX TB_DPS_TRSC_BASE_1IX ON TB_DPS_TRSC_BASE (ACCT_NO) ;
Parameter 設定
Alter Session Set “_optimizer_adaptive_cursor_sharing” = TRUE ;
統計情報を生成せずに10万件と10件をテスト
SELECT TABLE_NAME、COLUMN_NAME、HISTOGRAM FROM DBA_TAB_COLS WHERE TABLE_NAME=’TB_DPS_TRSC_BASE’;
TABLE_NAME COLUMN_NAME HISTOGRAM
————————- ————————- —————
| TB_DPS_TRSC_BASE | ACCT_NO | NONE |
| TB_DPS_TRSC_BASE | CUST_NO | NONE |
| TB_DPS_TRSC_BASE | AMT | NONE |
— テストのためにShared poolをFlushする.
ALTER SYSTEM FLUSH SHARED_POOL;
EXEC :A1 := 99; SELECT *
FROM TB_DPS_TRSC_BASE A WHERE ACCT_NO = :A1
———————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_DPS_TRSC_BASE | 1 | 33467 | 10 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | IND_01 | 1 | 33467 | 10 |00:00:00.01 | 2 |
———————————————————————————————————-
SQLの実行計画は変更できず、「N」の値で表示されます。
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE, IS_BIND_AWARE FROM V$SQL
WHERE SQL_ID = ‘%48barbrzj2a30’;
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
———— ———- ———– —————– ————- 0 1 3 N N
EXEC :A1 := 1; SELECT *
FROM TB_DPS_TRSC_BASE A WHERE ACCT_NO = :A1
———————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 100K |00:00:00.01 | 34454 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_DPS_TRSC_BASE | 1 | 33467 | 100K |00:00:00.01 | 34454 |
|* 2 | INDEX RANGE SCAN | IND_01 | 1 | 33467 | 100K |00:00:00.01 | 32 |
———————————————————————————————————- SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE, IS_BIND_AWARE FROM V$SQL
WHERE SQL_ID = ‘%48barbrzj2a30’;
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
———— ———- ———– —————– ————- 0 1 34454 N N
EXEC :A1 := 1; SELECT *
FROM TB_DPS_TRSC_BASE A WHERE ACCT_NO = :A1
———————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 100K |00:00:00.01 | 34454 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_DPS_TRSC_BASE | 1 | 33467 | 100K |00:00:00.01 | 34454 |
|* 2 | INDEX RANGE SCAN | IND_01 | 1 | 33467 | 100K |00:00:00.01 | 32 |
———————————————————————————————————- SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE, IS_BIND_AWARE FROM V$SQL
WHERE SQL_ID = ‘%48barbrzj2a30’;
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
———— ———- ———– —————– ————- 0 1 34454 N N
統計情報が存在しないため、Adaptive Cursor Sharing機能を使用できず、同じ実行
計画が確立され実行されます。（Index Rang Scan）
統計情報を生成して10万件の場合と10件の場合をテスト
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYSTEM’,’TB_DPS_TRSC_BASE’, ESTIMATE_PERCENT=>100, METHOD_OPT=>’FOR COLUMNS SIZE 2 ACCT_NO’);
SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM FROM DBA_TAB_COLS WHERE TABLE_NAME=’TB_DPS_TRSC_BASE’;
TABLE_NAME COLUMN_NAME HISTOGRAM
————————- ————————- —————
| TB_DPS_TRSC_BASE | ACCT_NO | HEIGHT BALANCED |
| TB_DPS_TRSC_BASE | CUST_NO | NONE |
| TB_DPS_TRSC_BASE | AMT | NONE |
ALTER SYSTEM FLUSH SHARED_POOL;
EXEC :A1 := 99; SELECT *
FROM TB_DPS_TRSC_BASE A WHERE ACCT_NO = :A1
———————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_DPS_TRSC_BASE | 1 | 33467 | 10 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | IND_01 | 1 | 33467 | 10 |00:00:00.01 | 2 |
———————————————————————————————————-
— SQLの実行計画を変更できる場合は、IS_BIND_SENSITIVE値が「Y」と表示されます（Bind変数の値がある場合は「Y」と表示されます）。)
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE, IS_BIND_AWARE FROM V$SQL
WHERE SQL_ID = ‘%48barbrzj2a30’;
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
———— ———- ———– —————– ————- 0 1 3 Y N
EXEC :A1 := 1; SELECT *
FROM TB_DPS_TRSC_BASE A WHERE ACCT_NO = :A1
———————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 100K |00:00:00.01 | 34454 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_DPS_TRSC_BASE | 1 | 33467 | 100K |00:00:00.01 | 34454 |
|* 2 | INDEX RANGE SCAN | IND_01 | 1 | 33467 | 100K |00:00:00.01 | 32 |
———————————————————————————————————- SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE, IS_BIND_AWARE FROM V$SQL
WHERE SQL_ID = ‘%48barbrzj2a30’;
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
———— ———- ———– —————– ————- 0 1 34454 Y N
EXEC :A1 := 1; SELECT *
FROM TB_DPS_TRSC_BASE A WHERE ACCT_NO = :A1
————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 100K|00:00:00.06 | 6887 |
|* 1 | TABLE ACCESS FULL| TB_DPS_TRSC_BASE | 1 | 33467 | 100K|00:00:00.06 | 6887 |
————————————————————————————————
— Bind変数の値に基づいて実行計画を変更する必要があるかどうかを決定し、実行計画を変更する必要がある場合は、IS_BIND_AWARE値を「Y」と表示します。
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE, IS_BIND_AWARE FROM V$SQL
WHERE SQL_ID = ‘%48barbrzj2a30’;
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
———— ———- ———– —————– ————-
| 0 | 2 | 34454 | Y | N | |||
| 1 | 1 | 6687 | Y | Y |
EXEC :A1 := 99; SELECT *
FROM TB_DPS_TRSC_BASE A WHERE ACCT_NO = :A1
———————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_DPS_TRSC_BASE | 1 | 33467 | 10 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | IND_01 | 1 | 33467 | 10 |00:00:00.01 | 2 |
———————————————————————————————————- SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE, IS_BIND_AWARE FROM V$SQL
WHERE SQL_ID = ‘%48barbrzj2a30’;
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR
———— ———- ———– ——— ——— ———-
| 0 | 2 | 3454 | Y | N | N |
| 1 | 1 | 6687 | Y | Y | Y |
2 2 32 Y Y Y
結論
Bind変数を使用するSQLで初めて使用される変数値で実行計画を立てることができ、その実行計画がSQLのパフォーマンスに問題を発生させることがあり、Bind Peeking機能は使用できない機能になってしまいました。
しかし、この機能の改善としてAdaptive Cursor Sharing機能を使うことで、この問題を解決することができます。
この機能の長所と短所をよく知って使用することで、システムを安定的に使用できるはずです。
また、運用しているシステムにAdaptive Cursor Sharing機能を使用できる部分がある可能性性があります。 この機能を活用してSQLのパフォーマンスを向上させることで、より安定して最適化されたシステムに発展することになるでしょう。
