Oracle mechanics

25.05.2017

Формальный SQL тюнинг с использованием SQL Patch

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 00:00
Tags: ,

Активно читающий запрос из AWR:

SQL ordered by Reads           DB/Inst: ORCL/orcl2  Snaps: 344667-344671
-> %Total - Physical Reads as a percentage of Total Disk Reads
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Total Disk Reads:     369,567,665
-> Captured SQL account for   82.3% of Total

   Physical              Reads              Elapsed                             
      Reads  Executions per Exec   %Total   Time (s)   %CPU    %IO    SQL Id    
----------- ----------- ---------- ------ ---------- ------ ------ -------------
1.38570E+08          87 1.5928E+06   37.5   17,760.1   71.3   26.3 d95aab32us9ur -- вот этот
...
 65,092,102           1 6.5092E+07   17.6   12,031.6   55.6   35.2 apm9v1umcv9sa
...
 37,186,139           0        N/A   10.1    7,222.6   66.2   27.2 b6usrg82hwsa3
...

, заметность которого, похоже, обусловлена выбором и активным использованием, по-видимому, не самого оптимального плана 2944350538:

SQL> @shared_cu12_noxml d95aab32us9ur 0
 
EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ROWS_PER_EXEC ELA_PER_EXEC CURSOR_STATUS   PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE OPTIMIZER_STATS USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS V$SQL.Adapt XML.Adapt SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON BIND_EQ_FAILURE  CURSOR_PARTS_MISMATCH ROLL REASON#1                        SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
----- -------------------- ------------------- ------------- ------------ --------------- --------------- -------------- ----- ---------- ---------- --------- --------------- ------------------ --- ----- ----------- ----------- --------- --------- --------- -------- --- ---------- ---------------- --------------------- ---- ------------------------------- ----------------- --------- ---------------- -----------
    1 2017-05-19/00:44:46  19.05.2017 00:48:10         22712    203721659 INVALID_UNAUTH       2944350538           4058     6 Y          N          N         N               Y                      Y               4 Y           yes       24        6         2        1   hint       N                N                     Y    Auto Reoptimization Mismatch(1)
    1 2017-05-19/00:48:16  19.05.2017 00:48:18         20939      1487928 INVALID_UNAUTH       3645039897           3879     9 Y          N          N         N               Y                  yes Y               8 Y           yes       26        5         2        1   hint       N                N                     Y    Auto Reoptimization Mismatch(1)
    1 2017-05-19/00:48:25  19.05.2017 00:48:26         20832      1500673 INVALID_UNAUTH       2538717989           4094    11 Y          N          N         N               Y                  yes Y              11 Y           yes       26        5         2        1   hint       N                N                     Y    Auto Reoptimization Mismatch(1)
    1 2017-05-19/00:48:34  19.05.2017 00:48:36         21415      2796016 INVALID_UNAUTH       1465851797          62842    12 Y          N          N         N               Y                  yes Y                 N                     25        6         2        1   hint       N                N                     Y    Auto Reoptimization Mismatch(1)
    1 2017-05-19/01:36:48  19.05.2017 01:36:52         23806      2555929 VALID                 335288703           6010    13 Y          N          N         N               Y                  yes Y               9 Y           yes       24        7         2        1   hint       N                N                     N    Auto Reoptimization Mismatch(1)
    2 2017-05-19/01:36:56  19.05.2017 01:37:03         14978      1171290 VALID                2538717989           4046    15 Y          N          N         Y               Y                  yes Y              12 Y           yes       26        5         2        1   hint       N                N                     N    Bind mismatch(25)              
    1 2017-05-19/01:37:09  19.05.2017 01:37:10          6156       697269 VALID                1225157861           3764    20 Y          N          N         N               Y                      Y               5 Y           yes       25        6         2        1   hint       N                N                     N    Auto Reoptimization Mismatch(1)
    1 2017-05-19/01:50:03  19.05.2017 01:50:09         23392      5199280 VALID                2538717989           3883    21 Y          N          N         N               Y                  yes Y               8 Y           yes       26        5         2        1   hint       N                N                     N    Auto Reoptimization Mismatch(1)
    1 2017-05-19/01:50:18  19.05.2017 01:50:19          6156      1444397 VALID                1465851797           6037    22 Y          N          N         N               Y                  yes Y                 N                     25        6         2        1   hint       N                N                     N    Auto Reoptimization Mismatch(1)
  220 2017-05-19/01:55:04  19.05.2017 12:37:47         18450    179627186 VALID                2944350538           4426    23 Y          N          Y         N               N                      N                 Y           yes       24        6         2        1   hint       N                N                     N    Auto Reoptimization Mismatch(1)
 
10 rows selected
-- по причинам Reoptimization/Statistics Feedback -> SPD -> Dynamic Sampling для запроса со связанными переменными (BIND_SENSE=Y), не являющегося, однако Bind Aware эффектов (BIND_AWARE=N)
 
--------------------------------------------------------------
SQL_ID=d95aab32us9ur Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
EXECS LAST_ACTIVE_TIME    ELA_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE AVG_CBO_COST CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
----- ------------------- ------------ ------------- --------------- ------------ ------ ---------- ---------- ----- ----- ----------------- --------- ---------------- -----------
    1 19.05.2017 00:48:18      1487928         20939      3645039897         3879      1 Y          N          Y     Y
    1 19.05.2017 01:36:52      2555929         23806       335288703         6010      1 Y          N          Y     Y
    1 19.05.2017 01:37:10       697269          6156      1225157861         3764      1 Y          N          Y     Y
    4 19.05.2017 01:50:09      2260633         18545      2538717989         4008      3 Y          N          Y     Y
    2 19.05.2017 01:50:19      2120207         13786      1465851797        34440      2 Y          N          Y     N
  221 19.05.2017 12:37:47    179736211         18470      2944350538         4242      2 Y          N          Y     Y
-- статистика по планам показывает преобладание неудачного плана 2944350538 со ср.временем 179 сек и удачный 1225157861 ~ 0,7 сек
...
--------------------------------------------------------------
ASH TOP5 SQL_ID=d95aab32us9ur Executions by Elapsed Time
--------------------------------------------------------------
 
SQL_ID        SQL_PLAN_HASH_VALUE SQL_EXEC_ID   CHILD_ID ASH_ROWS DURATIONs                 MIN_SAMPLE_TIME           MAX_SAMPLE_TIME
------------- ------------------- ----------- ---------- -------- ------------------------- ------------------------- -------------------------
d95aab32us9ur          2944350538    33595327         23      282 +000000000 00:04:41.858   19-MAY-17 11.55.03.265 AM 19-MAY-17 11.59.45.123 AM
d95aab32us9ur          2944350538    33595325         23      276 +000000000 00:04:35.985   19-MAY-17 11.50.04.160 AM 19-MAY-17 11.54.40.145 AM
d95aab32us9ur          2944350538    33595323         23      241 +000000000 00:04:00.702   19-MAY-17 11.32.03.870 AM 19-MAY-17 11.36.04.572 AM
d95aab32us9ur          2944350538    33595315         23      239 +000000000 00:03:58.760   19-MAY-17 10.55.03.958 AM 19-MAY-17 10.59.02.718 AM
d95aab32us9ur          2944350538    33595313         23      238 +000000000 00:03:57.744   19-MAY-17 10.50.04.044 AM 19-MAY-17 10.54.01.788 AM

— ASH TOP5: самые длинные выполнения использовали неудачный план 2944350538 — всё сходится)

Сравним Outline удачного и неудачного планов:

SQL> @sql_plan_diff_outl_v2 d95aab32us9ur 1225157861 d95aab32us9ur 2944350538

--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_1225157861
----------------------------------------------------------------------------------------------------------------------
LEADING(@"SEL$5CDDB550" "DP"@"SEL$4" "PCB"@"SEL$4" "P"@"SEL$4" "DCRP"@"SEL$4" "RESULT_PAGE_DATA"@"SEL$4" "PD"@"SEL$4")
USE_NL(@"SEL$5CDDB550" "RESULT_PAGE_DATA"@"SEL$4") -- *
----------------------------------------------------------------------------------------------------
PHV_2944350538
----------------------------------------------------------------------------------------------------
LEADING(@"SEL$5CDDB550" "DP"@"SEL$4" "PCB"@"SEL$4" "RESULT_PAGE_DATA"@"SEL$4" "DCRP"@"SEL$4" "PD"@"SEL$4" "P"@"SEL$4")
SWAP_JOIN_INPUTS(@"SEL$5CDDB550" "RESULT_PAGE_DATA"@"SEL$4")
USE_HASH(@"SEL$5CDDB550" "RESULT_PAGE_DATA"@"SEL$4") -- *

— отличия в LEADING и методе соединения «RESULT_PAGE_DATA»@»SEL$4» (*)

Из ASH мониторинга выполнения запроса d95aab32us9ur с «плохим» планом 2944350538 может быть неочевидно, что именно Hash Join с RESULT_PAGE_DATA (линия 15) является причиной медленного выполнения — большая часть ash записей приходится на шаг 10 плана (также HASH JOIN, но более высокого уровня) и щаги 29-30 — NESTED LOOPS, расположенный также на пару уровней выше:

SQL> @ash_sqlmon2 d95aab32us9ur 2944350538
 
LAST_PLSQL    ID PLAN_OPERATION                                         QBLOCK_NAME     OBJECT_ALIAS           MAX_PGA_ALLOCATED MAX_TEMP_SPACE_ALLOCATED   ASH_ROWS WAIT_PROFILE
----------- ---- ------------------------------------------------------ --------------- ---------------------- ----------------- ------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------------
Main Query     0   SELECT STATEMENT                                                                                    166919168              10747904000          9 ON CPU(9)
               1     NESTED LOOPS                                       SEL$6                                                                                        
               2       MAT_VIEW ACCESS BY INDEX ROWID BATCHED           SEL$6           CR@SEL$6                                                                     
               3         INDEX RANGE SCAN                               SEL$6           CR@SEL$6                        59759616               7458521088          1 db file sequential read(1)
               4       TABLE ACCESS FULL                                SEL$6           CM@SEL$6                                                                     
               5     VIEW                                               SEL$3           EXP@SEL$5                                                                    
               6       WINDOW BUFFER                                    SEL$3                                                                                        
               7         TABLE ACCESS BY INDEX ROWID                    SEL$3           TPTE@SEL$3                                             
               8           INDEX RANGE SCAN                             SEL$3           TPTE@SEL$3                                             
               9     HASH GROUP BY                                      SEL$5CDDB550                                   262823936              14904459264         28 ON CPU(28)
              10       HASH JOIN                                                                                       298016768              14904459264       2712 ON CPU(1290); direct path read temp(979); direct path write temp(443)
              11         NESTED LOOPS                                                                                  168640512              11050942464         21 ON CPU(21)
              12           NESTED LOOPS                                                                                298016768              14800650240         36 ON CPU(36)
              13             HASH JOIN                                                                                 298016768              14591983616        601 ON CPU(601)
              14               MAT_VIEW ACCESS FULL                     SEL$5CDDB550    DCRP@SEL$4                                                                   
              15               HASH JOIN                                                                                97984512               3778019328          3 ON CPU(3)
              16                 INLIST ITERATOR                                                                                                                     
              17                   TABLE ACCESS BY INDEX ROWID BATCHED  SEL$5CDDB550    RESULT_PAGE_DATA@SEL$4                                                       
              18                     INDEX RANGE SCAN                   SEL$5CDDB550    RESULT_PAGE_DATA@SEL$4                                                       
              19                 HASH JOIN                                                                                                                           
              20                   NESTED LOOPS                                                                                                                      
              21                     NESTED LOOPS                                                                                                                    
              22                       STATISTICS COLLECTOR                                                                                                          
              23                         TABLE ACCESS FULL              SEL$5CDDB550    DP@SEL$4                                                                     
              24                       PARTITION RANGE SINGLE                                                          101523456               1108344832          1 ON CPU(1)
              25                         INDEX RANGE SCAN               SEL$5CDDB550    PCB@SEL$4                      195444736               7219445760         10 ON CPU(5); gc cr block busy(3); gc cr block 2-way(1); gc cr multi block request(1)
              26                     TABLE ACCESS BY LOCAL INDEX ROWID  SEL$5CDDB550    PCB@SEL$4                      197738496               6733955072         14 gc cr multi block request(5); ON CPU(4); gc cr block busy(2); db file parallel read(1); gc cr block 2-way(1); db file sequential read(1)
              27                   PARTITION RANGE SINGLE                                                                                                            
              28                     TABLE ACCESS FULL                  SEL$5CDDB550    PCB@SEL$4                                                                    
              29             INDEX RANGE SCAN                           SEL$5CDDB550    PD@SEL$4                       298016768              14835253248       1318 ON CPU(1318)
              30           TABLE ACCESS BY INDEX ROWID                  SEL$5CDDB550    PD@SEL$4                       298016768              14473494528        520 ON CPU(520)
              31         MAT_VIEW ACCESS FULL                           SEL$5CDDB550    P@SEL$4                        297492480              10140778496          8 ON CPU(5); gc cr multi block request(3)
SQL Summary    0 ASH fixed 30 execs from 30 sessions                                                                                                            5282  ash rows were fixed from 19.05.2017 10:56:42 to 19.05.2017 13:02:18

— кроме этого стоит отметить значительное потребление PGA и временного пространства: MAX_TEMP_SPACE_ALLOCATED => 14+GB + MAX_PGA_ALLOCATED => 300MB

Предыдущее сравнение Outline планов SQL_PLAN_DIFF_OUTL_V2 можно попробовать использовать для SQL Patch:

SQL> @sqlpatch+
&SQL_ID: d95aab32us9ur
&SQL_PATCH_TEXT: LEADING(@"SEL$5CDDB550" "DP"@"SEL$4" "PCB"@"SEL$4" "P"@"SEL$4" "DCRP"@"SEL$4" "RESULT_PAGE_DATA"@"SEL$4" "PD"@"SEL$4") USE_NL(@"SEL$5CDDB550" "RESULT_PAGE_DATA"@"SEL$4")
&SQL_PATCH_NAME: d95aab32us9ur_1225157861

PL/SQL procedure successfully completed
 
SQL_ID        NAME                      CATEGORY  CREATED               STATUS   FMATCH
------------- ------------------------- --------- --------------------- -------- ------
d95aab32us9ur d95aab32us9ur_1225157861  DEFAULT   19.05.2017 13:07:30   ENABLED  NO
 
SQL_PATCH_HINTS
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LEADING(@"SEL$5CDDB550" "DP"@"SEL$4" "PCB"@"SEL$4" "P"@"SEL$4" "DCRP"@"SEL$4" "RESULT_PAGE_DATA"@"SEL$4" "PD"@"SEL$4") USE_NL(@"SEL$5CDDB550" "RESULT_PAGE_DATA"@"SEL$4")

В рез-те:

SQL> @shared_cu12_noxml d95aab32us9ur 0
 
EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ROWS_PER_EXEC ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  OPTIMIZER_STATS  USE_FEEDBACK_STATS CF  REOPT REOPT_HINTS V$SQL.Adapt XML.Adapt SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON REASON#1                        SQL_PATCH
----- -------------------- ------------------- ------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ---------------- ------------------ --- ----- ----------- ----------- --------- --------- --------- -------- --- ---------- ------------------------------- ------------------------
...
    2 2017-05-23/04:20:03  23.05.2017 04:25:03         15650      1279105 VALID              1735353024           3769    49 Y          N          N          Y                N                      N                 Y           yes       6         4         2        1   hint       Bind mismatch(25)               d95aab32us9ur_1225157861
    1 2017-05-23/04:25:04  23.05.2017 04:26:18         23363      2419310 VALID              3714880446           5692    50 Y          Y          N          N                Y                      Y               9 Y           yes       6         4         2        1   hint       Optimizer mismatch(13)          d95aab32us9ur_1225157861
    1 2017-05-23/04:26:18  23.05.2017 04:32:03          7936      1856129 VALID               852232849 3,649378885668    51 Y          Y          N          N                Y                  yes Y               9 Y           yes       7         3         2        1   hint       Optimizer mismatch(13)          d95aab32us9ur_1225157861
    1 2017-05-23/04:32:03  23.05.2017 04:32:21         23363      5691866 VALID               852232849 3,649378885668    52 Y          Y          N          N                Y                  yes Y               9 Y           yes       7         3         2        1   hint       Optimizer mismatch(13)          d95aab32us9ur_1225157861
    1 2017-05-23/04:32:21  23.05.2017 04:37:07          7936      1658793 VALID               852232849 3,649378885668    53 Y          Y          N          N                Y                  yes Y               9 Y           yes       7         3         2        1   hint       Optimizer mismatch(13)          d95aab32us9ur_1225157861
    1 2017-05-23/04:37:07  23.05.2017 04:37:19         21133      5121533 VALID               852232849 3,649378885668    54 Y          Y          N          N                Y                  yes Y               9 Y           yes       7         3         2        1   hint       Optimizer mismatch(13)          d95aab32us9ur_1225157861
    1 2017-05-23/04:37:19  23.05.2017 04:37:32         23259      5939777 VALID               852232849 3,649378885668    55 Y          Y          N          N                Y                  yes Y                 Y           yes       7         3         2        1   hint       Optimizer mismatch(13)          d95aab32us9ur_1225157861
    1 2017-05-23/04:37:43  23.05.2017 04:37:45         23360      2169067 VALID              1735353024           3460    56 Y          N          N          N                Y                      Y               6 Y           yes       6         4         2        1   hint       Auto Reoptimization Mismatch(1) d95aab32us9ur_1225157861
    1 2017-05-23/04:37:52  23.05.2017 04:37:57         22712      5449022 VALID               852232849 3,734340501377    57 Y          N          N          N                Y                  yes Y                 Y           yes       7         3         2        1   hint       Auto Reoptimization Mismatch(1) d95aab32us9ur_1225157861
  289 2017-05-23/04:38:04  23.05.2017 16:32:21         20300      1945095 VALID              3714880446          48183    58 Y          N          Y          N                N                      N                 Y           yes       6         4         2        1   hint       Auto Reoptimization Mismatch(1) d95aab32us9ur_1225157861
 
59 rows selected -- курсоров многовато, но это другая некатастрофическая проблема
 
--------------------------------------------------------------
SQL_ID=d95aab32us9ur Shared Pool statistics by PLAN_HASH_VALUE -- **
--------------------------------------------------------------
 
EXECS ELA_PER_EXEC PLAN_HASH_VALUE AVG_CBO_COST CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH
----- ------------ --------------- ------------ ------ ---------- ---------- ----- ----- ----------------- ------------------------
   13      3156642      4093555613         3749     13 Y          N          Y     Y                       d95aab32us9ur_1225157861
   23      2513715      1735353024         3565     22 Y          N          Y     Y                       d95aab32us9ur_1225157861
    9      4500610       852232849 3,5961894428      8 Y          Y          Y     Y                       d95aab32us9ur_1225157861
  304      1986652      3714880446        13117     16 Y          Y          Y     Y                       d95aab32us9ur_1225157861

-- под влиянием SQL Patch ср.время выполнения снизилось со 170+ до 2-4 секунд
...
 
--------------------------------------------------------------
ASH TOP5 SQL_ID=d95aab32us9ur Executions by Elapsed Time
--------------------------------------------------------------
 
SQL_ID        SQL_PLAN_HASH_VALUE SQL_EXEC_ID   CHILD_ID ASH_ROWS DURATIONs                 MIN_SAMPLE_TIME           MAX_SAMPLE_TIME
------------- ------------------- ----------- ---------- -------- ------------------------- ------------------------- -------------------------
d95aab32us9ur          3714880446    33597258         58       16 +000000000 00:00:15.030   23-MAY-17 02.50.04.114 PM 23-MAY-17 02.50.19.144 PM
d95aab32us9ur          3714880446    33597259         58        4 +000000000 00:00:03.000   23-MAY-17 02.50.42.205 PM 23-MAY-17 02.50.45.205 PM
d95aab32us9ur          3714880446    33597261         58        3 +000000000 00:00:02.010   23-MAY-17 02.56.19.423 PM 23-MAY-17 02.56.21.433 PM
d95aab32us9ur          3714880446    33597276         58        3 +000000000 00:00:02.000   23-MAY-17 04.20.05.376 PM 23-MAY-17 04.20.07.376 PM
d95aab32us9ur          3714880446    33597277         58        3 +000000000 00:00:02.010   23-MAY-17 04.20.14.396 PM 23-MAY-17 04.20.16.406 PM
-- при максимальном времени - 16 секунд - разовый случай

Отличие используемых планов (**) от целевого PHV=1225157861 несущественны и не касаются ни порядка соединения/LEADING, ни метода соединения таблицы «RESULT_PAGE_DATA»@»SEL$4»:

SQL> @sql_plan_diff_outl_v2 d95aab32us9ur 1225157861 d95aab32us9ur 4093555613
--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_1225157861
----------------------------------------------------------------------------------------------------
INDEX(@"SEL$5CDDB550" "PD"@"SEL$4" ("T_PAGE_DATA"."PAGE_ID"))
NLJ_BATCHING(@"SEL$5CDDB550" "PD"@"SEL$4")
USE_NL(@"SEL$5CDDB550" "PD"@"SEL$4")
----------------------------------------------------------------------------------------------------
PHV_4093555613
----------------------------------------------------------------------------------------------------
FULL(@"SEL$5CDDB550" "PD"@"SEL$4")
USE_HASH(@"SEL$5CDDB550" "PD"@"SEL$4")
 
SQL> @sql_plan_diff_outl_v2 d95aab32us9ur 1225157861 d95aab32us9ur 1735353024
--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_1225157861
----------------------------------------------------------------------------------------------------
INDEX(@"SEL$5CDDB550" "PD"@"SEL$4" ("T_PAGE_DATA"."PAGE_ID"))
NLJ_BATCHING(@"SEL$5CDDB550" "PD"@"SEL$4")
SWAP_JOIN_INPUTS(@"SEL$5CDDB550" "DCRP"@"SEL$4")
USE_NL(@"SEL$5CDDB550" "PD"@"SEL$4")
----------------------------------------------------------------------------------------------------
PHV_1735353024
----------------------------------------------------------------------------------------------------
FULL(@"SEL$5CDDB550" "PD"@"SEL$4")
USE_HASH(@"SEL$5CDDB550" "PD"@"SEL$4")
 
SQL> @sql_plan_diff_outl_v2 d95aab32us9ur 1225157861 d95aab32us9ur 852232849
--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_1225157861
----------------------------------------------------------------------------------------------------
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5CDDB550" "RESULT_PAGE_DATA"@"SEL$4")
INDEX(@"SEL$5CDDB550" "PD"@"SEL$4" ("T_PAGE_DATA"."PAGE_ID"))
INDEX_RS_ASC(@"SEL$5CDDB550" "RESULT_PAGE_DATA"@"SEL$4" ("T_PAGE_DATA"."PAGE_ID"))
NLJ_BATCHING(@"SEL$5CDDB550" "PD"@"SEL$4")
USE_NL(@"SEL$5CDDB550" "PD"@"SEL$4")
----------------------------------------------------------------------------------------------------
PHV_852232849
----------------------------------------------------------------------------------------------------
FULL(@"SEL$5CDDB550" "PD"@"SEL$4")
FULL(@"SEL$5CDDB550" "RESULT_PAGE_DATA"@"SEL$4")
SWAP_JOIN_INPUTS(@"SEL$5CDDB550" "PD"@"SEL$4")
USE_HASH(@"SEL$5CDDB550" "PD"@"SEL$4")

SQL> @sql_plan_diff_notes d95aab32us9ur 1225157861 d95aab32us9ur 3714880446
 
------------------------------
SQL Plan "Notes" sections diff
------------------------------
 
PHV_1225157861                                     PHV_3714880446 -- частоиспользуемый план при создании использовал:
-------------------------------------------------- ----------------------------------------------
                                                   
   sql_profile:                                       sql_profile:
   sql_patch:                                         sql_patch:       "d95aab32us9ur_1225157861" -- созданный SQL Patch
   baseline:                                          baseline:
   outline:                                           outline:
   dyn_sampling:    2                                 dyn_sampling:    2                          -- Adaptive Dynamic Sampling
   dop:             1                                 dop:             1
   dop_reason:      hint                              dop_reason:      hint
   card_feedback:                                     card_feedback:
   perf_feedback:                                     perf_feedback:
   adaptive_plan:   yes                               adaptive_plan:   yes                       -- адаптивный план
   spd_used:        6                                 spd_used:        4                         -- директивы
   spd_valid:       25                                spd_valid:       6
   gtt_sess_stat:                                     gtt_sess_stat:
   db_version:      12.1.0.2                          db_version:      12.1.0.2
   plan_hash_full:  1265045415                        plan_hash_full:  3963608505
   plan_hash:       1225157861                        plan_hash:       3714880446
   plan_hash_2:     1919490158                        plan_hash_2:     4166406591
 
--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_1225157861
----------------------------------------------------------------------------------------------------
INDEX(@"SEL$5CDDB550" "PD"@"SEL$4" ("T_PAGE_DATA"."PAGE_ID"))
NLJ_BATCHING(@"SEL$5CDDB550" "PD"@"SEL$4")
USE_NL(@"SEL$5CDDB550" "PD"@"SEL$4")
----------------------------------------------------------------------------------------------------
PHV_3714880446
----------------------------------------------------------------------------------------------------
FULL(@"SEL$5CDDB550" "PD"@"SEL$4")
SWAP_JOIN_INPUTS(@"SEL$5CDDB550" "P"@"SEL$4")
USE_HASH(@"SEL$5CDDB550" "PD"@"SEL$4")

— патч работает

ASH мониторинг выполнения основного используемого плана 3714880446:

SQL> @ash_sqlmon2 d95aab32us9ur 3714880446
 
LAST_PLSQL   ID PLAN_OPERATION                                            QBLOCK_NAME     OBJECT_ALIAS           MAX_PGA_ALLOCATED MAX_TEMP_SPACE_ALLOCATED   ASH_ROWS WAIT_PROFILE
----------- --- --------------------------------------------------------- --------------- ---------------------- ----------------- ------------------------ ---------- ---------------------------------------------------------------------------------------------
Main Query    0   SELECT STATEMENT                                                                                                                                     
              1     NESTED LOOPS                                          SEL$6                                                                                        
              2       MAT_VIEW ACCESS BY INDEX ROWID BATCHED              SEL$6           CR@SEL$6                                                                     
              3         INDEX RANGE SCAN                                  SEL$6           CR@SEL$6                                                                     
              4       TABLE ACCESS FULL                                   SEL$6           CM@SEL$6                                                                     
              5     VIEW                                                  SEL$3           EXP@SEL$5                                                                    
              6       WINDOW BUFFER                                       SEL$3                                                                                        
              7         TABLE ACCESS BY INDEX ROWID                       SEL$3           TPTE@SEL$3                                             
              8           INDEX RANGE SCAN                                SEL$3           TPTE@SEL$3                                             
              9     HASH GROUP BY                                         SEL$5CDDB550                                    34283520                  1048576         23 ON CPU(23)
             10       HASH JOIN                                                                                           33103872                  1048576          4 ON CPU(4)
             11         NESTED LOOPS                                                                                                                                   
             12           NESTED LOOPS                                                                                                                                 
             13             STATISTICS COLLECTOR                                                                                                                       
             14               NESTED LOOPS                                                                                                                             
             15                 HASH JOIN                                                                                 21421056                        0          1 ON CPU(1)
             16                   MAT_VIEW ACCESS FULL                    SEL$5CDDB550    DCRP@SEL$4                                                                   
             17                   HASH JOIN                                                                               23387136                  1048576          3 ON CPU(3)
             18                     MAT_VIEW ACCESS FULL                  SEL$5CDDB550    P@SEL$4                          9969664                  1048576         13 ON CPU(10); gc cr multi block request(2); db file scattered read(1)
             19                     HASH JOIN                                                                                                                          
             20                       NESTED LOOPS                                                                                                                     
             21                         NESTED LOOPS                                                                                                                   
             22                           STATISTICS COLLECTOR                                                                                                         
             23                             TABLE ACCESS FULL             SEL$5CDDB550    DP@SEL$4                                                                     
             24                           PARTITION RANGE SINGLE                                                          26288128                  1048576          1 ON CPU(1)
             25                             INDEX RANGE SCAN              SEL$5CDDB550    PCB@SEL$4                       28254208                  1048576         11 ON CPU(7); db file parallel read(2); db file sequential read(1); gc cr multi block request(1)
             26                         TABLE ACCESS BY LOCAL INDEX ROWID SEL$5CDDB550    PCB@SEL$4                       30285824                  1048576         11 ON CPU(7); db file parallel read(2); db file sequential read(1); gc cr grant 2-way(1)
             27                       PARTITION RANGE SINGLE                                                                                                           
             28                         TABLE ACCESS FULL                 SEL$5CDDB550    PCB@SEL$4                                                                    
             29                 TABLE ACCESS BY INDEX ROWID BATCHED       SEL$5CDDB550    RESULT_PAGE_DATA@SEL$4          23404544                        0          2 ON CPU(2)
             30                   INDEX RANGE SCAN                        SEL$5CDDB550    RESULT_PAGE_DATA@SEL$4          29023232                  1048576         11 ON CPU(11)
             31             INDEX RANGE SCAN                              SEL$5CDDB550    PD@SEL$4                                                                     
             32           TABLE ACCESS BY INDEX ROWID                     SEL$5CDDB550    PD@SEL$4                                                                     
             33         TABLE ACCESS FULL                                 SEL$5CDDB550    PD@SEL$4                                                                     
SQL Summary   0 ASH fixed 42 execs from 41 sessions                                                                                                                 80  ash rows were fixed from 23.05.2017 14:55:05 to 23.05.2017 17:32:17

— показывает 80 строк на 42 выполнения / ~2 секунды на выполнение, 30MB PGA и 1MB MAX_TEMP_SPACE_ALLOCATED

Т.о. относительно простой и «мягкий» SQL Patch, полученный «формальным» методом, исправляя отдельные шаги плана, не фиксируя жёстко PLAN_HASH_VALUE и не блокируя 12c Adaptive Features, может быть полезен в ограничении оптимизатора набором удовлетворительных планов, один из которых (как в моём случае) может использоваться как основной/наиболее часто выполняемый

При этом новые планы под влиянием в т.ч. 12c Adaptive Features могут создаваться со временем, а «основной» план может меняться:

SQL> @shared_cu12_noxml d95aab32us9ur 0
...
--------------------------------------------------------------
SQL_ID=d95aab32us9ur Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
EXECS LAST_LOAD_TIME       ELA_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE AVG_CBO_COST CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT  SQL_PATCH
----- -------------------- ------------ ------------- --------------- ------------ ------ ---------- ---------- ----- -----  ------------------------
    2 2017-05-24/00:12:16        983510         11518       216622950         3778      1 Y          N          N     Y      d95aab32us9ur_1225157861
    6 2017-05-24/01:25:03       1772613         22843      3714880446         4796      6 Y          N          Y     Y      d95aab32us9ur_1225157861
   23 2017-05-24/01:55:03       3156585         22014      1735353024         3525     22 Y          Y          Y     Y      d95aab32us9ur_1225157861
    8 2017-05-24/02:20:14       3765635         14877       852232849 3,8671725347      7 Y          Y          Y     Y      d95aab32us9ur_1225157861
  266 2017-05-24/02:25:03       2353927         19572      4093555613         3738      8 Y          Y          Y     Y      d95aab32us9ur_1225157861
...
Реклама

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

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

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