Oracle mechanics

28.04.2015

12c: реоптимизация baseline-ов

Filed under: CBO,Optimizer features,Oracle,Oracle new features — Игорь Усольцев @ 13:49
Tags: ,

При тестировании Oracle 12c наблюдали с Леонидом Борчуком в своём роде замечательное поведение, адекватно отражаемое запросом shared_cu12.sql (V$SQL+V$SQL_PLAN+V$SQL_SHARED_CURSOR с добавлением адаптивных эффектов):

12.1.0.2@ SQL> @shared_cu12 ch28y57dby48a
 
EXECS LAST_LOAD_TIME      LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  REOPT REOPT_HINTS ADAPT USE_FEEDBACK_STATS SQL_PLAN_DIRECTIVES OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON1                              SQL_PLAN_BASELINE              SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
----- ------------------- ------------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ----- ----------- ----- ------------------ ------------------- ---------------- ---------------- ---- ------------------------------------ ------------------------------ --------- ---------------- ----------- -----------
    2 2015-04-23/17:37:22 23.04.2015 17:45:14    220816797      2061850922           3073     0 N          N          N          Y              19 Y     Y                  valid:13; used:3    N                N                N    Auto Reoptimization Mismatch(1)  |                                                                         N
    2 2015-04-23/18:25:09 23.04.2015 18:33:26    221786319      3290307628       19309221     1 N          N          N          Y              36       Y                  valid:13; used:3    N                N                N    Auto Reoptimization Mismatch(1)  |                                                                         N
    4 2015-04-24/12:41:54 24.04.2015 13:10:57     88755911      2061850922           3072     2 N          N          Y          Y              19 Y     Y                  valid:13; used:4    N                N                N    SQL Tune Base Object Different(3)  |                                                                       N
    1 2015-04-24/14:30:11 24.04.2015 14:30:14      3347403       506569056           3104     3 N          N          Y          N               0       N                  valid:9; used:2     N                N                N                                         SQL_PLAN_8aa3jkjfw98tf56386d53                                        N
    1 2015-04-24/14:31:09 24.04.2015 14:31:34     15259875       506569056           3104     4 N          N          N          Y              43       Y                  valid:9; used:2     N                N                N    Auto Reoptimization Mismatch(1)  |   SQL_PLAN_8aa3jkjfw98tf56386d53                                        N
    1 2015-04-24/14:42:43 24.04.2015 14:43:10     13913257       506569056           3104     6 N          N          N          Y              44       Y                  valid:9; used:2     N                N                N    Auto Reoptimization Mismatch(1)  |   SQL_PLAN_8aa3jkjfw98tf56386d53                                        N
    4 2015-04-24/16:50:05 24.04.2015 16:52:30      3762610       506569056           3105     5 N          N          Y          N               0       N                  valid:9; used:2     N                N                N                                         SQL_PLAN_8aa3jkjfw98tf56386d53                                        N

— запрос ch28y57dby48a, вызывавший опасения в плане скорости выполнения (ELA_PER_EXEC в первых 3-х строках вывода) после фиксации хорошего плана выполнения PLAN_HASH_VALUE = 506569056:

SQL> @spm_check4sql_id ch28y57dby48a
 
SPM_TYPE          SQL_HANDLE            PLAN_NAME                      ORIGIN      VERSION    LAST_MODIFIED       LAST_EXECUTED       ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
----------------- --------------------- ------------------------------ ----------- ---------- ------------------- ------------------- ------- -------- ----- ---------- ---------
SQL Plan Baseline SQL_852871945dc4a32e  SQL_PLAN_8aa3jkjfw98tf56386d53 MANUAL-LOAD 12.1.0.2.0 24.04.2015 14:30:12 24.04.2015 14:30:12 YES     YES      NO    YES        YES

— одновременно с мирным использованием зафиксированного baseline-ом плана, формально продолжал применять SQL Plan Directives (SQL_PLAN_DIRECTIVES) и использовать реоптимизацию (столбцы REOPT и REOPT_HINTS) как следствие деятельности Statistics Feedback (USE_FEEDBACK_STATS), т.о. производя ненужную работу и генерируя избыточные дочерние курсоры

Сгенерированные/использованные планы выполнения при этом либо не отличались — как у child 4 и 6, что корректо отражено в неизменной стоимости, либо местами значительно отличались оценками cardinality источников и, как следствие, предположениями по использованию памяти, что однако никак не откажалось на собственно плане, как наборе и порядке операций, зафиксированными значением Plan Hash Value, — как у child 4/6 и 5:

SQL_ID  ch28y57dby48a, child number 4
-------------------------------------
 
Plan hash value: 506569056
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem |  O/1/M   |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |        |       |  3104 (100)|          |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED                   |      1 |    47 |     3   (0)| 00:00:01 |       |       |          |
|   2 |   INDEX RANGE SCAN                                     |      1 |       |     2   (0)| 00:00:01 |       |       |          |
|   3 |  HASH JOIN RIGHT OUTER                                 |      1 |  3316 |  3101   (1)| 00:00:01 |  1034K|  1034K|     1/0/0|
|   4 |   VIEW                                                 |   1803 |   350K|   266   (2)| 00:00:01 |       |       |          |
|   5 |    HASH JOIN                                           |   1803 |   128K|   266   (2)| 00:00:01 |  1599K|  1599K|     1/0/0|
...
| 156 |                   INDEX RANGE SCAN                     |   5256 |       |    16   (0)| 00:00:01 |       |       |          |
| 157 |                  TABLE ACCESS BY INDEX ROWID           |   5256 |  1529K|   248   (0)| 00:00:01 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL plan baseline SQL_PLAN_8aa3jkjfw98tf56386d53 used for this statement
   - 2 Sql Plan Directives used for this statement
 

SQL_ID  ch28y57dby48a, child number 5
-------------------------------------
 
Plan hash value: 506569056
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem |  O/1/M   |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |        |       |  3105 (100)|          |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED                   |      1 |    47 |     3   (0)| 00:00:01 |       |       |          |
|   2 |   INDEX RANGE SCAN                                     |      1 |       |     2   (0)| 00:00:01 |       |       |          |
|   3 |  HASH JOIN RIGHT OUTER                                 |  15307 |    48M|  3102   (1)| 00:00:01 |  1034K|  1034K|     4/0/0|
|   4 |   VIEW                                                 |   1803 |   350K|   266   (2)| 00:00:01 |       |       |          |
|   5 |    HASH JOIN                                           |   1803 |   128K|   266   (2)| 00:00:01 |  1599K|  1599K|     4/0/0|
...
| 156 |                   INDEX RANGE SCAN                     |   5256 |       |    16   (0)| 00:00:01 |       |       |          |
| 157 |                  TABLE ACCESS BY INDEX ROWID           |    116M|    32G|   248   (0)| 00:00:01 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL plan baseline SQL_PLAN_8aa3jkjfw98tf56386d53 used for this statement
   - statistics feedback used for this statement
   - 2 Sql Plan Directives used for this statement

Набор Reoptimization Hints использовался типовой:

SQL> select distinct regexp_substr(hint_text, '[^ ]+', 1, 1) as HINT,
  2                  regexp_substr(hint_text, '[^ ]+', 1, 3) as IDENTIFIER,
  3                  regexp_substr(hint_text, '(\ )([A-Z_]+)(\=)', 1, 1) as ADJUSTMENT
  4    from v$sql_reoptimization_hints rh
  5    join v$sql s
  6   using (sql_id, child_number)
  7   where sql_id = 'ch28y57dby48a'
  8     and sql_plan_baseline = 'SQL_PLAN_8aa3jkjfw98tf56386d53'
  9   order by 1, 2, 3
 10  /
 
HINT            IDENTIFIER     ADJUSTMENT
--------------- -------------- ----------
OPT_ESTIMATE    INDEX_FILTER    MIN=
OPT_ESTIMATE    INDEX_SCAN      MIN=
OPT_ESTIMATE    JOIN            ROWS=
OPT_ESTIMATE    TABLE           MIN=
OPT_ESTIMATE    TABLE           ROWS=

, но никак не отражался в секции Outline плана выполнения

Но есть и хорошие новости: после создания baseline-а адаптивным план выполнения в смысле V$SQL.IS_RESOLVED_ADAPTIVE_PLAN (столбец ADAPT в самом первом выводе запроса shared_cu12.sql) быть перестал)

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

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

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