概要
Hint Report(DBMS_XPLAN)を後で確認する際、パラレルクエリの場合、見え方に差異が出る場合があります。
これは、コーディネータのインスタンスとスレーブが実行されたインスタンスでのHint Reportで見え方が異なる場合があるためです。
その動作確認と対応策を記載します。
動作確認
1 号機(SQL 実行ノード=コーディネータ)
- Hint Report は明示的に指定したヒントが使用される
SQL> show user ユーザーは"SCOTT"です。 SQL> select instance_name from v$instance; INSTANCE_NAME ------------------------------------------------ orcl1 SQL> CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(10), c3 DATE, CONSTRAINT pk_t1 PRIMARY KEY (c1)) PARTITION BY HASH (c1) ( PARTITION t1_p1 , PARTITION t1_p2 , PARTITION t1_p3 , PARTITION t1_p4 ); 2 3 4 5 6 7 8 表が作成されました。 SQL> BEGIN FOR i IN 1..10000 LOOP INSERT INTO t1 VALUES(i, LPAD(TO_CHAR(i), 10, '0'), SYSDATE); END LOOP; COMMIT; END; / 2 3 4 5 6 7 PL/SQLプロシージャが正常に完了しました。 SQL> ALTER SESSION SET PARALLEL_DEGREE_POLICY = AUTO; セッションが変更されました。 SQL> select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1; COUNT(*) ---------- 10000 SQL> select inst_id, sql_id, sql_text from gv$sql where sql_text like 'select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1'; INST_ID SQL_ID SQL_TEXT ---------- --------------------------------------- ---------------------------------------------------------------------------------------------------- 1 5n7jvs7k6jbgp select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1 2 5n7jvs7k6jbgp select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1 2行が選択されました。 SQL> set lines 1000 pages 2000 SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('5n7jvs7k6jbgp', '0',format=>'ALL ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5n7jvs7k6jbgp, child number 0 ------------------------------------- select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1 Plan hash value: 974985148 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 303 (100)| | | | | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 17370 | 303 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWC | | |* 6 | TABLE ACCESS FULL| T1 | 17370 | 303 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 6 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 6 - access(:Z>=:Z AND :Z<=:Z) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT()[22] 2 - SYS_OP_MSR()[10] 3 - (#keys=0) SYS_OP_MSR()[10] 4 - (#keys=0) SYS_OP_MSR()[10] 5 - (rowset=1019) 6 - (rowset=1019) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 2 --------------------------------------------------------------------------- 0 - STATEMENT - PARALLEL(4) 6 - SEL$1 / T1@SEL$1 - FULL(t1) Note ----- - dynamic statistics used: dynamic sampling (level=2) - Degree of Parallelism is 4 because of hint - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 57行が選択されました。
- 指定したヒント（PARALLEL/FULL）がオプティマイザに正常に解釈され、使用されている
- 「 Degree of Parallelism is 4 because of hint」と出力されている
2 号機(パラレルスレーブ)
- Hint Report はコーディネータのオプティマイザで指示された内部的なヒントで実行される
SQL> select instance_name from v$instance; INSTANCE_NAME ------------------------------------------------ orcl2 SQL> select inst_id, sql_id, sql_text from gv$sql where sql_text like 'select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1'; INST_ID SQL_ID SQL_TEXT ---------- -------------------- ---------------------------------------------------------------------------------------------------- 1 5n7jvs7k6jbgp select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1 2 5n7jvs7k6jbgp select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1 SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('5n7jvs7k6jbgp', '0',format=>'ALL ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5n7jvs7k6jbgp, child number 0 ------------------------------------- select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1 Plan hash value: 974985148 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 303 (100)| | | | | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 10000 | 303 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWC | | |* 6 | TABLE ACCESS FULL| T1 | 10000 | 303 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 6 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 6 - access(:Z>=:Z AND :Z<=:Z) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT()[22] 2 - SYS_OP_MSR()[10] 3 - (#keys=0) SYS_OP_MSR()[10] 4 - (#keys=0) SYS_OP_MSR()[10] 5 - (rowset=1019) 6 - (rowset=1019) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 7 (U - Unused (1)) --------------------------------------------------------------------------- 0 - STATEMENT - PARALLEL(4) - ALL_ROWS - DB_VERSION('19.1.0') - IGNORE_OPTIM_EMBEDDED_HINTS - OPTIMIZER_FEATURES_ENABLE('19.1.0') 6 - SEL$1 / T1@SEL$1 U - FULL(t1) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS - FULL(@"SEL$1" "T1"@"SEL$1") Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level - parallel query server generated this plan using optimizer hints from coordinator
- Hint Reportの出力内容が異なっている
- FULLヒントに「U」がついている
- 「parallel query server generated this plan using optimizer hints from coordinator」と出力されている
まとめ
上記の通り、パラレル処理でスレーブプロセス側のノードで確認すると正しい情報が採取できない
対応策
実行ノードで確認するのが良いと考えられるが、後で確認する場合は、例えば以下の情報（QC_INSTANCE_ID）から実行ノードの確認が可能
Oracle DatabaseRelease 19c データベース・リファレンス
7.7 V$ACTIVE_SESSION_HISTORY
QC_INSTANCE_ID NUMBER
問合せコーディネータ・インスタンスID。この情報は、サンプリングされたセッションがパラレル問合せスレーブである場合にのみ表示される。
その他のすべてのセッションに対しては、この値は0になる。
SELECT SAMPLE_TIME,SESSION_ID,SESSION_SERIAL#,SQL_ID,SQL_PLAN_HASH_VALUE,QC_INSTANCE_ID,QC_SESSION_ID,QC_SESSION_SERIAL# FROM GV$ACTIVE_SESSION_HISTORY WHERE SQL_ID = '5n7jvs7k6jbgp';
SQL> SELECT SAMPLE_TIME,SESSION_ID,SESSION_SERIAL#,SQL_ID,SQL_PLAN_HASH_VALUE,QC_INSTANCE_ID,QC_SESSION_ID,QC_SESSION_SERIAL# FROM GV$ACTIVE_SESSION_HISTORY WHERE SQL_ID = '5n7jvs7k6jbgp'; 2 SAMPLE_TIME SESSION_ID SESSION_SERIAL# SQL_ID SQL_PLAN_HASH_VALUE QC_INSTANCE_ID QC_SESSION_ID QC_SESSION_SERIAL# ------------------------ ---------- --------------- --------------- ------------------- -------------- ------------- ------------------ 21-04-21 10:27:14.664 155 8432 5n7jvs7k6jbgp 974985148 1 157 50842 21-04-21 10:27:14.664 269 63811 5n7jvs7k6jbgp 974985148 1 157 50842
