Oracle mechanics

04.04.2016

12c: 2 x FULL_PLAN_HASH_VALUE для одного плана выполнения / PLAN_HASH_VALUE и скрипт ASH_SQLMON12.SQL

Filed under: Active Session History,Oracle — Игорь Усольцев @ 23:34
Tags: ,

При интенсивном выполнении (конец месяца, подсчёт итогов,..) запроса типа SELECT наблюдал картину:

SQL> @shared_cu12_noxml 35hnzb2q6xrkc
 
  EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CLU_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL BIND_EQ_FAILURE REASON#1          SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
------- -------------------- ------------------- ------------ ------------ ------------- -------------- ------------ ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- --------------- ----------------- ----------------- --------- ---------------- ----------- -----------
   1015 2016-04-01/10:10:12  01.04.2016 10:16:44       470644        74060         24316            462       303651       107095 VALID               434062702           3150513221         194928     3 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
    384 2016-04-01/10:16:47  01.04.2016 10:19:05       482506        66008         20388            401       309564       118371 VALID               434062702           3150513221          11350     5 Y          N          N          N                  N                 Y     5         2         2        N               Bind mismatch(25)                                                          N
    481 2016-04-01/10:19:05  01.04.2016 10:23:02       440066        44582         13717            295       214687       188648 VALID               434062702           3150513221             72     6 Y          Y          N          N                  N                 Y     5         2         2        N               Bind mismatch(33)                                                          N
    538 2016-04-01/10:22:06  01.04.2016 10:27:07       622140        69344         24159            422       305390       258859 VALID               434062702           3150513221         315650     7 Y          Y          N          N                  N                 Y     6         1         2        Y               Bind mismatch(33)                                                          N
   2426 2016-04-01/10:27:04  01.04.2016 10:35:02       324502        49090         15276            266       209320        73840 VALID               434062702           3150513221            345     8 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  18369 2016-04-01/10:34:44  01.04.2016 10:54:36       212043        24282          6648            142       121101        69878 VALID               434062702           3150513221          11390     2 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  11009 2016-04-01/10:54:26  01.04.2016 11:00:00       103472        16452          4501             87        70872        18074 VALID               434062702           3150513221          42424     9 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  15439 2016-04-01/10:59:58  01.04.2016 11:07:58       157499        16593          4644             78       127386        14786 VALID               434062702           1196564125         385981    10 Y          Y          N          N                  N                       5         2         2        Y               Bind mismatch(33)                                                          N
 148072 2016-04-01/11:07:57  01.04.2016 12:01:52        53123         9772          2884             50        41752         2766 VALID               434062702           3150513221           8859    11 Y          Y          Y          N                  N                 Y     5         2         2        Y                                                                                          N
 
10 rows selected
 
--------------------------------------------------------------
SQL_ID=35hnzb2q6xrkc Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
  EXECS ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC FETCH_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
------- ------------ ------------ ------------- -------------- ------------ ------------- ------------ -------------- ------------- --------------- -------------------- ------------ ---------- ---------- ---------- ----- ----- ----------------- --------- ---------------- -----------
  15439       157499        16593          4644             78       127386            16        14786              1             1       434062702           1196564125       385981          1 Y          Y          N     N                                                  
 182294        81718        12905          3774             69        56993            32        13470              1             1       434062702           3150513221        73127          8 Y          Y          N     Y

— любопытную прежде всего тем, что по причине BIND_AWARE / Bind mismatch(33) Oracle периодически перегенерировал один и тот же курсор с неизменным рез-татом в виде плана PLAN_HASH_VALUE = 434062702, но с использованием разных FULL_PLAN_HASH_VALUE — см. CHILD 9-11, например

Из вывода предыдущего скрипта SHARED_CU12_NOXML.SQL можно предположить, что курсоры с различными FULL_PLAN_HASH_VALUE отличаются в части Adaptive Plan (столбец ADAPT), что надёжно подтверждает DBMS_XPLAN.DISPLAY_CURSOR для адаптивного дочернего курсора с CHILD_NUMBER = 11 и FULL_PLAN_HASH_VALUE = 3150513221:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('35hnzb2q6xrkc',11,format => '+adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  35hnzb2q6xrkc, child number 11
--------------------------------------

Plan hash value: 434062702
 
-----------------------------------------------------------...-----------------
| Id  | Operation                                         |   | Pstart| Pstop |
-----------------------------------------------------------...-----------------
|   0 | SELECT STATEMENT                                  |   |       |       |
|   1 |  VIEW                                             |   |       |       |
|   2 |   SORT AGGREGATE                                  |   |       |       |
|   3 |    NESTED LOOPS                                   |   |       |       |
|   4 |     NESTED LOOPS                                  |   |       |       |
|   5 |      NESTED LOOPS                                 |   |       |       |
|-  6 |       HASH JOIN                                   |   |       |       | -- *
|   7 |        NESTED LOOPS                               |   |       |       |
|-  8 |         STATISTICS COLLECTOR                      |   |       |       | -- *
|   9 |          TABLE ACCESS BY INDEX ROWID BATCHED      |   |       |       |
|  10 |           INDEX RANGE SCAN                        |   |       |       |
|  11 |         PARTITION LIST ALL                        |   |     1 |     2 |
|  12 |          TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|   |     1 |     2 |
|  13 |           INDEX RANGE SCAN                        |   |     1 |     2 |
|- 14 |        PARTITION LIST ALL                         |   |     1 |     2 | -- *
|- 15 |         TABLE ACCESS FULL                         |   |     1 |     2 | -- *
|  16 |       TABLE ACCESS BY INDEX ROWID BATCHED         |   |       |       |
|  17 |        INDEX RANGE SCAN                           |   |       |       |
|  18 |      INDEX UNIQUE SCAN                            |   |       |       |
|  19 |     TABLE ACCESS BY INDEX ROWID                   |   |       |       |
-----------------------------------------------------------...-----------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan (rows marked '-' are inactive)                     -- *
   - 2 Sql Plan Directives used for this statement

, отличающегося от курсора №10 с FULL_PLAN_HASH_VALUE = 1196564125 лишь отмеченными (*), очевидно адаптивного происхождения, строками, отсутствующими в плане CHILD_NUMBER = 10:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('35hnzb2q6xrkc',10,format => '+adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  35hnzb2q6xrkc, child number 10
--------------------------------------

Plan hash value: 434062702

--------------------------------------------------------------------------
| Id  | Operation                                        | Pstart| Pstop |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |       |       |
|   1 |  VIEW                                            |       |       |
|   2 |   SORT AGGREGATE                                 |       |       |
|   3 |    NESTED LOOPS                                  |       |       |
|   4 |     NESTED LOOPS                                 |       |       |
|   5 |      NESTED LOOPS                                |       |       |
|   6 |       NESTED LOOPS                               |       |       |
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED       |       |       |
|*  8 |         INDEX RANGE SCAN                         |       |       |
|   9 |        PARTITION LIST ALL                        |     1 |     2 |
|  10 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|     1 |     2 |
|* 11 |          INDEX RANGE SCAN                        |     1 |     2 |
|* 12 |       TABLE ACCESS BY INDEX ROWID BATCHED        |       |       |
|* 13 |        INDEX RANGE SCAN                          |       |       |
|* 14 |      INDEX UNIQUE SCAN                           |       |       |
|* 15 |     TABLE ACCESS BY INDEX ROWID                  |       |       |
--------------------------------------------------------------------------

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

Попутно на примере этого запроса скорректировал запрос ASH-мониторинга выполнения запросов ASH_SQLMON12.SQL с учётом адаптиных планов выполнения имени Oracle 12c

Для рассматриваемого запроса вертикально сокращённый вывод выглядит теперь так:

SQL> @ash_sqlmon12 35hnzb2q6xrkc 434062702
 
LAST_PLSQL       SQL_ID        PLAN_HASH_VALUE ID    DISP  PLAN_OPERATION                                                  COST TEMP_SPACE  PX MAX_PGA_ALLOCATED MAX_TEMP_SPACE_ALLOCATED   ASH_ROWS WAIT_PROFILE                                                                                                                                                                                             
---------------- ------------- --------------- ----- ----- ------------------------------------------------------------- ------ ---------- --- ----------------- ------------------------ ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Soft Parse       35hnzb2q6xrkc       434062702     0       sql_plan_hash_value > 0; sql_exec_id is null                                     11           8510464                 26214400         33 ON CPU(14); ON CPU(8); gc current grant busy(5); gc cr block congested(1); library cache lock(1); gc current grant congested(1); gc cr multi block request(1); db file scattered read(1);.. 
Main Query       35hnzb2q6xrkc       434062702     0    0    SELECT STATEMENT                                             39616             15           8003584                 27262976         16 ON CPU(16)                                                                                                                                                                                               
                                                   1    1      VIEW                                                       39616                                                                                                                                                                                                                                                                               
                                                   2    2        SORT AGGREGATE                                                              3           9117696                 14680064          4 ON CPU(4)                                                                                                                                                                                                
                                                   3    3          NESTED LOOPS                                           39616              0           8069120                  5242880          1 ON CPU(1)                                                                                                                                                                                                
                                                   4    4            NESTED LOOPS                                         39616              1           4464640                 12582912          2 ON CPU(2)                                                                                                                                                                                                
                                                   5    5              NESTED LOOPS                                       37289              3           4709376                 18874368          4 ON CPU(4)                                                                                                                                                                                                
                                               -   6    5                HASH JOIN                                        12520              9           5906432                 20971520         10 ON CPU(10)                                                                                                                                                                                               
                                                   7    6                  NESTED LOOPS                                   12520              5           5644288                 31457280          9 ON CPU(6); gc cr block 2-way(2); gc cr block congested(1)                                                                                                                                                
                                               -   8    6                    STATISTICS COLLECTOR                                            3           5578752                 14680064          8 db file sequential read(5); ON CPU(2); ON CPU(1)                                                                                                                                                         
                                                   9    7                      TABLE ACCESS BY INDEX ROWID BATCHED          261             12           9707520                 28311552         39 ON CPU(13); gc current block 2-way(9); gc current block congested(6); gc cr block congested(3); gc cr block 2-way(2); ON CPU(2); db file sequential read(2); db file parallel read(2)                    
                                                  10    8                        INDEX RANGE SCAN                             9             61           9576448                 23068672        435 db file sequential read(347); ON CPU(28); db file parallel read(26); db file sequential read(13); gc current grant busy(13); ON CPU(5); gc current grant busy(2); gc cr block 2-way(1)                   
                                                  11    9                    PARTITION LIST ALL                              30             54          10100736                 22020096        317 db file sequential read(212); gc current grant busy(38); ON CPU(16); ON CPU(15); gc current grant 2-way(14); gcs drm freeze in enter server mode(11); gc current grant congested(4);.. 
                                                  12   10                      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED     30            299          11673600                 34603008       2784 db file sequential read(1217); db file sequential read(843); ON CPU(276); gc current grant busy(163); db file parallel read(125); db file parallel read(90); ON CPU(42); log buffer space(8);.. 
                                                  13   11                        INDEX RANGE SCAN                             0            331          10952704                 34603008       2048 db file sequential read(1266); gc current grant busy(413); ON CPU(230); gc current grant 2-way(47); gc cr grant 2-way(28); gc current grant congested(15); gcs drm freeze in enter server mode(10);.. 
                                               -  14   11                  PARTITION LIST ALL                                30             16           8069120                 20971520         27 db file sequential read(20); ON CPU(6); db file parallel read(1) -- **
                                               -  15   11                    TABLE ACCESS FULL                               30             26          10297344                 20971520         50 db file sequential read(45); db file parallel read(4); ON CPU(1) -- **
                                                  16   12                TABLE ACCESS BY INDEX ROWID BATCHED                  1            368          11852800                 35651584       4688 db file sequential read(3870); db file parallel read(465); ON CPU(334); read by other session(10); gc current grant busy(3); latch: gcs resource hash(2); latch: gc element(2); gc cr block 2-way(1);.. 
                                                  17   13                  INDEX RANGE SCAN                                   0            359          11542528                 35651584       4278 db file sequential read(2226); gc current grant busy(1126); ON CPU(907); log buffer space(10); buffer busy waits(4); gc current grant 2-way(2); latch: gcs resource hash(1); gc cr block 2-way(1);..  
                                                  18   14              INDEX UNIQUE SCAN                                      0             80          10625024                 29360128        152 db file sequential read(94); ON CPU(42); db file parallel read(16)                                                                                                                                       
                                                  19   15            TABLE ACCESS BY INDEX ROWID                              1            159          10690560                 35651584        319 db file sequential read(257); db file parallel read(34); ON CPU(25); read by other session(2); db file scattered read(1)                                                                                 
Recurs.waits                                 0     0       sql_id is null and plsql[_entry]_object_id is null                                0                 0                        0          2 log file sync(2)                                                                                                                                                                                         
Main SQL Summary                             0             ASH fixed 13011 execs from 380 sessions                                                                                                                        15224  ash rows were fixed from 01.04.2016 10:37:30 to 01.04.2016 12:01:51

— для правильного отображения Adaptive Plan к столбцу ID (sql_plan_line_id) добавлен указатель «-» опциональности шага выполнения

— столбец DISP отражает порядок реального выполнения отображаемого адаптивного плана, что делает чтение статитики в рассматриваемом случае (2 x FULL_PLAN_HASH_VALUE / 1 x PLAN_HASH_VALUE) занимательным занятием, к примеру, строки плана ID 14 и 15 (**) с неожиданными строками в ASH_ROWS следует относить к строкам плана с DISP 14 и 15 неадаптивного плана выполнения с SQL_FULL_PLAN_HASH_VALUE = 1196564125:

SQL> select Sql_Adaptive_Plan_Resolved,
  2         Sql_Full_Plan_Hash_Value,
  3         Sql_Plan_Line_Id,
  4         Sql_Plan_operation,
  5         count(*)
  6    from gv$active_session_history
  7   where sql_id = '35hnzb2q6xrkc'
  8     and sql_plan_hash_value = 434062702
  9     and Sql_Plan_Line_Id in (6, 8, 14, 15)
 10   group by Sql_Adaptive_Plan_Resolved,
 11            Sql_Full_Plan_Hash_Value,
 12            Sql_Plan_Line_Id,
 13            Sql_Plan_operation
 14   order by 2, 3
 15  /
 
SQL_ADAPTIVE_PLAN_RESOLVED SQL_FULL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID SQL_PLAN_OPERATION     COUNT(*)
-------------------------- ------------------------ ---------------- -------------------- ----------
                         1               1196564125                8 INDEX                         6
                         1               1196564125               14 INDEX                        27
                         1               1196564125               15 TABLE ACCESS                 50
                         1               3150513221                6 HASH JOIN                    10 -- ***
                         1               3150513221                8 STATISTICS COLLECTOR          2

— хотя и в ASH содержится статистический мусор для в действительности невыполняемой операции HASH JOIN (***)

Случай интересен также тем, что поскольку в AWR сохраняются планы по уникальному PLAN_HASH_VALUE, план с одним из 2-х использованных FULL_PLAN_HASH_VALUE — в этом случае FULL_PLAN_HASH_VALUE = 1196564125 — оказывается не сохранён для истории:

SQL> @sql_plan_hashs 35hnzb2q6xrkc
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR 35hnzb2q6xrkc            579460447      3109947038 21.03.2016 00:04:11
AWR 35hnzb2q6xrkc           3150513221       434062702 18.03.2016 00:00:14
SQL 35hnzb2q6xrkc           3150513221       434062702 02.04.2016 19:27:17

и впоследствии может быть восстановлен только искусственно, с использованием того же столбца DISP из DBA_HIST_SQL_PLAN.OTHER_XML (/other_xml/display_map/row@dis)

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

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

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