Oracle mechanics

30.03.2016

12c: Automatic Dynamic Statistics в присутствии SPM Baseline — 2. Testcases

Filed under: Oracle,Plan Management — Игорь Усольцев @ 09:16
Tags: ,

В дополнение к предыдущему могут быть полезны простые testcase-ы поведения Oracle 12.1.0.2 при разборе / hard parse запроса с применением SQL Plan Baseline

Первый case был предложен/подготовлен коллегой Русланом Бикбаевым на противопоставлении Baseline-а,фиксирующего FULL TABLE SCAN и добавленным позднее индексом:

SQL> create table large_table (c1 number, c2 varchar2 (1000))
  2  /

Table created.

SQL> insert into large_table
  2    select level, 'some fool text and number ' || level
  3      from dual
  4    connect by level < 1000000 5 / 999999 rows created. SQL> exec dbms_stats.gather_table_stats('','LARGE_TABLE')

PL/SQL procedure successfully completed.

SQL> select c2 from large_table where c1 = 402200;

C2
---------------------------------------------------------------------------------
some fool text and number 402200

1 row selected.

SQL> @xplan "" "" "-predicate +alias"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  cgrtc39b07cg4, child number 0
-------------------------------------
select c2 from large_table where c1 = 402200

Plan hash value: 1101256009

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  1643 (100)|          |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |     1 |    38 |  1643   (1)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - SEL$1 / LARGE_TABLE@SEL$1

SQL> -- фиксация плана 
SQL> @bl_create cgrtc39b07cg4 1101256009 "2test_baseline"

Baseline SQL_ff24385815ccded7 SQL_PLAN_gy91sb0awtrqr07ba49cd was [re]created
for SQL_ID=cgrtc39b07cg4, SQL_PLAN_HASH=1101256009

SQL> create index lt_idx on large_table(c1)
  2  /

Index created.

SQL> select c2 from large_table where c1 = 402200;

1 row selected.

SQL> @xplan "" "" "-predicate +alias"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  cgrtc39b07cg4, child number 1
-------------------------------------
select c2 from large_table where c1 = 402200

Plan hash value: 1101256009

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  1643 (100)|          |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |     1 |    38 |  1643   (1)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - SEL$1 / LARGE_TABLE@SEL$1

Note
-----
   - SQL plan baseline SQL_PLAN_gy91sb0awtrqr07ba49cd used for this statement

, 10053 трейс которого отчётливо показывает все 3 фазы разбора:

I. THE OPTIMIZER GENERATES A BEST-COST PLAN
II. SPM REPRODUCE PHASE
III. SPM RE-PARSE SELECTED ACCEPTED PLAN

, первая из которых заканчивается вычислением лучшего плана с Cost = 4 для единственного блока запроса SEL$1:

   1877 Final cost for query block SEL$1 (#0) - All Rows Plan:
   1878   Best join order: 1
   1879   Cost: 4.000751  Degree: 1  Card: 1.000000  Bytes: 38.000000
   1880   Resc: 4.000751  Resc_io: 4.000000  Resc_cpu: 28856
   1881   Resp: 4.000751  Resp_io: 4.000000  Resc_cpu: 28856 -- I. THE OPTIMIZER GENERATES A BEST-COST PLAN finish 
...
   1907 SPM: finding a match for the generated plan, planId = 3291975493 -- II. SPM REPRODUCE PHASE start
   1908 SPM: fixed planId''s of plan baseline are: 129649101
   1909 SPM: using qksan to reproduce, cost and select accepted plan, sig = 18384881529807232727
   1910 SPM: plan reproducibility round 1 (plan outline + session OFE)
   1911 SPM: using qksan to reproduce accepted plan, planId = 129649101

Этот вычисленный лучший план естественно соответствующего индексному доступу:

SQL> select/*no baseline*/ c2 from large_table where c1 = 402200;

1 row selected.

SQL> @xplan "" "" "-predicate"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  gmytx8q0mckat, child number 0
-------------------------------------
select/*no baseline*/ c2 from large_table where c1 = 402200

Plan hash value: 3117982213

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LARGE_TABLE |     1 |    38 |     4   (0)| 00:00:01 |
|   2 |   INDEX RANGE SCAN                  | LT_IDX      |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Другой замечательный тест найден у Tim Hall.SQL Plan Directives in Oracle Database 12c Release 1 (12.1)

DDL с намеренно неравномерным распределением данных/data is deliberately skewed:

SQL> @bl_drop SQL_PLAN_brracysgah35cdc710c2c
Baseline SQL_bbdd4cf61ea80cac SQL_PLAN_brracysgah35cdc710c2c was dropped

SQL> @purge_cu a6g1synp9hm3n

SQL> DROP TABLE tab1 PURGE;

Table dropped.

SQL> CREATE TABLE tab1 (
  2    id               NUMBER,
  3    gender           VARCHAR2(1),
  4    has_y_chromosome VARCHAR2(1),
  5    CONSTRAINT tab1_pk PRIMARY KEY (id),
  6    CONSTRAINT tab1_gender_chk CHECK (gender IN ('M', 'F')),
  7    CONSTRAINT tab1_has_y_chromosome_chk CHECK (has_y_chromosome IN ('Y', 'N'))
  8  );

Table created.

SQL> INSERT /*+ APPEND */ INTO tab1
  2  SELECT level, 'M', 'Y'
  3  FROM   dual
  4  CONNECT BY level <= 10; 10 rows created. SQL> COMMIT;

Commit complete.

SQL> INSERT /*+ APPEND */ INTO tab1
  2  SELECT 10+level, 'F', 'N'
  3  FROM   dual
  4  CONNECT BY level <= 90; 90 rows created. SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX tab1_gender_idx ON tab1(gender);

Index created.

SQL> CREATE INDEX tab1_has_y_chromosome_idx ON tab1(has_y_chromosome);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

PL/SQL procedure successfully completed.

Запрос на основе имеющейся статистики демонстрирует ошибочные предположения:

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y';

10 rows selected.

SQL> @xplan "" "" "allstats last +note"

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  a6g1synp9hm3n, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND
has_y_chromosome = 'Y'

Plan hash value: 1552452781

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |     10 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1            |      1 |     25 |     10 |00:00:00.01 |       4 | -- тут
|*  2 |   INDEX RANGE SCAN                  | TAB1_GENDER_IDX |      1 |     50 |     10 |00:00:00.01 |       2 | -- и тут
-----------------------------------------------------------------------------------------------------------------

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

   1 - filter("HAS_Y_CHROMOSOME"='Y')
   2 - access("GENDER"='M')

Фиксируем план:

SQL> @bl_create a6g1synp9hm3n 1552452781

Baseline SQL_bbdd4cf61ea80cac SQL_PLAN_brracysgah35cdc710c2c was [re]created
for SQL_ID=a6g1synp9hm3n, SQL_PLAN_HASH=1552452781

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y';

10 rows selected.

Plan hash value: 1552452781

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |     10 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1            |      1 |     25 |     10 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | TAB1_GENDER_IDX |      1 |     50 |     10 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline SQL_PLAN_brracysgah35cdc710c2c used for this statement

— выполнение с использованием Baseline и по-прежнему неточными оценками

При последующих выполнениях оценка E-Rows уточняется благодаря Statistics Feedback:

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y';

10 rows selected.

Plan hash value: 1552452781

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |     10 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1            |      1 |     10 |     10 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | TAB1_GENDER_IDX |      1 |     10 |     10 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline SQL_PLAN_brracysgah35cdc710c2c used for this statement
   - statistics feedback used for this statement

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y';

10 rows selected.

Plan hash value: 1552452781

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |     10 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1            |      1 |     10 |     10 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | TAB1_GENDER_IDX |      1 |     10 |     10 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline SQL_PLAN_brracysgah35cdc710c2c used for this statement
   - statistics feedback used for this statement

, что отражается в виде 2-х различных курсоров в V$SQL/V$SQL_SHARED_CURSOR:

SQL> @shared_cu12_noxml a6g1synp9hm3n
 
EXECS ELA_PER_EXEC CURSOR_STATUS  PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1                        SQL_PLAN_BASELINE              SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
----- ------------ -------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ------------------------------- ------------------------------ --------- ---------------- ----------- -----------
    1        39105 VALID               1552452781           3698396204              2     0 N          N          N          Y                  Y               3                                    Auto Reoptimization Mismatch(1) SQL_PLAN_brracysgah35cdc710c2c                                        N
    2         2952 VALID               1552452781           3698396204              2     1 N          N          Y          N                  N                                                                                    SQL_PLAN_brracysgah35cdc710c2c                                        N

, один из которых (последний) использует 3 reoptimization hints:

SQL> select * from v$sql_reoptimization_hints where sql_id = 'a6g1synp9hm3n';
 
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER    HINT_ID HINT_TEXT                                                                         CLIENT_ID    REPARSE     CON_ID
---------------- ---------- ------------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
000007FF075BDC10  714624116 a6g1synp9hm3n            0          1 OPT_ESTIMATE (@"SEL$1" TABLE "TAB1"@"SEL$1" ROWS=10.000000 )                              1          1          0
000007FF075BDC10  714624116 a6g1synp9hm3n            0          2 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "TAB1"@"SEL$1" "TAB1_GENDER_IDX" ROWS=10.000          1          1          0
000007FF075BDC10  714624116 a6g1synp9hm3n            0          3 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "TAB1"@"SEL$1" "TAB1_GENDER_IDX" MIN=10.000000          1          1          0

— которые, судя по трейсу 10053, при разборе курсора на основе Baseline напрямую добавляются к запросу уже в первой фазе parse:

-- I. THE OPTIMIZER GENERATES A BEST-COST PLAN
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "TAB1"@"SEL$1" "TAB1_GENDER_IDX" MIN=10.000000 ) OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "TAB1"@"SEL$1" "TAB1_GENDER_IDX" ROWS=10.000000 ) OPT_ESTIMATE (@"SEL$1" TABLE "TAB1"@"SEL$1" ROWS=10.000000 ) */ "TAB1"."ID" "ID","TAB1"."GENDER" "GENDER","TAB1"."HAS_Y_CHROMOSOME" "HAS_Y_CHROMOSOME" FROM "SCOTT"."TAB1" "TAB1" WHERE "TAB1"."GENDER"='M' AND "TAB1"."HAS_Y_CHROMOSOME"='Y'

Access path analysis for TAB1
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TAB1[TAB1] 
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Column (#2): GENDER(VARCHAR2)
    AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.500000
  Column (#3): HAS_Y_CHROMOSOME(VARCHAR2)
    AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.500000

  Table: TAB1  Alias: TAB1
    Card: Original: 100.000000                                                       -- и способствуют созданию SPD директив, генерируя finding id:
    SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 118176, objtyp = 1, vecsize = 4, colvec = [2, 3, ], fid = 7171040874582585555
    SPD: Modified felem, fid=7171040874582585555, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = YES
    SPD: qosdCreateFindingSingTab retCode = CREATED, fid = 7171040874582585555
    SPD: qosdCreateDirCmp retCode = CREATED, fid = 7171040874582585555
    >> Single Tab Card adjusted from 25.000000 to 10.000000 due to opt_estimate hint -- попутно поправляя Cardinality

 ****** Costing Index TAB1_GENDER_IDX
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
    >> Index Card adjusted from 50.000000 to 50.000000 due to opt_estimate hint
...                                                                                  -- --//--
    SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 118176, objtyp = 1, vecsize = 3, colvec = [2, ], fid = 13387538247227856198
    SPD: Modified felem, fid=13387538247227856198, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = YES
SPD: qosdCreateFind4Idx retCode = CREATED, fid = 13387538247227856198
    SPD: qosdCreateDirCmp retCode = CREATED, fid = 13387538247227856198
    >> Index Card adjusted from 50.000000 to 10.000000 due to opt_estimate hint      -- --//--

SPM: finding a match for the generated plan, planId = 3698396204    -- при этом в окончании фазы I оказывается
SPM: generated plan found in the plan baseline, planId = 3698396204 -- , что сгенерированный CBO план
SPM: generated plan successfully matched, planId = 3698396204       -- совпадает с планом в Baseline *

============
Plan Table
============
--------------------------------------------------------------+-----------------------------------+
| Id  | Operation                            | Name           | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                     |                |       |       |     2 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID BATCHED | TAB1           |    10 |    70 |     2 |  00:00:01 |
| 2   |   INDEX RANGE SCAN                   | TAB1_GENDER_IDX|    10 |       |     1 |  00:00:01 |
--------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("HAS_Y_CHROMOSOME"='Y')
2 - access("GENDER"='M')
 
Content of other_xml column
===========================
  cardinality_feedback: yes -- отмечено использование
  db_version     : 12.1.0.2
  parse_schema   : SCOTT
  plan_hash_full : 3698396204
  plan_hash      : 1552452781
  plan_hash_2    : 3698396204
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TAB1"@"SEL$1" ("TAB1"."GENDER"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TAB1"@"SEL$1")
    END_OUTLINE_DATA
  */

— в этом случае фазы разбора II. SPM REPRODUCE PHASE и III. SPM RE-PARSE SELECTED ACCEPTED PLAN не требуются!

Т.е. если на выходе фазы I. THE OPTIMIZER GENERATES A BEST-COST PLAN получается план, совпадающий с планом в SPM Baseline (*), иначе говоря, если в используемом (ENABLED/ACCEPTED) SPM Baseline зафиксирован A BEST-COST PLAN — фазы II и III не требуются, и разбор/hard parse экономично завершается в один проход

В этот момент, как видно из трейса, на основании Statistics Feedback (Cardinality Misestimate) создаются SPD finding id (неовеществлённая SQL plan directive in the SGA), из которых впоследствии будут сформированы SQL Plan Directives (которые в свою очередь будут стимулировать Automatic Dynamic Statistics), или словами Tim Hall:

Since the statement is marked as reoptimizable, it is likely SQL plan directives has been created

, и далее у него же Statistics Feedback and SQL Plan Directives (How They Interact):

  • In a situation where both statistics feedback and a SQL plan directive have been created in the SGA, but the SQL plan directive *has not* yet been persisted to the SYSAUX tablespace, the statistics feedback is used during reoptimization and the SQL plan directive is ignored

  • In a situation where both statistics feedback and a SQL plan directive have been created, and the SQL plan directive *has* been persisted to the SYSAUX tablespace, the SQL plan directive, and possibly the statistics feedback, is used during reoptimization

, или словами суровой, но справедливой докуметации в части Reoptimization: Statistics Feedback:

At the end of execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution. If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use. The optimizer also creates a SQL plan directive so that other SQL statements can benefit from the information obtained during this initial execution

Процесс создания SQL Plan Directives можно форсировать:

SQL> exec dbms_spd.flush_sql_plan_directive
 
PL/SQL procedure successfully completed

SQL> @spd_obj SCOTT TAB1
 
DIRECTIVE_ID           SPD_TYPE         ENABLED INT_STATE        STATE      REASON                                  TAB_CNT REDUNDANT TABLE_LIST                                                                                           COLUMN_LIST                                                                                          EQ_PRED_ONLY  SIMPLE_COL_PRED_ONLY  IND_ACCESS_BY_JOIN_PRED  FILTER_ON_JOIN_OBJ  CREATED
---------------------- ---------------- ------- ---------------- ---------- ------------------------------------ ---------- --------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ------------- --------------------- ------------------------ ------------------- -----------------
  14620323240738647241 DYNAMIC_SAMPLING YES     NEW              USABLE     SINGLE TABLE CARDINALITY MISESTIMATE          1 NO        SCOTT.TAB1                                                                                           GENDER, HAS_Y_CHROMOSOME                                                                             YES           YES                   NO                       NO                  26.03.16 23:16:04
   3359754040876084039 DYNAMIC_SAMPLING YES     NEW              USABLE     SINGLE TABLE CARDINALITY MISESTIMATE          1 NO        SCOTT.TAB1                                                                                           GENDER                                                                                               YES           YES                   NO                       NO                  26.03.16 23:16:04

, удалить курсор, использующий Statistics Feedback для стимуляции re-parse при след.выполнении:

SQL> @purge_cu a6g1synp9hm3n
 
PL/SQL procedure successfully completed

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y';

10 rows selected.

SQL> @xplan "" "" "allstats last -predicate +note"

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  a6g1synp9hm3n, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND
has_y_chromosome = 'Y'

Plan hash value: 1552452781

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |     10 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1            |      1 |     10 |     10 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | TAB1_GENDER_IDX |      1 |     10 |     10 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL plan baseline SQL_PLAN_brracysgah35cdc710c2c used for this statement
   - 2 Sql Plan Directives used for this statement

— в рез-те новый курсор для уточнения оценки Cardinality наряду с SQL plan baseline использует 2 вышесозданные Sql Plan Directives и, как следствие, запросы Dynamic Statistics и не использует отработавший своё Statistics Feedback:

SQL> @shared_cu12_noxml a6g1synp9hm3n
 
EXECS ELA_PER_EXEC CURSOR_STATU PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1                        SQL_PLAN_BASELINE              SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
----- ------------ ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ------------------------------- ------------------------------ --------- ---------------- ----------- -----------
    1        81048 VALID             1552452781              2     0 N          N          Y          N                  N                       0         2         2        Auto Reoptimization Mismatch(1) SQL_PLAN_brracysgah35cdc710c2c                                        N

Соответственно, в трейсе к запросу уже не применяются подсказки OPT_ESTIMATE:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TAB1"."ID" "ID","TAB1"."GENDER" "GENDER","TAB1"."HAS_Y_CHROMOSOME" "HAS_Y_CHROMOSOME" FROM "SCOTT"."TAB1" "TAB1" WHERE "TAB1"."GENDER"='M' AND "TAB1"."HAS_Y_CHROMOSOME"='Y'
...
Query Block SEL$1 (#0)
Applicable DS directives:
   dirid = 14620323240738647241, state = 1, flags = 1, loc = 1 {EC(118189)[2, 3]}
   dirid = 3359754040876084039, state = 1, flags = 1, loc = 1 {EC(118189)[2]}
...
Access path analysis for TAB1
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TAB1[TAB1] 
  SPD: Directive valid: dirid = 14620323240738647241, state = 1, flags = 1, loc = 1 {EC(118189)[2, 3]}
  SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE       -- , но применяются директивы
  Column (#2): GENDER(VARCHAR2)
    AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.500000
  Column (#3): HAS_Y_CHROMOSOME(VARCHAR2)
    AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.500000

  Table: TAB1  Alias: TAB1                                          -- и запросы ADS:
    Card: Original: 100.000000    >> Single Tab Card adjusted from 25.000000 to 10.000000 due to adaptive dynamic sampling
...
 ****** Costing Index TAB1_GENDER_IDX
  SPD: Directive valid: dirid = 3359754040876084039, state = 1, flags = 1, loc = 1 {EC(118189)[2]}
  SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_SCAN
  SPD: Directive valid: dirid = 3359754040876084039, state = 1, flags = 1, loc = 1 {EC(118189)[2]}
  SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_FILTER
    >> Index Card adjusted from 50.000000 to 10.000000 due to adaptive dynamic sampling
    >> Index Card adjusted from 50.000000 to 10.000000 due to adaptive dynamic sampling

SPM: finding a match for the generated plan, planId = 3698396204    -- и аналогично пред.случаю со Statistics Feedback в конце фазы I
SPM: generated plan found in the plan baseline, planId = 3698396204 -- сгенерированный CBO-based план
SPM: generated plan successfully matched, planId = 3698396204       -- удачно совпадает с планом из Baseline и фазы II-III уже не требуются **

============
Plan Table
============
--------------------------------------------------------------+-----------------------------------+
| Id  | Operation                            | Name           | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                     |                |       |       |     2 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID BATCHED | TAB1           |    10 |    70 |     2 |  00:00:01 |
| 2   |   INDEX RANGE SCAN                   | TAB1_GENDER_IDX|    10 |       |     1 |  00:00:01 |
--------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("HAS_Y_CHROMOSOME"='Y')
2 - access("GENDER"='M')
 
Content of other_xml column
===========================
  db_version     : 12.1.0.2
  parse_schema   : SCOTT
  dynamic_sampling: 2         -- ADS usage
  plan_hash_full : 3698396204
  plan_hash      : 1552452781
  plan_hash_2    : 3698396204
<spd>
<cv>0</cv>
<cu>2</cu>                    -- SPD used
</spd>

В SYS.SQLOBJ$PLAN при этом сохраняется исторический план (на момент создания Baseline) с неуточненными значениями CARDINALITY без следов влияния Statistics Feedback / SQL Plan Directives:

SQL> @spb12 SQL_PLAN_brracysgah35cdc710c2c
 
OPERATION                                OBJECT_OWNER  OBJECT_NAME      OPTIMIZER  COST CARDINALITY
---------------------------------------- ------------- ---------------- ---------- ---- -----------
  SELECT STATEMENT                                                      ALL_ROWS      2            
    TABLE ACCESS BY INDEX ROWID BATCHED  SCOTT         TAB1                           2          25
      INDEX RANGE SCAN                   SCOTT         TAB1_GENDER_IDX                1          50
 
Notes
--------------------------------------------------------------------------------
   sql_profile:
   sql_patch:
   baseline:
   outline:
   dyn_sampling:
   dop:
   dop_reason:
   card_feedback:
   perf_feedback:
   adaptive_plan:
   spd_used:
   spd_valid:
   gtt_sess_stat:
   db_version:      12.1.0.2
   plan_hash_full:  3698396204
   plan_hash:       1552452781
   plan_hash_2:     3698396204

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: