12c: директивы плана выполнения — SQL Plan Directives

SQL Plan Directives (SPD) определяются как дополнительная информация/указания/инструкции, генерируемые и фиксируемые в бд в процессе выполнения запроса в тех случаях, когда оптимизатор на основе имеющейся статистики строит неточный в части оценки кол-ва возвращаемых строк (cardinality misestimate) план выполнения

В дальнейшем эти SPD инструкции, привязанные к объектам бд (таблицам, группам таблиц, столбцам и группам столбцов), используются:

• в процессе Automatic Reoptimization (т.е. на основе статистики предыдущего выполнения стимулируя создание нового плана / дочернего курсора / hard parse запроса установкой флага V$SQL.IS_REOPTIMIZABLE=’Y’) для генерации более точного плана выполнения через форсированное выполнение Automatis Dynamic Statistics / Sampling (ADS) — запросов типа SELECT /* DS_SVC */ /*+ dynamic_sampling(0) …*/
• для создания на основании этих SPD записей Extended Statistics по группам столбцов в процессе очередного ручного или автоматического сбора статистики с использованием процедур пакета DBMS_STATS

И поскольку и Automatis Dynamic Statistics — запросы, и автоматически создаваемая Extended Statistics легко могут стать причинами определённых неудобств после миграции на 12c, SPD директивы заслуживают внимания, например, в плане управления и контроля использования

1) Вкл/Выкл SPD

В ряде случаев, например:

Bug 20465582 : SQL PLAN DIRECTIVES CAUSING SLOWDOWN OF DICTIONARY QUERIES
Bug 20465582 High parse time in 12c for multi-table join SQL with SQL plan directives enabled — superseded
…

— отключить использование и/или создание оптимизатором директив плана SPD рекомендуется след.образом:

Workaround
 Settin the following hidden parameters will work around this problem:
  _optimizer_dsdir_usage_control   = 0   -- disable use of directives
  _sql_plan_directive_mgmt_control = 0   -- disable creation of directives

Практически мы столкнулись с такой необходимостью, получив неожиданно массовую инвалидацию обзоров и PL/SQL кода в ERP-приложении (подробнее — в блоге Леонида Барчука), причиной чего был непофикшенные Bug 19450314 : INVALIDATIONS IN 12C, Bug 21152981 : PACKAGE INVALIDATIONS AFTER STATISTICS GATHERING IN 12C, etc., где подтверждается/обсуждается возможность необоснованной инвалидации объектов при автоматическом создании Extended Statistics рекурсивными DDL вида:

alter table "OKE"."OKE_K_TERMS" add (SYS_STSMZAM9TY9D_Z9UH4IFYKAZNE as (sys_op_combined_hash("TERM_CODE", "TERM_VALUE_PK1")) virtual BY SYSTEM for statistics);

Единственным надёжным вариантом решения, предлагаемом в Are Extended Statistics Collected Automatically on Oracle 12c? (Doc ID 1964223.1), является отключение всех адаптивных возможностей Oracle 12c:

SQL> alter system set OPTIMIZER_ADAPTIVE_FEATURES=false;

or

SQL> alter session set OPTIMIZER_ADAPTIVE_FEATURES=false;

, что представляется достаточно серьёзным ограничением возможностей новой версии, и мы попробовали решить проблему только запретом использования SQL Plan Directives:

Thu May 14 12:02:24 2015
ALTER SYSTEM SET _optimizer_dsdir_usage_control=0 SCOPE=BOTH SID='*';

, в рез-тате чего в планах выполнения, действительно, перестали использоваться SPD директивы, в чем можно убедиться, поискав в текущем Shared Pool кэше запросы, всё ещё использующие SPD:

12.1.0.2.@ SQL> select first_load_time,
  2         inst_id,
  3         sql_id,
  4         count(*)      as SPD_CURSORS,
  5         sum(spd_used) as spd_used
  6    from (SELECT inst_id,
  7                 sql_id,
  8                 extractvalue(xmlval, '/*/spd/cu') as spd_used
  9            from (select p.inst_id,
 10                         p.sql_id,
 11                         xmltype(other_xml) xmlval
 12                    from gv$sql_plan p, gv$sql s
 13                   where p.inst_id = s.inst_id
 14                     and p.child_address = s.child_address
 15                     and p.sql_id = s.sql_id
 16                     and p.other_xml is not null))
 17  join gv$sqlarea s using(inst_id, sql_id)
 18   where spd_used > 0                      -- SPD используются
 19   group by first_load_time, inst_id, sql_id
 20   order by first_load_time desc
 21  /
 
FIRST_LOAD_TIME      INST_ID SQL_ID        SPD_CURSORS   SPD_USED
-------------------- ------- ------------- ----------- ----------
2015-05-21/16:00:40        2 ag930sspt2546           2          6 -- *
2015-05-14/12:17:55        1 5v18j5jkr101w           3         12 -- запрос был загружен чуть позже изменения параметра, вероятно, в долгой сессии
2015-05-13/12:09:40        1 3mf9uj08kxj30           1          3 -- запрос был загружен в Shared Pool до изменения параметра

3 rows selected                                                   -- всего 3 запроса, использующих SPD

— отмеченный запрос (*) представляет собой особый случай, поскольку функционирует под влиянием hand-made SQL Patch:

SQL> @spm_check4sql_id ag930sspt2546
 
SIGNATURE             SPM_TYPE   SQL_HANDLE            PLAN_NAME  ORIGIN         VERSION    LAST_MODIFIED       ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ---------- --------------------- ---------- -------------- ---------- ------------------- ------- -------- ----- ---------- ---------
 12797335771656744535 SQL Patch  SQL_b1994514c4ffee57  TASK-1811  MANUAL-SQLTUNE 12.1.0.2.0 18.05.2015 17:48:11 YES     NO       NO    YES        NO
 
SQL> @sqlpatch_hints TASK-1811
 
SQL_PATCH_HINTS
-----------------------------------------------
opt_param('_optimizer_dsdir_usage_control' 126)

, созданного в том числе для проверки возможности включения использования SPD на уровне запроса через OPT_PARAM:

SQL> begin
  2    dbms_sqldiag.drop_sql_patch('TASK-1811', ignore => TRUE);
  3    for reco in (select sql_fulltext from v$sqlarea where sql_id = 'ag930sspt2546') loop
  4      sys.dbms_sqldiag_internal.i_create_patch(sql_text  => reco.sql_fulltext,
  5                                           hint_text => 'opt_param(''_optimizer_dsdir_usage_control'' 126)',
  6                                           name      => 'TASK-1811');
  7    end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed

После чего этот запрос в отличие от остальных смог успешно пользоваться всеми прелестями адаптивных фич 12c, включая SPD:)

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('ag930sspt2546'));
...
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL patch "TASK-1811" used for this statement
   - 5 Sql Plan Directives used for this statement

Важно отметить, что автоматическое создание Extended Statistics установкой _OPTIMIZER_DSDIR_USAGE_CONTROL = 0 не отключается, и виртуальные столбцы продолжили создаваться, хотя и со значительно меньшей интенсивностью чем непосредственно на вторые сутки после обновления на 12c, и уже без катастофических заметных последствий

Остаётся проверить как повлияло отключение _OPTIMIZER_DSDIR_USAGE_CONTROL на автоматическую генерацию запросов Automatis Dynamic Statistics?

SQL> select trunc(sn.BEGIN_INTERVAL_TIME),
  2         sum(nvl2(ds.sql_id, 1, 0)) as ADS_COUNT,
  3         count(*) as TOTAL_COUNT
  4    from dba_hist_sqlstat ss
  5    join dba_hist_snapshot sn
  6   using (snap_id)
  7    join dba_hist_parameter p
  8   using (snap_id)
  9    left join (select sql_id
 10                 from dba_hist_sqltext
 11                where sql_text like 'SELECT /* DS_SVC */ /*+ dynamic_sampling(%') ds
 12      on ss.sql_id = ds.sql_id
 13   where p.PARAMETER_NAME = 'optimizer_features_enable'
 14     and p.value = '12.1.0.2'
 15   group by trunc(sn.BEGIN_INTERVAL_TIME)
 16   order by trunc(sn.BEGIN_INTERVAL_TIME)
 17  /
 
TRUNC(SN.BEGIN_INTERVAL_TIME)  ADS_COUNT TOTAL_COUNT
----------------------------- ---------- -----------
11.05.2015                           920       19864
12.05.2015                          2308       55548
13.05.2015                          2808       60188 -- было
14.05.2015                           692       62940 -- ALTER SYSTEM SET _optimizer_dsdir_usage_control=0
15.05.2015                             0       62392 -- стало
16.05.2015                             0       42772
17.05.2015                             0       43256
18.05.2015                           108       59276 -- ADS в отдельных запросах, был создан SQL Patch для ag930sspt2546
19.05.2015                           132       61872
20.05.2015                            64       63800

— и судя по AWR ТОПу запросов кол-во ADS-запросов действительно заметно сократилось, местами до 0

Любопытно, что Automatis Dynamic Statistics — запросы , как результат/последствие SPD — директив, кроме просто избыточной нагрузки, могут создавать реальные проблемы с Latch-конкуренцией в части Result Cache — Latch free waits, Dynamic Statistics, and the Result Cache, что подтверждается специалистами поддержки — High «Latch Free» Waits with Contention on ‘Result Cache: RC Latch’ when RESULT_CACHE_MODE = MANUAL on Oracle 12c (Doc ID 2002089.1):

When Automatic Dynamic Sampling is used for the SQL statements, it can decide, based upon these statistics, that a better response time could be achieved by using the result cache for those queries

— где, кроме грубых мер типа полного отключения SPD или Dynamic Sampling (OPTIMIZER_DYNAMIC_SAMPLING=0), указано красивое решение через изменение нового параметра:

SQL> @param_ _optimizer_ads_use_result_cache
 
NAME                             VALUE  IS_DEF  DSC
-------------------------------- ------ ------- --------------------------------
_optimizer_ads_use_result_cache  TRUE   TRUE    use result cache for ADS queries

Кстати, в реальной системе доля ADS — запросов, по крайней мере генерирующих RESULT_CACHE (в том смысле, что не факт что эти кэшированные рез-ты запросов будут использованы в дальнейшем) и обозначенных как ADS + result_cache может быть весьма значительной:

SQL> select decode(instr(sql_text, 'result_cache'),
  2                0, 'ADS - result_cache',
  3                   'ADS + result_cache') as result_cache_ads_usage,
  4         count(*)
  5    from gv$sqlarea
  6   where sql_text like 'SELECT /* DS_SVC */ /*+ dynamic_sampling(%'
  7   group by decode(instr(sql_text, 'result_cache'),
  8                   0, 'ADS - result_cache',
  9                      'ADS + result_cache')
 10  /
 
RESULT_CACHE_ADS_USAGE   COUNT(*)
---------------------- ----------
ADS - result_cache            160
ADS + result_cache           1672

2) Увидеть перечень доступных и/или используемых запросом SPD директив иногда помогает, как это ни странно, команда EXPLAIN PLAN:

SQL> explain plan for
  2  select
...
 17  /

Explained.

SQL> select * from table(dbms_xplan.display('','','advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 1589873370

--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                           |  1 |  1021    |  1159   (1)| 00:00:01 |
...
|  73 |     INDEX SKIP SCAN  | FND_ATTACHED_DOCUMENTS_N1 |  2 |          |    18   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

...

Sql Plan Directive information:
-------------------------------

  Valid directive ids:
    2586539785235786453
    8923136371829069803
    9552811215888641646
    6248313326856689732
    7549866709825056856
    5703994506773569805
    17898415053231391331
    8645499492382550557
    12236138182618426770
    12439921342598936248
    488437860916751747
    3630698652509299339
    4891404684583961815
    14936726261226438681
    11354576936090754877
    11930530698132040958
    780986904298410168
    4146763535431749532
    7538132299524481410

  Used directive ids:
    12236433879016080600
    16122377648489408595
    11452601050385912375

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 3 Sql Plan Directives used for this statement

Как оказалось, Franck Pachot недавно блестяще описал этот эффект, дополнительно определив конкретную/новую опцию формата ADVANCED функции DBMS_XPLAN.DISPLAY(format => ‘+METRICS’) — Matching SQL Plan Directives and queries using it, и метод этот, несмотря на не-100% надёжность, вполне годится в качестве вспомогательного средства

Другим способом увидеть перечень доступных и используемых SPD-директив является трейс оптимизатора, например, получаемый из Shared Pool в рез-те выполнения DBMS_SQLDIAG.DUMP_TRACE(&sql_id, P_COMPONENT=>’Optimizer’):

Applicable DS directives:                                                       -- список доступных директив
   dirid = 7737246727099321555, state = 1, flags = 1, loc = 1 {EC(88222)[3, 4]}
   dirid = 14322725572191151175, state = 3, flags = 1, loc = 1 {EC(88222)[3]}
   dirid = 6530516933799656345, state = 1, flags = 1, loc = 1 {EJ(88222)[2]}
   dirid = 11378594116199125643, state = 5, flags = 1, loc = 2 {(88222)[]; F(88221)[]}
   dirid = 17755564143531297775, state = 2, flags = 1, loc = 1 {EC(88221)[2, 3]}
   dirid = 4193946993328655323, state = 2, flags = 1, loc = 1 {C(88221)[2, 3]}
   dirid = 1575606783788740067, state = 3, flags = 1, loc = 1 {C(88221)[3]}
Checking valid directives for the query block
  SPD: Return code in qosdDSDirSetup: NODIR, estType = QUERY_BLOCK
       Return code in qosdSetupDirCtx4QB: EXISTS
...
SPD: qosdRecDSDirChange dirid = 7737246727099321555, retCode = NODIR
SPD: qosdRecDSDirChange dirid = 14322725572191151175, retCode = NODIR
SPD: qosdRecDSDirChange dirid = 6530516933799656345, retCode = NODIR
SPD: qosdRecDSDirChange dirid = 11378594116199125643, retCode = EXISTS -- указание на применимую (used) SPD - EXISTS: found valid SQL Plan Directive*
...
  Best NL cost: 84472122.958123
          resc: 84472122.958123  resc_io: 84452994.715979  resc_cpu: 705619599108
          resp: 84472122.958123  resp_io: 84452994.715979  resc_cpu: 705619599108
  SPD: Directive valid: dirid = 11378594116199125643, state = 5, flags = 1, loc = 2 {(88222)[]; F(88221)[]} -- SPD валидна для JOIN объектов 88222 и 88221**
  SPD: Return code in qosdDSDirSetup: EXISTS, estType = JOIN
Join Card:  41054023.196285 = outer (43279193.000000) * inner (4176326.838581) * sel (2.2713e-07)
>> Join Card adjusted from 41054023.196285 to 8.418594 due to adaptive dynamic sampling, prelen=2 -- применение директивы серьёзно меняет оценочную Cardinality на основе выполненного ADS
Adjusted Join Cards: adjRatio=0.000000 cardHjSmj=8.418594 cardHjSmjNPF=8.418594 cardNlj=8.418594 cardNSQ=8.418594 cardNSQ_na=41054023.196285
Join Card - Rounded: 8 Computed: 8.418594

**) собственно, содержимое директив:

SQL> select to_char(d.directive_id,'99999999999999999999') as directive_id,
  2         d.type,
  3         d.enabled,
  4         d.state,
  5         d.reason,
  6         extractvalue(d.notes, '/spd_note/spd_text') as spd_text
  7    from dba_sql_plan_directives d
  8   where directive_id in (7737246727099321555,
  9                          14322725572191151175,
 10                          6530516933799656345,
 11                          11378594116199125643,
 12                          17755564143531297775,
 13                          4193946993328655323,
 14                          1575606783788740067)
 15  /
 
DIRECTIVE_ID          TYPE             ENABLED STATE      REASON                               SPD_TEXT
--------------------- ---------------- ------- ---------- ------------------------------------ -------------------------------------------
 11378594116199125643 DYNAMIC_SAMPLING NO      USABLE     JOIN CARDINALITY MISESTIMATE         {(OID_OPSS.JPS_ATTRS) - F(OID_OPSS.JPS_DN)}
  6530516933799656345 DYNAMIC_SAMPLING YES     USABLE     SINGLE TABLE CARDINALITY MISESTIMATE {EJ(OID_OPSS.JPS_ATTRS)[JPS_DN_ENTRYID]}
  7737246727099321555 DYNAMIC_SAMPLING YES     USABLE     SINGLE TABLE CARDINALITY MISESTIMATE {EC(OID_OPSS.JPS_ATTRS)[ATTRNAME, ATTRVAL]}
 14322725572191151175 DYNAMIC_SAMPLING YES     SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE {EC(OID_OPSS.JPS_ATTRS)[ATTRNAME]}
 17755564143531297775 DYNAMIC_SAMPLING YES     SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE {EC(OID_OPSS.JPS_DN)[PARENTDN, RDN]}
  1575606783788740067 DYNAMIC_SAMPLING YES     SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE {C(OID_OPSS.JPS_DN)[PARENTDN]}
  4193946993328655323 DYNAMIC_SAMPLING YES     SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE {C(OID_OPSS.JPS_DN)[PARENTDN, RDN]}

, где в графе SPD_TEXT используются обозначения*:

equality_predicates_only        E (SCHEMA.TABLE) [COL, COL]
simple_column_predicates_only   C (SCHEMA.TABLE) [COL, COL]
index_access_by_join_predicates J (SCHEMA.TABLE) [COL, COL]
filter_on_joining_object        F (SCHEMA.TABLE) [COL, COL]

*) Franck Pachot. SQL Plan Directives. The memory of the optimizer

Из сравнения трейса и списка директив бд видно, что из трёх формально USABLE директив при построении плана запроса была использована только первая 11378594116199125643, под влиянием которой выбирался следующий формально намного более дорогой (!) план стандартного запроса:

12.1.0.2 @> SELECT * FROM TABLE(dbms_xplan.display_cursor('16tyg1x63jtam'));

SQL_ID  16tyg1x63jtam, child number 0
-------------------------------------
SELECT /*+ FIRST_ROWS(10) */  attr.* FROM JPS_ATTRS attr, JPS_DN DN -- даже несмотря на подсказку FIRST_ROWS
WHERE DN.PARENTDN = :1  AND DN.ENTRYID = attr.JPS_DN_ENTRYID
 
Plan hash value: 657837024
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |       |       |       | 80972 (100)|          |
|*  1 |  HASH JOIN            |                     |  8110K|  1662M|   418M| 80972   (1)| 00:00:04 |
|*  2 |   INDEX FAST FULL SCAN| IDX_JPS_PARENTDN_ID |  4176K|   370M|       | 17394   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | JPS_ATTRS           |  7068K|   411M|       | 18401   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DN"."ENTRYID"="ATTR"."JPS_DN_ENTRYID")
   2 - filter("DN"."PARENTDN"=:1)

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

— при этом частоиспользуемый запрос выполнялся непозволительно долго:

SQL> @shared_cu12 16tyg1x63jtam
 
EXECS USERS_OPENING ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  REOPT REOPT_HINTS ADAPT USE_FEEDBACK_STATS SQL_PLAN_DIRECTIVES SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
----- ------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ----- ----------- ----- ------------------ ------------------- ----------------- --------- ---------------- ----------- -----------
 4241            20     10244180       657837024          80972     0 N          N          Y          N               0       N                  valid:0; used:1                                                              N

— более 10 секунд на выполнение (ELA_PER_EXEC)

Одним из быстрых методов устранения проблемы (влияния SPD директивы) является блокирование использования директивы:

SQL> exec DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE (11378594116199125643, 'ENABLED','NO')
 
PL/SQL procedure successfully completed

, что надёжно предотвращает легковероятное пересоздание, но может негативно повлиять на планы других запросов, также использующих соединение перечисленных в директиве таблиц(

В рез-те ср.время выполнения «драматически» снизилость до десятков микросекунд, совсем без использования SPD (valid:; used:):

SQL> @shared_cu12 16tyg1x63jtam
 
EXECS USERS_OPENING ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  REOPT REOPT_HINTS ADAPT USE_FEEDBACK_STATS SQL_PLAN_DIRECTIVES SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
----- ------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ----- ----------- ----- ------------------ ------------------- ----------------- --------- ---------------- ----------- -----------
52485             9           55      4136394610             11     1 Y          N          Y          N               0       N                  valid:; used:                                                                N

, впрочем, так же «драматически» снизилась и формальная стоимость плана выполнения — с 80972 до 11 (т.е. где-то CBO всё-таки можно верить):

12.1.0.2.@ SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('16tyg1x63jtam',1));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  16tyg1x63jtam, child number 1
-------------------------------------
SELECT /*+ FIRST_ROWS(10) */  attr.* FROM JPS_ATTRS attr, JPS_DN DN
WHERE DN.PARENTDN = :1  AND DN.ENTRYID = attr.JPS_DN_ENTRYID

Plan hash value: 4136394610 -- план здорового CBO

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |       |       |    11 (100)|          |
|   1 |  NESTED LOOPS                |                     |    17 |  2618 |    11   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                     |    18 |  2618 |    11   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_JPS_PARENTDN_ID |  4035K|   357M|     4   (0)| 00:00:01 | -- ***
|*  4 |    INDEX RANGE SCAN          | IDX_ATTR_DNID       |     6 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| JPS_ATTRS           |     6 |   366 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DN"."PARENTDN"=:1)
   4 - access("DN"."ENTRYID"="ATTR"."JPS_DN_ENTRYID")

***) справедливости ради стоит заметить, что с избирательность индекса IDX_JPS_PARENTDN_ID, точнее, со структурой и использованием FREQUENCY-гистограм для VARCHAR2 столбца есть проблемы:

SQL> select table_name,
  2         column_name,
  3         num_distinct,
  4         num_nulls,
  5         num_buckets,
  6         global_stats,
  7         user_stats,
  8         histogram
  9    from dba_tab_col_statistics
 10   where (owner, table_name, column_name) in
 11         (select table_owner, table_name, column_name
 12            from dba_ind_columns
 13           where index_name in ('IDX_JPS_PARENTDN_ID'))
 14  /
 
TABLE_NAME  COLUMN_NAME  NUM_DISTINCT  NUM_NULLS NUM_BUCKETS GLOBAL_STATS USER_STATS HISTOGRAM
----------- ------------ ------------ ---------- ----------- ------------ ---------- ---------
JPS_DN      PARENTDN              873          0           2 YES          NO         FREQUENCY
JPS_DN      ENTRYID           4177087          0           1 YES          NO         NONE

— т.е. основания сомневаться в точности своей же статистики у Oracle были, но этот вопрос к SPD-директивам никоим образом не относится

UPD How to Transport SQL Plan Directives (SPD) From One Database to Another (Doc ID 2064227.1)