Oracle mechanics

30.07.2013

Cardinality Feedback: многочисленные версии курсоров с повторяющимся планом выполнения

Практический запрос запрос (11.2.0.3 Linunx x86_64), по причине «удачного» совпадения условий стабильно генерирует дополнительные курсоры с повторяющимся планом выполнения под влиянием Cardinality Feedback (CF):

11.2.0.3.@ SQL> alter session set statistics_level=all;

Session altered.

SQL> SELECT sum(view_sp_credit.spent) AS spent,
  2         sum(view_sp_credit.spent_currency) AS spent_currency,
  3         view_sp_credit.product_id
  4    FROM view_sp_credit
  5   WHERE view_sp_credit.contract_id = 48746
  6   GROUP BY view_sp_credit.product_id
  7  /

2 rows selected.

Elapsed: 00:00:06.17

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','',format => 'all allstats advanced last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7a0stv191ztsb, child number 0 --при первом выполнении создаётся 0-ая версия курсора со значительными различиями в плане и статистике выполнения
-------------------------------------

Plan hash value: 3758890385

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |      1 |        |       |   193K(100)|          |      2 |00:00:06.09 |    1305K|     39 |
|   1 |  HASH GROUP BY                       |                           |      1 |    868 | 43400 |   193K  (1)| 00:35:26 |      2 |00:00:06.09 |    1305K|     39 |
|   2 |   NESTED LOOPS                       |                           |      1 |        |       |            |          |   9445 |00:00:06.08 |    1305K|     39 |
|   3 |    NESTED LOOPS                      |                           |      1 |   1482 | 74100 | 10744   (1)| 00:01:59 |   9445 |00:00:05.92 |    1295K|     39 |
|   4 |     NESTED LOOPS                     |                           |      1 |   1482 | 57798 |  9262   (1)| 00:01:42 |   9445 |00:00:05.85 |    1276K|     39 |
|   5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |      1 |   9875 |   183K|  1310   (1)| 00:00:15 |    115K|00:00:00.38 |   74138 |      0 | -- здесь сравниваются E-Rows и A-Rows
|*  6 |       INDEX RANGE SCAN               | INV_CONTRACT_CREDIT_INDEX |      1 |   9875 |       |    53   (0)| 00:00:01 |    115K|00:00:00.06 |     760 |      0 | -- , получая оценки для CF
|*  7 |      TABLE ACCESS BY INDEX ROWID     |                           |    115K|      1 |    20 |     1   (0)| 00:00:01 |   9445 |00:00:05.41 |    1202K|     39 | -- дальнейшая часть плана интереса не представляет
|*  8 |       INDEX RANGE SCAN               |                           |    115K|      1 |       |     0   (0)|          |   9445 |00:00:03.91 |    1195K|     10 |
|   9 |        MERGE JOIN CARTESIAN          |                           |    115K|      1 |    42 |     9   (0)| 00:00:01 |    107K|00:00:02.46 |     860K|      0 |
|  10 |         NESTED LOOPS                 |                           |    115K|        |       |            |          |    107K|00:00:01.23 |     574K|      0 |
|  11 |          NESTED LOOPS                |                           |    115K|      1 |    28 |     5   (0)| 00:00:01 |    108K|00:00:00.93 |     466K|      0 |
|  12 |           TABLE ACCESS BY INDEX ROWID|                           |    115K|      1 |    12 |     4   (0)| 00:00:01 |    108K|00:00:00.57 |     279K|      0 |
|* 13 |            INDEX RANGE SCAN          |                           |    115K|      1 |       |     3   (0)| 00:00:01 |    108K|00:00:00.38 |     171K|      0 |
|* 14 |           INDEX UNIQUE SCAN          |                           |    108K|      1 |       |     0   (0)|          |    108K|00:00:00.29 |     186K|      0 |
|* 15 |          TABLE ACCESS BY INDEX ROWID |                           |    108K|      1 |    16 |     1   (0)| 00:00:01 |    107K|00:00:00.22 |     108K|      0 |
|  16 |         BUFFER SORT                  |                           |    107K|      1 |    14 |     8   (0)| 00:00:01 |    107K|00:00:01.08 |     285K|      0 |
|* 17 |          TABLE ACCESS BY INDEX ROWID |                           |    107K|      1 |    14 |     4   (0)| 00:00:01 |    107K|00:00:00.65 |     285K|      0 |
|* 18 |           INDEX RANGE SCAN           |                           |    107K|      1 |       |     3   (0)| 00:00:01 |    107K|00:00:00.41 |     178K|      0 |
|* 19 |     INDEX UNIQUE SCAN                |                           |   9445 |      1 |       |     0   (0)|          |   9445 |00:00:00.06 |   18879 |      0 |
|  20 |    TABLE ACCESS BY INDEX ROWID       |                           |   9445 |      1 |    11 |     1   (0)| 00:00:01 |   9445 |00:00:00.15 |    9465 |      0 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   6 - access("INV"."CONTRACT_ID"=48746 AND "INV"."CREDIT"=2)
...

