Oracle mechanics

15.07.2015

12c: Сравнение планов выполнения и выявление соответствий в паре PLAN_HASH_VALUE >-< FULL_PLAN_HASH_VALUE

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

Для одного из запросов внезапно сработал Statistics Feedback, что в V$SQL отразилось следующим образом:

EXEC LOAD_TIME   OPENING LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE    COST CHILD BIND_SENSITIVE BIND_AWARE SHARABLE FEEDBACK_STATISTIC    ROWS PARSE_CALLS
---- ----------- ------- ---------------- ------------ --------------- ------- ----- -------------- ---------- -------- ------------------ ------- -----------
   1 29.06 12:07       0      29.06 12:45   1912450710      2108937779  396162     0 N              N          N        Y                  1106472           1
   1 29.06 15:17       1      30.06 15:07  85765923098      3773907172 5597267     1 N              N          Y        N                        0           1

— т.е. 29.06 в 12:45 запрос выполнялся с нормальным планом 2108937779, а при следующем выполнении в 30.06 в 15:07 под действием FEEDBACK_STATISTIC=Y с новым планом 3773907172 наблюдается затянувшееся выполнение, что в принципе не является чем-то необычным, любопытно что в AWR таблице DBA_HIST_SQLSTAT оба выполнения записаны с одним планом 2108937779, что, мягко говоря, не способствует правильной диагностике (первая строка SNAP_ID=87604 соотв.1-му, последующие — 2-му выполнению):

12.1.0.2.@ SQL> @dba_hist_sqlstat "sql_id = '38dyq2ab12nju' and snap_id >= 87605"
 
INST BEGIN_SNAP_ID BEGIN_SNAP_TIME    EXECS ROWS_PROCESSED SQL_ID              PLAN       COST PARSE_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC DISK_READS_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC FETCHES_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US
---- ------------- --------------- -------- -------------- ------------- ---------- ---------- -------------- ------------ ------------ ------------- ------------------- ---------------- -------------- ------------------ --------------- ---------------------- ------------- ---------------- ---------------- -------------------
   2         87604 29.06 12:30            0              0 38dyq2ab12nju 2108937779     396162              0    553193496    117012000      10507935             1083047             8461         954997                910            7763                 116445       1106472           158068        462927031             3887793
   2         87609 29.06 15:00            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
   2         87610 29.06 15:30            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
...
   2         87655 30.06 14:00            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
   2         87656 30.06 14:30            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0


Более того, в ASH и истории ея (DBA_HIST_ACTIVE_SESS_HISTORY) записи о медленном выполнении записаны с PLAN_HASH_VALUE = 0, правда, с ненулевым FULL_PLAN_HASH_VALUE:

SQL> select sql_id,
  2         sql_exec_id,
  3         sql_plan_hash_value                                    as PLAN_HASH_VALUE,
  4         sql_full_plan_hash_value                               as FULL_PLAN_HASH_VALUE,
  5         sql_plan_line_id as LINE_ID,
  6         sql_plan_operation || ' ' || sql_plan_options          as PLAN_OPERATION,
  7         decode(session_state, 'WAITING', event, session_state) as SESSION_WAIT,
  8         count(*)                                               as ASH_ROWS,
  9         min(cast(sample_time as date))                         as MIN_SAMPLE_TIME,
 10         max(cast(sample_time as date))                         as MAX_SAMPLE_TIME
 11    from dba_hist_active_sess_history
 12   where snap_id between 87609 and 87660
 13     and sql_id = '38dyq2ab12nju' and sql_exec_id = 33554433
 14   group by sql_id,
 15            sql_exec_id,
 16            sql_plan_hash_value,
 17            sql_full_plan_hash_value,
 18            sql_plan_line_id,
 19            sql_plan_operation || ' ' || sql_plan_options,
 20            decode(session_state, 'WAITING', event, session_state),
 21            event
 22   order by nvl(sql_plan_line_id, 0), count(*) desc
 23  /
 
