Oracle mechanics

21.05.2017

12c: Dynamic Sampling при индексном доступе к данным

Filed under: 12.2,Oracle — Игорь Усольцев @ 14:33
Tags: ,

В дополнение к старому описанию особенностей DS версии 10.2 — Dynamic Sampling при индексном доступе к данным — на том же тесткейсе наблюдал как измененилось поведение Old Style Dynamic Sampling (DS) и Adaptive Dynamic Sampling (ADS) в новых версиях Oracle 12c

Итак, тестовый DDL без изменений:

alter session set statistics_level=all;
alter system flush shared_pool;
drop table t1
/
create table t1
as
select * from all_objects
where rownum < 10001
/
create index t1_object_type on t1(object_type);
create index t1_created on t1(created);
alter table t1 add constraint t1_pk primary key (object_id);
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T1',method_opt => 'for all columns size 1', cascade => true); -- чтобы DS было чем заняться)
select distinct histogram from user_tab_col_statistics where table_name = 'T1';

1) Old Style Dynamic Sampling, версия 12.1

12.1.0.2.@ SQL> -- использую подсказку INDEX для фиксации плана, позднее будет тест без фиксации
SQL> SELECT --+ INDEX(T11 (OBJECT_TYPE)) DYNAMIC_SAMPLING(4)
  2   t12.status, COUNT(t12.object_id) as CNT
  3    FROM t1 t11, t1 t12
  4   WHERE t11.object_id = t12.object_id
  5     and t11.created >=
  6         TO_DATE('21.07.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  7     AND t12.object_type = 'VIEW'
  8   GROUP BY t12.status
  9  /

STATUS          CNT
-------- ----------
VALID          3619

1 row selected.

SQL> /

STATUS          CNT
-------- ----------
VALID          3619

1 row selected.

SQL> /

STATUS          CNT
-------- ----------
VALID          3619

1 row selected.

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  8husdrhwgr2ph, child number 0
-------------------------------------

Plan hash value: 2010114863

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |       |    30 (100)|          |      1 |
|   1 |  HASH GROUP BY                       |                |      1 |      1 |    21 |    30   (4)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1             |      1 |   3960 | 83160 |    29   (0)| 00:00:01 |   3619 |
|*  3 |    INDEX RANGE SCAN                  | T1_OBJECT_TYPE |      1 |    526 |       |     2   (0)| 00:00:01 |   3619 | -- **
--------------------------------------------------------------------------------------------------------------------------

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$DF69B110")
      ELIMINATE_JOIN(@"SEL$1" "T12"@"SEL$1")                           -- *
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$DF69B110" "T11"@"SEL$1" ("T1"."OBJECT_TYPE"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$DF69B110" "T11"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$DF69B110")
      END_OUTLINE_DATA
  */

Note
-----
   - dynamic statistics used: dynamic sampling (level=4)

— в отличие от 10.2 план значительно изменился благодаря развитию в 11g-12c технологии Join Elimination/JE(*)
В части DS всё, к сожалению, по-прежнему: ошибки на шаге INDEX RANGE SCAN (**) такие же как в 10.2
Кроме того, план стабилен, в смысле не подвержен активностям Adaptive Statistics:

SQL> @shared_cu12_noxml 8husdrhwgr2ph
 
EXECS ROWS_PER_EXEC ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD SHAREABLE  OPTIMIZER_STATS  USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON
----- ------------- ------------ ------------- --------------- -------------- ----- ---------- ---------------- ------------------ --- ----- ----------- --------- --------- -------- --- ----------
    3             1        17212 VALID              2010114863             30     0 Y          N                N                      N                                     4

— т.е. фактически ручной/old style Dynamic Sampling отключает адаптивную цепочку Statistics Feedback => создание SPD директив => Adaptive Dynamic Sampling ввиду избыточноти — применение Dynamic Sampling уже форсировано подсказкой

Если же SPD уже были созданы до выполнения:

SQL> SELECT --+ INDEX(T11 (OBJECT_TYPE)) DYNAMIC_SAMPLING(4)
  2   t12.status, COUNT(t12.object_id) as CNT
  3    FROM t1 t11, t1 t12
  4   WHERE t11.object_id = t12.object_id
  5     and t11.created >=
  6         TO_DATE('21.07.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  7     AND t12.object_type = 'VIEW'
  8   GROUP BY t12.status
  9  /

STATUS         CNT
------- ----------
VALID         3619

1 row selected.

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8husdrhwgr2ph, child number 0
-------------------------------------

Plan hash value: 2010114863

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |       |   196 (100)|          |      1 |
|   1 |  HASH GROUP BY                       |                |      1 |      1 |    21 |   196   (1)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1             |      1 |   3619 | 75999 |   195   (0)| 00:00:01 |   3619 |
|*  3 |    INDEX RANGE SCAN                  | T1_OBJECT_TYPE |      1 |   3619 |       |    10   (0)| 00:00:01 |   3619 | -- ***
--------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=4)
   - 2 Sql Plan Directives used for this statement

— оценки E-Rows в плане этого же запроса становятся много точнее(***) ввиду применения нового ADS, что лишний раз указывает на преимущество Adaptive Dynamic Sampling (3 запроса типа SELECT /* DS_SVC */ на parsing этого запроса) против старого Old Style Dynamic Sampling (с одним запросом типа SELECT /* OPT_DYN_SAMP */)

2) Запрос с фиксацией плана (INDEX hint) без форсирования Dynamic Sampling

SQL> SELECT --+ INDEX(T11 (OBJECT_TYPE))
  2   t12.status, COUNT(t12.object_id) as CNT
  3    FROM t1 t11, t1 t12
  4   WHERE t11.object_id = t12.object_id
  5     and t11.created >=
  6         TO_DATE('21.07.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  7     AND t12.object_type = 'VIEW'
  8   GROUP BY t12.status
  9  /

STATUS          CNT
-------- ----------
VALID          3619

1 row selected.

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1nh89mn197dzh, child number 0
-------------------------------------

Plan hash value: 2010114863

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |       |    30 (100)|          |      1 |
|   1 |  HASH GROUP BY                       |                |      1 |      1 |    21 |    30   (4)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1             |      1 |    526 | 11046 |    29   (0)| 00:00:01 |   3619 | -- ****
|*  3 |    INDEX RANGE SCAN                  | T1_OBJECT_TYPE |      1 |    526 |       |     2   (0)| 00:00:01 |   3619 | -- ****
--------------------------------------------------------------------------------------------------------------------------

— в первый раз выполняется с тем же фиксированным PHV=2010114863, но гораздо менее точными оценками(****), однако в рез-те первого же выполнения генерируется набор Reoptimization Hints:

SQL> select * from v$sql_reoptimization_hints where sql_id = '1nh89mn197dzh';

CHILD_NUMBER HINT_ID HINT_TEXT
------------ ------- -------------------------------------------------------------------------------------
           0       1 OPT_ESTIMATE (@"SEL$1" TABLE "T11"@"SEL$1" ROWS=3619.000000 )
           0       2 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "T11"@"SEL$1" "T1_OBJECT_TYPE" ROWS=3619.000000 )
           0       3 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "T11"@"SEL$1" "T1_OBJECT_TYPE" MIN=3619.000000 )

Флажок V$SQL_SHARED_CURSOR.USE_FEEDBACK_STATS=Y т.о. обозначает факт сбора статистики выполнения / Feedback Statistics Gathering, как и V$SQL.IS_REOPTIMIZABLE(поле REOPT=Y):

SQL> @shared_cu12_noxml 1nh89mn197dzh
 
EXECS ROWS_PER_EXEC ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS DS_LEVEL
----- ------------- ------------ ------------- --------------- -------------- ----- ------------------ --- ----- ----------- --------
    1             1        38091 VALID              2010114863             30     0 Y                      Y               3

Созданные OPT_ESTIMATE подсказки используются во 2-м и далее выполнениях:

SQL> SELECT --+ INDEX(T11 (OBJECT_TYPE))
...
  9  /

STATUS          CNT
-------- ----------
VALID          3619

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID  1nh89mn197dzh, child number 1
-------------------------------------