SQL> SELECT sum(view_sp_credit.spent) AS spent,
...
  7  /

2 rows selected.

Elapsed: 00:00:04.04

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  7a0stv191ztsb, child number 1 -- повторное выполнения генерирует новый дочерний курсор
-------------------------------------

Plan hash value: 3758890385           -- с тем же планом выполнения

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |       |       |  3156K(100)|          |
|   1 |  HASH GROUP BY                       |                           |     2 |   100 |  3156K  (1)| 09:38:43 |
|   2 |   NESTED LOOPS                       |                           |       |       |            |          |
|   3 |    NESTED LOOPS                      |                           | 17301 |   844K|   125K  (1)| 00:22:58 |
|   4 |     NESTED LOOPS                     |                           | 17301 |   658K|   107K  (1)| 00:19:48 |
|   5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |   115K|  2139K| 15107   (1)| 00:02:47 |-- изменились оценки Rows для таблицы
|*  6 |       INDEX RANGE SCAN               | INV_CONTRACT_CREDIT_INDEX |   114K|       |   577   (1)| 00:00:07 |-- изменились оценки Rows для индекса
...
------------------------------------------------------------------------------------------------------------------

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

   6 - access("INV"."CONTRACT_ID"=48746 AND "INV"."CREDIT"=2)
...

Note
-----
   - cardinality feedback used for this statement

— хорошо заметно, что для второго курсора child number 1 в качестве cardinality (Rows) при построении плана в строках строк 5,6 взяты данные статистики (A-Rows) первого выполнения , а увеличившаяся оценка времени выполнения (Time) — результат расчётов оптимизатора без учёта полного кэширования блоков таблицы INVOICEES и индекса INV_CONTRACT_CREDIT_INDEX — судя невысокому по количеству физических чтений Reads в статистике 1-го выполнения

Проблема — в излишней генерации курсоров с полностью совпадающими планами выполнения, за исключением только расчётных величин — Rows, Bytes, Cost, Time вследствие применения CF:

SQL> @shared_cu 7a0stv191ztsb

EXECS LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST  CHILD SHAREABLE USE_FEEDBACK_STATS REASON1
----- ------------------- ------------ --------------- -------------- ------ --------- ------------------ -------------------------
    1 24.07.2013 09:56:38      6144160      3758890385         193263      0 N         Y                  Optimizer mismatch(13)  |
    1 24.07.2013 09:58:33      4876285      3758890385        3156558      1 Y         N                  

Поведение является штатной для CF реакцией на существенное отличие предполагаемой (E-Rows) и актуальной (A-Rows) статистики выполнения запроса, и не является (проверил) побочным следствием ни использования в плане операции MERGE JOIN CARTESIAN (что можно было бы предположить), ни использования в запросе, например, конструкции GROUP BY (что молго бы быть одним из ряда существующих багов CF) — т.е. наблюдается классический CF

Dynamic Sampling

Любопытно, что величины Rows, Bytes, и, как следствие, Cost, Time «вновь создаваемого» технологией CF плана могут браться как из актуальной статистики выполнения (в полном соответствии с описанием технологии CF), так и из данных Dynamic Sampling — при определённых условиях
Например, для того же запроса при optimizer_dynamic_sampling=4:

SQL> SELECT /*+ dynamic_sampling(4)*/
...
  8  /

2 rows selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  fvcvuscm2x7mq, child number 0
-------------------------------------

Plan hash value: 3758890385

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |      1 |        |       |  6657K(100)|          |      2 |00:00:04.11 |    1316K|
|   1 |  HASH GROUP BY                       |                           |      1 |    868 | 43400 |  6657K  (1)| 20:20:36 |      2 |00:00:04.11 |    1316K|
|   2 |   NESTED LOOPS                       |                           |      1 |        |       |            |          |  10995 |00:00:04.10 |    1316K|
|   3 |    NESTED LOOPS                      |                           |      1 |  35993 |  1757K|   260K  (1)| 00:47:43 |  10995 |00:00:04.07 |    1305K|
|   4 |     NESTED LOOPS                     |                           |      1 |  35993 |  1370K|   224K  (1)| 00:41:07 |  10995 |00:00:04.02 |    1283K|
|   5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |      1 |    239K|  4444K| 31520   (1)| 00:05:47 |    116K|00:00:00.30 |   74946 | -- оценки E-Rows отличаются от A-Rows в 2+ раза
|*  6 |       INDEX RANGE SCAN               | INV_CONTRACT_CREDIT_INDEX |      1 |    239K|       |  1233   (1)| 00:00:14 |    116K|00:00:00.06 |     770 | -- --//--
...
------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=4)

SQL> SELECT /*+ dynamic_sampling(4)*/
...
  8  /

2 rows selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  fvcvuscm2x7mq, child number 1
-------------------------------------

Plan hash value: 3758890385

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |       |       |  3195K(100)|          |
|   1 |  HASH GROUP BY                       |                           |     2 |   100 |  3195K  (1)| 09:45:55 |
|   2 |   NESTED LOOPS                       |                           |       |       |            |          |
|   3 |    NESTED LOOPS                      |                           | 17512 |   855K|   126K  (1)| 00:23:12 |
|   4 |     NESTED LOOPS                     |                           | 17512 |   666K|   108K  (1)| 00:19:59 |
|   5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |   116K|  2162K| 15176   (1)| 00:02:47 | -- Rows из статистики A-Rows
|*  6 |       INDEX RANGE SCAN               | INV_CONTRACT_CREDIT_INDEX |   115K|       |   595   (1)| 00:00:07 | -- --//--
...
------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=4)
   - cardinality feedback used for this statement

— для плана 2-го выполнения данные взяты из статистики A-Rows первого, видимо, поскольку оценка E-Rows=239K значительно отличается от статистики A-Rows=116K, имхо

Но уже при при optimizer_dynamic_sampling=5 точность оценки становится достаточно высокой:

SQL> SELECT /*+ dynamic_sampling(5)*/
...
  8  /

2 rows selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5wq9uzsynp8a8, child number 0
-------------------------------------

Plan hash value: 3758890385

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |      1 |        |       |  3747K(100)|          |      2 |00:00:04.10 |    1316K|
|   1 |  HASH GROUP BY                       |                           |      1 |    868 | 43400 |  3747K  (1)| 11:27:07 |      2 |00:00:04.10 |    1316K|
|   2 |   NESTED LOOPS                       |                           |      1 |        |       |            |          |  10995 |00:00:04.09 |    1316K|
|   3 |    NESTED LOOPS                      |                           |      1 |  20458 |   998K|   147K  (1)| 00:27:08 |  10995 |00:00:04.06 |    1305K|
|   4 |     NESTED LOOPS                     |                           |      1 |  20458 |   779K|   127K  (1)| 00:23:22 |  10995 |00:00:04.02 |    1283K|
|   5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |      1 |    136K|  2526K| 17917   (1)| 00:03:18 |    116K|00:00:00.29 |   74946 | -- здесь
|*  6 |       INDEX RANGE SCAN               | INV_CONTRACT_CREDIT_INDEX |      1 |    136K|       |   702   (1)| 00:00:08 |    116K|00:00:00.06 |     770 | -- и здесь
...
------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=5)

SQL> SELECT /*+ dynamic_sampling(5)*/
  ...
  8  /

2 rows selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  5wq9uzsynp8a8, child number 1
-------------------------------------

Plan hash value: 3758890385

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |       |       |  3747K(100)|          |
|   1 |  HASH GROUP BY                       |                           |     2 |   100 |  3747K  (1)| 11:27:07 |
|   2 |   NESTED LOOPS                       |                           |       |       |            |          |
|   3 |    NESTED LOOPS                      |                           | 20458 |   998K|   147K  (1)| 00:27:08 |
|   4 |     NESTED LOOPS                     |                           | 20458 |   779K|   127K  (1)| 00:23:22 |
|   5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |   136K|  2526K| 17917   (1)| 00:03:18 |
|*  6 |       INDEX RANGE SCAN               | INV_CONTRACT_CREDIT_INDEX |   136K|       |   702   (1)| 00:00:08 |
...
------------------------------------------------------------------------------------------------------------------

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

   6 - access("INV"."CONTRACT_ID"=48746 AND "INV"."CREDIT"=2)
...

Note
-----
   - dynamic sampling used for this statement (level=5)
   - cardinality feedback used for this statement

— и при построении плана второго выполнения вводные Rows и Bytes взяты из оценки, полученной при выполнении dynamic sampling запросов, зафиксированной в значении E-Rows 1-го плана выполнения, а не из реальной статистики A-Rows

Т.о. при адекватной оценке (начиная с определённого уровня dynamic_sampling), CF предпочитает изпользовать данные dynamic sampling, а не статистику реального выполнения запроса!

Причины

применения CF в описываемом случае достаточно понятны — классическая неравномерность распределения кол-ва строк таблицы INVOICEES по паре столбцов (CONTRACT_ID, CREDIT), используемой в качестве условия Access Predicate доступа по индексу INV_CONTRACT_CREDIT_INDEX(CONTRACT_ID,CREDIT):

SQL> select nvl(nvl(to_char(CONTRACT_ID), 'Null'), 'All Contracts') as CONTRACT_ID,
  2         nvl(to_char(CREDIT), 'All Credits') as CREDIT,
  3         count(distinct CONTRACT_ID),
  4         count(*)
  5    from INVOICEES
  6   group by rollup(CREDIT, nvl(to_char(CONTRACT_ID), 'Null'))
  7  having count(*) > 10000
  8   order by count(*) desc
  9  /

CONTRACT_ID     CREDIT        COUNT(DISTINCTCONTRACT_ID)   COUNT(*)
--------------- ------------- -------------------------- ----------
All Contracts   All Credits                        13666   22073491
All Contracts   0                                  13344   20009474
Null            0                                      0   19218154 -- огромное кол-во строк с CONTRACT_ID=NULL
All Contracts   2                                   1332    1803778
61154           2                                      1     694575
All Contracts   1                                   1328     260239
49989           2                                      1     216588
48746           2                                      1     116558 -- условия тестируемого запроса
48165           2                                      1      52732
56002           2                                      1      35136
48649           2                                      1      19761

, при этом по отдельности столбцы имеют максимально доступную в версии 11g статистику:

SQL> select * from dba_tab_col_statistics where table_name = 'INVOICEES' and column_name in ('CREDIT','CONTRACT_ID');

COLUMN_NAME  NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS HISTOGRAM
------------ ------------ ---------- ---------- ----------- ------------- ----------- ------------ ---------------
CREDIT                  3 2,27852147          0           3 23.07.2013 10       18848 YES          FREQUENCY
CONTRACT_ID         13666 0,00108813   19226817         254 23.07.2013 10       11091 YES          HEIGHT BALANCED

Поиски workaround-а:

1. Логичным было бы собрать расширенную статистику по группе столбцов (CREDIT,CONTRACT_ID):

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('', 'INVOICEES', method_opt => 'FOR COLUMNS (CONTRACT_ID,CREDIT)');

PL/SQL procedure successfully completed

SQL> select * from dba_stat_extensions where table_name = 'INVOICEES'
  2  /

TABLE_NAME                     EXTENSION_NAME                 EXTENSION                CREATOR DROPPABLE
------------------------------ ------------------------------ ------------------------ ------- ---------
INVOICEES                      SYS_STU$MDELYW7YG_C19JBFG38XJ0 ("CONTRACT_ID","CREDIT") USER    YES

SQL> select * from dba_tab_col_statistics where table_name = 'INVOICEES' and column_name = 'SYS_STU$MDELYW7YG_C19JBFG38XJ0'
  2  /

TABLE_NAME COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED AVG_COL_LEN HISTOGRAM
---------- ------------------------------ ------------ ---------- ---------- ----------- ------------- ----------- ---------------
INVOICEES  SYS_STU$MDELYW7YG_C19JBFG38XJ0        15998 0,00131061          0          75 24.07.2013 16          12 HEIGHT BALANCED

— после чего в плане первого выполнения появляются значительно менее точные оценки Rows, чем до создания Extended Statistics:

Plan hash value: 3758890385

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |       |       |   248 (100)|          |
|   1 |  HASH GROUP BY                       |                           |    45 |  2250 |   248   (1)| 00:00:03 |
|   2 |   NESTED LOOPS                       |                           |       |       |            |          |
|   3 |    NESTED LOOPS                      |                           |     2 |   100 |    23   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |                           |     2 |    78 |    21   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |    17 |   323 |     7   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN               | INV_CONTRACT_CREDIT_INDEX |    17 |       |     4   (0)| 00:00:01 |
...
------------------------------------------------------------------------------------------------------------------

— эффект не зависит от объёма гистограммы (использование DBMS_STATS.GATHER_TABLE_STATS(», ‘INVOICEES’, method_opt => ‘FOR COLUMNS (CONTRACT_ID,CREDIT) SIZE 254’) не меняет план) и объясняется тем, что кол-во строк таблицы INVOICEES, удовлетворяющих условию запроса, сравненимо с минимальным размером бакета:

SQL> select round(count(*)/254) as "MIN_BUCKET_SIZE/ROW_COUNT" from INVOICEES
  2  union all
  3  select count(*) from INVOICEES where CONTRACT_ID=48746 AND CREDIT=2
  4  /

MIN_BUCKET_SIZE/ROW_COUNT
-------------------------
                    86908
                   116573

— и в этом месте увеличение максимального размера гистограммы до 2000 бакетов, обещанное в Oracle 12c было бы очень кстати. Стоит также заметить, что при построении плана в присутствии статистики по группе столбцов оптимизатор не учитывает имеющуюся штатную статистику по отдельным столбцам таблицы

Пока же (в версии 11.2) повторное выполнение запроса по-прежнему генерирует тот же план с уточнёнными оценками:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  aaqr5x0hkabru, child number 1
-------------------------------------

Plan hash value: 3758890385

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |       |       |  3162K(100)|          |
|   1 |  HASH GROUP BY                       |                           |     2 |   100 |  3162K  (1)| 09:39:44 |
|   2 |   NESTED LOOPS                       |                           |       |       |            |          |
|   3 |    NESTED LOOPS                      |                           | 17328 |   846K|   125K  (1)| 00:23:05 |
|   4 |     NESTED LOOPS                     |                           | 17328 |   659K|   108K  (1)| 00:19:54 |
|   5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |   115K|  2142K| 15531   (1)| 00:02:51 |
|*  6 |       INDEX RANGE SCAN               | INV_CONTRACT_CREDIT_INDEX |   120K|       |   609   (1)| 00:00:07 |
...
------------------------------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

SQL> exec dbms_stats.drop_extended_stats('', 'INVOICEES', '(CONTRACT_ID,CREDIT)'); -- такая статистика нам не нужна ;)

PL/SQL procedure successfully completed

2. Попытка использовать в запросе индекс IDX_INVOICE_CONTRACT(CONTRACT_ID) вместо INV_CONTRACT_CREDIT_INDEX(CONTRACT_ID,CREDIT):

SQL> SELECT /*+ index(INV@SEL$2 IDX_INVOICE_CONTRACT)*/          -- требует принуждения
  2  sum(view_sp_credit.spent) AS spent,
  3         sum(view_sp_credit.spent_currency) AS spent_currency,
  4         view_sp_credit.product_id
  5    FROM view_sp_credit
  6   WHERE view_sp_credit.contract_id = 48746
  7   GROUP BY view_sp_credit.product_id
  8  /

2 rows selected.

Elapsed: 00:00:05.57

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dhm8m17h807w0, child number 0
-------------------------------------

Plan hash value: 1710380567

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |      1 |        |       |   237K(100)|          |      2 |00:00:05.45 |    1320K|
|   1 |  HASH GROUP BY                       |                           |      1 |    868 | 43400 |   237K  (1)| 00:43:29 |      2 |00:00:05.45 |    1320K|
|   2 |   NESTED LOOPS                       |                           |      1 |        |       |            |          |  11071 |00:00:05.42 |    1320K|
|   3 |    NESTED LOOPS                      |                           |      1 |   1382 | 69100 | 72554   (1)| 00:13:19 |  11071 |00:00:05.32 |    1308K|
|   4 |     NESTED LOOPS                     |                           |      1 |   1382 | 53898 | 71172   (1)| 00:13:03 |  11071 |00:00:05.23 |    1286K|
|*  5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |      1 |   9197 |   170K| 63772   (1)| 00:11:42 |    116K|00:00:00.33 |   77730 | -- по-прежнему неточная оценка для таблицы
|*  6 |       INDEX RANGE SCAN               | IDX_INVOICE_CONTRACT      |      1 |    111K|       |  1016   (1)| 00:00:12 |    120K|00:00:00.05 |     937 | -- при почти точной оценке E-Rows для индекса
...
------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   5 - filter("INV"."CREDIT"=2)
   6 - access("INV"."CONTRACT_ID"=48746)

SQL> SELECT /*+ index(INV@SEL$2 IDX_INVOICE_CONTRACT)*/
...
  8  /

2 rows selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  dhm8m17h807w0, child number 1
-------------------------------------

Plan hash value: 1710380567

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |       |       |  3192K(100)|          |
|   1 |  HASH GROUP BY                       |                           |     2 |   100 |  3192K  (1)| 09:45:14 |
|   2 |   NESTED LOOPS                       |                           |       |       |            |          |
|   3 |    NESTED LOOPS                      |                           | 17523 |   855K|   120K  (1)| 00:22:09 |
|   4 |     NESTED LOOPS                     |                           | 17523 |   667K|   103K  (1)| 00:18:56 |
|*  5 |      TABLE ACCESS BY INDEX ROWID     | INVOICEES                 |   116K|  2163K|  9449   (1)| 00:01:44 | -- Rows из статистики A-Rows 1-го выполнения
|*  6 |       INDEX RANGE SCAN               | IDX_INVOICE_CONTRACT      | 15040 |       |  1016   (1)| 00:00:12 | -- интересная оценка Rows
...
------------------------------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

— значительно более точная оценка E-Rows для операции INDEX RANGE SCAN на основании гистограм индексированного столбца CONTRACT_ID никак не влияет на по-прежнему неправильную оценку E-Rows для таблицы INVOICEES при первом выполнении, и этого оказывается достаточно для запуска механизма CF

Побочным эффектом применения CF в плане 2-го выполнения кроме уточнённой оценки Rows таблицы INVOICEES является достаточно странная оценка Rows для индекса IDX_INVOICE_CONTRACT, очевидно не основанная на статистике 1-го выполнения, вероятно — обратно вычисляемая величина (reverse engineering?)

3. Как я уже отмечал, стандартный, начиная с Oracle 11g метод управления планами — SPM Baselene в борьбе с бессмысленным и беспощадным CF также не всегда оказывается эффективным:

SQL> @create_bsline 7a0stv191ztsb 3758890385 "Testing#001"
Baseline SQL_985503c3ef6ef2f5 SQL_PLAN_9hp83sgrqxwrp0b44cf59 was [re]created
for SQL_ID=7a0stv191ztsb, SQL_PLAN_HASH=3758890385

SQL> @purge_cu 7a0stv191ztsb

PL/SQL procedure successfully completed.

SQL> SELECT sum(view_sp_credit.spent) AS spent,
...
  7  /

2 rows selected.

SQL> /

2 rows selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  7a0stv191ztsb, child number 1
-------------------------------------

Plan hash value: 3758890385

...

Note
-----
   - SQL plan baseline SQL_PLAN_9hp83sgrqxwrp0b44cf59 used for this statement
   - cardinality feedback used for this statement

SQL> @shared_cu 7a0stv191ztsb

EXECS LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST  CHILD SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  REASON1                   SQL_PLAN_BASELINE
----- ------------------- ------------ --------------- -------------- ------ ---------- ------------------ ---------------- ------------------------- ----------------------
    1 26.07.2013 18:15:32      3252708      3758890385         205574      0 N          Y                  N                Optimizer mismatch(13)  | SQL_PLAN_9hp83sgrqxwrp0b44cf59
    1 26.07.2013 18:15:36      3294718      3758890385        3191168      1 Y          N                  N                                          SQL_PLAN_9hp83sgrqxwrp0b44cf59

— успешное использование Baselene ни в коей степени не препятствует генерации планов, отличающихся только по стоимости, что, к сожалению, вполне логично и непротиворечиво.
Понятно, что SPM Baselene суть набор подсказок, фиксирующих план в виде набора операций доступа и соединения данных, при этом оценки Rows, и как следствие, стоимость планов никак не фиксируется

Другие предложения?

Масштабы проблемы

Найти запросы подобного рода, с CF-типа избыточными (повторяющийся план выполнения!) дочерними курсорами:

SQL> select sc.inst_id,
  2         sc.sql_id,
  3         sq.plan_hash_value,
  4         count(distinct sq.child_address)  as CHILD_COUNT,
  5         count(distinct sq.optimizer_cost) as COSTS_COUNT
  6    from gv$sql_shared_cursor sc, gv$sql sq
  7   where sc.inst_id = sq.inst_id
  8     and sc.sql_id = sq.sql_id
  9     and sc.child_address = sq.child_address
 10     and sc.address = sq.address
 11     and sq.executions > 0
 12   GROUP BY sc.inst_id, sc.sql_id ,sq.plan_hash_value
 13  having count(distinct sq.child_address) > 1
 14     and sum(decode(sc.use_feedback_stats, 'Y', 1, 0)) > count(distinct sq.child_address) / 2
 15   order by count(distinct sq.child_address) desc, sc.inst_id
 16  /

INST_ID SQL_ID        PLAN_HASH_VALUE CHILD_COUNT COSTS_COUNT
------- ------------- --------------- ----------- -----------
      1 bvn4b8cm2hfdb      1538715020         101           5
      2 14cvcufj3xtbz      1995788015          76           6
      1 30k8gc4ppymnr      1538715020          69           5
      1 14cvcufj3xtbz      1995788015          61           4
...

SQL> @shared_cu_sum_noxml bvn4b8cm2hfdb -- проверим

INST_ID USE_FEEDBACK_STATS REASON#1                                 REASON#2                                 REASON#3                                   COUNT(*)
------- ------------------ ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
      1 Y                  Optimizer mismatch(13)                   Optimizer mismatch(13)                   Optimizer mismatch(13)                           56
      1 Y                  Optimizer mismatch(13)                   Optimizer mismatch(13)                   Rolling Invalidate Window Exceeded(3)            13
      1 Y                  Optimizer mismatch(13)                                                                                                             11
      1 Y                  Optimizer mismatch(13)                   Optimizer mismatch(13)                                                                     8
      1 Y                  Optimizer mismatch(13)                   Rolling Invalidate Window Exceeded(3)    Rolling Invalidate Window Exceeded(2)             4
      1 Y                  Optimizer mismatch(13)                   Optimizer mismatch(13)                   Rolling Invalidate Window Exceeded(2)             3
      1 N                  Rolling Invalidate Window Exceeded(2)    Rolling Invalidate Window Exceeded(3)                                                      2
      1 N                  Rolling Invalidate Window Exceeded(2)    Rolling Invalidate Window Exceeded(3)    Rolling Invalidate Window Exceeded(2)             1
      1 N                                                                                                                                                      1
      1 Y                  Optimizer mismatch(13)                   Authorization Check failed(14)           Optimizer mismatch(13)                            1
      1 Y                  Optimizer mismatch(13)                   Rolling Invalidate Window Exceeded(2)    Optimizer mismatch(13)                            1

SQL> @shared_cu_sum_noxml 14cvcufj3xtbz -- на паре запросов из ТОПа
&1: 14cvcufj3xtbz

INST_ID USE_FEEDBACK_STATS REASON#1                                 REASON#2                                 REASON#3                                   COUNT(*)
------- ------------------ ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
      2 Y                  Optimizer mismatch(13)                   Optimizer mismatch(13)                                                                    42
      1 Y                  Optimizer mismatch(13)                                                                                                             33
      2 Y                  Optimizer mismatch(13)                                                                                                             25
      1 Y                  Optimizer mismatch(13)                   Optimizer mismatch(13)                                                                    23
      2 Y                  Optimizer mismatch(13)                   Rolling Invalidate Window Exceeded(3)    Rolling Invalidate Window Exceeded(2)            10
      1 Y                  Optimizer mismatch(13)                   Rolling Invalidate Window Exceeded(3)    Rolling Invalidate Window Exceeded(2)             7
      2 N                  Rolling Invalidate Window Exceeded(2)    Rolling Invalidate Window Exceeded(3)                                                      3
      1 N                  Rolling Invalidate Window Exceeded(2)                                                                                               2
      1 N                  Rolling Invalidate Window Exceeded(2)    Rolling Invalidate Window Exceeded(3)                                                      2
      1 Y                                                                                                                                                      1
      2 N                                                                                                                                                      1

Оценить долю подобного рода избыточных курсоров в общей массе неодиноких дочерних курсоров можно как-то так:

SQL> with q as (select sc.inst_id,
  2                     sc.sql_id,
  3                     sq.plan_hash_value,
  4                     count(distinct sq.child_address) - 1          as EXCESS_CHILD_COUNT,
  5                     sum(decode(sc.use_feedback_stats, 'Y', 1, 0)) as CF_REDUNDANT_COUNT
  6                from gv$sql_shared_cursor sc, gv$sql sq
  7               where sc.inst_id = sq.inst_id
  8                 and sc.sql_id = sq.sql_id
  9                 and sc.child_address = sq.child_address
 10                 and sc.address = sq.address
 11                 and sq.executions > 0
 12               GROUP BY sc.inst_id, sc.sql_id ,sq.plan_hash_value
 13               HAVING count(distinct sq.child_address) > 1
 14                  and sum(decode(sc.use_feedback_stats, 'Y', 1, 0)) >= count(distinct sq.child_address) / 2)
 15  select nvl(to_char(inst_id), 'All instances') as "Instance",
 16         'CF-redundant' as "Reason",
 17         sum(EXCESS_CHILD_COUNT) as EXCESS_CHILD_COUNT
 18    from Q
 19   group by cube(inst_id)
 20  union all
 21  select nvl(to_char(inst_id), 'All instances') as "Instance",
 22         'All-reasons' as "Reason",
 23         count(distinct sc.child_address) - count(distinct sc.sql_id) as EXCESS_CHILD_COUNT
 24    from gv$sql_shared_cursor sc
 25   group by rollup(sc.inst_id, sc.sql_id)
 26  HAVING count(distinct sc.child_address) > 1 and GROUPING(sc.sql_id) = 1
 27   order by "Instance", EXCESS_CHILD_COUNT
 28  /

Instance                                 Reason       EXCESS_CHILD_COUNT
---------------------------------------- ------------ ------------------
1                                        CF-redundant               1155
1                                        All-reasons               20107
2                                        CF-redundant                693
2                                        All-reasons               15480
All instances                            CF-redundant               1848
All instances                            All-reasons               43196

— на этой бд, с разнообразной и активной нагрузкой смешанного типа, такие курсоры составляют ~4% от общего кол-ва дополнительных дочерних курсоров

На более стабильной бд ERP приложения:

Instance                                 Reason       EXCESS_CHILD_COUNT
---------------------------------------- ------------ ------------------
1                                        CF-redundant                124
1                                        All-reasons               10070
2                                        CF-redundant                200
2                                        All-reasons               12299
All instances                            CF-redundant                324
All instances                            All-reasons               29197

— таких CF-redundant совсем мало

Единственным надёжным вариантом отключения CF в 11.2 остаётся установка «_optimizer_use_feedback» = false на уровне сессии или запроса

Отключение использования CF на уровне системы таким же способом возможно, но не является бесспорным решением по причине отключения генерации действительно полезных неповторяющихся планов, которые могут быть полезны / использованы при настройке сложных запросов

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

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

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