Oracle mechanics

17.12.2015

Фиксация плана с помощью SQL Profile и накладные расходы/overhead версии 12c

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

Процедура DBMS_SQLTUNE.CREATE_TUNING_TASK для конкретного SQL_ID игнорирует наличие SQL Profile для настраимоемого запроса:

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'fppuw3hpvww2d' ) FROM DUAL;

...
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "TASK-3017" exists for this statement and was ignored
  during the tuning process.
...

, в то время как запрос с существующим SQL Patch настройке не подлежит:)

...
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-16961: SQL statement with SQL patch is ignored

При этом список подсказок автоматически генерируемого SQL Profile, по крайней мере, с версии 12c, уже не ограничивается привычными корректирующими OPT_ESTIMATE:

SQL> SELECT distinct extractvalue(value(d), '/hint/text()') as outline_hints,
  2                  version
  3    from sys.sqlobj$data od
  4    join sys.sqlobj$ o using (signature, category, obj_type, plan_id)
  5    join sqlobj$auxdata ad using (signature, category, obj_type, plan_id)
  6   CROSS JOIN XMLTABLE('/outline_data/hint' PASSING xmltype(comp_data)) d
  7   where obj_type = 1                                                    -- SQL Profile
  8     and od.comp_data is not null
  9     and name like 'SYS_SQLPROF%'                                        -- , созданные DBMS_SQLTUNE.ACCEPT_SQL_PROFILE,
 10     and extractvalue(value(d), '/hint/text()') not like 'OPT_ESTIMATE%' -- и содержащие подсказки кроме OPT_ESTIMATE
 11   order by 1
 12  /
 
OUTLINE_HINTS                                   VERSION
----------------------------------------------- ----------
ALL_ROWS                                        12.1.0.2.0 -- хинты типа OPTIMIZER GOAL
FIRST_ROWS(1)                                   12.1.0.2.0 -- --//--
IGNORE_OPTIM_EMBEDDED_HINTS                     12.1.0.2.0
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')           12.1.0.2.0 -- группа хинтов OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')           12.1.0.2.0 -- --//--
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')           12.1.0.2.0 -- --//--
OPTIMIZER_FEATURES_ENABLE(default)              12.1.0.2.0

— т.е. SQL Tuning Advisor использует разные, основанные не только на статистике плана выполнения, стратегии, что замечательно

Большинство же SQL Profile-ей по-прежнему состоит из подсказок типа OPT_ESTIMATE, например, профиль SYS_SQLPROF_0151589074920002 для запроса 2xd3dtdtz21g9:

SQL> @sql_profile_hints SYS_SQLPROF_0151589074920002
 
OUTLINE_HINTS
-----------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$018A8974", NLJ_INDEX_SCAN, "U"@"SEL$11", ("WF_LOCAL_USER_ROLE...
OPT_ESTIMATE(@"SEL$018A8974", NLJ_INDEX_FILTER, "WURA"@"SEL$11", ("U"@"SEL$11"),...
...
OPT_ESTIMATE(@"SEL$1", TABLE, "U"@"SEL$1", SCALE_ROWS=4.344448432)
OPTIMIZER_FEATURES_ENABLE(default)
 
33 rows selected

однако, наличие и даже успешное применение этого SQL Profile не исключает генерацию разных планов (PLAN_HASH_VALUE) для оптимизируемого запроса, под влиянием Adaptive Features (SPD_Valid/SPD_Used), AutoDynSampling (DS_LEVEL) и/или Bind Aware (BIND_SENSE), например:

SQL> @shared_cu12_noxml 2xd3dtdtz21g9

INST EXECS LAST_LOAD_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  DS_LEVEL ROLL REASON#1                              SQL_PROFILE
---- ----- ------------------- ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---- ------------------------------------- ----------------------------
   1 12834 2015-12-10/14:02:50        12474 VALID                62317689           4068173129         921252    99 Y          N          Y          N                  N                       17        1         2        N    Rolling Invalidate Window Exceeded(2) SYS_SQLPROF_0151589074920002
   1 15414 2015-12-10/08:59:39        18487 VALID                62317689           4068173129         921252     0 N          N          Y          N                  N                       18        0                  N    Rolling Invalidate Window Exceeded(2) SYS_SQLPROF_0151589074920002
   1    12 2015-12-10/22:43:29       203193 VALID               920244703           3310004048         921252   255 Y          N          Y          N                  N                       16        2         2        Y    Rolling Invalidate Window Exceeded(2) SYS_SQLPROF_0151589074920002
   1    25 2015-12-11/00:00:09       109844 VALID               920244703           3310004048         921252   257 Y          N          Y          N                  N                       17        1         2        Y    NLS Settings(2)                       SYS_SQLPROF_0151589074920002
   1     2 2015-12-11/00:38:31      1112243 VALID               920244703           3310004048         921252   258 Y          N          Y          N                  N                       17        1         2        Y    NLS Settings(2)                       SYS_SQLPROF_0151589074920002
   1   830 2015-12-10/13:56:10        10302 VALID                62317689           4068173129         921252    98 Y          N          Y          N                  N                       17        1         2        N    Rolling Invalidate Window Exceeded(2) SYS_SQLPROF_0151589074920002
...
617 rows selected -- кол-во дочерних курсоров

и планы эти могут оказаться более (PHV=62317689) или менее (PHV=920244703) удачными судя по среднему ELA_PER_EXEC

Отличия планов запроса 2xd3dtdtz21g9 — в использовании различных индексов F_USER_ROLE_ASSIGNMENTS_N2/WF_USER_ROLE_ASSIGNMENTS_N3 для доступа к таблице «APPLSYS».»WF_USER_ROLE_ASSIGNMENTS» (WURA@SEL$21) что заметно из сравнения планов для блока/QBLOCK_NAME SEL$65CB9793:

SQL> @sql_plan_diff 2xd3dtdtz21g9 62317689 2xd3dtdtz21g9 920244703 SEL$65CB9793
 
-------------------------------
SQL Plans [by Query block] diff
-------------------------------
 
PLAN_HASH_VALUE QBLOCK_NAME     ID OPERATION                                     QBLOCK_NAME          OBJECT_ALIAS                   OBJECT                                            CARDINALITY                BYTES COST(IO) TEMP TIME
--------------- ------------- ---- --------------------------------------------- -------------------- ------------------------------ ---------------------------------------- -------------------- -------------------- ------------- ----
SQL_62317689    SEL$65CB9793    35 NESTED LOOPS SEMI                             SEL$65CB9793                                                                                               236243             30002861 61(61)           1
                                36  MERGE JOIN CARTESIAN                                                                                                                                         1                   80 32(32)           1
                                37   NESTED LOOPS                                                                                                                                                1                   27 3(3)             1
                                38    NESTED LOOPS                                                                                                                                               1                   27 3(3)             1
                SEL$D67CB2D2    39     VIEW                                      SEL$D67CB2D2         U@SEL$26                                                                                   1                   13 2(2)             1
                SEL$D67CB2D2    40      TABLE ACCESS FULL                        SEL$D67CB2D2         T1@SEL$D67CB2D2                "SYS"."SYS_TEMP_0FD9DC7A5_B6E3479E"                         1                    5 2(2)             1
                SEL$65CB9793    41     INDEX UNIQUE SCAN                         SEL$65CB9793         U@SEL$21                       "APPLSYS"."FND_USER_U1"                                     1                      0(0)          
                SEL$65CB9793    42    TABLE ACCESS BY INDEX ROWID                SEL$65CB9793         U@SEL$21                       "APPLSYS"."FND_USER"                                        1                   14 1(1)             1
                                43   BUFFER SORT                                                                                                                                                 1                   53 31(31)           1
                                44    PARTITION RANGE ALL                                                                                                                                        1                   53 29(29)           1
                SEL$65CB9793    45     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED SEL$65CB9793         WF_LOCAL_USER_ROLES@SEL$25     "APPLSYS"."WF_LOCAL_USER_ROLES"                             1                   53 29(29)           1
                SEL$65CB9793    46      INDEX RANGE SCAN                         SEL$65CB9793         WF_LOCAL_USER_ROLES@SEL$25     "APPLSYS"."WF_LOCAL_USER_ROLES_N7"                          1                      28(28)           1
                                47  PARTITION RANGE ALL                                                                                                                        4,54990674240811E18  1,84467440737096E19 29(29)           1
                SEL$65CB9793    48   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED   SEL$65CB9793         WURA@SEL$21                    "APPLSYS"."WF_USER_ROLE_ASSIGNMENTS"      4,54990674240811E18  1,84467440737096E19 29(29)           1
                SEL$65CB9793    49    INDEX RANGE SCAN                           SEL$65CB9793         WURA@SEL$21                    "APPLSYS"."WF_USER_ROLE_ASSIGNMENTS_N3"                     1                      28(28)           1
--------------- -------------      --------------------------------------------- -------------------- ------------------------------ ----------------------------------------                                                         
SQL_920244703   SEL$65CB9793    35 NESTED LOOPS SEMI                             SEL$65CB9793                                                                                               236243             30002861 61(61)           1
                                36  MERGE JOIN CARTESIAN                                                                                                                                         1                   80 32(32)           1
                                37   NESTED LOOPS                                                                                                                                                1                   27 3(3)             1
                                38    NESTED LOOPS                                                                                                                                               1                   27 3(3)             1
                SEL$D67CB2D2    39     VIEW                                      SEL$D67CB2D2         U@SEL$26                                                                                   1                   13 2(2)             1
                SEL$D67CB2D2    40      TABLE ACCESS FULL                        SEL$D67CB2D2         T1@SEL$D67CB2D2                "SYS"."SYS_TEMP_0FDA0250B_B7569831"                         1                    5 2(2)             1
                SEL$65CB9793    41     INDEX UNIQUE SCAN                         SEL$65CB9793         U@SEL$21                       "APPLSYS"."FND_USER_U1"                                     1                      0(0)          
                SEL$65CB9793    42    TABLE ACCESS BY INDEX ROWID                SEL$65CB9793         U@SEL$21                       "APPLSYS"."FND_USER"                                        1                   14 1(1)             1
                                43   BUFFER SORT                                                                                                                                                 1                   53 31(31)           1
                                44    PARTITION RANGE ALL                                                                                                                                        1                   53 29(29)           1
                SEL$65CB9793    45     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED SEL$65CB9793         WF_LOCAL_USER_ROLES@SEL$25     "APPLSYS"."WF_LOCAL_USER_ROLES"                             1                   53 29(29)           1
                SEL$65CB9793    46      INDEX RANGE SCAN                         SEL$65CB9793         WF_LOCAL_USER_ROLES@SEL$25     "APPLSYS"."WF_LOCAL_USER_ROLES_N7"                          1                      28(28)           1
                                47  PARTITION RANGE ALL                                                                                                                        4,57104542008108E18  1,84467440737096E19 29(29)           1
                SEL$65CB9793    48   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED   SEL$65CB9793         WURA@SEL$21                    "APPLSYS"."WF_USER_ROLE_ASSIGNMENTS"      4,57104542008108E18  1,84467440737096E19 29(29)           1
                SEL$65CB9793    49    INDEX RANGE SCAN                           SEL$65CB9793         WURA@SEL$21                    "APPLSYS"."WF_USER_ROLE_ASSIGNMENTS_N2"                     1                      28(28)           1
 
------------------------------
SQL Plan "Notes" sections diff
------------------------------
 
PHV_62317689                                        PHV_920244703
--------------------------------------------------- --------------------------------------------------
                                                    
   sql_profile:     "SYS_SQLPROF_0151589074920002"     sql_profile:     "SYS_SQLPROF_0151589074920002" -- один SQL Profile
   sql_patch:                                          sql_patch:
   baseline:                                           baseline:
   outline:                                            outline:
   dyn_sampling:    2                                  dyn_sampling:    2          -- применяется ADS,
   dop:                                                dop:
   dop_reason:                                         dop_reason:
   card_feedback:                                      card_feedback:
   perf_feedback:                                      perf_feedback:
   adaptive_plan:                                      adaptive_plan:
   spd_used:        2                                  spd_used:        1          -- используются отличающиеся
   spd_valid:       16                                 spd_valid:       17         -- наборы SQL Plan Directives,
   gtt_sess_stat:                                      gtt_sess_stat:
   db_version:      12.1.0.2                           db_version:      12.1.0.2
   plan_hash_full:  4068173129                         plan_hash_full:  3310004048
   plan_hash:       62317689                           plan_hash:       920244703  -- в рез-те генерируются разные планы
   plan_hash_2:     4068173129                         plan_hash_2:     3310004048

--------------------------------
SQL Plan "Outline" sections diff
--------------------------------
 
PHV_62317689                                                                          PHV_920244703
------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$65CB9793" "WURA"@"SEL$21"
 ("WF_USER_ROLE_ASSIGNMENTS"."ROLE_NAME" "WF_USER_ROLE_ASSIGNMENTS"."USER_NAME"))                                                             
 
                                                                                      INDEX_RS_ASC(@"SEL$65CB9793" "WURA"@"SEL$21"
                                                                                       ("WF_USER_ROLE_ASSIGNMENTS"."USER_NAME" "WF_USER_ROLE_ASSIGNMENTS"."ROLE_NAME"))

