Oracle mechanics

30.01.2017

12c Adaptive Bitmap Pruning — особый случай адаптивного выполнения: PHV, PHV2, FPHV & Outline Hints

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

В процессе подготовки семинара, столкнулся с любопытным случаем, для адаптивного плана выполнения:

12.1.0.2.SCOTT@/ORCL1201 SQL> exec dbms_stats.gather_schema_stats('SH')

PL/SQL procedure successfully completed.

SQL> SELECT /*+ star_transformation(s)                                   -- star_transformation_enabled = TRUE на уровне запроса
  2             gather_plan_statistics
  3             OPT_PARAM('_optimizer_dsdir_usage_control' 0)            -- для исключения эффектов
  4  --           OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
  5             OPT_PARAM('_optimizer_gather_feedback' 'false') */       -- --//--
  6   c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  7    FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  8   WHERE s.time_id = t.time_id
  9     AND s.cust_id = c.cust_id
 10     AND s.channel_id = ch.channel_id
 11     AND c.cust_state_province = 'CA'
 12     AND ch.channel_desc = 'Internet'
 13     AND t.calendar_quarter_desc IN ('1999-01', '1999-02')
 14   GROUP BY c.cust_city, t.calendar_quarter_desc
 15  /

22 rows selected.

SQL> / -- несколько выполнений используется просто для закрепления SQL_ID с планом в Shared Pool

22 rows selected.

SQL> /

22 rows selected.

SQL> /

22 rows selected.

SQL> /

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('','','adaptive last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2acdtg51zcqxk, child number 0
-------------------------------------

Plan hash value: 2164696140

---------------------------------------------------------------------------------------------------------
|   Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                    |                            |       |       |  1040 (100)|
|     1 |  TEMP TABLE TRANSFORMATION          |                            |       |       |            |
|     2 |   LOAD AS SELECT                    |                            |       |       |            |
|  *  3 |    TABLE ACCESS FULL                | CUSTOMERS                  |  3341 | 86866 |   423   (1)|
|     4 |   HASH GROUP BY                     |                            |   877 | 49989 |   618   (1)|
|  *  5 |    HASH JOIN                        |                            | 16848 |   937K|   616   (0)|
|     6 |     TABLE ACCESS FULL               | SYS_TEMP_0FD9D6610_1B2E17E |  3341 | 50115 |     4   (0)|
|  *  7 |     HASH JOIN                       |                            | 16848 |   691K|   612   (0)|
|  *  8 |      TABLE ACCESS FULL              | TIMES                      |   181 |  2896 |    18   (0)|
|     9 |      VIEW                           | VW_ST_A3F94988             | 16848 |   427K|   594   (0)|
|    10 |       NESTED LOOPS                  |                            | 16848 |   937K|   569   (0)|
|    11 |        PARTITION RANGE SUBQUERY     |                            | 16848 |   460K|   378   (1)|
|    12 |         BITMAP CONVERSION TO ROWIDS |                            | 16848 |   460K|   378   (1)|
|    13 |          BITMAP AND                 |                            |       |       |            |
|    14 |           BITMAP MERGE              |                            |       |       |            |
|    15 |            BITMAP KEY ITERATION     |                            |       |       |            |
|    16 |             BUFFER SORT             |                            |       |       |            |
|  * 17 |              TABLE ACCESS FULL      | CHANNELS                   |     1 |    13 |     3   (0)|
|  * 18 |             BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX          |       |       |            |
|-   19 |           STATISTICS COLLECTOR      |                            |       |       |            | -- странные и бессмысленные операции*
|    20 |            BITMAP MERGE             |                            |       |       |            |
|    21 |             BITMAP KEY ITERATION    |                            |       |       |            |
|    22 |              BUFFER SORT            |                            |       |       |            |
|  * 23 |               TABLE ACCESS FULL     | TIMES                      |   181 |  2896 |    18   (0)|
|  * 24 |              BITMAP INDEX RANGE SCAN| SALES_TIME_BIX             |       |       |            |
|-   25 |           STATISTICS COLLECTOR      |                            |       |       |            | -- странные и бессмысленные операции*
|    26 |            BITMAP MERGE             |                            |       |       |            |
|    27 |             BITMAP KEY ITERATION    |                            |       |       |            |
|    28 |              BUFFER SORT            |                            |       |       |            |
|    29 |               TABLE ACCESS FULL     | SYS_TEMP_0FD9D6610_1B2E17E |  3341 | 16705 |     4   (0)|
|  * 30 |              BITMAP INDEX RANGE SCAN| SALES_CUST_BIX             |       |       |            |
|    31 |        TABLE ACCESS BY USER ROWID   | SALES                      |     1 |    29 |   217   (1)|
---------------------------------------------------------------------------------------------------------

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

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("ITEM_1"="C0")
   7 - access("ITEM_2"="T"."TIME_ID")
   8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  17 - filter("CH"."CHANNEL_DESC"='Internet')
  18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  23 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  24 - access("S"."TIME_ID"="T"."TIME_ID")
  30 - access("S"."CUST_ID"="C0")

Note
-----
   - star transformation used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)

*) в условиях реального отсутствия какого-либо Adaptive Bitmap Pruning!

Неадаптивный план:

SQL> SELECT /*+ star_transformation(s)
  2             gather_plan_statistics
  3             OPT_PARAM('_optimizer_dsdir_usage_control' 0)
  4             OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') -- адаптивность этого типа отключена
  5             OPT_PARAM('_optimizer_gather_feedback' 'false') */
  6   c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  7    FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  8   WHERE s.time_id = t.time_id
  9     AND s.cust_id = c.cust_id
 10     AND s.channel_id = ch.channel_id
 11     AND c.cust_state_province = 'CA'
 12     AND ch.channel_desc = 'Internet'
 13     AND t.calendar_quarter_desc IN ('1999-01', '1999-02')
 14   GROUP BY c.cust_city, t.calendar_quarter_desc
 15  /

22 rows selected.

SQL> /

22 rows selected.

SQL> /

22 rows selected.

SQL> /

22 rows selected.

SQL> /

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('','','adaptive last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6nanb0wh0g696, child number 0
-------------------------------------

Plan hash value: 2164696140

------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                       | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                            |       |       |  1040 (100)|
|   1 |  TEMP TABLE TRANSFORMATION         |                            |       |       |            |
|   2 |   LOAD AS SELECT                   |                            |       |       |            |
|*  3 |    TABLE ACCESS FULL               | CUSTOMERS                  |  3341 | 86866 |   423   (1)|
|   4 |   HASH GROUP BY                    |                            |   877 | 49989 |   618   (1)|
|*  5 |    HASH JOIN                       |                            | 16848 |   937K|   616   (0)|
|   6 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D6611_1B2E17E |  3341 | 50115 |     4   (0)|
|*  7 |     HASH JOIN                      |                            | 16848 |   691K|   612   (0)|
|*  8 |      TABLE ACCESS FULL             | TIMES                      |   181 |  2896 |    18   (0)|
|   9 |      VIEW                          | VW_ST_A3F94988             | 16848 |   427K|   594   (0)|
|  10 |       NESTED LOOPS                 |                            | 16848 |   937K|   569   (0)|
|  11 |        PARTITION RANGE SUBQUERY    |                            | 16848 |   460K|   378   (1)|
|  12 |         BITMAP CONVERSION TO ROWIDS|                            | 16848 |   460K|   378   (1)|
|  13 |          BITMAP AND                |                            |       |       |            |
|  14 |           BITMAP MERGE             |                            |       |       |            |
|  15 |            BITMAP KEY ITERATION    |                            |       |       |            |
|  16 |             BUFFER SORT            |                            |       |       |            |
|* 17 |              TABLE ACCESS FULL     | CHANNELS                   |     1 |    13 |     3   (0)|
|* 18 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX          |       |       |            |
|  19 |           BITMAP MERGE             |                            |       |       |            |
|  20 |            BITMAP KEY ITERATION    |                            |       |       |            |
|  21 |             BUFFER SORT            |                            |       |       |            |
|* 22 |              TABLE ACCESS FULL     | TIMES                      |   181 |  2896 |    18   (0)|
|* 23 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX             |       |       |            |
|  24 |           BITMAP MERGE             |                            |       |       |            |
|  25 |            BITMAP KEY ITERATION    |                            |       |       |            |
|  26 |             BUFFER SORT            |                            |       |       |            |
|  27 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D6611_1B2E17E |  3341 | 16705 |     4   (0)|
|* 28 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX             |       |       |            |
|  29 |        TABLE ACCESS BY USER ROWID  | SALES                      |     1 |    29 |   217   (1)|
------------------------------------------------------------------------------------------------------

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

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("ITEM_1"="C0")
   7 - access("ITEM_2"="T"."TIME_ID")
   8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  17 - filter("CH"."CHANNEL_DESC"='Internet')
  18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  23 - access("S"."TIME_ID"="T"."TIME_ID")
  28 - access("S"."CUST_ID"="C0")

Note
-----
   - star transformation used for this statement

Наибольший диссонанс вызвал вывод скрипта отличий секций Notes и Outline Data из OTHER_XML вышеприведённых планов выполнения:

SQL> @sql_plan_diff_notes 2acdtg51zcqxk 2164696140 6nanb0wh0g696 2164696140 ""

-----------------------------
SQL Plan "Notes" sections diff
-----------------------------

PHV_2164696140                    PHV_2164696140
--------------------------------- ------------------------------------------------------------

   sql_profile:                      sql_profile:
   sql_patch:                        sql_patch:
   baseline:                         baseline:
   outline:                          outline:
   dyn_sampling:                     dyn_sampling:
   dop:                              dop:
   dop_reason:                       dop_reason:
   card_feedback:                    card_feedback:
   perf_feedback:                    perf_feedback:
   adaptive_plan:   yes              adaptive_plan: -- понятно, что первый план адаптивный, а второй - нет
   spd_used:                         spd_used:
   spd_valid:                        spd_valid:
   gtt_sess_stat:                    gtt_sess_stat:
   db_version:      12.1.0.2         db_version:      12.1.0.2
   plan_hash_full:  2745907449       plan_hash_full:  1321540369 -- для неадаптивного совпадает с PHV2 обоих планов
   plan_hash:       2164696140       plan_hash:       2164696140 -- совпадает для  обоих планов
   plan_hash_2:     1321540369       plan_hash_2:     1321540369 -- совпадает для  обоих планов

-------------------------------
SQL Plan "Outline" sections diff
-------------------------------

PHV_2164696140
------------------------------------------------------------------------------------------------------------
BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CHANNEL_ID") 1)
BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CUST_ID") 3)
BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."TIME_ID") 2)
------------------------------------------------------------------------------------------------------------
PHV_2164696140
------------------------------------------------------------------------------------------------------------
BITMAP_TREE(@"SEL$5E9A798F" "S"@"SEL$1" AND(("SALES"."CHANNEL_ID") ("SALES"."TIME_ID") ("SALES"."CUST_ID")))
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') -- просто отключение адаптивности 2-го плана

8 rows selected.

— точнее даже, несовпадение хинтов в Outline для планов с совпадающими Plan Hash Value вызывает некоторое смущение, кажется, раньше не видел такого)

Видимо, Star Transformation и адаптивные планы — что-то совсем совсем особенное (от всего остального оптимизатора)!

Реклама

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

  1. Добрый день!Что за семинар?

    комментарий от sshumeev — 30.01.2017 @ 08:09 | Ответить

    • Вероятно, «Oracle 12c: адаптивная оптимизация, SPM и другие методы управления планами выполнения» , 16-17/02/2017 Москва ?

      С нетерпением ждем ))

      комментарий от jimroll — 30.01.2017 @ 17:41 | Ответить

    • В УКЦ ФОРС

      комментарий от jimroll — 30.01.2017 @ 17:42 | Ответить

  2. Добрый день! Ну вот вы уже всё и выяснили — но пост был не об этом)

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


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