SQL_ID        SQL_EXEC_ID PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE LINE_ID PLAN_OPERATION                      SESSION_WAIT              ASH_ROWS MIN_SAMPLE_TIME MAX_SAMPLE_TIME
------------- ----------- --------------- -------------------- ------- ----------------------------------- ----------------------- ---------- --------------- ---------------
38dyq2ab12nju    33554433               0           2890587154         SELECT STATEMENT                    ON CPU                           1 29.06.2015 15:1 29.06.2015 15:1
38dyq2ab12nju    33554433               0           2890587154      19 INDEX RANGE SCAN                    db file sequential read         10 29.06.2015 15:1 29.06.2015 15:1
38dyq2ab12nju    33554433               0           2890587154      37 HASH JOIN                           ON CPU                          10 29.06.2015 15:1 29.06.2015 15:3
38dyq2ab12nju    33554433               0           2890587154      37 HASH JOIN                           direct path write temp           2 29.06.2015 15:3 29.06.2015 15:3
38dyq2ab12nju    33554433               0           2890587154      38 HASH JOIN RIGHT SEMI NA             ON CPU                           2 29.06.2015 15:3 29.06.2015 15:3
38dyq2ab12nju    33554433               0           2890587154      41 TABLE ACCESS FULL                   direct path read                95 29.06.2015 15:1 29.06.2015 15:4
38dyq2ab12nju    33554433               0           2890587154      41 TABLE ACCESS FULL                   ON CPU                          19 29.06.2015 15:2 29.06.2015 15:4
38dyq2ab12nju    33554433               0           2890587154      47 TABLE ACCESS BY INDEX ROWID BATCHED ON CPU                        6523 29.06.2015 15:4 30.06.2015 15:0
38dyq2ab12nju    33554433               0           2890587154      47 TABLE ACCESS BY INDEX ROWID BATCHED db file sequential read          3 30.06.2015 0:23 30.06.2015 8:14
38dyq2ab12nju    33554433               0           2890587154      48 INDEX RANGE SCAN                    ON CPU                        1895 29.06.2015 15:4 30.06.2015 15:0
38dyq2ab12nju    33554433               0           2890587154      48 INDEX RANGE SCAN                    db file sequential read         12 29.06.2015 15:4 30.06.2015 8:00

, что опять же не добавляет удобства при диагностике проблем/причин изменений плана запроса(

Но, если посмотреть соответствие SQL_ID и планов выполнения с помощью соотв.скрипта, выбирающего для SQL_ID планы с совпадением по full_plan_hash_value ИЛИ plan_hash_value:

SQL> --              SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE
SQL> --              V             V                    V
SQL> @sql_plan_hashs 38dyq2ab12nju 2890587154           2108937779
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR 38dyq2ab12nju           1611726134      2108937779 29.06.2015 12:07:23 -- быстрое выполнение
AWR 38dyq2ab12nju           2890587154      3773907172 29.06.2015 15:17:46 -- медленное выполнение

— можно видеть, что быстрое и медленное выполнения характеризуются вполне уникальными парами (PLAN_HASH_VALUE, FULL_PLAN_HASH_VALUE), что однако является скорее случайностью/удачей [см.Прим.№1]

Что же касается именно этого проблемного запроса 38dyq2ab12nju, то, получив плохой и хороший PLAN_HASH_VALUE, можно, для начала сравнить эти планы в части проблемного блока SEL$7A35C063 с точки зрения CBO:

SQL> @sql_plan_diff 38dyq2ab12nju 2108937779 38dyq2ab12nju 3773907172 SEL$7A35C063
 
-------------------------------
SQL Plans [by Query block] diff
-------------------------------
 
PLAN_HASH_VALUE QBLOCK_NAME    ID OPERATION                                         CARDINALITY                BYTES COST(IO)               TEMP TIME
--------------- ------------ ---- --------------------------------------------- --------------- -------------------- ---------------- ---------- ----
2108937779      SEL$7A35C063   27 HASH GROUP BY                                               1                  238 68059(67988)                   3 --*
                               28  HASH JOIN SEMI NA                                          1                  238 68058(67988)                   3
                               29     NESTED LOOPS                                            1                  214 68055(67985)                   3
                               30      NESTED LOOPS                                           1                  204 68053(67983)                   3
                               31       NESTED LOOPS OUTER                                    1                  154 68046(67976)                   3
                               32        NESTED LOOPS OUTER                                   1                  130 68044(67974)                   3
                               33         NESTED LOOPS                                        1                  109 68042(67972)                   3
                               34          SORT UNIQUE                                        1                   24 4(4)                           1
                SEL$7A35C063   35           TABLE ACCESS BY INDEX ROWID BATCHED               1                   24 4(4)                           1
                SEL$7A35C063   36            INDEX RANGE SCAN                                 1                      3(3)                           1
                SEL$7A35C063   37          TABLE ACCESS BY INDEX ROWID BATCHED           152457             12958845 68037(67968)                   3
                SEL$7A35C063   38           INDEX RANGE SCAN                             152457                      22224(22187)                   1
                SEL$7A35C063   39         TABLE ACCESS BY INDEX ROWID                         1                   21 2(2)                           1
                SEL$7A35C063   40          INDEX UNIQUE SCAN                                  1                      1(1)                           1
                SEL$7A35C063   41        TABLE ACCESS BY INDEX ROWID                          1                   24 2(2)                           1
                SEL$7A35C063   42         INDEX UNIQUE SCAN                                   1                      1(1)                           1
                SEL$7A35C063   43       TABLE ACCESS BY INDEX ROWID BATCHED                   1                   50 7(7)                           1
                SEL$7A35C063   44        INDEX RANGE SCAN                                    13                      3(3)                           1
                SEL$7A35C063   45      TABLE ACCESS BY INDEX ROWID                            1                   10 2(2)                           1
                SEL$7A35C063   46       INDEX UNIQUE SCAN                                     1                      1(1)                           1
                SEL$7A35C063   47   TABLE ACCESS BY INDEX ROWID BATCHED                       1                   24 3(3)                           1
                SEL$7A35C063   48    INDEX RANGE SCAN                                         1                      2(2)                           1
--------------- ------------      ---------------------------------------------                                                                  
3773907172      SEL$7A35C063   32 HASH GROUP BY                                         7557780           1798751640 4369027(4332608) 1934803000  171 -- **
                               33  NESTED LOOPS OUTER                                   7557780           1798751640 3980397(3944600)             156
                               34   NESTED LOOPS OUTER                                        1                  214 3980395(3944598)             156
                               35    NESTED LOOPS                                             1                  193 3980393(3944596)             156
                               36     MERGE JOIN CARTESIAN                                    1                  108 3951483(3915714)             155
                               37      HASH JOIN                                           3652               306768 3940523(3904758)             154
                               38       HASH JOIN RIGHT SEMI NA                            3652               270248 3937959(3902212)             154
                SEL$7A35C063   39        TABLE ACCESS BY INDEX ROWID BATCHED                  9                  216 5(5)                           1
                SEL$7A35C063   40         INDEX RANGE SCAN                                   27                      3(3)                           1
                SEL$7A35C063   41        TABLE ACCESS FULL                             13411860            670593000 3937894(3902207)             154
                SEL$7A35C063   42       TABLE ACCESS FULL                                330722              3307220 2563(2546)                     1
                               43      BUFFER SORT                                            1                   24 3948920(3913168)             155
                               44       SORT UNIQUE                                           1                   24 3(3)                           1
                SEL$7A35C063   45        TABLE ACCESS BY INDEX ROWID BATCHED                  1                   24 3(3)                           1
                SEL$7A35C063   46         INDEX RANGE SCAN                                    1                      2(2)                           1
                SEL$7A35C063   47     TABLE ACCESS BY INDEX ROWID BATCHED                     1                   85 28911(28882)                   2
                SEL$7A35C063   48      INDEX RANGE SCAN                                   64780                      9445(9429)                     1
                SEL$7A35C063   49    TABLE ACCESS BY INDEX ROWID                              1                   21 2(2)                           1
                SEL$7A35C063   50     INDEX UNIQUE SCAN                                       1                      1(1)                           1
                SEL$7A35C063   51   TABLE ACCESS BY INDEX ROWID                  47971836128344  1,15132406708026E15 2(2)                           1
                SEL$7A35C063   52    INDEX UNIQUE SCAN                                        1                      1(1)                           1
 
------------------------------
SQL Plan "Notes" sections diff
------------------------------
 
PHV_2108937779                           PHV_3773907172
---------------------------------------- ----------------------------------------
                                         
   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:   yes -- ***
   perf_feedback:                           perf_feedback:
   adaptive_plan:   yes                     adaptive_plan:   yes
   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:  1611726134              plan_hash_full:  2890587154
   plan_hash:       2108937779              plan_hash:       3773907172
   plan_hash_2:     270508258               plan_hash_2:     4236691329
 
 
--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_2108937779                                                                                       PHV_3773907172
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$7A35C063" "APP"@"SEL$5")                                           
INDEX_RS_ASC(@"SEL$7A35C063" "APP"@"SEL$5" ("AR_RECEIVABLE_APPLICATIONS_ALL"."CASH_RECEIPT_ID"))     
INDEX_RS_ASC(@"SEL$7A35C063" "CC"@"SEL$5" ("GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID"))            
LEADING(@"SEL$7A35C063" "ORG_INFO"@"SEL$6" "PS"@"SEL$5" "CUST_ACCT"@"SEL$5" "PARTY"@"SEL$5" "APP"@"S 
EL$5" "CC"@"SEL$5" "ORG_INFO"@"SEL$7")                                                               
USE_NL(@"SEL$7A35C063" "APP"@"SEL$5")                                                                
USE_NL(@"SEL$7A35C063" "CC"@"SEL$5")                                                                 
                                                                                                     FULL(@"SEL$7A35C063" "APP"@"SEL$5") -- ****
                                                                                                     FULL(@"SEL$7A35C063" "CC"@"SEL$5")  -- ****
                                                                                                     LEADING(@"SEL$7A35C063" "APP"@"SEL$5" "ORG_INFO"@"SEL$7" "CC"@"SEL$5" "ORG_INFO"@"SEL$6" "PS"@"SEL$5
                                                                                                     " "CUST_ACCT"@"SEL$5" "PARTY"@"SEL$5")
                                                                                                     SWAP_JOIN_INPUTS(@"SEL$7A35C063" "ORG_INFO"@"SEL$7")
                                                                                                     USE_HASH(@"SEL$7A35C063" "CC"@"SEL$5")
                                                                                                     USE_MERGE(@"SEL$7A35C063" "ORG_INFO"@"SEL$6") -- *****

— и увидеть:

1) что причиной резкого изменения плана было применение Cardinality Feedback с вытекающей/последующей Reoptimization (***) через применение Reoptimization Hints, при этом, несмотря на активное использование в запросе связанных переменных, ни BIND_SENSITIVE ни BIND_AWARE запрос признан не был, что также поспособствовало смелым изменениям плана

2) собственно хороший(*) и плохой(**) планы отличаются заметно, на пару порядков минимум, по всем вычисляемым оптимизатором метрикам: COST, CARDINALITY, BYTES, TEMP, TIME, но поскольку применение Statistics Feedback / Reoptimization выполняется уже без оглядки на CBO (точнее, в противоположность ему, при этом все-таки используя оптимизатор при вычислении/построении результирующего плана(!)) наблюдается противоречие между расчётами CBO и решимостью к реоптимизации/Reoptimization

3) вследствие Reoptimization в Outline части «плохого» плана появляются канонически неблаговидные хинты(****), которые в результате и определяют неудачный план и выполнение

Примечание №1

Как я уже отметил, вышеописанный случай — удачный в плане уникального соответствия в парах PLAN_HASH_VALUE — FULL_PLAN_HASH_VALUE, для других запросов это соотношение может быть совсем не уникальным, к примеру, нетрудно заметить запрос (0dzx2vt6xwpp3), для которого FULL_PLAN_HASH_VALUE = 3967811173 соответствует пара PLAN_HASH_VALUE:

SQL> @sql_plan_hashs 0dzx2vt6xwpp3 3967811173
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR 0dzx2vt6xwpp3           3967811173      1426659657 04.06.2015 11:55:42
AWR 0dzx2vt6xwpp3           3967811173      4231429026 04.06.2015 11:55:42
SQL 0dzx2vt6xwpp3           3967811173      4231429026 07.07.2015 14:56:59

Продолжая, можно увидеть множество запросов, с соотношением FULL_PLAN_HASH_VALUE — PLAN_HASH_VALUE один ко многим:

SQL> select inst_id,
  2         sql_id,
  3         full_plan_hash_value,
  4         count(distinct plan_hash_value),
  5         LISTAGG(plan_hash_value, ',') WITHIN GROUP (order by plan_hash_value) as PLAN_HASH_LIST
  6    from (select distinct inst_id, sql_id, full_plan_hash_value, plan_hash_value from gv$sql_plan)
  7   group by inst_id, sql_id, full_plan_hash_value
  8  having count(distinct plan_hash_value) >= 4
  9  order by count(distinct plan_hash_value) desc
 10  /
 
   INST_ID SQL_ID        FULL_PLAN_HASH_VALUE COUNT(DISTINCTPLAN_HASH_VALUE) PLAN_HASH_LIST
---------- ------------- -------------------- ------------------------------ ---------------------------------------------------------------
         2 4wbkdxm8a5zwr            833499037                              6 171337234,887616622,1226677234,3747391061,3847024824,4077132808
         1 4wbkdxm8a5zwr            157483128                              4 617110974,887616622,1587960748,4155149535
         1 8b59f8nttup79           3573279419                              4 536520638,606974983,1599916361,2976288278
         1 f5qvh0dfqv85j           2596262501                              4 839643910,2575261189,3162305992,3927587791
         2 bdd5t84a38knr           4178099976                              4 588026345,1231554927,3602929895,3803334905
         2 8uqaq29s76y2n           2864692843                              4 52185611,1551580374,1822532883,4109099670
         2 b7gtn71dk4ut8           4282075884                              4 377040364,755204711,1088002384,1348545322
         1 40pncms8ws73m            985029131                              4 657251300,1237042760,1604105196,2723391066
         1 f5qvh0dfqv85j           3067941791                              4 110267802,1322978784,1949663041,4143461477
...

SQL> @shared_cu12s_noxml 4wbkdxm8a5zwr
 
   INST_ID BIND_SENSE BIND_AWARE FEEDBACK_STATS OPT_STATS REOPT ADAPT BIND_EQ_FAIL ROLL_INVALID_MISMATCH CURSOR_COUNT  PHV_COUNT FPHV_COUNT    EXECS USERS_OPENING
---------- ---------- ---------- -------------- --------- ----- ----- ------------ --------------------- ------------ ---------- ---------- -------- -------------
         2 Y          Y          N              N         N     Y     Y            Y                               33         11          5      280             2
         1 Y          Y          N              N         N     Y     Y            Y                               10          7          4       52             1
         2 Y          N          Y              N         Y     Y     N            Y                                8          8          8       23             1
         2 Y          Y          Y              N         Y     Y     Y            Y                                7          4          6       20             0
         2 Y          Y          Y              N         Y     Y     N            Y                                4          4          4       21             0
         1 Y          N          Y              N         Y     Y     N            Y                                3          3          3        5             0
         2 Y          N          N              N         N     Y     N            Y                                3          3          3       59             0
         2 Y          Y          N              N         N     Y     N            Y                                3          3          3       84             0
         1 Y          Y          N              N         N     Y     Y            N                                2          2          2       42             0
         2 N          N          Y              N         N           N            Y                                2          2          2        8             0
         2 Y          Y          N              N         N           N            Y                                1          1          1        6             0
         1 N          N          Y              N         Y     Y     N            Y                                1          1          1       15             0
         1 Y          N          Y              N         Y     Y     N            N                                1          1          1        2             0
 
13 rows selected

— сформированных под влиянием Adaptive Cursor Sharing (BIND_AWARE), Cardinality Feedback + Reoptimization (FEEDBACK_STATS = REOPT), Adaptive Plan (ADAPT) и их комбинаций

И в обратном порядке — одному набору (sql_id, plan_hash_value) может соответствовать более одного неповторяющегося FULL_PLAN_HASH_VALUE:

SQL> select inst_id,
  2         sql_id,
  3         plan_hash_value,
  4         count(distinct s.full_plan_hash_value)
  5    from gv$sql s
  6   group by inst_id, sql_id, plan_hash_value
  7  having count(distinct s.full_plan_hash_value) > 2
  8   order by count(distinct s.full_plan_hash_value) desc
  9  /
 
INST_ID SQL_ID        PLAN_HASH_VALUE COUNT(DISTINCTS.FULL_PLAN_HASH
------- ------------- --------------- ------------------------------
      1 6sypg9sqphq0c      2768407924                              4
      1 8nxbnp8cdvd01      2119751493                              3
      1 3faqzvzkj0jmc       697602395                              3
      1 8zfxw40scs2yx       209789994                              3
      1 d4ctn2j9q1k11       598379885                              3
      1 6sypg9sqphq0c      2682244003                              3
      1 91mhcwz2h69k8      3447023776                              3
...
 
SQL> @shared_cu12_noxml 6sypg9sqphq0c 2768407924
 
EXECS LAST_ACTIVE_TIME    ELA_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  OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON#1                                 REASON#2
----- ------------------- ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- ---------------- ---------------- ---- ---------------------------------------- -------------------------------------
    2 28.06.2015 18:01:21       158033 VALID              2768407924           4020462225             43    14 N          N          Y          N                  N                 Y     16        3         N                N                Y    Rolling Invalidate Window Exceeded(2)    Rolling Invalidate Window Exceeded(3)
    1 02.07.2015 06:04:12       312318 VALID              2768407924           3709612064             44    19 N          N          N          Y                  Y               2 Y     15        4         N                N                Y    Auto Reoptimization Mismatch(1)                                               
    3 03.07.2015 00:03:06       112281 VALID              2768407924           4020462225                   20 N          N          N          N                  N                                           N                N                Y    Rolling Invalidate Window Exceeded(2)    Rolling Invalidate Window Exceeded(3)
    1 05.07.2015 12:03:21       313257 VALID              2768407924            521662218                   25 N          N          N          Y                  N                                           N                N                Y    Auto Reoptimization Mismatch(1)                                               
    1 06.07.2015 06:04:18       308021 VALID              2768407924            521662218                   27 N          N          N          Y                  N                                           N                N                Y    Auto Reoptimization Mismatch(1)                                               
    5 12.07.2015 06:04:19       129208 VALID              2768407924            521662218             44     7 N          N          N          Y                  Y               2 Y     16        3         N                N                Y    Auto Reoptimization Mismatch(1)          Rolling Invalidate Window Exceeded(3)
    5 12.07.2015 18:01:41       127063 VALID              2768407924           1335054151             43     9 N          N          Y          N                  N                 Y     16        3         N                N                Y    Rolling Invalidate Window Exceeded(2)    Rolling Invalidate Window Exceeded(3)
    5 13.07.2015 00:03:16       113518 VALID              2768407924            521662218             44    10 N          N          N          Y                  Y               2 Y     15        4         N                N                Y    Auto Reoptimization Mismatch(1)          Rolling Invalidate Window Exceeded(3)
 
8 rows selected

— иногда сформированных с разной стоимостью по схожей комбинации причин: Cardinality Feedback + Reoptimization, SQL Plan Directives + Dynamic Statistics и Adaptive Plan (при этом заметно, что для указанных запросов присутствует проблема High Version Count, но это — другая сторона вопроса

Тем не менее, поскольку уникальное соответствие в парах PLAN_HASH_VALUE — FULL_PLAN_HASH_VALUE в общем случае не наблюдается, а осмысленное значение PLAN_HASH_VALUE в ASH (и, как следствие, в DBA_HIST_SQLSTAT) в верскии 12.1.0.2, по крайней мере, отобращается далеко не всегда:

SQL> select decode(NVL(SQL_PLAN_HASH_VALUE, -1),
  2                     0, 'PHV = 0',
  3                    -1, 'PHV is null',
  4                        'PHV > 0') as PLAN_HASH_VALUE,
  5         decode(nvl(sql_plan_line_id, -1),
  6                     0, 'line_id = 0',
  7                    -1, 'line_id is null',
  8                        'line_id > 0') as SQL_PLAN_LINE_ID,
  9         count(*)
 10    from gv$active_session_history
 11   where sql_opname = 'SELECT'
 12   group by decode(NVL(SQL_PLAN_HASH_VALUE, -1),
 13                        0, 'PHV = 0',
 14                       -1, 'PHV is null',
 15                           'PHV > 0'),
 16            decode(nvl(sql_plan_line_id, -1),
 17                        0, 'line_id = 0',
 18                       -1, 'line_id is null',
 19                           'line_id > 0')
 20   order by 1, count(*) desc
 21  /
 
PLAN_HASH_VALUE SQL_PLAN_LINE_ID   COUNT(*)
--------------- ---------------- ---------- -- до 10% записей ASH типа SELECT могут иметь PLAN_HASH_VALUE = 0
PHV = 0         line_id > 0           10568 -- при этом в бОльшей части записей указана SQL_PLAN_LINE_ID > 0, т.е. какой-то план подразумеваются
PHV = 0         line_id is null        1446
PHV = 0         line_id = 0            1416
PHV > 0         line_id > 0          144920
PHV > 0         line_id = 0           10899
PHV > 0         line_id is null        5080

, снимки V$ACTIVE_SESSION_HISTORY имеет смысл совмещать с данными V$SQL как-то так:

create table ash_snap as
select s.plan_hash_value      as SQL_PHV,
       s.full_plan_hash_value as SQL_FPHV,
       s.last_load_time       as SQL_last_load_time,
       s.last_active_time     as SQL_last_active_time,
       ash.*
  from gv$active_session_history ash
  left join gv$sql s
    on ash.sql_id = s.sql_id
   and ash.sql_child_number = s.child_number
   and ash.inst_id = s.inst_id
/

— для получения надёжных значений PLAN_HASH_VALUE при последующем разборе полётов

P.S. Параноидальная проверка на соответствие [FULL_]PLAN_HASH_VALUE, фиксируемого Oracle в V$SQL и в столце V$SQL_PLAN.OTHER_XML показала,
что эти поля заполняются в целом согласованно, а немногочисленные исключения носят кратковременный характер:)

SQL> select inst_id,
  2         sql_id,
  3         child_address,
  4         s.plan_hash_value  as SQL_PLAN_HASH_VALUE,
  5         ph.plan_hash_value as OTHER_XML_PHV
  6    from gv$sql s
  7         left join
  8         (select inst_id,
  9                 sql_id,
 10                 child_address,
 11                 to_number(extractvalue(h.column_value, '/info')) plan_hash_value
 12            from gv$sql_plan p,
 13                 table(xmlsequence(extract(xmltype(p.other_xml), '/other_xml/info'))) h
 14           where p.other_xml is not null
 15             and extractvalue(h.column_value, '/info/@type') = 'plan_hash') PH
 16          using(inst_id, sql_id, child_address)
 17    where s.plan_hash_value <> ph.plan_hash_value
 18  /
 
   INST_ID SQL_ID        CHILD_ADDRESS    SQL_PLAN_HASH_VALUE OTHER_XML_PHV
---------- ------------- ---------------- ------------------- ---------------
         1 8m8nk268jp27q 00000027D1E6E0B0                   0       718499952 
         2 dxhckxz65p53p 00000028BFE4D068                   0      1742286823
         1 asgrz0k3964t0 00000029997A1A70          1029610772       400753227 -- странное не-0 значение в V$SQL.PLAN_HASH_VALUE
         1 byw5vxu25mquc 0000002874619E00                   0      4058149677

4 rows selected -- немногочисленные исключения, лишь подтверждающие

SQL> select inst_id,
  2         sql_id,
  3         child_address,
  4         s.plan_hash_value  as SQL_PLAN_HASH_VALUE,
  5         ph.plan_hash_value as OTHER_XML_PHV
  6    from gv$sql s
  7         left join
  8         (select inst_id,
  9                 sql_id,
 10                 child_address,
 11                 to_number(extractvalue(h.column_value, '/info')) plan_hash_value
 12            from gv$sql_plan p,
 13                 table(xmlsequence(extract(xmltype(p.other_xml), '/other_xml/info'))) h
 14           where p.other_xml is not null
 15             and extractvalue(h.column_value, '/info/@type') = 'plan_hash') PH
 16          using(inst_id, sql_id, child_address)
 17          where (inst_id, sql_id, child_address) in ((1,'8m8nk268jp27q','00000027D1E6E0B0')
 18                                                    ,(2,'dxhckxz65p53p','00000028BFE4D068')
 19                                                    ,(1,'asgrz0k3964t0','00000029997A1A70')
 20                                                    ,(1,'byw5vxu25mquc','0000002874619E00'))
 21  /
 
   INST_ID SQL_ID        CHILD_ADDRESS    SQL_PLAN_HASH_VALUE OTHER_XML_PHV
---------- ------------- ---------------- ------------------- ---------------
         1 8m8nk268jp27q 00000027D1E6E0B0           718499952       718499952
         2 dxhckxz65p53p 00000028BFE4D068          1742286823      1742286823 -- сразу же последовшая проверка
         1 asgrz0k3964t0 00000029997A1A70           400753227       400753227 -- уже не содержит расхождений
         1 byw5vxu25mquc 0000002874619E00          4058149677      4058149677

SQL> -- Тот же рез-т можно видеть и по FULL_PLAN_HASH_VALUE
SQL> select inst_id,
  2         sql_id,
  3         child_address,
  4         s.full_plan_hash_value,
  5         ph.full_plan_hash_value
  6    from gv$sql s
  7         left join
  8         (select inst_id,
  9                 sql_id,
 10                 child_address,
 11                 to_number(extractvalue(h.column_value, '/info')) full_plan_hash_value
 12            from gv$sql_plan p,
 13                 table(xmlsequence(extract(xmltype(p.other_xml), '/other_xml/info'))) h
 14           where p.other_xml is not null
 15             and extractvalue(h.column_value, '/info/@type') = 'plan_hash_full') PH
 16          using(inst_id, sql_id, child_address)
 17    where s.full_plan_hash_value <> ph.full_plan_hash_value
 18  /
 
   INST_ID SQL_ID        CHILD_ADDRESS    FULL_PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE
---------- ------------- ---------------- -------------------- --------------------
         2 50r59pqt8d4mk 000000284AB41EC8                    0           1605778374
         2 1c98hkca35uzs 000000284316B848                    0           3868623365
 
2 rows selected 
 
SQL> select inst_id,
  2         sql_id,
  3         child_address,
  4         s.full_plan_hash_value,
  5         ph.full_plan_hash_value
  6    from gv$sql s
  7         left join
  8         (select inst_id,
  9                 sql_id,
 10                 child_address,
 11                 to_number(extractvalue(h.column_value, '/info')) full_plan_hash_value
 12            from gv$sql_plan p,
 13                 table(xmlsequence(extract(xmltype(p.other_xml), '/other_xml/info'))) h
 14           where p.other_xml is not null
 15             and extractvalue(h.column_value, '/info/@type') = 'plan_hash_full') PH
 16          using(inst_id, sql_id, child_address)
 17    where (inst_id, sql_id, child_address) in ((2,'50r59pqt8d4mk','000000284AB41EC8')
 18                                              ,(2,'1c98hkca35uzs','000000284316B848'))
 19  /
 
   INST_ID SQL_ID        CHILD_ADDRESS    FULL_PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE
---------- ------------- ---------------- -------------------- --------------------
         2 50r59pqt8d4mk 000000284AB41EC8           1605778374           1605778374
         2 1c98hkca35uzs 000000284316B848           3868623365           3868623365

— т.е. столбцы V$SQL лишь изредка могут заполняться с небольшой задержкой по отношению к V$SQL_PLAN

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

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

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