Plan hash value: 2010114863

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |       |   196 (100)|          |      1 |
|   1 |  HASH GROUP BY                       |                |      1 |      1 |    21 |   196   (1)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1             |      1 |   3619 | 75999 |   195   (0)| 00:00:01 |   3619 |
|*  3 |    INDEX RANGE SCAN                  | T1_OBJECT_TYPE |      1 |   3619 |       |    10   (0)| 00:00:01 |   3619 |
--------------------------------------------------------------------------------------------------------------------------

Note
-----
   - statistics feedback used for this statement

SQL> SELECT --+ INDEX(T11 (OBJECT_TYPE))
...
  9  /

STATUS          CNT
-------- ----------
VALID          3619

1 row selected.

SQL> -- Lets flush SDP!
SQL> exec DBMS_SPD.flush_sql_plan_directive

PL/SQL procedure successfully completed.

SQL> @spd_estat scott t1

OWNER  TABLE_NAME  HISTOGRAMS        DIRECTIVE_ID              INTERNAL_STATE SPD_TEXT                     TYPE             STATE      REDUNDANT ENABLED EXTENSION_NAME
------ ----------- ----------------- ------------------------- -------------- ---------------------------- ---------------- ---------- --------- ------- --------------
SCOTT  T1          CREATED[NONE]         5768727572516077756   NEW___________ {C(SCOTT.T1)[CREATED,        DYNAMIC_SAMPLING USABLE     NO        YES
                   OBJECT_TYPE[NONE]                                          OBJECT_TYPE]}

SCOTT  T1          OBJECT_TYPE[NONE]      921491504636518726   NEW___________ {EC(SCOTT.T1)[OBJECT_TYPE]}  DYNAMIC_SAMPLING USABLE     NO        YES

2 rows selected.

DIRECTIVE_ID              OWNER  TABLE_NAME  TYPE   EQUAL_PRED_ONLY SINGLE_COL_PRED_ONLY IDX_ACC_BY_JOIN_PRED FILTER_ON_JOIN_OBJ
------------------------- ------ ----------- ------ --------------- -------------------- -------------------- ------------------
    5768727572516077756   SCOTT  T1          TABLE  NO              YES          NO                   NO
     921491504636518726   SCOTT  T1          TABLE  YES             YES          NO                   NO

2 rows selected.

SQL> -- and exec query again!
SQL> SELECT --+ INDEX(T11 (OBJECT_TYPE))
...
  9  /

STATUS          CNT
-------- ----------
VALID          3619

1 row selected.

SQL> @shared_cu12_noxml 1nh89mn197dzh
 
EXECS ROWS_PER_EXEC ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS SPD_Valid SPD_Used  DS_LEVEL
----- ------------- ------------ ------------- --------------- -------------- ----- ------------------ --- ----- ----------- --------- --------- --------
    1             1        38091 VALID              2010114863             30     0 Y                      Y               3
    3             1        15135 VALID              2010114863            196     1 N                  yes N

— даже после автоматической или ручной генерации Sql Plan Directives (SPD), создаваемых по основе Feedback Statistics — что само по себе странно — появление SPD не является основанием для пересоздания курсора(?), вплоть до момента re-parsing-а по какой-то другой причине, например, после принудительного удаления курсора из Library Cache:

SQL> @purge_cu 1nh89mn197dzh
 
PL/SQL procedure successfully completed

SQL> SELECT --+ INDEX(T11 (OBJECT_TYPE))
  2   t12.status, COUNT(t12.object_id) as CNT
  3    FROM t1 t11, t1 t12
  4   WHERE t11.object_id = t12.object_id
  5     and t11.created >=
  6         TO_DATE('21.07.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  7     AND t12.object_type = 'VIEW'
  8   GROUP BY t12.status
  9  /

STATUS          CNT
-------- ----------
VALID          3619

1 row selected.

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1nh89mn197dzh, child number 0
-------------------------------------

Plan hash value: 2010114863

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |       |   196 (100)|          |      1 |
|   1 |  HASH GROUP BY                       |                |      1 |      1 |    21 |   196   (1)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1             |      1 |   3619 | 75999 |   195   (0)| 00:00:01 |   3619 |
|*  3 |    INDEX RANGE SCAN                  | T1_OBJECT_TYPE |      1 |   3619 |       |    10   (0)| 00:00:01 |   3619 |
--------------------------------------------------------------------------------------------------------------------------

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

— после чего новый курсор формируется уже под влиянием SPD => Adaptive Dynamic Sampling

SQL> @shared_cu12_noxml 1nh89mn197dzh
 
EXECS ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS SPD_Valid SPD_Used  DS_LEVEL REASON#1
----- ------------ ------------- --------------- -------------- ----- ------------------ --- ----- ----------- --------- --------- -------- -------------------------------
    1        86657 VALID              2010114863            196     0 N                      N                 0         2         2        Auto Reoptimization Mismatch(1)

— штатное поведение Oracle 12.1

3) Тот же тест без закрепления индексного доступа
при первом выполнении кроме ожидаемой неточности в оценках Cardinality:

SQL> SELECT t12.status, COUNT(t12.object_id) as CNT
  2    FROM t1 t11, t1 t12
  3   WHERE t11.object_id = t12.object_id
  4     and t11.created >=
  5         TO_DATE('21.07.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  6     AND t12.object_type = 'VIEW'
  7   GROUP BY t12.status
  8  /

STATUS         CNT
------- ----------
VALID         3619

1 row selected.

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID  gsnrraa59zvkg, child number 0
-------------------------------------

Plan hash value: 2010114863

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |       |    30 (100)|          |      1 |
|   1 |  HASH GROUP BY                       |                |      1 |      1 |    21 |    30   (4)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1             |      1 |    526 | 11046 |    29   (0)| 00:00:01 |   3619 |
|*  3 |    INDEX RANGE SCAN                  | T1_OBJECT_TYPE |      1 |    526 |       |     2   (0)| 00:00:01 |   3619 |
--------------------------------------------------------------------------------------------------------------------------

, аналогично предыдущему тесту генерирует набор реоптимизационных хинтов:

SQL> select * from v$sql_reoptimization_hints where sql_id = 'gsnrraa59zvkg';

SQL_ID        CHILD_NUMBER    HINT_ID HINT_TEXT
------------- ------------ ---------- -------------------------------------------------------------------------------------
gsnrraa59zvkg            0          1 OPT_ESTIMATE (@"SEL$1" TABLE "T11"@"SEL$1" ROWS=3619.000000 )
gsnrraa59zvkg            0          2 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "T11"@"SEL$1" "T1_OBJECT_TYPE" ROWS=3619.000000 )
gsnrraa59zvkg            0          3 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "T11"@"SEL$1" "T1_OBJECT_TYPE" MIN=3619.000000 )

3 rows selected.

, в точности так же используемых в виде statistics feedback 2-го выполнения:

SQL> SELECT t12.status, COUNT(t12.object_id) as CNT
  2    FROM t1 t11, t1 t12
  3   WHERE t11.object_id = t12.object_id
  4     and t11.created >=
  5         TO_DATE('21.07.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  6     AND t12.object_type = 'VIEW'
  7   GROUP BY t12.status
  8  /

STATUS         CNT
------- ----------
VALID         3619

1 row selected.

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gsnrraa59zvkg, child number 1
-------------------------------------

Plan hash value: 136660032

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    48 (100)|          |      1 |
|   1 |  HASH GROUP BY     |      |      1 |      1 |    21 |    48   (3)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   3619 | 75999 |    47   (0)| 00:00:01 |   3619 |
----------------------------------------------------------------------------------------------

Note
-----
   - statistics feedback used for this statement

— в процессе parsing-а в 10053 трейсе отражается применение OPT_ESTIMATE хинтов (>>) и формирование где-то в памяти SPD finding-ов (SPD: Generating finding), на основании которых впоследствии будут оформлены SQL Plan Directives:

Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T1[T11] 
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE -- пока SPD нет
  Column (#6): OBJECT_TYPE(VARCHAR2)
    AvgLen: 7 NDV: 19 Nulls: 0 Density: 0.052632

  Column (#7): CREATED(DATE)
    AvgLen: 8 NDV: 163 Nulls: 0 Density: 0.006135 Min: 0.000000 Max: 2456912.361690
  Table: T1  Alias: T11
    Card: Original: 10000.000000
    SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 132938, objtyp = 1, vecsize = 8, colvec = [6, 7, ], fid = 18218626916616538946
    SPD: Modified felem, fid=18218626916616538946, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = YES
    SPD: qosdCreateFindingSingTab retCode = CREATED, fid = 18218626916616538946
    SPD: qosdCreateDirCmp retCode = CREATED, fid = 18218626916616538946
    >> Single Tab Card adjusted from 526.315789 to 3619.000000 due to opt_estimate hint
  Rounded: 3619  Computed: 3619.000000  Non Adjusted: 526.315789
...
  Access Path: TableScan
    Cost:  47.120417  Resp: 47.120417  Degree: 0 --*
...
 ****** Costing Index T1_CREATED
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN   -- пока SPD нет
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  Access Path: index (RangeScan)
    Index: T1_CREATED
    resc_io: 182.000000  resc_cpu: 7096102
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 182.184731  Resp: 182.184731  Degree: 1
 ****** Costing Index T1_OBJECT_TYPE
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
    >> Index Card adjusted from 526.315789 to 3619.000000 due to opt_estimate hint

    SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 132938, objtyp = 1, vecsize = 7, colvec = [6, ], fid = 4914354623886680098
    SPD: Modified felem, fid=4914354623886680098, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = YES
SPD: qosdCreateFind4Idx retCode = CREATED, fid = 4914354623886680098
    SPD: qosdCreateDirCmp retCode = CREATED, fid = 4914354623886680098
    >> Index Card adjusted from 526.315789 to 3619.000000 due to opt_estimate hint
  Access Path: index (AllEqRange)
    Index: T1_OBJECT_TYPE
    resc_io: 195.000000  resc_cpu: 3488551
    ix_sel: 0.361900  ix_sel_with_filters: 0.361900 
    Cost: 195.090816  Resp: 195.090816  Degree: 1 --**

— с учётом opt_estimate hint adjustments стоимость сканирования таблицы (*) оказывается кратно меньше индекса (**), что пока выглядит логично

Однако последующие выполнения (вплоть до генерации SPD в бд), возможно, ввиду существенного изменения плана + применения Join Elimination (SR required?) уже не используют рез-тов реоптимизации, возвращаясь к первоначальному неуточнённому / нереоптимизированному плану, основанному на умышленно недостаточной (без гистограмм) статистике:

SQL> SELECT t12.status, COUNT(t12.object_id) as CNT
...
  8  /

STATUS         CNT
------- ----------
VALID         3619

1 row selected.

SQL> /

STATUS         CNT
------- ----------
VALID         3619

1 row selected.

SQL> /

STATUS         CNT
------- ----------
VALID         3619

1 row selected.

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gsnrraa59zvkg, child number 2
-------------------------------------

Plan hash value: 2010114863

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |       |    30 (100)|          |      1 |
|   1 |  HASH GROUP BY                       |                |      1 |      1 |    21 |    30   (4)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1             |      1 |    526 | 11046 |    29   (0)| 00:00:01 |   3619 |
|*  3 |    INDEX RANGE SCAN                  | T1_OBJECT_TYPE |      1 |    526 |       |     2   (0)| 00:00:01 |   3619 |
--------------------------------------------------------------------------------------------------------------------------

SQL> @shared_cu12_noxml gsnrraa59zvkg
 
EXECS ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD SHAREABLE USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS SPD_Valid SPD_Used  DS_LEVEL REASON#1
----- ------------ ------------- --------------- -------------- ----- --------- ------------------ --- ----- ----------- --------- --------- -------- -------------------------------
    1        17407 VALID              2010114863             30     0 N         Y                      Y               3                              Auto Reoptimization Mismatch(1)
    1         9624 VALID               136660032             48     1 N         Y                  yes Y                                              Auto Reoptimization Mismatch(1)
    3        15560 VALID              2010114863             30     2 Y         N                      N

— причина отказа от реоптимизации REOPT=N (V$SQL.IS_REOPTIMIZABLE) и, как следствие, повторного неиспользования реоптимизированного курсора CHILD=1 из V$SQL_SHARED_CURSOR / V$SQL неясна, cursortrace level 580 добавляет к пониманию немного(:

 Checking for already pinned child. fbcflg 108
 No valid child pinned
...
 kksCheckCursor: next child is #1
 kksCheckCursor: pinning child #1 in shared mode 000007FF0BAF4FD0 000007FF0EF54DD0
 Compilation environment difference Failed sharing : 0 -- ???
...
 kksCheckCursor: next child is #0
Child marked as do not use                             -- причину неиспользования child #0 понять можно как логичное следствие реоптимизации
...

        CursorDiagnosticsNodes:  
          ChildNode:  ChildNumber=1 ID=49 reason=Auto Reoptimization Mismatch(1) size=3x4 kxscflg=32 kxscfl4=4194560 dnum_kksfcxe=1 -- содержимое V$SQL_SHARED_CURSOR.OTHER_XML
          ChildNode:  ChildNumber=0 ID=49 reason=Auto Reoptimization Mismatch(1) size=3x4 kxscflg=32 kxscfl4=4194560 dnum_kksfcxe=0 -- --//--

— и этот странный неоптимизированный курсор CHILD=2 используется вплоть до генерации директив в бд:

SQL> exec DBMS_SPD.flush_sql_plan_directive

PL/SQL procedure successfully completed.

SQL> @spd_estat scott t1

OWNER  TABLE_NAME HISTOGRAMS        DIRECTIVE_ID              INTERNAL_STATE SPD_TEXT                     TYPE             STATE      REDUNDANT ENABLED EXTENSION_NAME
------ ---------- ----------------- ------------------------- -------------- ---------------------------- ---------------- ---------- --------- ------- --------------
SCOTT  T1         CREATED[NONE]        12040936354421329804   NEW___________ {C(SCOTT.T1)[CREATED,        DYNAMIC_SAMPLING USABLE     NO        YES
                  OBJECT_TYPE[NONE]                                          OBJECT_TYPE]}

SCOTT  T1         OBJECT_TYPE[NONE]      313456920416436233   NEW___________ {EC(SCOTT.T1)[OBJECT_TYPE]}  DYNAMIC_SAMPLING USABLE     NO        YES

DIRECTIVE_ID              OWNER  TABLE_NAME TYPE   EQUAL_PRED_ONLY SINGLE_COL_PRED_ONLY IDX_ACC_BY_JOIN_PRED FILTER_ON_JOIN_OBJ
------------------------- ------ ---------- ------ --------------- -------------------- -------------------- ------------------
   12040936354421329804   SCOTT  T1         TABLE  NO              YES          NO                   NO
     313456920416436233   SCOTT  T1         TABLE  YES             YES          NO                   NO

— после чего стандартно под влиянием SPD -> ADS формируется точный в оценках Cost-Based изменённый план:

SQL> @purge_cu gsnrraa59zvkg

PL/SQL procedure successfully completed.

SQL> SELECT t12.status, COUNT(t12.object_id) as CNT
  2    FROM t1 t11, t1 t12
  3   WHERE t11.object_id = t12.object_id
  4     and t11.created >=
  5         TO_DATE('21.07.2008 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  6     AND t12.object_type = 'VIEW'
  7   GROUP BY t12.status
  8  /

STATUS         CNT
------- ----------
VALID         3619

1 row selected.

SQL> @xplan "" "" "all allstats advanced last"

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gsnrraa59zvkg, child number 0
-------------------------------------

Plan hash value: 136660032

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    48 (100)|          |      1 |
|   1 |  HASH GROUP BY     |      |      1 |      1 |    21 |    48   (3)| 00:00:01 |      1 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   3619 | 75999 |    47   (0)| 00:00:01 |   3619 |
----------------------------------------------------------------------------------------------

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

— т.о. в версии 12.1 эта наблюдаемая фича/баг(?) кроме генерации на промежуточном этапе доп.курсоров не оказывает влияния на итоговый план при default adaptive features параметрах, позволяющих адаптивной связке SPD -> ADS в конечном счёте исправить ситуацию)

4) В Oracle 12.2 рез-т первых 3-х выполнений будет в точности совпадать с 12.1:

12.2.0.1.@ SQL> @shared_cu12_noxml gsnrraa59zvkg
 
EXECS ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD SHAREABLE USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS REASON#1                       
----- ------------ ------------- --------------- -------------- ----- --------- ------------------ --- ----- ----------- -------------------------------
    1        31007 VALID              2010114863             35     0 N         Y                      Y               3 Auto Reoptimization Mismatch(1)
    1        26632 VALID               136660032             53     1 N         Y                  yes Y                 Auto Reoptimization Mismatch(1)
    3        22044 VALID              2010114863             35     2 Y         N                      N

и даже директивы по рез-там CF успешно создаются — не запрещено:

SQL> exec DBMS_SPD.flush_sql_plan_directive

PL/SQL procedure successfully completed.

SQL> @spd_estat scott t1

OWNER  TABLE_NAME HISTOGRAMS        DIRECTIVE_ID              INTERNAL_STATE SPD_TEXT                    TYPE             STATE  REDUNDANT ENABLED EXTENSION_NAME
------ ---------- ----------------- ------------------------- -------------- --------------------------- ---------------- ------ --------- ------- --------------
SCOTT  T1         CREATED[NONE]        13708907266590020226   NEW___________ {C(SCOTT.T1)[CREATED,       DYNAMIC_SAMPLING USABLE NO        YES
                  OBJECT_TYPE[NONE]                                          OBJECT_TYPE]}

SCOTT  T1         OBJECT_TYPE[NONE]    16832696994617203794   NEW___________ {EC(SCOTT.T1)[OBJECT_TYPE]} DYNAMIC_SAMPLING USABLE NO        YES

DIRECTIVE_ID              OWNER  TABLE_NAME TYPE  EQUAL_PRED_ONLY SINGLE_COL_PRED_ONLY IDX_ACC_BY_JOIN_PRED FILTER_ON_JOIN_OBJ
------------------------- ------ ---------- ----- --------------- -------------------- -------------------- ------------------
   13708907266590020226   SCOTT  T1         TABLE NO              YES          NO                   NO
   16832696994617203794   SCOTT  T1         TABLE YES             YES          NO                   NO

но, в отличие от 12.1 после re-parsing новые курсоры будут опять создавать reoptimization hits, использовать Statistics Feedback, отказываться от реоптимизации и далее — по кругу, обильно генерирую новые курсоры:

SQL> @shared_cu12_noxml gsnrraa59zvkg
 
EXECS ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD SHAREABLE  USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS SPD_Valid SPD_Used  DS_LEVEL REASON#1
----- ------------ ------------- --------------- -------------- ----- ---------- ------------------ --- ----- ----------- --------- --------- -------- -------------------------------
    1        27051 VALID              2010114863             35     0 N          Y                      Y               3                              Auto Reoptimization Mismatch(1)
    1        20390 VALID               136660032             53     1 N          Y                  yes Y                                              Auto Reoptimization Mismatch(1)
    3        22150 VALID              2010114863             35     2 Y          N                      N

— и не используя созданные SPD директивы в полном соответствии с модифицированной конфигурацией Adaptive Statistics 12.2:

SQL> -- Statistics Feedback собирается и используется (для одной таблицы), как и было в 12.1
SQL> @param_ feedback
 
NAME                            VALUE  IS_DEF   IS_MOD     IS_ADJ   DSC
------------------------------- ------ -------- ---------- -------- -------------------------------------------------------
_optimizer_gather_feedback      TRUE   TRUE     FALSE      FALSE    optimizer gather feedback
_optimizer_use_feedback         TRUE   TRUE     FALSE      FALSE    optimizer use feedback
 
SQL> --, и по-прежнему генерирует неиспользуемые директивы, которые не приводят к запуску Adaptive Dynamic Sampling и изменениям планов в соответствии с default значением:
SQL> @param_ dsdir
 
NAME                            VALUE  IS_DEF   IS_MOD     IS_ADJ   DSC
------------------------------- ------ -------- ---------- -------- -------------------------------------------------------
_optimizer_dsdir_usage_control  0      TRUE     FALSE      FALSE    controls optimizer usage of dynamic sampling directives

— из цепочки адаптивной оптимизации исключено финальное звено SPD => ADS by default

Реклама

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

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

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 такие блоггеры, как: