GAGA LIFE.

インフラエンジニアブログ

スポンサーリンク

パーティション統計のコピー(DBMS_STATS.COPY_TABLE_STATS)

パーティション統計のコピー

パーティション表の任意のパーティション/サブパーティションから、別のパーティション/サブパーティションに統計情報をコピーできる。
新規パーティションを追加し、データ投入後に対象パーティションに統計情報収集すると時間を要する場合があります。
パーティション毎のデータ量に偏りがないのであれば、既存パーティションの統計情報をコピーした方が早いことが多いです。

実行例

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..100000 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プロシージャが正常に完了しました。

経過: 00:00:07.43

SQL> alter session set max_dump_file_size = UNLIMITED;

セッションが変更されました。

経過: 00:00:00.01
SQL> alter session set timed_statistics=true;

セッションが変更されました。

経過: 00:00:00.00
SQL> alter session set tracefile_identifier='gather';

セッションが変更されました。

経過: 00:00:00.00
SQL> alter session set events '10046 trace name context forever, level 16';

セッションが変更されました。

経過: 00:00:00.03
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1',PARTNAME =>'T1_P1',GRANULARITY=>'ALL');

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:02.65
SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

経過: 00:00:00.00
SQL> SELECT NUM_ROWS,AVG_ROW_LEN,BLOCKS,PARTITION_NAME
 FROM USER_TAB_PARTITIONS
 WHERE TABLE_NAME='T1';
  2    3  
  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- --------------------
     24945          24       1006 T1_P1
                                  T1_P2
                                  T1_P3
                                  T1_P4

経過: 00:00:00.10

SQL> alter session set max_dump_file_size = UNLIMITED;
alter session set timed_statistics=true;
alter session set tracefile_identifier='copy';
alter session set events '10046 trace name context forever, level 16';

セッションが変更されました。

SQL> 
セッションが変更されました。

SQL> 
セッションが変更されました。

SQL> 
セッションが変更されました。

SQL> 
SQL> set timing on
SQL> EXEC DBMS_STATS.COPY_TABLE_STATS('SCOTT','T1','T1_P1','T1_P2');

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.29
SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

経過: 00:00:00.00
SQL> SELECT NUM_ROWS,AVG_ROW_LEN,BLOCKS,PARTITION_NAME
 FROM USER_TAB_PARTITIONS
 WHERE TABLE_NAME='T1';
  2    3  
  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- --------------------
     24945          24       1006 T1_P1
     24945          24       1006 T1_P2
                                  T1_P3
                                  T1_P4

経過: 00:00:00.00

T1_P1の統計情報がT1_P2にコピーされている

SQLトレース

gather

$ cat orcl1_ora_26006_gather.txt
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.02          3         38          0           0
Execute      2      0.23       0.26         14        220         17           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.24       0.29         17        258         17           1

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      274      0.22       0.24          2        199         10           0
Execute    993      1.45       1.72         77       1548        380          57
Fetch     1247      0.36       0.38         33       4028          0        1934
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2514      2.04       2.35        112       5775        390        1991

Misses in library cache during parse: 143
Misses in library cache during execute: 126

   12  user  SQL statements in session.
  230  internal SQL statements in session.
  242  SQL statements in session.

copy

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.04       0.06          0          9          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.06          0          9          3           1

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       53      0.03       0.03          0         36          0           0
Execute     99      0.15       0.16          0         20         62          10
Fetch      179      0.00       0.00          0        308          0         174
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      331      0.19       0.20          0        364         62         184

Misses in library cache during parse: 5
Misses in library cache during execute: 3

    4  user  SQL statements in session.
   52  internal SQL statements in session.
   56  SQL statements in session.

新規で統計情報を収集するより、既存のパーティションの統計情報をコピーした方がコストが低いことが多い

Hint Report(DBMS_XPLAN)のパラレル処理確認時の注意点

概要

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

実行計画の比較(DBMS_XPLAN.COMPARE_PLANSファンクション)

概要

DBMS_XPLAN.COMPARE_PLANS APIを使用して実行プランを比較する19cの新機能を試したのでメモします。

DBMS_XPLAN.COMPARE_PLANSは、実行計画の比較機能を提供する19cの新機能です。
参照計画とテスト計画の任意のリストを取得し、それらの違いを強調表示します。

用途

  • 実行計画の良し悪しを比較して、論理的な差異を特定
  • 差異の原因を特定することにより、SQL計画ベースラインやSQLプロファイルで計画の再現性の問題を優先順位付け
  • パフォーマンスが低下しているクエリの現在の実行計画をAWRでキャプチャされた古い実行計画と比較
  • ヒントの追加、パラメーターの変更、インデックスの作成/削除などの際のSQL実行の影響を評価する
  • SQL ProfileまたはSQL Performance Analyzerによって生成された計画が元の計画とどのように異なるかを判別する

DBMS_XPLAN.COMPARE_PLANSプロシージャ

DBMS_XPLAN.COMPARE_PLANS(
    reference_plan        IN generic_plan_object,
    compare_plan_list     IN plan_object_list,
    type                  IN VARCHAR2 := 'TEXT',
    level                 IN VARCHAR2 := 'TYPICAL',
    section               IN VARCHAR2 := 'ALL')
RETURN CLOB;
Options Description
REFERENCE_PLAN 比較される実行計画 – ひとつだけ指定
COMPARE_PLAN_LIST 比較する実行計画 – 複数指定
TYPE TEXT, HTML, XMLのどれか
LEVEL BASIC, TYPICAL, ALLのどれか
SECTION SUMMARY, FINDINGS, PLANS, INFORMATION, ERRORS, ALLのどれか

REFERENCE_PLAN, COMPARE_PLAN_LIST引数の指定対象

計画ソース 引数指定方法
PLAN TABLE plan_table_object(owner, plan_table_name, statement_id, plan_id)
カーソル・キャッシュ cursor_cache_object(sql_id, child_number)
AWR awr_object(sql_id, dbid, con_dbid, plan_hash_value)
SQLチューニング・セット sqlset_object(sqlset_owner, sqlset_name, sql_id, plan_hash_value)
SQL計画管理 spm_object(sql_handle, plan_name)
SQLプロファイル sql_profile_object(profile_name)
アドバイザ advisor_object(task_name, execution_name, sql_id, plan_id)

実行例

  1. Nested Loopsの外部表はSALES
  2. Nested Loopsの外部表はPRODUCTS
SQL> SELECT
    COUNT(*)
FROM
    PRODUCTS P,
    SALES S
WHERE
    P.PROD_ID = S.PROD_ID
AND P.PROD_MIN_PRICE > 200
;
  2    3    4    5    6    7    8    9  
  COUNT(*)
----------
     63763

1行が選択されました。

SQL_ID: gpuwkvycagvbt
SQL> SELECT
    COUNT(*)
FROM
    PRODUCTS P,
    SALES S
WHERE
    P.PROD_ID = S.PROD_ID
AND S.QUANTITY_SOLD = 43
;
  2    3    4    5    6    7    8    9  

  COUNT(*)
----------
         0

1行が選択されました。

SQL_ID: bk1328vh6m6y9

SQL> VARIABLE v_rep CLOB
SQL_ID: 304dsva0bvz67
SQL> BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('gpuwkvycagvbt', null),
compare_plan_list =>
plan_object_list(cursor_cache_object('bk1328vh6m6y9', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
  2    3    4    5    6    7    8    9   10  

PL/SQLプロシージャが正常に完了しました。

SQL_ID: 9txapcbw16vhb
SQL> SET PAGESIZE 50000
SQL> SET LONG 100000 LINESIZE 210
SQL> COLUMN report FORMAT a200
SQL> SELECT :v_rep REPORT FROM DUAL;

REPORT
---------------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
  Current user           : SH
  Total number of plans  : 2
  Number of findings     : 1
---------------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : gpuwkvycagvbt
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : SELECT COUNT(*) FROM PRODUCTS P, SALES S WHERE
                        P.PROD_ID = S.PROD_ID AND P.PROD_MIN_PRICE > 200

Plan
-----------------------------

 Plan Hash Value  : 3037679890

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows   | Bytes    | Cost | Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |        |          |   36 |          |
|   1 |   SORT AGGREGATE                  |                |      1 |       13 |      |          |
| * 2 |    HASH JOIN                      |                | 781685 | 10161905 |   36 | 00:00:01 |
| * 3 |     TABLE ACCESS FULL             | PRODUCTS       |     61 |      549 |    3 | 00:00:01 |
|   4 |     PARTITION RANGE ALL           |                | 918843 |  3675372 |   29 | 00:00:01 |
|   5 |      BITMAP CONVERSION TO ROWIDS  |                | 918843 |  3675372 |   29 | 00:00:01 |
|   6 |       BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX |        |          |      |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_MIN_PRICE">200)

Notes
-----
- This is an adaptive plan

---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : bk1328vh6m6y9
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : SELECT COUNT(*) FROM PRODUCTS P, SALES S WHERE
                        P.PROD_ID = S.PROD_ID AND S.QUANTITY_SOLD = 43

Plan
-----------------------------

 Plan Hash Value  : 4261227730

--------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |      |       |  521 |          |
|   1 |   SORT AGGREGATE        |             |    1 |    11 |      |          |
|   2 |    NESTED LOOPS         |             |    1 |    11 |  521 | 00:00:01 |
|   3 |     PARTITION RANGE ALL |             |    1 |     7 |  521 | 00:00:01 |
| * 4 |      TABLE ACCESS FULL  | SALES       |    1 |     7 |  521 | 00:00:01 |
| * 5 |     INDEX UNIQUE SCAN   | PRODUCTS_PK |    1 |     4 |    0 |          |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter("S"."QUANTITY_SOLD"=43)
* 5 - access("P"."PROD_ID"="S"."PROD_ID")


Comparison Results (1):
-----------------------------
 1. 問合せブロックSEL$1: 結合順序が位置1で異なります(参照プラン: "P"@"SEL$1"、現在のプラン: "S"@"SEL$1")。

---------------------------------------------------------------------------------------------

1行が選択されました。

結合順序が異なると報告されている

参考資料

Oracle Database SQLチューニング・ガイド 19c 6.4 実行計画の比較 https://docs.oracle.com/cd/F19136_01/tgsql/generating-and-displaying-execution-plans.html#GUID-0023D232-5695-4BA8-89C5-88672B7647E2

マルチテナント・データベース(PDB)のAWRスナップショットおよびレポート出力

概要

12.2よりPDBレベルでAWRレポートを作成できるようになりました。 その方法を記載します。

手順

PDB レベルで awr_pdb_autoflush_enabled=true を設定する

SQL> conn /as sysdba
接続されました。
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL> alter session set container=pdb1;

セッションが変更されました。

SQL> alter system set awr_pdb_autoflush_enabled=true;
システムが変更されました。

SQL> select * from cdb_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION            TOPNSQL                            CON_ID   SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ------------------------------ ---------- ---------- --------------------
4110743488 +40150 00:01:00.0              +00008 00:00:00.0    DEFAULT                                 3 4110743488 PDB1

※awr_pdb_autoflush_enabledの設定のみでは、スナップショットの取得間隔が40150日(110年)であるため実質取られない

AWRスナップショットを任意の間隔に設定する(例:30分)

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 30);
PL/SQLプロシージャが正常に完了しました。

SQL> select * from cdb_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION            TOPNSQL                            CON_ID   SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ------------------------------ ---------- ---------- --------------------
4110743488 +00000 00:30:00.0              +00008 00:00:00.0    DEFAULT                                 3 4110743488 PDB1

複数PDBが存在する場合は、同時に複数のPDBがスナップショットを作成するときのパフォーマンスの問題を避けるために、AWR_SNAPSHOT_TIME_OFFSETを1000000に設定する。

SQL> alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;
システムが変更されました。

注意点

PDB側でスナップショットが1度でも取得されるとCDB_HIST_SYSSTATなどのビューに情報が出力される動作となります。

SQL> conn /as sysdba
接続されました。

SQL> select distinct(CON_ID) from CDB_HIST_SYSSTAT;
    CON_ID
----------
         1

SQL> alter system set awr_pdb_autoflush_enabled=true;
システムが変更されました。

SQL> select * from cdb_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION            TOPNSQL                            CON_ID   SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ------------------------------ ---------- ---------- --------------------
4110743488 +40150 00:01:00.0              +00008 00:00:00.0    DEFAULT                                 3 4110743488 PDB1

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 30);
PL/SQLプロシージャが正常に完了しました。

SQL> select * from cdb_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION            TOPNSQL                            CON_ID   SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ------------------------------ ---------- ---------- --------------------
4110743488 +00000 00:30:00.0              +00008 00:00:00.0    DEFAULT                                 3 4110743488 PDB1

SQL> exec dbms_workload_repository.create_snapshot();
PL/SQLプロシージャが正常に完了しました。

SQL> conn /as sysdba
接続されました。

SQL> select distinct(CON_ID) from CDB_HIST_SYSSTAT;
    CON_ID
----------
         1
         3 <---★

Oracle 19c環境(コンテナデータベース)でOS認証によりPDBに接続する方法

概要

19cより前のバージョンでは、SQL*Plusを使ってPluggable Database (PDB)に接続する際には以下の方法がありました

  1. tnsname.oraを設定し、リスナー経由で接続
  2. CDB$ROOTからALTER SESSION SET CONTAINER文を実行し、接続

今回は、19cからPDBへのOS認証がサポートされるようになったため、この点について動作確認します。
具体的には、環境変数ORACLE_PDB_SIDにPDB名を指定して接続します。
※詳細バージョンは19.7から使用可能です。

その前に、これまでのPDBへの接続方法を確認します。

(1)リスナー経由の接続

$ cat $ORACLE_HOME/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.oracle19c.jp)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.oracle19c.jp)
    )
  )
$ sqlplus scott@pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on 土 327 10:55:01 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

パスワードを入力してください: 
最終正常ログイン時間: 木 325 2021 20:02:32 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
に接続されました。
SQL> show con_name

CON_NAME
------------------------------
PDB1

(2)ALTER SESSION SET CONTAINER文での接続

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 土 327 10:57:03 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.



Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
に接続されました。
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb1;

セッションが変更されました。

SQL> show con_name

CON_NAME
------------------------------
PDB1

【新】PDBにOS認証で接続(ORACLE_PDB_SID指定)

以降で新しい接続方法(PDBへのOS認証)を確認します。

$ export ORACLE_PDB_SID=pdb1
$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on 土 327 11:02:08 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

SQL> conn /as sysdba
接続されました。
SQL> show con_name

CON_NAME
------------------------------
PDB1

留意事項(ORACLE_PDB_SID)

  • "AS SYSDBA"を指定しないユーザーを使ったPDB接続はできない。※通常ユーザーでの接続はできない
  • 事前に、接続先CDBを選択するため、環境変数ORACLE_SIDが必要
  • 環境変数ORACLE_PDB_SIDに存在しないPDB名を指定した場合は、CDB$ROOTに接続される
  • PDBがマウント状態でも接続可能

Oracleのヒント句の有効性を確認する Hint Report(DBMS_XPLAN)

概要

Oracle 18cまではヒント句の使用有無や記述の正誤を簡単に判断する方法はありませんでした。
これまでは、イベントの10053(CBOトレース)を取得し、出力されたトレース内の「Dumping Hints」というセクションで確認していました。
ただし、それも実際にヒントが有効になったかどうかの判断としては使えないケースがありました。
※従来バージョンでは、ヒントが有効であるため実行計画が決定されているのか、ヒントの指定は誤っているがCBOによりその実行計画になったのかを簡単に判別できなかった。

リリース19.3以降のDBMS_XPLANのDISPLAYやDISPLAY_CURSORでは、ヒントが有効にならなかった場合に、
「Hint Report」というセクションが表示され、この出力内容からヒントの利用有無が確認できるようになりました。

ヒント使用状況レポートは19cの新機能であり、実行計画の出力からオプティマイザーによって使用され、オプティマイザーによって使用されないすべてのヒントを判別するのに役立ちます。

ヒント句が無視される理由

  • 構文エラー:記述ミスや無効な引数を指定した場合
  • 未解決ヒント:構文エラー以外の理由。索引指定誤りなど
  • 競合するヒント:競合するヒントの同時指定。FULLヒントとINDEXヒントを同時に指定するなど
  • 問合せ変換の影響を受けるヒント:CBOによって内部で問合せ変換が発生した場合、ヒントが無効になる時がある

レポート出力可能なファンクション

  • DISPLAY(PLAN TABLE)
  • DISPLAY_CURSOR(カーソル・キャッシュ)
  • DISPLAY_WORKLOAD_REPOSITORY(AWR)
  • DISPLAY_SQL_PLAN_BASELINE(SQL計画ベースライン)
  • DISPLAY_SQLSET(SQLチューニング・セット)

ヒントレポートの出力制御(FORMATパラメータ)

Value Description
HINT_REPORT ヒントレポートを出力
HINT_REPORT_USED 使われたヒントレポートのみを出力
HINT_REPORT_UNUSED 使われなかったヒントのみ出力
TYPICAL(デフォルト) 使われなかったヒントのみ出力
ALL 使用/未使用の両方を出力

パラメータ指定例

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'TYPICAL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'BASIC +HINT_REPORT'));

レポート出力内容

Hint Reportでは、以下の出力がなされます。
- SQL文に対するヒント数(合計ヒント数と未使用ヒント数)を出力
- その後に、それぞれのヒントに対応する実行計画の行番号/問合せブロック名/オブジェクト別名を出力
- 同一オブジェクトでは、未使用のヒントを最初に表示し、その後に使用されたヒントを表示
- 未使用のヒント・テキストが出力されたときに、理由として以下のいずれかの注釈が表示される
(非使用ヒント数に、それぞれの注釈の数も出力されます)
- 正しくないヒントや最終計画に存在しない問合せブロックに対するヒントは、行番号が0

Value Description
E 構文エラー
N 未解決ヒント
U 未使用

実行例

SQL> select
    /*+ 
    INDEX(e emp_idx) 
    PUSH_PRED(dept) 
    FULL(@sel$2 l) 
    PARALLEL(2) 
    OPT_PARAM('_simple_view_merging','false')
    */
    e.first_name,
    e.last_name,
    dept_locs_v.street_address,
    dept_locs_v.postal_code
from
    employees e,
    (
        select
            /*+ FULL(l) FUL(d) */
            d.department_id,
            d.department_name,
            l.street_address,
            l.postal_code
        from
            depa  2  rtments d,
            locations l
        where
            d.location_id = l.location_id
    ) dept_locs_v
where
    dept_locs_v.department_id = e.department_id
and e.last_name = 'XXXXX'
;
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31  
レコードが選択されませんでした。

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3ghnkvfjbt5kz, child number 2
-------------------------------------
select     /*+     INDEX(e emp_idx)     PUSH_PRED(dept)     FULL(@sel$2
l)     PARALLEL(2)     OPT_PARAM('_simple_view_merging','false')     */
    e.first_name,     e.last_name,     dept_locs_v.street_address,
dept_locs_v.postal_code from     employees e,     (         select
       /*+ FULL(l) FUL(d) */             d.department_id,
d.department_name,             l.street_address,
l.postal_code         from             departments d,
locations l         where             d.location_id = l.location_id
) dept_locs_v where     dept_locs_v.department_id = e.department_id and
e.last_name = 'XXXXX'

Plan hash value: 1795193073

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |       |       |     6 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000    |     1 |    61 |     6   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |             |     1 |    61 |     6   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     JOIN FILTER CREATE  | :BF0000     |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  5 |      TABLE ACCESS FULL  | EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     VIEW                |             |    27 |  1161 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |      HASH JOIN          |             |    27 |  1026 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       PX BLOCK ITERATOR |             |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  9 |        TABLE ACCESS FULL| LOCATIONS   |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       JOIN FILTER USE   | :BF0000     |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 11 |        TABLE ACCESS FULL| DEPARTMENTS |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   5 - SEL$1 / E@SEL$1
   6 - SEL$2 / DEPT_LOCS_V@SEL$1
   7 - SEL$2
   9 - SEL$2 / L@SEL$2
  11 - SEL$2 / D@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - filter("E"."LAST_NAME"='XXXXX')
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
   9 - access(:Z>=:Z AND :Z<=:Z)
  11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"D"."DEPARTMENT_ID"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   2 - (#keys=0) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   3 - (#keys=1; rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   4 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   5 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   6 - (rowset=256) "DEPT_LOCS_V"."DEPARTMENT_ID"[NUMBER,22], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12]
   7 - (#keys=1; rowset=256) "L"."POSTAL_CODE"[VARCHAR2,12], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "D"."DEPARTMENT_ID"[NUMBER,22]
   8 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
   9 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
  10 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]
  11 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 7 (U - Unused (2), N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------------------

   0 -  STATEMENT
           -  OPT_PARAM('_simple_view_merging','false')
           -  PARALLEL(2)

   1 -  SEL$1
         N -  PUSH_PRED(dept)

   5 -  SEL$1 / E@SEL$1
         U -  INDEX(e emp_idx) / index specified in the hint doesn't exist

   7 -  SEL$2
         E -  FUL

   9 -  SEL$2 / L@SEL$2
         U -  FULL(l) / hint overridden by another in parent query block
           -  FULL(@sel$2 l)

Note
-----
   - Degree of Parallelism is 2 because of hint

上記のHint Reportでは、オプティマイザーは以下のように判断しています。
- PARALLEL(2)、OPT_PARAM('_ simple_view_merging'、'false')およびFULL(@sel$2 l)ヒントのみを考慮
- U:指定されたインデックスが存在しないため、ヒントINDEX(e emp_idx)は使用しない
- U:同様のヒントが親クエリブロックで指定され、サブクエリブロックのクエリが上書きされるため、ヒントFULL(l)は使用しない
- E:FULヒントに構文エラーがあるため、考慮されない(※FULではなくFULL)
- N:ヒントPUSH_PRED(dept)は未解決(※「dept」という名前のビューはない)

参考資料

津島博士のパフォーマンス講座 第72回 SQLパッチとヒント使用状況レポートについて
https://blogs.oracle.com/otnjp/tsushima-hakushi-72

INDEX SKIP SCAN

INDEX SKIP SCAN

概要

INDEX SKIP SCANの概要について記載します。

INDEX SKIP SCAN

INDEX SKIP SCAN(9i~)により第1キーが抽出条件に無くてもインデックスを使用する機能がありますが、この実行計画が選択される条件として 第1キーのカーディナリティが非常に低い値である必要があります。
そうでない場合は、FULL SCANのほうが低コストと判断されます。

複合索引の付与列の順序の効率が良くないケース

  • 下記のような表に対して、①DEPTNO+②EMPNOの順で索引を作成している
    f:id:undercovergeek:20210227105454p:plain

複合索引の2列目だけの条件でクエリ実行

f:id:undercovergeek:20210227105521p:plain

複合索引をカーディナリティの高い列から付与することで改善

  • 複合索引を(①EMPNO + ②DEPTNOで再作成)
CREATE INDEX idx_comp
ON  emp2(
        empno,
        deptno
    )
;

f:id:undercovergeek:20210227105721p:plain

スポンサーリンク