— что также отражено в различающихся подсказках секции Outline при точном совпадении стоимости/Cost планов (теория) и определёнными отклонениями среднего ELA_PER_EXEC (практика)

Стоит также заметить, что хинты OPT_ESTIMATE автопрофиля SYS_SQLPROF_0151589074920002 для блока SEL$65CB9793 никак не упоминают таблицу «WURA»@»SEL$21», т.е. Adviser на момент создания SQL Profile не отметил проблем в путях доступа к этой таблице, что, кроме прочих факторов риска также допускает определённую «свободу» построения плана в дальнейшем:

SQL> @sql_profile_hints SYS_SQLPROF_0151589074920002 SEL$65CB9793
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$65CB9793", NLJ_INDEX_SCAN, "U"@"SEL$21", ("WF_LOCAL_USER_ROLES"@"SEL$25", "WURA"@"SEL$21"), "FND_USER_U2", SCALE_ROWS=19187.1175)
OPT_ESTIMATE(@"SEL$65CB9793", NLJ_INDEX_FILTER, "U"@"SEL$21", ("WF_LOCAL_USER_ROLES"@"SEL$25", "WURA"@"SEL$21"), "FND_USER_U2", SCALE_ROWS=19187.1175)

Использование автоматически создаваемых SQL Profile с хинтами типа OPT_ESTIMATE кроме показанной нестабильности имеет и естественные временные ограничения: со временем меняются данные, распределение значений по столбцам, кардинальность соединений и т.д.

В итоге, при использовании профиля SYS_SQLPROF_0151589074920002 для этого интенсивного (судя по кол-ву EXECS) имеем 2 разных плана выполнения со вр.временем ELA_PER_EXEC ~ 30 ms и сотнями child cursors (поле CHILDS):

SQL> @v$sqlstats2 2xd3dtdtz21g9 0
 
--------------------------------------------------------------
SQL_ID=2xd3dtdtz21g9 Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PROFILE
---- ----- -------------------- ------------------- ------------ ------------ --------------- -------------------- ------------ ------ ---------- ---------- ----- ----- ----------------------------
   2 15941 2015-12-16/15:46:05  16.12.2015 15:47:47        32231        28333        62317689           4068173129       921252    134 Y          N          N     N     SYS_SQLPROF_0151589074920002
   1 13796 2015-12-16/15:46:55  16.12.2015 15:47:49        37750        33710        62317689           4068173129       921252    152 Y          N          N     N     SYS_SQLPROF_0151589074920002
   1 23031 2015-12-15/22:03:56  16.12.2015 00:01:13        30289        25392       920244703           3310004048       921252    152 Y          N          N     N     SYS_SQLPROF_0151589074920002
   2 23681 2015-12-15/21:34:46  16.12.2015 01:56:19        29125        25582       920244703           3310004048       921252    178 Y          N          N     N     SYS_SQLPROF_0151589074920002

Причинами генерации дочерних курсоров/child cursors судя по статистике V$SQL_SHARED_CURSOR являются формально безобидные NLS Settings(2) и Rolling Invalidate Window Exceeded(2):

SQL> @shared_cu12s_noxml 2xd3dtdtz21g9 62317689
 
   INST_ID PLAN_HASH_VALUE BIND_SENSE BIND_AWARE FEEDBACK_STATS OPT_STATS REOPT ADAPT BIND_EQ_FAIL ROLL_INVALID_MISMATCH REASON#1                                 FIX_CONTROL#1 CURSOR_COUNT  PHV_COUNT FPHV_COUNT        EXECS USERS_OPENING
---------- --------------- ---------- ---------- -------------- --------- ----- ----- ------------ --------------------- ---------------------------------------- ------------- ------------ ---------- ---------- ------------ -------------
         1        62317689 Y          N          N              N         N           N            Y                     NLS Settings(2)                                                 178          1          1        12355             6
         2       920244703 Y          N          N              N         N           N            N                     Rolling Invalidate Window Exceeded(2)                           176          1          1        19509             0
         2        62317689 Y          N          N              N         N           N            Y                     NLS Settings(2)                                                 158          1          1        14772             5
         1       920244703 Y          N          N              N         N           N            N                     Rolling Invalidate Window Exceeded(2)                           146          1          1        12597             0
         1       920244703 N          N          N              N         N           N            N                                                                                       4          1          1          364             0
         1        62317689 N          N          N              N         N           N            Y                     NLS Settings(2)                                                   2          1          1         5397            15
         2        62317689 N          N          N              N         N           N            Y                     NLS Settings(2)                                                   2          1          1         4076             6
         2       920244703 N          N          N              N         N           N            Y                     Rolling Invalidate Window Exceeded(2)                             1          1          1         4143             0
         2       920244703 N          N          N              N         N           N            N                     Rolling Invalidate Window Exceeded(2)                             1          1          1           29             0
         2        62317689 Y          N          N              N         N           N            Y                     Rolling Invalidate Window Exceeded(2)                             1          1          1            2             0
         2        62317689 Y          N          N              N         N           N            N                     Rolling Invalidate Window Exceeded(2)                             1          1          1           89             0
         1       920244703 N          N          N              N         N           N            Y                     Rolling Invalidate Window Exceeded(2)                             1          1          1         9935             0
         1       920244703 N          N          N              N         N           N            N                     Rolling Invalidate Window Exceeded(2)                             1          1          1          135             0
         1        62317689 Y          N          N              N         N           N            Y                     Rolling Invalidate Window Exceeded(2)                             1          1          1           46             0
         1        62317689 Y          N          N              N         N           N            N                     Rolling Invalidate Window Exceeded(2)                             1          1          1           86             0

, что неудивительно, учитывая, что запрос является рекурсивным и активно используется разными программами/модулями/активностями Oracle EBS:

SQL> select count(distinct program) *
  2         count(distinct module) *
  3         count(distinct action) *
  4         count(distinct client_id) *
  5         count(distinct top_level_sql_id) as variant_count
  6    from gv$active_session_history
  7   where sql_id in ('2xd3dtdtz21g9')
  8  /
 
VARIANT_COUNT
-------------
         9504

Обращает на себя внимание статистика плана выполнения этого запроса по данным ASH на примере PHV=62317689:

SQL> @ash_sqlmon2 2xd3dtdtz21g9 62317689
 
LAST_PLSQL     ID PLAN_OPERATION                                                QBLOCK_NAME     OBJECT_ALIAS OBJECT_OWNER OBJECT_NAME                  CARDINALITY  COST  ASH_ROWS WAIT_PROFILE
------------ ---- ------------------------------------------------------------- --------------- ------------ ------------ ---------------------------- ----------- ----- --------- ----------------------------------------------------------------------------------------------------------
Soft Parse      0 sql_plan_hash_value > 0; sql_exec_id is null                                                                                                                 632 ON CPU(627); enq: PS - contention(2); reliable message(1); PX Deq: Signal ACK EXT(1); gc cr block 2-way(1)
Main Query      0   SELECT STATEMENT                                                                                                                                 325         3 ON CPU(3)
                1     TEMP TABLE TRANSFORMATION                                 SEL$2                                                                                            7 ON CPU(6); log buffer space(1)
                2       LOAD AS SELECT                                          SEL$1                                                                                           66 direct path write temp(53); ON CPU(12); CSS initialization(1)
.................
               47                   PARTITION RANGE ALL                                                                                                      19264    29         1 ON CPU(1)
               48                     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED SEL$65CB9793    WURA@SEL$21  APPLSYS      WF_USER_ROLE_ASSIGNMENTS           19264    29        10 ON CPU(10)
               49                       INDEX RANGE SCAN                        SEL$65CB9793    WURA@SEL$21  APPLSYS      WF_USER_ROLE_ASSIGNMENTS_N3            1    28        87 ON CPU(87)
Recurs.waits    0 sql_id is null and plsql[_entry]_object_id is null                                                                                                             2 log file sync(1); ON CPU(1)
SQL Summary     0 ASH fixed 522 execs from 548 sessions                                                                                                                       1154  ash rows were fixed from 16.12.2015 10:39:35 to 16.12.2015 17:59:59

, а именно то, что из 1154 ASH строк 632 пришлось на стадию Soft Parse т.е. фазу выполнения когда PLAN_HASH_VALUE=62317689 уже был сформрован/отображался в ASH, а фаза выполнения ещё не стартовала (sql_plan_hash_value > 0; sql_exec_id is null), и бОльшая часть этого времени была истрачена не на предсказуемую конкуренцию за структуры Shared Pool, а на процессорную активность неясной природы — ON CPU(627)

Предполагая, что такое необычное поведение может быть связано с родовыми травмами 12c, можно попробовать применить универсальный патч имени 12-й версии, который не должен повлиять на уже имеющийся SQL Profile:

SQL> @sqlpatch_12c 2xd3dtdtz21g9
 
PL/SQL procedure successfully completed
 
SIGNATURE             SPM_TYPE     SQL_HANDLE            PATCH_NAME                     ORIGIN         VERSION    CREATED               LAST_MODIFIED       ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ------------ --------------------- ------------------------------ -------------- ---------- --------------------- ------------------- ------- -------- ----- ---------- ---------
  8470718686859003988 SQL Patch    SQL_758e09c4127e8854  NO_ADAPT_2xd3dtdtz21g9         MANUAL-SQLTUNE 12.1.0.2.0 16.12.2015 18:08:33   16.12.2015 18:08:33 YES     NO       NO    YES        NO
  8470718686859003988 SQL Profile  SQL_758e09c4127e8854  SYS_SQLPROF_0151589074920002   MANUAL-LOAD    12.1.0.2.0 30.11.2015 16:24:00   30.11.2015 16:24:00 YES     NO       NO    YES        NO
 
SQL_PATCH_HINTS
--------------------------------------------------------------------------------------------------------
opt_param('optimizer_adaptive_features' 'false') no_bind_aware OPT_PARAM('optimizer_dynamic_sampling' 0)

Что немедленно приводит к сокращению ср.времени выполнения запроса ELA_PER_EXEC почти в 2 раза и сокращению кол-ва CHILDS с сотен до 6:

SQL> @shared_cu12_noxml 2xd3dtdtz21g9 0
 
INST EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_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 ROLL REASON#1        SQL_PATCH              SQL_PROFILE                  IS_OBSOLETE
---- ----- ------------- -------------------- ------------------- ------------ ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---- --------------- ---------------------- ---------------------------- -----------
   1     6             1 2015-12-16/18:14:29  16.12.2015 18:15:58       100386        97500 VALID                62317689           4068173129         921252     2 N          N          Y          N                  N                                                    N    NLS Settings(2) NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002 N
   1   355             4 2015-12-16/18:11:16  16.12.2015 18:17:35        12399        10259 VALID                62317689           4068173129         921252     1 N          N          Y          N                  N                                                    N    NLS Settings(2) NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002 N
   1   119             4 2015-12-16/18:10:09  16.12.2015 18:17:35        20292        16899 VALID                62317689           4068173129         921252     0 N          N          Y          N                  N                                                    Y    NLS Settings(2) NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002 N
   2    44             1 2015-12-16/18:08:54  16.12.2015 18:13:19        28268        24636 VALID                62317689           4068173129         921252     0 N          N          Y          N                  N                                                    Y    NLS Settings(2) NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002 N
   2    10             0 2015-12-16/18:10:24  16.12.2015 18:17:31        88692        84500 VALID                62317689           4068173129         921252     2 N          N          Y          N                  N                                                    N    NLS Settings(2) NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002 N
   2   246             2 2015-12-16/18:09:54  16.12.2015 18:17:35        13073         9748 VALID                62317689           4068173129         921252     1 N          N          Y          N                  N                                                    N    NLS Settings(2) NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002 N
 
SQL> @v$sqlstats2 2xd3dtdtz21g9 0

--------------------------------------------------------------
SQL_ID=2xd3dtdtz21g9 Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PATCH              SQL_PROFILE
---- ----- -------------------- ------------------- ------------ ------------ --------------- -------------------- ------------ ---------- ---------- ---------- ----- ----- ---------------------- ----------------------------
   1   480 2015-12-16/18:14:29  16.12.2015 18:17:35        15455        12996        62317689           4068173129       921252          3 N          N          N     N     NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002
   2   300 2015-12-16/18:10:24  16.12.2015 18:17:35        17822        14423        62317689           4068173129       921252          3 N          N          N     N     NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002

, правда, на относительно небольшом числе выполнений EXECS

Возвращаясь к содержимому SQL Profile, нестабильно состоящему из подсказок ala OPT_ESTIMATE, было бы разумно стабилизировать «удачный» план, например, пересоздав SQL Profile на основе секции Outline правильного плана выполнения PHV=62317689 из V$SQL_PLAN/DBA_HIST_SQL_PLAN, при созданнии которого, в свою очередь были использованы автоматический профиль SYS_SQLPROF_0151589074920002 и рукотворный SQL Patch NO_ADAPT_2xd3dtdtz21g9:

SQL> @sql_profile_from_sql 2xd3dtdtz21g9 62317689 "SYS_SQLPROF_0151589074920002" "SYS_SQLPROF_0151589074920002 w/o OPT_ESTIMATE"
 
PL/SQL procedure successfully completed

В пересозданный таким образом профиль SYS_SQLPROF_0151589074920002 попали подсказки «универсального» SQL Patch, отключающие некоторые адаптивниые фичи 12c:

SQL> @sql_profile_hints SYS_SQLPROF_0151589074920002 OPT_PARAM
 
HINT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('optimizer_dynamic_sampling' 0)

, и подсказки, определяющие найденные SQL Tuning Adviser-ом удачные шаги плана выполнения из автоматически созданного профиля, например, для блока SEL$65CB9793:

SQL> @sql_profile_hints SYS_SQLPROF_0151589074920002 SEL$65CB9793
 
HINT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$65CB9793" "WF_LOCAL_USER_ROLES"@"SEL$25")
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$65CB9793" "WURA"@"SEL$21")
INDEX(@"SEL$65CB9793" "U"@"SEL$21" ("FND_USER"."USER_ID"))
INDEX_RS_ASC(@"SEL$65CB9793" "WF_LOCAL_USER_ROLES"@"SEL$25" ("WF_LOCAL_USER_ROLES"."ROLE_ORIG_SYSTEM_ID" "WF_LOCAL_USER_ROLES"."ROLE_ORIG_SYSTEM"))
INDEX_RS_ASC(@"SEL$65CB9793" "WURA"@"SEL$21" ("WF_USER_ROLE_ASSIGNMENTS"."ROLE_NAME" "WF_USER_ROLE_ASSIGNMENTS"."USER_NAME")) -- указание на нужный индекс
...

Вернувшись через некоторое время к статистике child cursors/выполнения запроса, можно заметить:

SQL> @shared_cu12_noxml 2xd3dtdtz21g9 0
 
INST EXECS LAST_LOAD_TIME       ELA_PER_EXEC CPU_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 OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON#1                              SQL_PLAN_BASELINE SQL_PATCH               SQL_PROFILE                   IS_OBSOLETE
---- ----- -------------------- ------------ ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ---------------- ---------------- ---- ------------------------------------- ----------------- ----------------------- ----------------------------- -----------
   1    62 2015-12-16/18:42:13          7275         5710 VALID                62317689           4068173129            325     3 N          N          Y          N                  N                                                    N                N                N    Rolling Invalidate Window Exceeded(2)                   NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   1   116 2015-12-16/18:22:11         22014        17991 VALID                62317689           4068173129            325     2 N          N          Y          N                  N                                                    N                N                N    Rolling Invalidate Window Exceeded(2)                   NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   1  5961 2015-12-16/18:21:59         13839         9812 VALID                62317689           4068173129            325     1 N          N          Y          N                  N                                                    N                N                N    Rolling Invalidate Window Exceeded(2)                   NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   1    66 2015-12-17/12:01:42          6005         5727 VALID                62317689           4068173129            325     7 N          N          Y          N                  N                                                    N                N                Y    NLS Settings(2)                                         NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   1   280 2015-12-17/08:50:57         19329        11843 VALID                62317689           4068173129            325     5 N          N          Y          N                  N                                                    N                N                Y    NLS Settings(2)                                         NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   1  8835 2015-12-17/09:08:17         17870        13688 VALID                62317689           4068173129            325     6 N          N          Y          N                  N                                                    N                N                Y    NLS Settings(2)                                         NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   1 19090 2015-12-17/03:01:52         11634         9259 VALID                62317689           4068173129            325     4 N          N          Y          N                  N                                                    N                N                Y    NLS Settings(2)                                         NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   2     2 2015-12-16/19:24:27         50529        48000 VALID                62317689           4068173129            325     0 N          N          Y          N                  N                                                    N                N                N    Rolling Invalidate Window Exceeded(2)                   NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   2  3895 2015-12-16/18:21:49         11091         8697 VALID                62317689           4068173129            325     2 N          N          Y          N                  N                                                    N                N                N    Rolling Invalidate Window Exceeded(2)                   NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   2   589 2015-12-16/18:29:11         15353        12428 VALID                62317689           4068173129            325     1 N          N          Y          N                  N                                                    N                N                Y    Rolling Invalidate Window Exceeded(2)                   NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   2    86 2015-12-16/18:23:16         12741        10907 VALID                62317689           4068173129            325     3 N          N          Y          N                  N                                                    N                N                N    Rolling Invalidate Window Exceeded(2)                   NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   2    64 2015-12-17/13:54:51          6451         6344 VALID                62317689           4068173129            325     7 N          N          Y          N                  N                                                    N                N                Y    NLS Settings(2)                                         NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   2  5770 2015-12-17/10:10:56         17485        13269 VALID                62317689           4068173129            325     6 N          N          Y          N                  N                                                    N                N                Y    NLS Settings(2)                                         NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   2   362 2015-12-17/09:26:02         14182        11688 VALID                62317689           4068173129            325     5 N          N          Y          N                  N                                                    N                N                Y    NLS Settings(2)                                         NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
   2 21055 2015-12-16/22:28:31         11870         9310 VALID                62317689           4068173129            325     4 N          N          Y          N                  N                                                    N                N                Y    NLS Settings(2)                                         NO_ADAPT_2xd3dtdtz21g9  SYS_SQLPROF_0151589074920002  N
 
15 rows selected -- кол-во дочерних курсоров

SQL> @v$sqlstats2 2xd3dtdtz21g9 0

--------------------------------------------------------------
SQL_ID=2xd3dtdtz21g9 Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST EXECS LAST_LOAD_TIME      ELA_PER_EXEC CPU_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH              SQL_PROFILE
---- ----- ------------------- ------------ ------------ --------------- -------------------- ------------ ---------- ---------- ---------- ----- ----- ----------------- ---------------------- ----------------------------
   1 34410 2015-12-17/12:01:42        13696        10529        62317689           4068173129          325          7 N          N          N     N                       NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002
   2 31823 2015-12-17/13:54:51        12877        10038        62317689           4068173129          325          8 N          N          N     N                       NO_ADAPT_2xd3dtdtz21g9 SYS_SQLPROF_0151589074920002

, что на 65,000+ выполнений сформировалось всего 15 дочерних курсоров при ср.времени ELA_PER_EXEC не более 14 ms

P.S. После проведённых «оптимзаций» моя статистика плана выполнения запроса по данным ASH (которая, в отличие от стандартного SQL_MONITOR берёт в расчёт ASH строки с пустыми/нулевыми PLAN_HASH_VALUE/SQL_EXEC_ID для оценки различных фаз Parse + рекурсивные SQL и PL/SQL вызовы по возможности), запущенная уже без указания PLAN_HASH_VALUE за ненадобностью ввиду стабилизации плана выполнения:

SQL> @ash_sqlmon2 2xd3dtdtz21g9
 
LAST_PLSQL  SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                                                CARDINALITY      BYTES COST    ASH_ROWS WAIT_PROFILE
----------- ------------- --------------- ---- ------------------------------------------------------------- ----------- ---------- ----  ---------- --------------------------------------------------------------------
Soft Parse  2xd3dtdtz21g9        62317689    0 sql_plan_hash_value > 0; sql_exec_id is null                                                        5 ON CPU(5)
Main Query  2xd3dtdtz21g9        62317689    0   SELECT STATEMENT                                                                    325           2 ON CPU(2)
                                             1     TEMP TABLE TRANSFORMATION                                                                       2 ON CPU(2)
                                             2       LOAD AS SELECT                                                                               38 direct path write temp(26); ON CPU(12)
...
                                            47                   PARTITION RANGE ALL                               19264     905408   29           1 ON CPU(1)
                                            48                     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED       19264     905408   29           9 ON CPU(9)
                                            49                       INDEX RANGE SCAN                                  1              28          72 ON CPU(72)
SQL Summary                             0    0 ASH fixed 382 execs from 231 sessions                                                             387  ash rows were fixed from 17.12.2015 01:48:44 to 17.12.2015 14:24:39

показала, что лишь 5 из 387 (1% против прежних 55%) записей относятся к Soft Parse (sql_plan_hash_value > 0; sql_exec_id is null) стадии выполнения, что и определило вышеозначенный эффект 2+кратного улучшения ср.времени выполнения

Стоит отметить, что в рассмотренном случае «адаптивный» Soft Parse Oracle 12c потреблял не менее 50% от общего времени выполнения быстрого OLTP-style запроса — своеобразный 12c overhead)

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

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

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