Oracle mechanics

27.07.2016

Презентация Oracle 12c ADS

Filed under: Oracle,Oracle 12c — Игорь Усольцев @ 01:28
Tags: ,

, представленная на Oracle Database Community Day 2016 (добавил комментарии на сером фоне):

Небольшое дополнение и иллюстрация:

1) Распределение DYNAMIC SAMPLING LEVEL, указываемого в V$SQL_PLAN.OTHER_XML можно наблюдать следующее:

SQL> select case
  2           when p.other_xml is null then 'no_plan'
  3           when instr(p.other_xml,'<info type="dynamic_sampling" note="y">') > 0 then 'with_ADS'
  4           else 'no_ADS'
  5         end as "SQL Type",
  6         case when REGEXP_SUBSTR(dbms_lob.substr(p.other_xml,4000),'<(cu)>([^<]+)</\1>',1,1,NULL,2) > 0 then 'SPD_Used'
  7              else 'SPD_not_Used'
  8         end as "SPD",
  9         REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(info) type="dynamic_sampling" note="y">([^<]+)</\1>', 1, 1, NULL, 2) as DS_LEVEL,
 10         sum(s.executions),
 11         to_char(RATIO_TO_REPORT(sum(s.executions)) OVER() * 100, '990.99') as "EXECS %"
 12    from gv$sql s
 13    left join gv$sql_plan p
 14      on s.inst_id = p.inst_id
 15     and s.sql_id = p.sql_id
 16     and s.plan_hash_value = p.plan_hash_value
 17     and s.child_address = p.child_address
 18     and p.other_xml is not null
 19   where s.sql_text not like 'SELECT /* DS_SVC */%'
 20     and sql_text not like 'SELECT /* OPT_DYN_SAMP */%'
 21     and instr(p.other_xml,'<info type="dynamic_sampling" note="y">') > 0 -- only queries with ADS
 22   group by case
 23              when p.other_xml is null then 'no_plan'
 24              when instr(p.other_xml,'<info type="dynamic_sampling" note="y">') > 0 then 'with_ADS'
 25              else 'no_ADS'
 26            end,
 27            case when REGEXP_SUBSTR(dbms_lob.substr(p.other_xml,4000),'<(cu)>([^<]+)</\1>',1,1,NULL,2) > 0 then 'SPD_Used'
 28                 else 'SPD_not_Used'
 29            end,
 30            REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(info) type="dynamic_sampling" note="y">([^<]+)</\1>', 1, 1, NULL, 2)
 31  order by 2, 4 desc
 32  /
 
SQL Type SPD          DS_LEVEL SUM(S.EXECUTIONS) EXECS %
-------- ------------ -------- ----------------- -------
with_ADS SPD_Used            2         478190065   91.00 -- Adaptive ADS, в основном, использует level=2
with_ADS SPD_Used            4                 8    0.00 -- погрешность? проверил - это системные запросы с хинтом /*+ DYNAMIC_SAMPLING(4)*/
with_ADS SPD_not_Used        2          47266924    9.00 -- Non-Adaptive ADS также часто использует level=2
with_ADS SPD_not_Used        7              7338    0.00 -- и реже - другие уровни
with_ADS SPD_not_Used       11               315    0.00
with_ADS SPD_not_Used        4                64    0.00
with_ADS SPD_not_Used        0                 2    0.00 -- погрешность?
with_ADS SPD_not_Used        5                 2    0.00 -- системные запросы с хинтом /*+ PARALLEL(5)*/

2) Практический случай, в котором ADS работает прекрасно, но практически бесполезно

Свежий (без истории выполнения) запрос:

12.1.0.2.@ SQL> SELECT --+ GATHER_PLAN_STATISTICS
...
 31  /

5 rows selected.

Elapsed: 00:09:15.91 -- не быстро!

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1dhp2b1vsmnyp, child number 0
-------------------------------------
Plan hash value: 2042896302

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                           |      1 |        |       | 14127 (100)|          |       |       |      5 |00:08:59.92 |     375M|   6186 |       |       |          |
|   1 |  NESTED LOOPS                                |                           |      1 |      9 |   702 | 14127   (1)| 00:00:01 |       |       |      5 |00:08:59.92 |     375M|   6186 |       |       |          |
|   2 |   NESTED LOOPS                               |                           |      1 |  42691 |  2793K| 14124   (1)| 00:00:01 |       |       |    147M|00:02:39.54 |      15M|   2744 |       |       |          |
|   3 |    MERGE JOIN CARTESIAN                      |                           |      1 |   5828 |   290K|   271   (1)| 00:00:01 |       |       |   3824 |00:00:00.01 |     440 |      0 |       |       |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID              | INVOICESS                 |      1 |      3 |   129 |     4   (0)| 00:00:01 |       |       |      2 |00:00:00.01 |       6 |      0 |       |       |          |
|*  5 |      INDEX RANGE SCAN                        | INVOICESS________IDX      |      1 |      1 |       |     3   (0)| 00:00:01 |       |       |      2 |00:00:00.01 |       4 |      0 |       |       |          |
|   6 |     BUFFER SORT                              |                           |      2 |   1912 | 15296 |   267   (1)| 00:00:01 |       |       |   3824 |00:00:00.01 |     434 |      0 | 68608 | 68608 |61440  (0)|
|*  7 |      MAT_VIEW ACCESS FULL                    | PRODUCTOS                 |      1 |   1912 | 15296 |    89   (0)| 00:00:01 |       |       |   1912 |00:00:00.01 |     434 |      0 |       |       |          |
|   8 |    PARTITION LIST ALL                        |                           |   3824 |      7 |   112 |     3   (0)| 00:00:01 |     1 |     2 |    147M|00:02:26.76 |      15M|   2744 |       |       |          |
|   9 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONSUMERZ                 |   7648 |      7 |   112 |     3   (0)| 00:00:01 |     1 |     2 |    147M|00:02:13.19 |      15M|   2744 |       |       |          |
|* 10 |      INDEX RANGE SCAN                        | CONSUMERZ_IDX_INVOICE_ID  |   7648 |      7 |       |     0   (0)|          |     1 |     2 |    147M|00:00:37.63 |    2476K|      0 |       |       |          |
|* 11 |   INDEX RANGE SCAN                           | ORDEROS______________IDX2 |    147M|      1 | 11    |     0   (0)|          |       |       |      5 |00:06:34.76 |     359M|   3442 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      ...
      END_OUTLINE_DATA
  */

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

Statistics
----------------------------------------------------------
       1896  recursive calls
          4  db block gets
     353876  consistent gets
     268945  physical reads                                -- ***
...

— выполняется достаточно долго (9 сек!) с использованием когда-то для др.запросов созданных Sql Plan Directives и, как следствие, ADS уровня 2 (*)

Из 10053 трейса можно увидеть, каким образом необратимый поворот в построении плана происходит на этапе соединения таблиц INVOICESS и CONSUMERZ:

Join order[4]:  INVOICESS[INVOICESS]#0  CONSUMERZ[CONSUMERZ]#3  PRODUCTOS[PRODUCTOS]#1  ORDEROS[ORDEROS]#2

***************
Now joining: CONSUMERZ[CONSUMERZ]#3
***************
NL Join
...
  Best NL cost: 11.132319
          resc: 11.132319  resc_io: 11.125963  resc_cpu: 85093
          resp: 11.132319  resp_io: 11.125963  resc_cpu: 85093
  SPD: Directive valid: dirid = 10381951350005600077, state = 5, flags = 1, loc = 2 {(2607875)[]; F(4085)[]}
  SPD: Return code in qosdDSDirSetup: EXISTS, estType = JOIN
Join Card:  22.328120 = outer (3.047982) * inner (274549573.000000) * sel (2.6682e-08)
>> Join Card adjusted from 22.328120 to 76029.230000 due to adaptive dynamic sampling, prelen=2              -- здесь **
Adjusted Join Cards: adjRatio=3405.088769 cardHjSmj=76029.230000 cardHjSmjNPF=76029.230000 cardNlj=76029.230000 cardNSQ=76029.230000 cardNSQ_na=22.328120
Join Card - Rounded: 76029 Computed: 76029.230000

— далее уточнённая Join Cardinality (**) просто блокирует использование этого порядка соединения

С другой стороны статистика выполнения того же запроса с классическим CBO (отключенным ADS) показывает точность выполненного для предыдущего варианта запроса на основе SQL Plan Directives + ADS уточнения Join Card (**), и в то же время демонстрирует намного лучшую скорость выполнения запроса без использования ADS!:

SQL> SELECT --+ DYNAMIC_SAMPLING(0) GATHER_PLAN_STATISTICS
...
 31  /

5 rows selected.

Elapsed: 00:00:00.46 -- против 9 секунд!

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

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

Plan hash value: 2043578017

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                           |      1 |        |       |    32 (100)|          |       |       |      5 |00:00:00.46 |     197K|       |       |          |
|*  1 |  HASH JOIN                                    |                           |      1 |      3 |   234 |    32   (0)| 00:00:01 |       |       |      5 |00:00:00.46 |     197K|    10M|  2528K|   12M (0)|
|   2 |   NESTED LOOPS                                |                           |      1 |      9 |   630 |    23   (0)| 00:00:01 |       |       |  77387 |00:00:00.42 |     196K|       |       |          |
|   3 |    NESTED LOOPS                               |                           |      1 |      9 |   531 |    14   (0)| 00:00:01 |       |       |  77387 |00:00:00.10 |    8240 |       |       |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED       | INVOICESS                 |      1 |      1 |    43 |    11   (0)| 00:00:01 |       |       |      2 |00:00:00.01 |       6 |       |       |          |
|*  5 |      INDEX RANGE SCAN                         | INVOICESS____________IDX2 |      1 |     48 |       |     4   (0)| 00:00:01 |       |       |      2 |00:00:00.01 |       4 |       |       |          |
|   6 |     PARTITION LIST ALL                        |                           |      2 |      7 |   112 |     3   (0)| 00:00:01 |     1 |     2 |  77387 |00:00:00.09 |    8234 |       |       |          |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONSUMERZ                 |      4 |      7 |   112 |     3   (0)| 00:00:01 |     1 |     2 |  77387 |00:00:00.08 |    8234 |       |       |          |
|*  8 |       INDEX RANGE SCAN                        | CONSUMERZ_IDX_INVOICE_ID  |      4 |      7 |       |     0   (0)|          |     1 |     2 |  77387 |00:00:00.02 |    1295 |       |       |          |
|*  9 |    INDEX RANGE SCAN                           | ORDEROS______________IDX2 |  77387 |      1 |    11 |     1   (0)| 00:00:01 |       |       |  77387 |00:00:00.28 |     188K|       |       |          |
|* 10 |   MAT_VIEW ACCESS FULL                        | PRODUCTOS                 |      1 |      1 |     8 |     1   (0)| 00:00:01 |       |       |   1912 |00:00:00.01 |     435 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       2272  recursive calls
          4  db block gets
     668793  consistent gets
       4895  physical reads                                -- ***
...

Join Card adjusted … to 76029 из трейса(**) достаточно хорошо согласуется с A-Rows (77387) из статистики плана, что говорит в пользу SQL Plan Directives и точности ADS-оценки!

Однако приведённая Autotrace Statistics (***) показывает, где первый план с фичами 12c проигрывает — ADS никак не учитывает (и не отражает в плане — а жаль!) распределение блоков отдельных объектов бд (чаще индексов) в Buffer Cache

И единственная доступная возможность повлиять — поиграть параметрами OPTIMIZER_INDEX_CACHING / OPTIMIZER_INDEX_COST_ADJ на уровне запроса/системы, что, во-первых, достаточно грубо и опять же предполагает ручное вмешательство

2 комментария »

  1. Игорь, вы много пишете про ADS, в основном о всяких его side-effects, но я так и не могу понять: для абстрактной сферической базы в вакууме, на ваш взгляд, есть ли смысл ковыряться во всём этом или, может быть, проще отключить ADS совсем и получить возможно не самую производительную, но стабильную систему?

    комментарий от Андрей — 27.07.2016 @ 12:13 | Ответить

    • Считаю, что это — вопрос подхода, Андрей

      Да, система будет стабильнее и более предсказуемой, и, скажем, для плотно нагруженного OLTP с жёсткими таймингами отключение DS вполне может быть разумным — тут, правда, можно пойти дальше: зафиксировать планы перед обновлением (что рекомендуется), отключить все 12c Adaptive Features, и далее до понижения OPTIMIZER_FEATURES_ENABLE:)

      С другой стороны, «абстрактная сферическая база» — обычно имеет смешанную нагрузку (где в части отчётов DS может быть весьма полезен),
      плюс обновление на 12c должно кропотливо тестироваться,
      и, что важно, со всеми этими 12c Adaptive Features и DS предстоит жить и дальше, как мне кажется, и в последующих версиях и в любом случае эти подходы придётся осваивать

      комментарий от Игорь Усольцев — 27.07.2016 @ 16:48 | Ответить


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