Oracle mechanics

17.05.2018

12c: EBS R12 система с отключёнными адаптивными фичами / OPTIMIZER_ADAPTIVE_FEATURES = FALSE

Filed under: Oracle,Oracle 12c,Plan Management — Игорь Усольцев @ 18:01
Tags:

В докуметации Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1) определённо(*) рекомендуется отключить адаптивные фичи:

#########
#
# Optimizer Parameters
#
# Release 12 uses cost based optimization. The following optimizer
# parameters must be set as shown, and should not be changed.
# It is recommended to disable the adaptive optimizer features: adaptive plans,
# automatic re-optimization, and SQL plan directives.
#
#########

optimizer_adaptive_features = FALSE #MP -- Mandatory parameters --*

— как указано, чтобы использовать «чистый» CBO, т.о. блокируя избыточную генерацию планов и общую нестабильность (см., например, 12c: адаптивная оптимизация и CBO), что и было давно выполнено на уровне системы:

12.1.0.2@ SQL> @param optimizer_adaptive_features
 
NAME                         VALUE  IS_DEF   IS_MOD     DSC
---------------------------- ------ -------- ---------- --------------------------
optimizer_adaptive_features  FALSE  FALSE    FALSE      controls adaptive features

, отключив Adaptive Plans, Automatic Re-Optimization / Statistics Feedback, and SQL plan directives как следствие

Однако, так просто условно-избыточная генерация планов выполнения не прекратилась:

SQL> select inst_id,
  2         sql_id,
  3         count(distinct s.PLAN_HASH_VALUE)                                            as "PHVs",
  4         count(distinct child_number)                                                 as CHILDS,
  5         max(s.IS_BIND_SENSITIVE)                                                     as "BIND_SENSE",
  6         max(s.IS_BIND_AWARE)                                                         as "BIND_AWARE",
  7         max(s.IS_REOPTIMIZABLE)                                                      as "REOPT",
  8         max(nvl(s.IS_RESOLVED_ADAPTIVE_PLAN,'N'))                                    as "ADAPT",
  9         sc.ROLL_INVALID_MISMATCH,
 10         sc.BIND_EQUIV_FAILURE,
 11         sc.LOAD_OPTIMIZER_STATS,
 12         sc.USE_FEEDBACK_STATS
 13    from gv$sql s join gv$sql_shared_cursor sc using(inst_id, child_number, sql_id)
 14   where bind_equiv_failure = 'Y' or user_bind_peek_mismatch = 'Y'
 15      or bind_uacs_diff       = 'Y'
 16      or load_optimizer_stats = 'Y'
 17      or use_feedback_stats   = 'Y'
 18  group by inst_id,
 19           sql_id,
 20           sc.ROLL_INVALID_MISMATCH,
 21           sc.BIND_EQUIV_FAILURE,
 22           sc.LOAD_OPTIMIZER_STATS,
 23           sc.USE_FEEDBACK_STATS
 24  having count(distinct s.PLAN_HASH_VALUE) > 1
 25  order by count(distinct child_number) desc
 26  /
 
INST_ID SQL_ID              PHVs     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT ROLL_INVALID_MISMATCH BIND_EQUIV_FAILURE LOAD_OPTIMIZER_STATS USE_FEEDBACK_STATS
------- ------------- ---------- ---------- ---------- ---------- ----- ----- --------------------- ------------------ -------------------- ------------------
      1 f5qvh0dfqv85j         12         68 Y          Y          N     N     Y                     Y                  N                    N                  -- **
      1 0sfn74nbwkmv8         10         66 Y          Y          N     N     Y                     Y                  N                    N
      1 5tru8vxmktswq          2         49 Y          Y          N     N     Y                     Y                  N                    N
      1 4jbsu2sp1uwmp          2         45 Y          Y          N     N     Y                     Y                  N                    N
      1 c2w1v85gmca0b          9         44 Y          Y          N     N     Y                     Y                  N                    N
      2 f5qvh0dfqv85j         11         39 Y          Y          N     N     Y                     Y                  N                    N
      2 9wp463zp25xm8          6         37 Y          Y          N     N     Y                     Y                  N                    N
...
      2 fj5s5c9g4kgy5          2         12 N          N          Y     N     Y                     N                  N                    Y                  -- ***
...
      2 0gu9drzw9mmqg          3         10 N          N          Y     N     Y                     N                  N                    Y
...
      1 fj5s5c9g4kgy5          2          9 N          N          Y     N     Y                     N                  N                    Y
...
      2 g4mtjfkd0pcus          2          2 Y          Y          N     N     Y                     Y                  N                    N
      1 10nswakdssb4h          2          2 Y          N          N     N     Y                     N                  Y                    N
 
260 rows selected

как по причине Adaptive Cursor Sharing, не относящегося с стэку 12c Adaptive Features, на примере 1-го в списке запроса f5qvh0dfqv85j с 10+ различными планами на каждом инстансе (**):

SQL> @v$sqlstats2 f5qvh0dfqv85j
 
--------------------------------------------------------------
SQL_ID=f5qvh0dfqv85j Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST        EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT
---- ------------ -------------------- ------------------- ------------ ------------- --------------- -------------------- ------------ ---------- ---------- ---------- ----- -----
   2          352 2018-05-10/10:31:49                             23187            11      3988911575           1850526610         3197          1 Y          Y          N     N
   1          270 2018-05-11/10:22:47                            126001            11      3666787597            706346678        33463          1 Y          Y          N     N
   2          113 2018-05-11/10:57:28                            140114            11      3666787597            706346678        33465          1 Y          Y          N     N
   2          560 2018-05-11/11:18:09                             63656            11       605025860            854032595         7080          2 Y          Y          N     N
   2         1174 2018-05-11/12:31:01                             84812            11      3422155797           2159379434        15352          3 Y          Y          N     N
   1           32 2018-05-12/13:19:46                            336294            11       863969107           3837899688        59595          1 Y          Y          N     N
   2           50 2018-05-12/18:52:36                            141563            12      2887973615           1687012711        38398          1 Y          N          N     N
   1          545 2018-05-13/20:32:19                             54577            10      3422155797           2159379434        14747          5 Y          Y          N     N
   1          986 2018-05-13/22:38:30                             47951            11       605025860            854032595        12762          4 Y          Y          N     N
   2          997 2018-05-14/11:06:56                             62735            11      2515945640           2701349370         8256          1 Y          Y          N     N
   2         1579 2018-05-14/11:20:21                             61085            10      3927587791           2982576833         8193          3 Y          Y          N     N
   1           10 2018-05-14/11:36:13                             57501             1      3522335050           4203810512         8183          1 Y          Y          N     N
   2         4736 2018-05-14/12:00:22                             24732            11      2027747911            923184485         4012         14 Y          Y          N     N
   1          694 2018-05-14/12:21:46                             58766            11      3927587791           2982576833         8185          3 Y          Y          N     N
   2          205 2018-05-14/12:57:27                            308607            16      1887727232           4216716507        24108          5 Y          Y          N     N
   1         2870 2018-05-14/16:05:49                              9453            10       149761934            662297721         1440         14 Y          Y          N     N
   2         1015 2018-05-14/16:22:23                            120006            11      4153595835           1697380643        17524          3 Y          Y          N     N
   1         4913 2018-05-14/17:03:33                             16171            11      2027747911            923184485         3873         15 Y          Y          N     N
   2         1575 2018-05-14/17:19:26                              4309             6      1166109753           4130160045          354          8 Y          Y          N     N
   2         4541 2018-05-15/11:07:30                             79344            10       839643910           1440000047           10          2 Y          N          N     N
   1         3795 2018-05-15/19:08:40                             79267            11       839643910           1440000047           10         18 Y          Y          N     N
   1         1256 2018-05-16/08:43:48                             22088            11      3988911575           1850526610         4123          5 Y          Y          N     N
   1          490 2018-05-16/10:21:43                            108310            11      4153595835           1697380643        16815          3 Y          Y          N     N
   2         6844 2018-05-16/10:40:07                             20177            10       149761934            662297721         1535         12 Y          Y          N     N
   1         2541 2018-05-16/10:44:52                             17955             9      1166109753           4130160045          334         19 Y          Y          N     N
 
--------------------------------------------------------------
SQL_ID=f5qvh0dfqv85j Shared Pool GV$SQLSTATS
--------------------------------------------------------------
 
INST        EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC DISK_READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
---- ------------ ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ ---------------- -------------- ------------------- ------------------ --------------- ---------------------- ------------- -----------
   2        23741 f5qvh0dfqv85j  149761934        47709        41731         15431             7          536         5121                0             13                   0                  0               0                      0            10           0
   1        18402 f5qvh0dfqv85j 1166109753        37876        34949         13285            30          467         1933                0              4                   0                  0               0                      0            10           0

, так и по причине [V$SQL_SHARED_CURSOR.]USE_FEEDBACK_STATS = ‘Y’, который с версии 12c входит а адаптивный пул — на примере fj5s5c9g4kgy5 (***):

SQL> @v$sqlstats2 fj5s5c9g4kgy5
 
--------------------------------------------------------------
SQL_ID=fj5s5c9g4kgy5 Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST        EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH      OUTLINE_CATEGORY SQL_PROFILE
---- ------------ -------------------- ------------------- ------------ ------------- --------------- -------------------- ------------ ---------- ---------- ---------- ----- ----- ----------------- -------------- ---------------- -----------
   2           54 2018-05-15/11:28:57                           1642585             0      1153055443            511844843          204          5 N          N          Y     N                       ORA_ADMIN-3515
   2           41 2018-05-16/05:10:04                         501369343             0      3617218866           3566786451          575         11 N          N          Y     N                       ORA_ADMIN-3515
   1           27 2018-05-16/05:14:10                         504304729             0      3617218866           3566786451          575          7 N          N          Y     N                       ORA_ADMIN-3515
   1           37 2018-05-16/06:00:12                           1856161             0      1153055443            511844843          205          8 N          N          Y     N                       ORA_ADMIN-3515
 
--------------------------------------------------------------
SQL_ID=fj5s5c9g4kgy5 Shared Pool GV$SQLSTATS
--------------------------------------------------------------
 
INST        EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC DISK_READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
---- ------------ ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ ---------------- -------------- ------------------- ------------------ --------------- ---------------------- ------------- -----------
   2           95 fj5s5c9g4kgy5 3617218866    217314133    216369559       1378152          1704        47899       146588                2            239                   0                  0               0                      0             0           0
   1           64 fj5s5c9g4kgy5 1153055443    213826651    212958255        986043          7122        63814       152669                2            258                   0                  0               0                      0             0           0

, но оказалось, что этот (и подобные) курсоры используют созданные руками (для других целей) Sql Patch-и типа ORA_ADMIN-3515:

SQL> @sqlpatch_hints "ORA_ADMIN-3515"
 
SQL_PATCH_HINTS
-------------------------------------
optimizer_features_enable('11.2.0.4')

и, следовательно, речь идёт о влиянии не адаптивного Statistics Feedback (12c), а old-style Cardinality Feedback версии 11g

При этом значение столбец REOPT = ‘Y’ (отражающий V$SQL.IS_REOPTIMIZABLE) заполняется на автомате и, очевидно, не подразумевает дважды отключённую (optimizer_adaptive_features+SQL Patch) 12c Adaptive Re-Optimization

Адаптивные планы также присутствуют, в основном, у Automatic Dynamic Sampling (ADS) запросов:

SQL> select s.inst_id,
  2         s.sql_id,
  3         case when sql_text like 'SELECT /* DS_SVC */%' then 'ADS' else 'non_ADS' end as SQL_TYPE,
  4         count(distinct s.PLAN_HASH_VALUE)                                            as "PHVs",
  5         count(distinct s.FULL_PLAN_HASH_VALUE)                                       as "FPHVs",
  6         count(distinct s.child_number)                                               as CHILDS,
  7         max(s.IS_BIND_SENSITIVE)                                                     as "BIND_SENSE",
  8         max(s.IS_BIND_AWARE)                                                         as "BIND_AWARE",
  9         max(s.IS_REOPTIMIZABLE)                                                      as "REOPT",
 10         max(nvl(s.IS_RESOLVED_ADAPTIVE_PLAN,'N'))                                    as  SQL_ADAPT,
 11         REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(info) type="adaptive_plan" note="y">([^<]+)</\1>', 1, 1, NULL, 2) as XML_ADAPT
 12    from gv$sql s
 13    left join gv$sql_plan p on p.inst_id = s.inst_id and p.child_address = s.child_address and p.sql_id = s.sql_id and p.other_xml is not null
 14   where s.IS_RESOLVED_ADAPTIVE_PLAN is not null
 15   or  REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(info) type="adaptive_plan" note="y">([^<]+)</\1>', 1, 1, NULL, 2) = 'yes'
 16  group by s.inst_id,
 17           s.sql_id
 18  , REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(info) type="adaptive_plan" note="y">([^<]+)</\1>', 1, 1, NULL, 2)
 19  , case when sql_text like 'SELECT /* DS_SVC */%' then 'ADS' else 'non_ADS' end
 20  having count(distinct s.child_number) > 10
 21  order by count(distinct s.child_number) desc
 22  /
 
INST_ID SQL_ID        SQL_TYPE       PHVs      FPHVs     CHILDS BIND_SENSE BIND_AWARE REOPT SQL_ADAPT XML_ADAPT
------- ------------- -------- ---------- ---------- ---------- ---------- ---------- ----- --------- ---------
      2 8w9grrxtukq01 ADS               1          1         55 N          N          N     Y         yes      -- ****
      2 fxdr28pajkr56 ADS               1          1         45 N          N          N     Y         yes
      2 cr21an9thnsm6 ADS               1          1         31 N          N          N     Y         yes
...
      2 3qh81a73q4yj9 ADS               1          1         17 N          N          N     Y         yes
      2 3zzj0w7y0s1sa non_ADS           5          6         17 Y          N          N     Y         yes      -- *****
      2 5gtm505rhym3m ADS               1          1         17 N          N          N     Y         yes
...
      2 dt74g3mmvj7fa ADS               1          1         11 N          N          N     Y         yes
      1 3vq6ugf2y3qmw ADS               1          1         11 N          N          N     Y         yes
 
131 rows selected

На примере 1-го по счёту запроса (****) кроме реального адаптивного плана можно видеть:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('8w9grrxtukq01',0,format => 'adaptive -predicate +outline'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8w9grrxtukq01, child number 0
-------------------------------------
SELECT /* DS_SVC */
 /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM -- одновренное использование NO_PARALLEL и PQ_DISTRIBUTE далее ?
(SELECT /*+ qb_name("innerQuery")  */ 1 AS C1 FROM
 (SELECT /*+ 2 LEADING ("H" "L" "FND_CURRENCIES" "CCID" "CCID1" "GSA" "GSA1" "GSA2" "GSA3") LEADING ("H")                                     -- необычную форму хинта 2 LEADING ... LEADING
 INDEX_RS_ASC ("FND_CURRENCIES" "FND_CURRENCIES_U1") USE_NL ("FND_CURRENCIES")
 PQ_DISTRIBUTE ("FND_CURRENCIES",NONE,BROADCAST)INDEX_RS_ASC ("GSA3" "GL_SUSPENSE_ACCOUNTS_U1") USE_NL ("GSA3") PQ_DISTRIBUTE("GSA3",NONE,BROADCAST) -- ?
...*/

Plan hash value: 1418237486
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                         |       |       |    58 (100)|          |       |       |
|   1 |  SORT AGGREGATE                                  |                         |     1 |   167 |            |          |       |       |
|-  2 |   HASH JOIN OUTER                                |                         |    13 |  2171 |    58   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS OUTER                            |                         |    13 |  2171 |    58   (0)| 00:00:01 |       |       |
|-  4 |     STATISTICS COLLECTOR                         |                         |       |       |            |          |       |       |
|   5 |      NESTED LOOPS OUTER                          |                         |    13 |  1924 |    45   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                               |                         |    13 |  1794 |    45   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS OUTER                        |                         |     4 |   464 |    29   (0)| 00:00:01 |       |       |
|   8 |         NESTED LOOPS OUTER                       |                         |     4 |   400 |    29   (0)| 00:00:01 |       |       |
|   9 |          NESTED LOOPS OUTER                      |                         |     4 |   336 |    29   (0)| 00:00:01 |       |       |
|  10 |           NESTED LOOPS OUTER                     |                         |     4 |   272 |    29   (0)| 00:00:01 |       |       |
|  11 |            TABLE ACCESS FULL                     | XLA_AE_HEADERS_GT       |     4 |   208 |    29   (0)| 00:00:01 |       |       |
|  12 |            INDEX UNIQUE SCAN                     | GL_SUSPENSE_ACCOUNTS_U1 |     1 |    16 |     0   (0)|          |       |       |
|  13 |           INDEX UNIQUE SCAN                      | GL_SUSPENSE_ACCOUNTS_U1 |     1 |    16 |     0   (0)|          |       |       |
|  14 |          INDEX UNIQUE SCAN                       | GL_SUSPENSE_ACCOUNTS_U1 |     1 |    16 |     0   (0)|          |       |       |
|  15 |         INDEX UNIQUE SCAN                        | GL_SUSPENSE_ACCOUNTS_U1 |     1 |    16 |     0   (0)|          |       |       |
|  16 |        PARTITION LIST SINGLE                     |                         |     3 |    66 |     4   (0)| 00:00:01 |   KEY |   KEY |
|  17 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| XLA_AE_LINES            |     3 |    66 |     4   (0)| 00:00:01 |     9 |     9 |
|  18 |          INDEX RANGE SCAN                        | XLA_AE_LINES_U1         |     3 |       |     2   (0)| 00:00:01 |     9 |     9 |
|  19 |       INDEX UNIQUE SCAN                          | FND_CURRENCIES_U1       |     1 |    10 |     0   (0)|          |       |       |
|  20 |     INDEX UNIQUE SCAN                            | GL_CODE_COMBINATIONS_U1 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|- 21 |    INDEX FAST FULL SCAN                          | GL_CODE_COMBINATIONS_U1 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optim_peek_user_binds' 'false') -- отключение bind peeking на уровне запроса, что как бы снижает необходимость в гибкости/адаптивности плана
      ALL_ROWS
      ...
      END_OUTLINE_DATA
  */
Note
-----
   - Degree of Parallelism is 1 because of hint             -- ?
   - this is an adaptive plan (rows marked '-' are inactive)

— но ADS запрос — рекурсивный, и может использовать ту степень адаптивности, которую разработчики Oracle сочтут разумной)

Что касается не относящегося к Dynamic Sampling (non_ADS) запроса sql_id = ‘3zzj0w7y0s1sa’ (*****) с адаптивным планом:

SQL> @v$sqlstats2 3zzj0w7y0s1sa
 
--------------------------------------------------------------
SQL_ID=3zzj0w7y0s1sa Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST EXECS LAST_LOAD_TIME       ELA_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
---- ----- -------------------- ------------ ------------- --------------- -------------------- ------------ ---------- ---------- ---------- ----- ----- ----------------- --------- ---------------- ----------------------------
   2    22 2018-04-22/21:34:08       9908356             0      2469475568           3259988405        35617          2 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
   1     1 2018-04-23/23:38:43      17637448             0      2769579431           2513670073        30289          1 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
   2    34 2018-04-25/12:05:53       7105430             0      2769579431           2513670073        30369          1 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
   2   112 2018-05-04/12:34:13       5489684             0      1257756530            844509383        30432          2 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
   1   165 2018-05-11/11:31:04       6804923             0      2469475568           2824571162        34991          8 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
   2     1 2018-05-11/17:27:43      11371095             0      1125026662            627561866        30389          1 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
   1     7 2018-05-14/12:01:33       8909230             0      1166097481            328163637        30389          2 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
   2   199 2018-05-14/12:39:10       5960311             0      2469475568           2824571162        33774         11 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
   2    63 2018-05-14/14:25:33       5427133             0      1166097481            328163637        31537          3 Y          N          N     Y                                                  SYS_SQLPROF_015b90ffd6040000
 
--------------------------------------------------------------
SQL_ID=3zzj0w7y0s1sa Shared Pool GV$SQLSTATS
--------------------------------------------------------------
 
INST        EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC DISK_READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
---- ------------ ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ ---------------- -------------- ------------------- ------------------ --------------- ---------------------- ------------- -----------
   1          173 3zzj0w7y0s1sa 1166097481      6952684      5340111       2259931           770      1218554       654045               32           1790                   0                  0               0                      0             0           0
   2          431 3zzj0w7y0s1sa 1166097481      6064490      4931488       2273694           540       522680       769546               25           1764                   0                  0               0                      0             0           0

— он оказывается также рекурсивным:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('3zzj0w7y0s1sa',0,format => 'adaptive -predicate -outline'));
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3zzj0w7y0s1sa, child number 0
-------------------------------------
SELECT /*+RULE*/ATTR_NO, ATTR_NAME, ATTR_TYPE_NAME, ATTR_TYPE_OWNER
FROM USER_TYPE_ATTRS WHERE TYPE_NAME IN (SELECT TABLE_NAME FROM
ALL_SYNONYMS START WITH SYNONYM_NAME = :1 AND  OWNER = 'PUBLIC' CONNECT
BY NOCYCLE PRIOR TABLE_NAME = SYNONYM_NAME AND TABLE_OWNER = OWNER
UNION SELECT :2  FROM DUAL) ORDER BY ATTR_NO
Plan hash value: 2469475568
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                    |       |       |       | 35617 (100)|          |
|   1 |  FILTER                                                      |                    |       |       |       |            |          |
|   2 |   NESTED LOOPS                                               |                    |     1 |    57 |       |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED                       | OBJ$               |     1 |    42 |       |     4   (0)| 00:00:01 |
|   4 |     INDEX RANGE SCAN                                         | I_OBJ1             |     1 |       |       |     3   (0)| 00:00:01 |
|   5 |    INDEX RANGE SCAN                                          | I_USER2            |     1 |    15 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID BATCHED                        | USER_EDITIONING$   |     1 |     7 |       |     2   (0)| 00:00:01 |
...
|  28 |  SORT ORDER BY                                               |                    |     1 |   334 |       | 35617   (1)| 00:00:02 |
|  29 |   FILTER                                                     |                    |       |       |       |            |          |
|- 30 |    HASH JOIN                                                 |                    |     3 |  1002 |       | 35605   (1)| 00:00:02 |
|  31 |     NESTED LOOPS                                             |                    |     3 |  1002 |       | 35605   (1)| 00:00:02 |
|- 32 |      STATISTICS COLLECTOR                                    |                    |       |       |       |            |          |
|- 33 |       HASH JOIN                                              |                    |     3 |   918 |       | 35602   (1)| 00:00:02 |
|  34 |        NESTED LOOPS                                          |                    |     3 |   918 |       | 35602   (1)| 00:00:02 |
|- 35 |         STATISTICS COLLECTOR                                 |                    |       |       |       |            |          |
|- 36 |          HASH JOIN                                           |                    |     3 |   873 |       | 35599   (1)| 00:00:02 |
|  37 |           NESTED LOOPS                                       |                    |     3 |   873 |       | 35599   (1)| 00:00:02 |
|- 38 |            STATISTICS COLLECTOR                              |                    |       |       |       |            |          |
|- 39 |             HASH JOIN                                        |                    |     1 |   247 |       | 35595   (1)| 00:00:02 |
|  40 |              NESTED LOOPS                                    |                    |     1 |   247 |       | 35595   (1)| 00:00:02 |
|- 41 |               STATISTICS COLLECTOR                           |                    |       |       |       |            |          |
|- 42 |                HASH JOIN                                     |                    |     1 |   220 |       | 35593   (1)| 00:00:02 |
...
| 254 |     INDEX RANGE SCAN                                         | I_USER_EDITIONING  |    12 |       |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------------

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

, форсированым на использование RBO (/*+RULE*/), однако, кроме Adaptive Plan использует Sql Plan Directives (!), несмотря на системный запрет:

SQL> @param_ dsdir
 
NAME                            VALUE  IS_DEF   IS_MOD     IS_ADJ   DSC
------------------------------- ------ -------- ---------- -------- -------------------------------------------------------
_optimizer_dsdir_usage_control  0      TRUE     FALSE      FALSE    controls optimizer usage of dynamic sampling directives

, что, возможно, связано с используемым SQL Profile:

SQL> @spm_check4sql_id 3zzj0w7y0s1sa
 
SPM_TYPE          SQL_HANDLE                     PATCH_NAME                     ORIGIN         VERSION    CREATED             LAST_MODIFIED       LAST_EXECUTED       LAST_VERIFIED       ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE SPM_SIGNATURE         SQL_EXACT_SIGNATURE   SQL_FORCE_SIGNATURE
----------------- ------------------------------ ------------------------------ -------------- ---------- ------------------- ------------------- ------------------- ------------------- ------- -------- ----- ---------- --------- --------------------- --------------------- ---------------------
SQL Profile       SQL_98518e0ffc82812a           SYS_SQLPROF_015b90ffd6040000   MANUAL-LOAD    12.1.0.2.0 21.04.2017 17:52:17 21.04.2017 17:52:17                                         YES     NO       NO    YES        NO         10975709966190674218  10975709966190674218  10975709966190674218

SQL> @sql_profile_hints SYS_SQLPROF_015b90ffd6040000
 
HINT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$6DB749BD", TABLE, "X$KZSPR"@"SEL$221", SCALE_ROWS=0.3814334511)
OPT_ESTIMATE(@"SEL$B18C3D8C", JOIN, ("U"@"SEL$215", "O"@"SEL$215", "U"@"SEL$214"), SCALE_ROWS=0.0006068745043)
...
COLUMN_STATS("SYS"."SYN$", "NAME", scale, length=23 distinct=76810 nulls=0 min=245035608295533986149222421712863232 max=599495910439565949617241606838026240)
COLUMN_STATS("SYS"."SYN$", "OBJ#", scale, length=5 distinct=78247 nulls=0 min=196 max=11427957)
TABLE_STATS("SYS"."SYN$", scale, blocks=4671 rows=119221.4362)
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE(default)
IGNORE_OPTIM_EMBEDDED_HINTS
 
721 rows selected

— в этом случае сгенерированным SQL Tuning Advisor / DBMS_SQLTUNE, и если далее попробовать выбрать все курсоры, использующие Sql Plan Directives:

SQL> select s.inst_id,
  2         s.sql_id,
  3         case when sql_text like 'SELECT /* DS_SVC */%' then 'ADS' else 'non_ADS' end                       as SQL_TYPE,
  4         count(distinct s.PLAN_HASH_VALUE)                                                                  as "PHVs",
  5         count(distinct s.FULL_PLAN_HASH_VALUE)                                                             as "FPHVs",
  6         count(distinct s.child_number)                                                                     as CHILDS,
  7         max(s.IS_BIND_SENSITIVE)                                                                           as "BIND_SENSE",
  8         max(s.IS_BIND_AWARE)                                                                               as "BIND_AWARE",
  9         max(s.IS_REOPTIMIZABLE)                                                                            as "REOPT",
 10         max(nvl(s.IS_RESOLVED_ADAPTIVE_PLAN,'N'))                                                          as  SQL_ADAPT,
 11         to_number(REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(cv)>([^<]+)</\1>', 1, 1, NULL, 2)) as "SPD_Valid",
 12         to_number(REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(cu)>([^<]+)</\1>', 1, 1, NULL, 2)) as "SPD_Used",
 13         s.sql_profile,
 14         s.sql_patch,
 15         s.sql_plan_baseline
 16    from gv$sql s
 17    left join gv$sql_plan p on p.inst_id = s.inst_id and p.child_address = s.child_address and p.sql_id = s.sql_id and p.other_xml is not null
 18   where to_number(REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(cu)>([^<]+)</\1>', 1, 1, NULL, 2)) > 0
 19  group by s.inst_id,
 20           s.sql_id
 21  , to_number(REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(cv)>([^<]+)</\1>', 1, 1, NULL, 2))
 22  , to_number(REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(cu)>([^<]+)</\1>', 1, 1, NULL, 2))
 23  , case when sql_text like 'SELECT /* DS_SVC */%' then 'ADS' else 'non_ADS' end
 24  , s.sql_profile
 25  , s.sql_patch
 26  , s.sql_plan_baseline
 27  order by to_number(REGEXP_SUBSTR ( dbms_lob.substr(p.other_xml,4000), '<(cu)>([^<]+)</\1>', 1, 1, NULL, 2)) desc
 28  /
 
INST_ID SQL_ID        SQL_TYPE       PHVs      FPHVs     CHILDS BIND_SENSE BIND_AWARE REOPT SQL_ADAPT SPD_Valid  SPD_Used SQL_PROFILE                   SQL_PATCH SQL_PLAN_BASELINE
------- ------------- -------- ---------- ---------- ---------- ---------- ---------- ----- --------- --------- --------- ----------------------------- --------- -----------------
      1 fmhc3117c9x6u non_ADS           2          2         13 N          N          N     N                 8        12 SYS_SQLPROF_01578a0782510003 
      2 fmhc3117c9x6u non_ADS           3          3         14 N          N          N     N                 8        12 SYS_SQLPROF_01578a0782510003 
      1 fmhc3117c9x6u non_ADS           1          1          1 N          N          N     N                 9        11 SYS_SQLPROF_01578a0782510003 
      2 fmhc3117c9x6u non_ADS           1          1          1 N          N          N     N                 9        11 SYS_SQLPROF_01578a0782510003 
      1 fmhc3117c9x6u non_ADS           1          1          1 N          N          N     N                11         9 SYS_SQLPROF_01578a0782510003 
      1 2pyxbu7kphnqa non_ADS           3          2          3 Y          N          N     Y                 2         8 ORA_ADMIN-4550               -- ******
      2 2pyxbu7kphnqa non_ADS           3          2          4 Y          N          N     Y                 2         8 ORA_ADMIN-4550               
      1 3zzj0w7y0s1sa non_ADS           3          3          4 Y          N          N     Y                 0         8 SYS_SQLPROF_015b90ffd6040000 
      2 3zzj0w7y0s1sa non_ADS           4          4          6 Y          N          N     Y                 0         8 SYS_SQLPROF_015b90ffd6040000 
      1 2pyxbu7kphnqa non_ADS           1          1          3 Y          N          N     Y                 3         7 ORA_ADMIN-4550               
      2 2pyxbu7kphnqa non_ADS           1          1          1 Y          N          N     Y                 3         7 ORA_ADMIN-4550               
      2 2w9rwsnwy50np non_ADS           1          1          1 N          N          N     Y                 1         7 DB_SUP-234281                
      2 3zzj0w7y0s1sa non_ADS           1          1          2 Y          N          N     Y                 1         7 SYS_SQLPROF_015b90ffd6040000 
      1 fnb3181tg1mnq non_ADS           1          1         10 N          N          N     Y                 6         7 SYS_SQLPROF_0160e44d9c160000 
      2 fnb3181tg1mnq non_ADS           1          1          8 N          N          N     Y                 6         7 SYS_SQLPROF_0160e44d9c160000 
      2 2w9rwsnwy50np non_ADS           1          1          1 N          N          N     Y                 2         6 DB_SUP-234281                
      1 3zzj0w7y0s1sa non_ADS           2          2          3 Y          N          N     Y                 2         6 SYS_SQLPROF_015b90ffd6040000 
      2 3zzj0w7y0s1sa non_ADS           3          3          5 Y          N          N     Y                 2         6 SYS_SQLPROF_015b90ffd6040000 
      1 12cxq5fz8n314 non_ADS           1          1         10 Y          Y          N     N                 0         5 12cxq5fz8n314_154023208      
      2 12cxq5fz8n314 non_ADS           1          1          3 Y          Y          N     N                 0         5 12cxq5fz8n314_154023208      
      1 2pyxbu7kphnqa non_ADS           1          1          1 Y          N          N     Y                 5         5 ORA_ADMIN-4550               
      2 3zzj0w7y0s1sa non_ADS           1          1          1 Y          N          N     Y                 3         5 SYS_SQLPROF_015b90ffd6040000 
      1 12cxq5fz8n314 non_ADS           1          1         29 Y          Y          N     N                 1         4 12cxq5fz8n314_154023208      
      2 12cxq5fz8n314 non_ADS           1          1         19 Y          Y          N     N                 1         4 12cxq5fz8n314_154023208      
      1 2w9rwsnwy50np non_ADS           3          2          3 N          N          N     Y                 4         4 DB_SUP-234281                
      2 2w9rwsnwy50np non_ADS           4          3          4 N          N          N     Y                 4         4 DB_SUP-234281                
      1 3zzj0w7y0s1sa non_ADS           1          1          4 Y          N          N     Y                 4         4 SYS_SQLPROF_015b90ffd6040000 
      2 3zzj0w7y0s1sa non_ADS           1          1          1 Y          N          N     Y                 4         4 SYS_SQLPROF_015b90ffd6040000 
      1 fdg8v3jwh36zx non_ADS           1          1          1 Y          N          N     N                 3         4 SYS_SQLPROF_0151582cc2530001 
      2 fdg8v3jwh36zx non_ADS           1          1          1 Y          N          N     N                 3         4 SYS_SQLPROF_0151582cc2530001 
      1 12cxq5fz8n314 non_ADS           1          1          2 Y          Y          N     N                 2         3 12cxq5fz8n314_154023208      
      2 12cxq5fz8n314 non_ADS           1          1          3 Y          Y          N     N                 2         3 12cxq5fz8n314_154023208      
      1 3zzj0w7y0s1sa non_ADS           1          1          2 Y          N          N     Y                 5         3 SYS_SQLPROF_015b90ffd6040000 
      2 3zzj0w7y0s1sa non_ADS           1          2          4 Y          N          N     Y                 5         3 SYS_SQLPROF_015b90ffd6040000 
      1 12cxq5fz8n314 non_ADS           1          1          2 Y          Y          N     N                 3         2 12cxq5fz8n314_154023208      
      2 12cxq5fz8n314 non_ADS           1          1          1 Y          Y          N     N                 3         2 12cxq5fz8n314_154023208      
      1 2w9rwsnwy50np non_ADS           1          1          1 N          N          N     Y                 6         2 DB_SUP-234281                
      2 2w9rwsnwy50np non_ADS           1          1          1 N          N          N     Y                 6         2 DB_SUP-234281                
      1 70rubgg4p9nwb non_ADS           1          1          9 Y          N          N     N                 1         1 ORA_ADMIN-7486               
      2 70rubgg4p9nwb non_ADS           1          1          6 Y          N          N     N                 1         1 ORA_ADMIN-7486               
      1 avy2617pg426x non_ADS           1          1         20 Y          N          N     N                 4         1 ORA_ADMIN-4351               
      2 avy2617pg426x non_ADS           1          1          9 Y          N          N     N                 4         1 ORA_ADMIN-4351               
 
42 rows selected

— оказывается, что для всех таких запросов в системе присутствуют SQL Profiles, сгенерированные либо SQL Tuning Advisor-ом с посказками OPT_ESTIMATE:

SQL> @sql_profile_hints SYS_SQLPROF_01578a0782510003
 
HINT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPTIMIZER_FEATURES_ENABLE(default)
OPT_ESTIMATE(@"SEL$726137A8", INDEX_SKIP_SCAN, "FND_LOOKUP_VALUES"@"SEL$54", "FND_LOOKUP_VALUES_U1", SCALE_ROWS=0.04224335506)
OPT_ESTIMATE(@"SEL$726137A8", INDEX_FILTER, "FND_LOOKUP_VALUES"@"SEL$58", "FND_LOOKUP_VALUES_U2", SCALE_ROWS=0.4780959201)
OPT_ESTIMATE(@"SEL$726137A8", INDEX_FILTER, "FND_LOOKUP_VALUES"@"SEL$50", "IDX$$_696270002", SCALE_ROWS=3.014511958)
...
OPT_ESTIMATE(@"SEL$B566AFB2", INDEX_SKIP_SCAN, "FND_LOOKUP_VALUES"@"SEL$50", "FND_LOOKUP_VALUES_U1", SCALE_ROWS=0.04186822163)
 
719 rows selected

, либо созданные вручную с целью закрепления плана выполнения, что, впрочем, судя по кол-ву различных планов (******) работает уже не совсем так, как было задумано(:

SQL> @sql_profile_hints ORA_ADMIN-4550
 
HINT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALL_ROWS
...
DB_VERSION('12.1.0.2')
ELIMINATE_JOIN(@"SEL$07BDC5B4" "PO_HEADERS_ALL"@"SEL$4")
...
NO_ACCESS(@"SEL$DA3CA8CA" "VW_SQ_1"@"SEL$6534715F")
NO_ACCESS(@"SEL$F47321C6" "VW_SQ_1"@"SEL$05EB4A6A")
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
....
USE_NL(@"SEL$F47321C6" "FND_LOOKUP_VALUES"@"SEL$29")
 
356 rows selected
 
SQL> @sql_profile_hints DB_SUP-234281
 
HINT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$16649755")
...
USE_NL(@"SEL$9549C5D3" "RA_CUST_TRX_TYPES_ALL"@"SEL$42")
 
133 rows selected

Учитывая, что в рассмотренных SQL Profiles отсутствуют хинты, допускающие использование optimizer_adaptive_features, остаётся неясным насколько случайно использование Sql Plan Directives при компиляции запросов с SQL Profiles (?)

Состояние Sql Plan Directives (созданных ещё до отключения optimizer_adaptive_features) подтверждает факт их недавнего использования (но не создания, успешно отключённого цепочкой OPTIMIZER_ADAPTIVE_FEATURES=FALSE => ‘_OPTIMIZER_GATHER_FEEDBACK’=FALSE):

SQL> select * from sys."_BASE_OPT_DIRECTIVE" d order by created desc fetch first 5 rows only;
 
  DIR_OWN#     DIR_ID       F_ID TYPE             INTERNAL_STATE AUTO_DROP REDUNDANT ENABLED CREATED                        LAST_USED
---------- ---------- ---------- ---------------- -------------- --------- --------- ------- ------------------------------ -------------------------------
1,71275055 1,28431598 1,20303471 DYNAMIC_SAMPLING MISSING_STATS  YES       NO        YES     10-OCT-16 10.42.53.000000 AM   12-MAY-18 06.00.11.000000000 AM
1,01637544 1,56668980 9,64003696 DYNAMIC_SAMPLING PERMANENT      YES       NO        YES     30-AUG-16 11.43.48.000000 AM   05-JUL-17 01.04.07.000000000 PM
1,36856292 1,66703217 1,53330614 DYNAMIC_SAMPLING PERMANENT      YES       NO        YES     15-JUL-16 01.16.34.000000 PM   15-MAY-18 10.00.03.000000000 PM
1,71275055 1,61411180 1,01429517 DYNAMIC_SAMPLING PERMANENT      YES       NO        YES     14-JUN-16 04.34.01.000000 PM   12-MAY-18 06.00.11.000000000 AM
1,05303096 3,94344300 7,10942042 DYNAMIC_SAMPLING PERMANENT      YES       NO        YES     31-MAY-16 10.23.50.000000 AM   11-JAN-18 02.41.39.000000000 PM
Реклама

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

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

RSS feed for comments on this post. TrackBack URI

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

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: