Oracle Scratchpad

March 7, 2022

v$sql_hint

Filed under: Hints,Oracle — Jonathan Lewis @ 9:50 am GMT Mar 7,2022

Here’s a quick and dirty script I thought I’d published a very long time ago. It has to be run as SYS and queries v$sql_hint to produce a list of all the things which (at least in theory) could be used as hints somewhere in the code.

I’ve ordered the list by the columns version then version_outline. I believe the version is supposed to be the version in which the hint first appeared (even if it wasn’t documented) and the version_outline is the version at which the hint could appear as part of a query’s “Outline Information” (which can then be used to generate a Stored Outline or SQL Plan Baseline – or SQL Patch). You’ll notice that a lot of the hints don’t have a version_outline.

One of the conveniences of this report is the appearance of the inverse column. For many hints there is an “opposite” hint. Hints often come in pairs (especially in recent versions of Oracle) following the pattern XXXX / NO_XXXX though there are many hints that don’t have an inverse and a few that don’t follow the pattern (e.g. use_concat / no_expand).

Perhaps one of the most useful parts of the report comes from the target_level which is a bitmap that can be decoded to show the query level(s) that the hint can apply to. There are 4 levels (corresponding to bits 0 to 3): statement, query block, object and join and that’s a useful piece of information to know when you’re trying to make best use of hints. For example, the cardinality hint is one that is not well-known and most of the people who knew anything about it seemed to assume that it could apply only to a table when in fact it can also be used for a query block (which can be extremely useful with subquery factoring) or a multi-table join (although that option is quite hard to use effectively except in the simplest cases).

rem
rem     Script:         sql_hints.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2007
rem

set linesize 255
set pagesize 100
set trimspool on
set tab off

column  name            format a32
column  inverse         format a32
column  sql_feature     format a30
column  version         format a8
column  outline         format a8
column  class           format a35
column  target          format 999
column  prop            format 999

column  bit0            format a6
column  bit1            format a6
column  bit2            format a6
column  bit3            format a6

column  bit4            format a3
column  bit5            format a3
column  bit6            format a3
column  bit7            format a3
column  bit8            format a3

break on version skip 1
compute count of version on version

spool sql_hints

select
        version,
        version_outline         outline,
        name,
        inverse,
        sql_feature,
        class,
        decode(bitand(target_level,1),1,'State')        bit0,
        decode(bitand(target_level,2),2,'QBlock')       bit1,
        decode(bitand(target_level,4),4,'Object')       bit2,
        decode(bitand(target_level,8),8,'Join')         bit3,
        target_level                                    target,
        decode(bitand(property,16),16,'16')     bit4,
        decode(bitand(property,32),32,'32')     bit5,
        decode(bitand(property,64),64,'64')     bit6,
        decode(bitand(property,128),128,'128')  bit7,
        decode(bitand(property,256),256,'256')  bit8,
        property                                prop
from
        v$sql_hint
order by
        to_number(substr(version,1,3)),
        version,
        to_number(substr(version_outline,1,3)) nulls last,
        version_outline nulls last,
        name
;

spool off

Footnote

There’s also a property column, which seems to be another bitmap but (at present) only using bits 4 to 8 of “something”. That points to the possibility that the level and property might be derived from the same couple of bytes but a quick check on x$qksht (the x$ structure underneath gv$sql_hint) shows two different columns (level_qkshtsyrow, props_qkshtsyrow) – with a 4 byte difference in their offsets. (This may be a fake, of course, since the address (addr) of every single “row” in the structure is the same, which means that when you’re looking at the x$ it’s been dynamically constructed from somewhere else.)

I haven’t yet worked out a consistent pattern for the bits in the property column, even though there are a number “coincidences” that look almost convincing, but I keep reporting the property – broken down by bits as well as the actual value – in this report in case one day I spot some clear correlation between the bits and the function of the hints.

If you’re interested in the results and don’t have access to v$sql_hint, here’s the output from 21.3.0.0. Of the 388 hints listed only about 120 are (officially) documented anywhere – there are a lot of hints you’re not supposed to use:

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
8.0.0    8.1.7    CLUSTER                                                           QKSFM_CBO                      ACCESS                                            Object             4 16              256  272
         8.1.7    INDEX                            NO_INDEX                         QKSFM_INDEX                    ACCESS                                            Object             4 16  32          256  304
         8.1.7    ROWID                                                             QKSFM_CBO                      ACCESS                                            Object             4 16              256  272
         10.1.0   NO_MERGE                         MERGE                            QKSFM_CVM                      MERGE                                      QBlock Object             6 16                    16
                  EXPR_CORR_CHECK                                                   QKSFM_CBO                      EXPR_CORR_CHECK                     State                            1                        0
                  MERGE_CONST_ON                                                    QKSFM_CBO                      MERGE_CONST_ON                      State                            1                        0
                  NL_AJ                                                             QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
                  NL_SJ                                                             QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
                  NO_MONITORING                                                     QKSFM_ALL                      NO_MONITORING                       State                            1                        0
                  NO_ORDER_ROLLUPS                                                  QKSFM_TRANSFORMATION           NO_ORDER_ROLLUPS                           QBlock                    2                        0
                  NO_STATS_GSETS                                                    QKSFM_ALL                      NO_STATS_GSETS                             QBlock                    2                        0
                  ORDERED_PREDICATES                                                QKSFM_CBO                      ORDERED_PREDICATES                         QBlock                    2 16                    16
                  QUEUE_CURR                                                        QKSFM_CBO                      ACCESS                                            Object             4                 256  256
                  QUEUE_ROWP                                                        QKSFM_CBO                      ACCESS                                            Object             4                 256  256
--------
      14

8.1.0    8.1.5    BITMAP                                                            QKSFM_CBO                      BITMAP                                     QBlock                    2                 256  256
         8.1.5    NO_PUSH_PRED                     PUSH_PRED                        QKSFM_FILTER_PUSH_PRED         PUSH_PRED                                  QBlock Object             6 16                    16
         8.1.5    PUSH_PRED                        NO_PUSH_PRED                     QKSFM_FILTER_PUSH_PRED         PUSH_PRED                                  QBlock Object             6 16                    16
         8.1.5    RULE                                                              QKSFM_RBO                      MODE                                State                            1 16                    16
         8.1.7    AND_EQUAL                                                         QKSFM_AND_EQUAL                ACCESS                                            Object             4 16  32          256  304
         8.1.7    DRIVING_SITE                                                      QKSFM_ALL                      DRIVING_SITE                                      Object             4                 256  256
         8.1.7    FACT                             NO_FACT                          QKSFM_STAR_TRANS               FACT                                              Object             4 16              256  272
         8.1.7    FULL                                                              QKSFM_FULL                     ACCESS                                            Object             4 16              256  272
         8.1.7    HASH                                                              QKSFM_ALL                      ACCESS                                            Object             4 16              256  272
         8.1.7    HASH_AJ                                                           QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         8.1.7    HASH_SJ                                                           QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         8.1.7    INDEX_COMBINE                                                     QKSFM_INDEX_COMBINE            ACCESS                                            Object             4 16  32      128 256  432
         8.1.7    INDEX_DESC                       NO_INDEX                         QKSFM_INDEX_DESC               ACCESS                                            Object             4 16  32          256  304
         8.1.7    INDEX_FFS                                                         QKSFM_INDEX_FFS                ACCESS                                            Object             4 16  32          256  304
         8.1.7    MERGE_AJ                                                          QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         8.1.7    MERGE_SJ                                                          QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         8.1.7    NO_EXPAND                        USE_CONCAT                       QKSFM_USE_CONCAT               OR_EXPAND                                  QBlock                    2 16                    16
         8.1.7    NO_FACT                          FACT                             QKSFM_STAR_TRANS               FACT                                              Object             4 16              256  272
         8.1.7    ORDERED                                                           QKSFM_CBO                      ORDERED                                    QBlock                    2 16                    16
         8.1.7    SEMIJOIN_DRIVER                                                   QKSFM_CBO                      SEMIJOIN_DRIVER                            QBlock                    2 16                    16
         8.1.7    STAR_TRANSFORMATION              NO_STAR_TRANSFORMATION           QKSFM_STAR_TRANS               STAR_TRANSFORMATION                        QBlock Object             6 16                    16
         8.1.7    SWAP_JOIN_INPUTS                 NO_SWAP_JOIN_INPUTS              QKSFM_CBO                      SWAP_JOIN_INPUTS                                  Object             4 16              256  272
         8.1.7    USE_CONCAT                       NO_EXPAND                        QKSFM_USE_CONCAT               OR_EXPAND                                  QBlock                    2 16                    16
         8.1.7    USE_HASH                         NO_USE_HASH                      QKSFM_USE_HASH                 JOIN                                              Object             4 16      64  128 256  464
         8.1.7    USE_MERGE                        NO_USE_MERGE                     QKSFM_USE_MERGE                JOIN                                              Object             4 16      64      256  336
         8.1.7    USE_NL                           NO_USE_NL                        QKSFM_USE_NL                   JOIN                                              Object             4 16      64      256  336
         10.1.0   MERGE                            NO_MERGE                         QKSFM_CVM                      MERGE                                      QBlock Object             6 16                    16
         10.2.0.1 ALL_ROWS                                                          QKSFM_ALL_ROWS                 MODE                                State                            1 16                    16
         10.2.0.1 FIRST_ROWS                                                        QKSFM_FIRST_ROWS               MODE                                State                            1 16                    16
         10.2.0.5 PUSH_SUBQ                        NO_PUSH_SUBQ                     QKSFM_TRANSFORMATION           PUSH_SUBQ                                  QBlock                    2 16                    16
                  APPEND                           NOAPPEND                         QKSFM_CBO                      APPEND                              State                            1                        0
                  CACHE                            NOCACHE                          QKSFM_EXECUTION                CACHE                                             Object             4                 256  256
                  CHOOSE                                                            QKSFM_CHOOSE                   MODE                                State                            1 16                    16
                  DEREF_NO_REWRITE                                                  QKSFM_ALL                      DEREF_NO_REWRITE                    State                            1                        0
                  INDEX_ASC                        NO_INDEX                         QKSFM_INDEX_ASC                ACCESS                                            Object             4 16  32          256  304
                  NESTED_TABLE_GET_REFS                                             QKSFM_ALL                      NESTED_TABLE_GET_REFS               State                            1                        0
                  NOAPPEND                         APPEND                           QKSFM_CBO                      APPEND                              State                            1                        0
                  NOCACHE                          CACHE                            QKSFM_EXECUTION                CACHE                                             Object             4                 256  256
                  NOPARALLEL                       SHARED                           QKSFM_PARALLEL                 SHARED                              State         Object             5                 256  256
                  NO_PARALLEL_INDEX                PARALLEL_INDEX                   QKSFM_PQ                       PARALLEL_INDEX                                    Object             4     32          256  288
                  PARALLEL_INDEX                   NO_PARALLEL_INDEX                QKSFM_PQ                       PARALLEL_INDEX                                    Object             4     32          256  288
                  PIV_GB                                                            QKSFM_ALL                      PIV_GB                                     QBlock                    2                        0
                  PIV_SSF                                                           QKSFM_ALL                      PIV_SSF                                    QBlock                    2                        0
                  REMOTE_MAPPED                                                     QKSFM_ALL                      REMOTE_MAPPED                              QBlock                    2 16              256  272
                  SHARED                           NO_PARALLEL                      QKSFM_PARALLEL                 SHARED                              State         Object             5                 256  256
                  STAR                                                              QKSFM_STAR_TRANS               STAR                                       QBlock                    2 16                    16
                  TIV_GB                                                            QKSFM_ALL                      PIV_GB                                     QBlock                    2                        0
                  TIV_SSF                                                           QKSFM_ALL                      PIV_SSF                                    QBlock                    2                        0
                  USE_ANTI                                                          QKSFM_CBO                      USE_ANTI                                          Object             4 16              256  272
                  USE_SEMI                                                          QKSFM_CBO                      USE_SEMI                                          Object             4 16              256  272
--------
      50

8.1.5    8.1.7    NO_ACCESS                                                         QKSFM_ALL                      NO_ACCESS                                         Object             4                 256  256
         8.1.7    NO_INDEX                         INDEX                            QKSFM_INDEX                    NO_INDEX                                          Object             4 16  32          256  304
         8.1.7    NO_REWRITE                       REWRITE                          QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2 16                    16
         8.1.7    PQ_DISTRIBUTE                                                     QKSFM_PQ_DISTRIBUTE            PQ_DISTRIBUTE                                     Object             4 16              256  272
         8.1.7    REWRITE                          NO_REWRITE                       QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 INDEX_JOIN                                                        QKSFM_INDEX_JOIN               ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 DOMAIN_INDEX_NO_SORT             DOMAIN_INDEX_SORT                QKSFM_CBO                      DOMAIN_INDEX_SORT                          QBlock                    2                        0
         10.2.0.1 DOMAIN_INDEX_SORT                DOMAIN_INDEX_NO_SORT             QKSFM_CBO                      DOMAIN_INDEX_SORT                          QBlock                    2                        0
                  BUFFER                           NO_BUFFER                        QKSFM_CBO                      BUFFER                                     QBlock                    2                        0
                  BYPASS_UJVC                                                       QKSFM_CBO                      BYPASS_UJVC                                QBlock                    2                        0
                  CACHE_CB                         NOCACHE                          QKSFM_CBO                      CACHE_CB                                          Object             4                 256  256
                  CUBE_GB                                                           QKSFM_CBO                      CUBE_GB                                    QBlock                    2                        0
                  NESTED_TABLE_SET_SETID                                            QKSFM_ALL                      NESTED_TABLE_SET_SETID              State                            1                        0
                  NO_BUFFER                        BUFFER                           QKSFM_CBO                      BUFFER                                     QBlock                    2                        0
                  RESTORE_AS_INTERVALS                                              QKSFM_CBO                      RESTORE_AS_INTERVALS                       QBlock                    2                        0
                  SAVE_AS_INTERVALS                                                 QKSFM_CBO                      SAVE_AS_INTERVALS                          QBlock                    2                        0
                  SCN_ASCENDING                                                     QKSFM_ALL                      SCN_ASCENDING                       State                            1                        0
--------
      17

8.1.6    10.1.0   NO_UNNEST                        UNNEST                           QKSFM_UNNEST                   UNNEST                                     QBlock                    2 16                    16
         10.1.0   UNNEST                           NO_UNNEST                        QKSFM_UNNEST                   UNNEST                                     QBlock                    2 16                    16
         10.1.0.3 LEADING                                                           QKSFM_JOIN_ORDER               LEADING                                                  Join        8 16              256  272
                  SYS_PARALLEL_TXN                                                  QKSFM_CBO                      SYS_PARALLEL_TXN                           QBlock                    2                        0
--------
       4


VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
9.0.0    10.2.0.1 INDEX_SS                         NO_INDEX_SS                      QKSFM_INDEX_SS                 ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 INDEX_SS_DESC                    NO_INDEX_SS                      QKSFM_INDEX_SS_DESC            ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 PQ_MAP                           PQ_NOMAP                         QKSFM_PQ_MAP                   PQ_MAP                                            Object             4 16              256  272
         10.2.0.1 PQ_NOMAP                         PQ_MAP                           QKSFM_PQ_MAP                   PQ_MAP                                            Object             4 16              256  272
         18.1.0   INLINE                           MATERIALIZE                      QKSFM_TRANSFORMATION           INLINE                                     QBlock                    2 16                    16
         18.1.0   MATERIALIZE                      INLINE                           QKSFM_TRANSFORMATION           INLINE                                     QBlock                    2 16                    16
                  ANTIJOIN                                                          QKSFM_TRANSFORMATION           ANTIJOIN                                   QBlock                    2 16                    16
                  BYPASS_RECURSIVE_CHECK                                            QKSFM_ALL                      BYPASS_RECURSIVE_CHECK                     QBlock                    2                        0
                  CARDINALITY                                                       QKSFM_STATS                    CARDINALITY                                QBlock Object Join       14 16              256  272
                  CPU_COSTING                      NO_CPU_COSTING                   QKSFM_CPU_COSTING              CPU_COSTING                                QBlock                    2 16                    16
                  CURSOR_SHARING_EXACT                                              QKSFM_CBO                      CURSOR_SHARING_EXACT                       QBlock                    2                        0
                  DML_UPDATE                                                        QKSFM_CBO                      DML_UPDATE                          State                            1                        0
                  GBY_CONC_ROLLUP                                                   QKSFM_TRANSFORMATION           GBY_CONC_ROLLUP                            QBlock                    2                        0
                  HWM_BROKERED                                                      QKSFM_CBO                      HWM_BROKERED                               QBlock                    2                        0
                  INDEX_RRS                                                         QKSFM_CBO                      ACCESS                                            Object             4 16  32          256  304
                  INDEX_SS_ASC                     NO_INDEX_SS                      QKSFM_INDEX_SS_ASC             ACCESS                                            Object             4 16  32          256  304
                  LOCAL_INDEXES                                                     QKSFM_CBO                      LOCAL_INDEXES                              QBlock                    2                        0
                  MV_MERGE                                                          QKSFM_TRANSFORMATION           MV_MERGE                                   QBlock                    2                        0
                  NO_CPU_COSTING                   CPU_COSTING                      QKSFM_CPU_COSTING              CPU_COSTING                                QBlock                    2 16                    16
                  NO_PRUNE_GSETS                                                    QKSFM_TRANSFORMATION           NO_PRUNE_GSETS                             QBlock                    2                        0
                  NO_SEMIJOIN                      SEMIJOIN                         QKSFM_TRANSFORMATION           SEMIJOIN                                   QBlock                    2 16                    16
                  OVERFLOW_NOMOVE                                                   QKSFM_CBO                      OVERFLOW_NOMOVE                            QBlock                    2                        0
                  SEMIJOIN                         NO_SEMIJOIN                      QKSFM_TRANSFORMATION           SEMIJOIN                                   QBlock                    2 16                    16
                  SKIP_EXT_OPTIMIZER                                                QKSFM_CBO                      SKIP_EXT_OPTIMIZER                         QBlock                    2 16                    16
                  SQLLDR                                                            QKSFM_CBO                      SQLLDR                              State                            1                        0
                  USE_TTT_FOR_GSETS                                                 QKSFM_TRANSFORMATION           USE_TTT_FOR_GSETS                          QBlock                    2                        0
--------
      26

9.2.0    10.1.0   EXPAND_GSET_TO_UNION             NO_EXPAND_GSET_TO_UNION          QKSFM_TRANSFORMATION           EXPAND_GSET_TO_UNION                       QBlock                    2                        0
         10.1.0   NO_EXPAND_GSET_TO_UNION          EXPAND_GSET_TO_UNION             QKSFM_TRANSFORMATION           EXPAND_GSET_TO_UNION                       QBlock                    2                        0
         10.2.0.5 NO_PUSH_SUBQ                     PUSH_SUBQ                        QKSFM_TRANSFORMATION           PUSH_SUBQ                                  QBlock                    2 16                    16
         11.1.0.6 FORCE_XML_QUERY_REWRITE          NO_XML_QUERY_REWRITE             QKSFM_XML_REWRITE              FORCE_XML_QUERY_REWRITE             State                            1                        0
         11.1.0.6 NO_XML_QUERY_REWRITE             FORCE_XML_QUERY_REWRITE          QKSFM_XML_REWRITE              FORCE_XML_QUERY_REWRITE             State                            1                        0
                  DYNAMIC_SAMPLING                                                  QKSFM_DYNAMIC_SAMPLING         DYNAMIC_SAMPLING                           QBlock Object             6 16              256  272
                  DYNAMIC_SAMPLING_EST_CDN                                          QKSFM_DYNAMIC_SAMPLING_EST_CDN DYNAMIC_SAMPLING_EST_CDN                          Object             4 16              256  272
                  IGNORE_WHERE_CLAUSE                                               QKSFM_ALL                      IGNORE_WHERE_CLAUSE                 State                            1                        0
                  NO_QKN_BUFF                                                       QKSFM_CBO                      NO_QKN_BUFF                                QBlock                    2                        0
                  NO_REF_CASCADE                   REF_CASCADE_CURSOR               QKSFM_CBO                      REF_CASCADE_CURSOR                  State                            1                        0
                  REF_CASCADE_CURSOR               NO_REF_CASCADE                   QKSFM_CBO                      REF_CASCADE_CURSOR                  State                            1                        0
                  SYS_DL_CURSOR                                                     QKSFM_CBO                      SYS_DL_CURSOR                       State                            1                        0
                  SYS_RID_ORDER                                                     QKSFM_ALL                      SYS_RID_ORDER                              QBlock                    2                        0
--------
      13

10.1.0.3 10.1.0.3 NO_BASETABLE_MULTIMV_REWRITE     REWRITE                          QKSFM_ALL                      REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 NO_INDEX_FFS                     INDEX_FFS                        QKSFM_INDEX_FFS                NO_INDEX_FFS                                      Object             4 16  32          256  304
         10.1.0.3 NO_INDEX_SS                      INDEX_SS                         QKSFM_INDEX_SS                 NO_INDEX_SS                                       Object             4 16  32          256  304
         10.1.0.3 NO_MULTIMV_REWRITE               REWRITE                          QKSFM_ALL                      REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 NO_SET_TO_JOIN                   SET_TO_JOIN                      QKSFM_SET_TO_JOIN              SET_TO_JOIN                                QBlock                    2 16                    16
         10.1.0.3 NO_STAR_TRANSFORMATION           STAR_TRANSFORMATION              QKSFM_STAR_TRANS               STAR_TRANSFORMATION                        QBlock Object             6 16                    16
         10.1.0.3 NO_SWAP_JOIN_INPUTS              SWAP_JOIN_INPUTS                 QKSFM_CBO                      SWAP_JOIN_INPUTS                                  Object             4 16              256  272
         10.1.0.3 NO_USE_HASH                      USE_HASH                         QKSFM_USE_HASH                 NO_USE_HASH                                       Object             4 16      64      256  336
         10.1.0.3 NO_USE_MERGE                     USE_MERGE                        QKSFM_USE_MERGE                NO_USE_MERGE                                      Object             4 16      64      256  336
         10.1.0.3 NO_USE_NL                        USE_NL                           QKSFM_USE_NL                   NO_USE_NL                                         Object             4 16      64      256  336
         10.1.0.3 SET_TO_JOIN                      NO_SET_TO_JOIN                   QKSFM_SET_TO_JOIN              SET_TO_JOIN                                QBlock                    2 16                    16
         10.2.0.1 IGNORE_OPTIM_EMBEDDED_HINTS                                       QKSFM_ALL                      IGNORE_OPTIM_EMBEDDED_HINTS         State                            1                        0
         10.2.0.1 OPTIMIZER_FEATURES_ENABLE                                         QKSFM_ALL                      OPTIMIZER_FEATURES_ENABLE           State                            1 16              256  272
                  COLUMN_STATS                                                      QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  FBTSCAN                                                           QKSFM_CBO                      FBTSCAN                             State                            1                        0
                  GATHER_PLAN_STATISTICS                                            QKSFM_GATHER_PLAN_STATISTICS   GATHER_PLAN_STATISTICS              State                            1                        0
                  INCLUDE_VERSION                                                   QKSFM_ALL                      INCLUDE_VERSION                     State                            1                        0
                  INDEX_STATS                                                       QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  MODEL_DONTVERIFY_UNIQUENESS                                       QKSFM_TRANSFORMATION           MODEL_DONTVERIFY_UNIQUENESS                QBlock                    2                        0
                  MODEL_MIN_ANALYSIS                                                QKSFM_TRANSFORMATION           MODEL_MIN_ANALYSIS                         QBlock                    2                        0
                  MODEL_NO_ANALYSIS                                                 QKSFM_ALL                      MODEL_MIN_ANALYSIS                         QBlock                    2                        0
                  MODEL_PUSH_REF                   NO_MODEL_PUSH_REF                QKSFM_TRANSFORMATION           MODEL_PUSH_REF                             QBlock                    2                        0
                  NESTED_TABLE_FAST_INSERT                                          QKSFM_ALL                      NESTED_TABLE_FAST_INSERT            State                            1                        0
                  NO_MODEL_PUSH_REF                MODEL_PUSH_REF                   QKSFM_ALL                      MODEL_PUSH_REF                             QBlock                    2                        0
                  NO_PARALLEL                      SHARED                           QKSFM_CBO                      SHARED                              State         Object             5                 256  256
                  NO_PARTIAL_COMMIT                                                 QKSFM_CBO                      NO_PARTIAL_COMMIT                   State                            1                        0
                  NO_QUERY_TRANSFORMATION                                           QKSFM_TRANSFORMATION           NO_QUERY_TRANSFORMATION             State                            1 16                    16
                  OPAQUE_TRANSFORM                                                  QKSFM_TRANSFORMATION           OPAQUE_TRANSFORM                    State                            1                        0
                  OPAQUE_XCANONICAL                                                 QKSFM_TRANSFORMATION           OPAQUE_XCANONICAL                   State                            1                        0
                  OPT_ESTIMATE                                                      QKSFM_OPT_ESTIMATE             OPT_ESTIMATE                               QBlock Object Join       14 16              256  272
                  QB_NAME                                                           QKSFM_ALL                      QB_NAME                                    QBlock                    2                 256  256
                  RESTRICT_ALL_REF_CONS                                             QKSFM_ALL                      RESTRICT_ALL_REF_CONS               State                            1                        0
                  REWRITE_OR_ERROR                                                  QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2                        0
                  SKIP_UNQ_UNUSABLE_IDX                                             QKSFM_CBO                      SKIP_UNQ_UNUSABLE_IDX               State                            1                        0
                  STREAMS                                                           QKSFM_CBO                      STREAMS                             State                            1                        0
                  TABLE_STATS                                                       QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  TRACING                                                           QKSFM_EXECUTION                TRACING                             State                            1                        0
                  USE_NL_WITH_INDEX                NO_USE_NL                        QKSFM_USE_NL_WITH_INDEX        USE_NL_WITH_INDEX                                 Object             4 16  32          256  304
                  USE_WEAK_NAME_RESL                                                QKSFM_ALL                      USE_WEAK_NAME_RESL                  State                            1                        0
                  VECTOR_READ                                                       QKSFM_CBO                      VECTOR_READ                         State                            1                        0
                  VECTOR_READ_TRACE                                                 QKSFM_CBO                      VECTOR_READ_TRACE                   State                            1                        0
                  X_DYN_PRUNE                                                       QKSFM_CBO                      X_DYN_PRUNE                                QBlock                    2                        0
--------
      42

10.2.0.1 10.2.0.1 BITMAP_TREE                                                       QKSFM_BITMAP_TREE              ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 ELIMINATE_JOIN                   NO_ELIMINATE_JOIN                QKSFM_TABLE_ELIM               ELIMINATE_JOIN                                    Object             4 16                    16
         10.2.0.1 ELIMINATE_OBY                    NO_ELIMINATE_OBY                 QKSFM_OBYE                     ELIMINATE_OBY                              QBlock                    2 16                    16
         10.2.0.1 NO_ELIMINATE_JOIN                ELIMINATE_JOIN                   QKSFM_TABLE_ELIM               ELIMINATE_JOIN                                    Object             4 16                    16
         10.2.0.1 NO_ELIMINATE_OBY                 ELIMINATE_OBY                    QKSFM_OBYE                     ELIMINATE_OBY                              QBlock                    2 16                    16
         10.2.0.1 NO_PULL_PRED                     PULL_PRED                        QKSFM_PULL_PRED                PULL_PRED                                         Object             4 16                    16
         10.2.0.1 OLD_PUSH_PRED                                                     QKSFM_OLD_PUSH_PRED            OLD_PUSH_PRED                              QBlock Object             6 16                    16

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
10.2.0.1 10.2.0.1 OPT_PARAM                                                         QKSFM_ALL                      OPT_PARAM                           State                            1 16              256  272
         10.2.0.1 OUTLINE                                                           QKSFM_ALL                      OUTLINE                                    QBlock                    2                        0
         10.2.0.1 OUTLINE_LEAF                                                      QKSFM_ALL                      OUTLINE_LEAF                               QBlock                    2                        0
         10.2.0.1 PULL_PRED                        NO_PULL_PRED                     QKSFM_PULL_PRED                PULL_PRED                                         Object             4 16                    16
         10.2.0.1 RBO_OUTLINE                                                       QKSFM_RBO                      RBO_OUTLINE                         State                            1                        0
         10.2.0.5 NO_USE_HASH_AGGREGATION          USE_HASH_AGGREGATION             QKSFM_ALL                      USE_HASH_AGGREGATION                       QBlock                    2                        0
         10.2.0.5 USE_HASH_AGGREGATION             NO_USE_HASH_AGGREGATION          QKSFM_ALL                      USE_HASH_AGGREGATION                       QBlock                    2                        0
         11.1.0.6 NO_PX_JOIN_FILTER                PX_JOIN_FILTER                   QKSFM_PX_JOIN_FILTER           PX_JOIN_FILTER                                    Object             4 16      64      256  336
         11.1.0.6 NO_XML_DML_REWRITE                                                QKSFM_XML_REWRITE              NO_XML_DML_REWRITE                  State                            1                        0
         11.1.0.6 PX_JOIN_FILTER                   NO_PX_JOIN_FILTER                QKSFM_PX_JOIN_FILTER           PX_JOIN_FILTER                                    Object             4 16      64      256  336
                  DBMS_STATS                                                        QKSFM_DBMS_STATS               DBMS_STATS                          State                            1                        0
                  INLINE_XMLTYPE_NT                                                 QKSFM_ALL                      INLINE_XMLTYPE_NT                   State                            1                        0
                  MODEL_COMPILE_SUBQUERY                                            QKSFM_TRANSFORMATION           MODEL_COMPILE_SUBQUERY                     QBlock                    2                        0
                  MODEL_DYNAMIC_SUBQUERY                                            QKSFM_TRANSFORMATION           MODEL_DYNAMIC_SUBQUERY                     QBlock                    2                        0
                  NO_CARTESIAN                                                      QKSFM_ALL                      NO_CARTESIAN                                      Object             4 16      64      256  336
                  NO_SQL_TUNE                                                       QKSFM_ALL                      NO_SQL_TUNE                         State                            1                        0
                  PRECOMPUTE_SUBQUERY                                               QKSFM_TRANSFORMATION           PRECOMPUTE_SUBQUERY                        QBlock                    2                        0
                  PRESERVE_OID                                                      QKSFM_ALL                      PRESERVE_OID                        State                            1                        0
--------
      25

10.2.0.2 10.2.0.2 CONNECT_BY_COST_BASED            NO_CONNECT_BY_COST_BASED         QKSFM_TRANSFORMATION           CONNECT_BY_COST_BASED                      QBlock                    2 16                    16
         10.2.0.2 CONNECT_BY_FILTERING             NO_CONNECT_BY_FILTERING          QKSFM_ALL                      CONNECT_BY_FILTERING                       QBlock                    2 16                    16
         10.2.0.2 NO_CONNECT_BY_COST_BASED         CONNECT_BY_COST_BASED            QKSFM_TRANSFORMATION           CONNECT_BY_COST_BASED                      QBlock                    2 16                    16
         10.2.0.2 NO_CONNECT_BY_FILTERING          CONNECT_BY_FILTERING             QKSFM_ALL                      CONNECT_BY_FILTERING                       QBlock                    2 16                    16
--------
       4

10.2.0.3 10.2.0.3 NATIVE_FULL_OUTER_JOIN           NO_NATIVE_FULL_OUTER_JOIN        QKSFM_ALL                      NATIVE_FULL_OUTER_JOIN                     QBlock                    2 16                    16
         10.2.0.3 NO_NATIVE_FULL_OUTER_JOIN        NATIVE_FULL_OUTER_JOIN           QKSFM_ALL                      NATIVE_FULL_OUTER_JOIN                     QBlock                    2 16                    16
         10.2.0.3 NUM_INDEX_KEYS                                                    QKSFM_CBO                      ACCESS                                            Object             4 16  32          256  304
--------
       3

10.2.0.4 10.2.0.4 CONNECT_BY_COMBINE_SW            NO_CONNECT_BY_COMBINE_SW         QKSFM_ALL                      CONNECT_BY_COMBINE_SW                      QBlock                    2 16                    16
         10.2.0.4 NO_CONNECT_BY_COMBINE_SW         CONNECT_BY_COMBINE_SW            QKSFM_ALL                      CONNECT_BY_COMBINE_SW                      QBlock                    2 16                    16
--------
       2

10.2.0.5 10.2.0.5 CONNECT_BY_CB_WHR_ONLY           NO_CONNECT_BY_CB_WHR_ONLY        QKSFM_TRANSFORMATION           CONNECT_BY_CB_WHR_ONLY                     QBlock                    2 16                    16
         10.2.0.5 GBY_PUSHDOWN                     NO_GBY_PUSHDOWN                  QKSFM_ALL                      GBY_PUSHDOWN                               QBlock                    2 16                    16
         10.2.0.5 NO_CONNECT_BY_CB_WHR_ONLY        CONNECT_BY_CB_WHR_ONLY           QKSFM_TRANSFORMATION           CONNECT_BY_CB_WHR_ONLY                     QBlock                    2 16                    16
         10.2.0.5 NO_GBY_PUSHDOWN                  GBY_PUSHDOWN                     QKSFM_ALL                      GBY_PUSHDOWN                               QBlock                    2 16                    16
--------
       4

11.1.0.6 11.1.0.6 COST_XML_QUERY_REWRITE           NO_COST_XML_QUERY_REWRITE        QKSFM_COST_XML_QUERY_REWRITE   COST_XML_QUERY_REWRITE              State                            1                        0
         11.1.0.6 DB_VERSION                                                        QKSFM_ALL                      DB_VERSION                          State                            1 16              256  272
         11.1.0.6 DOMAIN_INDEX_FILTER              NO_DOMAIN_INDEX_FILTER           QKSFM_CBO                      DOMAIN_INDEX_FILTER                               Object             4 16  32          256  304
         11.1.0.6 INDEX_RS_ASC                                                      QKSFM_INDEX_RS_ASC             ACCESS                                            Object             4 16  32          256  304
         11.1.0.6 INDEX_RS_DESC                                                     QKSFM_INDEX_RS_DESC            ACCESS                                            Object             4 16  32          256  304
         11.1.0.6 NLJ_BATCHING                     NO_NLJ_BATCHING                  QKSFM_EXECUTION                ACCESS                                            Object             4 16              256  272
         11.1.0.6 NLJ_PREFETCH                     NO_NLJ_PREFETCH                  QKSFM_EXECUTION                NLJ_PREFETCH                                      Object             4 16              256  272
         11.1.0.6 NO_COST_XML_QUERY_REWRITE        COST_XML_QUERY_REWRITE           QKSFM_COST_XML_QUERY_REWRITE   NO_COST_XML_QUERY_REWRITE           State                            1                        0
         11.1.0.6 NO_DOMAIN_INDEX_FILTER           DOMAIN_INDEX_FILTER              QKSFM_CBO                      NO_DOMAIN_INDEX_FILTER                            Object             4 16  32          256  304
         11.1.0.6 NO_NLJ_BATCHING                  NLJ_BATCHING                     QKSFM_EXECUTION                ACCESS                                            Object             4 16              256  272
         11.1.0.6 NO_NLJ_PREFETCH                  NLJ_PREFETCH                     QKSFM_EXECUTION                NLJ_PREFETCH                                      Object             4 16              256  272
         11.1.0.6 NO_OUTER_JOIN_TO_INNER           OUTER_JOIN_TO_INNER              QKSFM_OUTER_JOIN_TO_INNER      OUTER_JOIN_TO_INNER                        QBlock Object             6 16                    16
         11.1.0.6 NO_PLACE_GROUP_BY                PLACE_GROUP_BY                   QKSFM_PLACE_GROUP_BY           PLACE_GROUP_BY                             QBlock                    2 16                    16
         11.1.0.6 NO_SUBQUERY_PRUNING              SUBQUERY_PRUNING                 QKSFM_CBO                      SUBQUERY_PRUNING                                  Object             4 16              256  272
         11.1.0.6 NO_USE_INVISIBLE_INDEXES         USE_INVISIBLE_INDEXES            QKSFM_INDEX                    USE_INVISIBLE_INDEXES               State                            1                        0
         11.1.0.6 NO_XMLINDEX_REWRITE              XMLINDEX_REWRITE                 QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 NO_XMLINDEX_REWRITE_IN_SELECT    XMLINDEX_REWRITE_IN_SELECT       QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 OUTER_JOIN_TO_INNER              NO_OUTER_JOIN_TO_INNER           QKSFM_OUTER_JOIN_TO_INNER      OUTER_JOIN_TO_INNER                        QBlock Object             6 16                    16
         11.1.0.6 PLACE_GROUP_BY                   NO_PLACE_GROUP_BY                QKSFM_PLACE_GROUP_BY           PLACE_GROUP_BY                             QBlock                    2 16                    16
         11.1.0.6 SUBQUERY_PRUNING                 NO_SUBQUERY_PRUNING              QKSFM_CBO                      SUBQUERY_PRUNING                                  Object             4 16              256  272
         11.1.0.6 USE_INVISIBLE_INDEXES            NO_USE_INVISIBLE_INDEXES         QKSFM_INDEX                    USE_INVISIBLE_INDEXES               State                            1                        0
         11.1.0.6 USE_MERGE_CARTESIAN                                               QKSFM_USE_MERGE_CARTESIAN      JOIN                                              Object             4 16      64      256  336
         11.1.0.6 XMLINDEX_REWRITE                 NO_XMLINDEX_REWRITE              QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 XMLINDEX_REWRITE_IN_SELECT       NO_XMLINDEX_REWRITE_IN_SELECT    QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 XML_DML_RWT_STMT                                                  QKSFM_XML_REWRITE              XML_DML_RWT_STMT                    State                            1                        0
                  CHECK_ACL_REWRITE                NO_CHECK_ACL_REWRITE             QKSFM_CHECK_ACL_REWRITE        CHECK_ACL_REWRITE                   State                            1                        0
                  MONITOR                          NO_MONITOR                       QKSFM_ALL                      MONITOR                             State                            1                        0
                  NO_CHECK_ACL_REWRITE             CHECK_ACL_REWRITE                QKSFM_CHECK_ACL_REWRITE        NO_CHECK_ACL_REWRITE                State                            1                        0
                  NO_LOAD                                                           QKSFM_EXECUTION                NO_LOAD                             State                            1                        0
                  NO_MONITOR                       MONITOR                          QKSFM_ALL                      MONITOR                             State                            1                        0
                  NO_RESULT_CACHE                  RESULT_CACHE                     QKSFM_EXECUTION                RESULT_CACHE                               QBlock                    2                        0
                  RESULT_CACHE                     NO_RESULT_CACHE                  QKSFM_EXECUTION                RESULT_CACHE                               QBlock                    2                        0
--------
      32

11.1.0.7          BIND_AWARE                       NO_BIND_AWARE                    QKSFM_CURSOR_SHARING           BIND_AWARE                          State                            1                        0
                  CHANGE_DUPKEY_ERROR_INDEX                                         QKSFM_DML                      CHANGE_DUPKEY_ERROR_INDEX                         Object             4     32          256  288
                  IGNORE_ROW_ON_DUPKEY_INDEX                                        QKSFM_DML                      IGNORE_ROW_ON_DUPKEY_INDEX                        Object             4     32          256  288
                  NO_BIND_AWARE                    BIND_AWARE                       QKSFM_CURSOR_SHARING           BIND_AWARE                          State                            1                        0
                  RETRY_ON_ROW_CHANGE                                               QKSFM_DML                      RETRY_ON_ROW_CHANGE                 State                            1                        0
--------
       5

11.2.0.1 11.2.0.1 COALESCE_SQ                      NO_COALESCE_SQ                   QKSFM_COALESCE_SQ              COALESCE_SQ                                QBlock                    2 16                    16
         11.2.0.1 CONNECT_BY_ELIM_DUPS             NO_CONNECT_BY_ELIM_DUPS          QKSFM_ALL                      CONNECT_BY_ELIM_DUPS                       QBlock                    2 16                    16
         11.2.0.1 EXPAND_TABLE                     NO_EXPAND_TABLE                  QKSFM_TABLE_EXPANSION          EXPAND_TABLE                                      Object             4 16                    16
         11.2.0.1 FACTORIZE_JOIN                   NO_FACTORIZE_JOIN                QKSFM_JOINFAC                  FACTORIZE_JOIN                             QBlock                    2 16                    16
         11.2.0.1 NO_COALESCE_SQ                   COALESCE_SQ                      QKSFM_COALESCE_SQ              COALESCE_SQ                                QBlock                    2 16                    16
         11.2.0.1 NO_CONNECT_BY_ELIM_DUPS          CONNECT_BY_ELIM_DUPS             QKSFM_ALL                      CONNECT_BY_ELIM_DUPS                       QBlock                    2 16                    16
         11.2.0.1 NO_EXPAND_TABLE                  EXPAND_TABLE                     QKSFM_TABLE_EXPANSION          EXPAND_TABLE                                      Object             4 16                    16
         11.2.0.1 NO_FACTORIZE_JOIN                FACTORIZE_JOIN                   QKSFM_JOINFAC                  FACTORIZE_JOIN                             QBlock                    2 16                    16

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
11.2.0.1 11.2.0.1 NO_PLACE_DISTINCT                PLACE_DISTINCT                   QKSFM_DIST_PLCMT               PLACE_DISTINCT                             QBlock                    2 16                    16
         11.2.0.1 NO_TRANSFORM_DISTINCT_AGG        TRANSFORM_DISTINCT_AGG           QKSFM_TRANSFORMATION           TRANSFORM_DISTINCT_AGG                     QBlock                    2                        0
         11.2.0.1 PLACE_DISTINCT                   NO_PLACE_DISTINCT                QKSFM_DIST_PLCMT               PLACE_DISTINCT                             QBlock                    2 16                    16
         11.2.0.1 TRANSFORM_DISTINCT_AGG           NO_TRANSFORM_DISTINCT_AGG        QKSFM_TRANSFORMATION           TRANSFORM_DISTINCT_AGG                     QBlock                    2                        0
         21.1.0.1 NO_DST_UPGRADE_INSERT_CONV       DST_UPGRADE_INSERT_CONV          QKSFM_ALL                      DST_UPGRADE_INSERT_CONV             State                            1                        0
                  APPEND_VALUES                    NOAPPEND                         QKSFM_CBO                      APPEND_VALUES                       State                            1                        0
                  DST_UPGRADE_INSERT_CONV          NO_DST_UPGRADE_INSERT_CONV       QKSFM_ALL                      DST_UPGRADE_INSERT_CONV             State                            1                        0
                  NO_STATEMENT_QUEUING             STATEMENT_QUEUING                QKSFM_PARALLEL                 STATEMENT_QUEUING                   State                            1                        0
                  NO_SUBSTRB_PAD                                                    QKSFM_EXECUTION                NO_SUBSTRB_PAD                      State                            1                        0
                  STATEMENT_QUEUING                NO_STATEMENT_QUEUING             QKSFM_PARALLEL                 STATEMENT_QUEUING                   State                            1                        0
                  XMLINDEX_SEL_IDX_TBL                                              QKSFM_ALL                      XMLINDEX_SEL_IDX_TBL                State                            1                        0
--------
      19

11.2.0.2 11.2.0.2 NO_TABLE_LOOKUP_BY_NL            TABLE_LOOKUP_BY_NL               QKSFM_TABLE_LOOKUP_BY_NL       TABLE_LOOKUP_BY_NL                                Object             4 16                    16
         11.2.0.2 NO_USE_HASH_GBY_FOR_PUSHDOWN     USE_HASH_GBY_FOR_PUSHDOWN        QKSFM_ALL                      USE_HASH_GBY_FOR_PUSHDOWN                  QBlock                    2                        0
         11.2.0.2 TABLE_LOOKUP_BY_NL               NO_TABLE_LOOKUP_BY_NL            QKSFM_TABLE_LOOKUP_BY_NL       TABLE_LOOKUP_BY_NL                                Object             4 16                    16
         11.2.0.2 USE_HASH_GBY_FOR_PUSHDOWN        NO_USE_HASH_GBY_FOR_PUSHDOWN     QKSFM_ALL                      USE_HASH_GBY_FOR_PUSHDOWN                  QBlock                    2                        0
                  NO_XDB_FASTPATH_INSERT           XDB_FASTPATH_INSERT              QKSFM_ALL                      XDB_FASTPATH_INSERT                 State                            1                        0
                  XDB_FASTPATH_INSERT              NO_XDB_FASTPATH_INSERT           QKSFM_ALL                      XDB_FASTPATH_INSERT                 State                            1                        0
--------
       6

11.2.0.3 11.2.0.3 FULL_OUTER_JOIN_TO_OUTER         NO_FULL_OUTER_JOIN_TO_OUTER      QKSFM_CBO                      FULL_OUTER_JOIN_TO_OUTER                          Object             4 16              256  272
         11.2.0.3 NO_FULL_OUTER_JOIN_TO_OUTER      FULL_OUTER_JOIN_TO_OUTER         QKSFM_CBO                      FULL_OUTER_JOIN_TO_OUTER                          Object             4 16              256  272
         11.2.0.3 NO_OUTER_JOIN_TO_ANTI            OUTER_JOIN_TO_ANTI               QKSFM_CBO                      OUTER_JOIN_TO_ANTI                                Object             4 16              256  272
         11.2.0.3 NO_SEMI_TO_INNER                 SEMI_TO_INNER                    QKSFM_CBO                      NO_SEMI_TO_INNER                                  Object             4 16              256  272
         11.2.0.3 OUTER_JOIN_TO_ANTI               NO_OUTER_JOIN_TO_ANTI            QKSFM_CBO                      OUTER_JOIN_TO_ANTI                                Object             4 16              256  272
         11.2.0.3 SEMI_TO_INNER                    NO_SEMI_TO_INNER                 QKSFM_CBO                      SEMI_TO_INNER                                     Object             4 16              256  272
--------
       6

11.2.0.4          DISABLE_PARALLEL_DML             ENABLE_PARALLEL_DML              QKSFM_DML                      ENABLE_PARALLEL_DML                 State                            1                        0
                  ENABLE_PARALLEL_DML              DISABLE_PARALLEL_DML             QKSFM_DML                      ENABLE_PARALLEL_DML                 State                            1                        0
--------
       2

12.1.0.1 12.1.0.1 BATCH_TABLE_ACCESS_BY_ROWID      NO_BATCH_TABLE_ACCESS_BY_ROWID   QKSFM_EXECUTION                BATCH_TABLE_ACCESS_BY_ROWID                       Object             4 16              256  272
         12.1.0.1 BITMAP_AND                                                        QKSFM_BITMAP_TREE              BITMAP_AND                                        Object             4 16  32                48
         12.1.0.1 CLUSTERING                       NO_CLUSTERING                    QKSFM_CLUSTERING               CLUSTERING                          State                            1                        0
         12.1.0.1 CLUSTER_BY_ROWID                 NO_CLUSTER_BY_ROWID              QKSFM_CBO                      CLUSTER_BY_ROWID                                  Object             4 16              256  272
         12.1.0.1 CUBE_AJ                                                           QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         12.1.0.1 CUBE_SJ                                                           QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         12.1.0.1 DATA_SECURITY_REWRITE_LIMIT      NO_DATA_SECURITY_REWRITE         QKSFM_DATA_SECURITY_REWRITE    DATA_SECURITY_REWRITE_LIMIT         State                            1                        0
         12.1.0.1 DECORRELATE                      NO_DECORRELATE                   QKSFM_DECORRELATE              DECORRELATE                                QBlock                    2 16                    16
         12.1.0.1 NO_BATCH_TABLE_ACCESS_BY_ROWID   BATCH_TABLE_ACCESS_BY_ROWID      QKSFM_EXECUTION                BATCH_TABLE_ACCESS_BY_ROWID                       Object             4 16              256  272
         12.1.0.1 NO_CLUSTERING                    CLUSTERING                       QKSFM_CLUSTERING               CLUSTERING                          State                            1                        0
         12.1.0.1 NO_CLUSTER_BY_ROWID              CLUSTER_BY_ROWID                 QKSFM_CBO                      CLUSTER_BY_ROWID                                  Object             4 16              256  272
         12.1.0.1 NO_DATA_SECURITY_REWRITE         DATA_SECURITY_REWRITE_LIMIT      QKSFM_DATA_SECURITY_REWRITE    DATA_SECURITY_REWRITE_LIMIT         State                            1                        0
         12.1.0.1 NO_DECORRELATE                   DECORRELATE                      QKSFM_DECORRELATE              DECORRELATE                                QBlock                    2 16                    16
         12.1.0.1 NO_PARTIAL_JOIN                  PARTIAL_JOIN                     QKSFM_PARTIAL_JOIN             PARTIAL_JOIN                                      Object             4 16              256  272
         12.1.0.1 NO_PARTIAL_ROLLUP_PUSHDOWN       PARTIAL_ROLLUP_PUSHDOWN          QKSFM_PQ                       PARTIAL_ROLLUP_PUSHDOWN                    QBlock                    2 16                    16
         12.1.0.1 NO_PQ_CONCURRENT_UNION           PQ_CONCURRENT_UNION              QKSFM_PQ                       PQ_CONCURRENT_UNION                 State  QBlock                    3                        0
         12.1.0.1 NO_PQ_REPLICATE                  PQ_REPLICATE                     QKSFM_PQ_REPLICATE             PQ_REPLICATE                                      Object             4 16              256  272
         12.1.0.1 NO_PQ_SKEW                       PQ_SKEW                          QKSFM_PQ                       PQ_SKEW                                           Object             4 16              256  272
         12.1.0.1 NO_PX_FAULT_TOLERANCE            PX_FAULT_TOLERANCE               QKSFM_PQ                       PX_FAULT_TOLERANCE                  State                            1                        0
         12.1.0.1 NO_USE_CUBE                      USE_CUBE                         QKSFM_USE_CUBE                 JOIN                                              Object             4 16      64      256  336
         12.1.0.1 NO_ZONEMAP                       ZONEMAP                          QKSFM_ZONEMAP                  ZONEMAP                                           Object             4                 256  256
         12.1.0.1 PARTIAL_JOIN                     NO_PARTIAL_JOIN                  QKSFM_PARTIAL_JOIN             PARTIAL_JOIN                                      Object             4 16              256  272
         12.1.0.1 PARTIAL_ROLLUP_PUSHDOWN          NO_PARTIAL_ROLLUP_PUSHDOWN       QKSFM_PQ                       PARTIAL_ROLLUP_PUSHDOWN                    QBlock                    2 16                    16
         12.1.0.1 PQ_CONCURRENT_UNION              NO_PQ_CONCURRENT_UNION           QKSFM_PQ                       PQ_CONCURRENT_UNION                 State  QBlock                    3                        0
         12.1.0.1 PQ_DISTRIBUTE_WINDOW                                              QKSFM_PQ                       PQ_DISTRIBUTE_WINDOW                       QBlock                    2 16                    16
         12.1.0.1 PQ_FILTER                                                         QKSFM_PQ                       PQ_FILTER                                  QBlock                    2                        0
         12.1.0.1 PQ_REPLICATE                     NO_PQ_REPLICATE                  QKSFM_PQ_REPLICATE             PQ_REPLICATE                                      Object             4 16              256  272
         12.1.0.1 PQ_SKEW                          NO_PQ_SKEW                       QKSFM_PQ                       PQ_SKEW                                           Object             4 16              256  272
         12.1.0.1 PX_FAULT_TOLERANCE               NO_PX_FAULT_TOLERANCE            QKSFM_PQ                       PX_FAULT_TOLERANCE                  State                            1                        0
         12.1.0.1 USE_CUBE                         NO_USE_CUBE                      QKSFM_USE_CUBE                 JOIN                                              Object             4 16      64      256  336
         12.1.0.1 ZONEMAP                          NO_ZONEMAP                       QKSFM_ZONEMAP                  ZONEMAP                                           Object             4                 256  256
                  AUTO_REOPTIMIZE                  NO_AUTO_REOPTIMIZE               QKSFM_AUTO_REOPT               AUTO_REOPTIMIZE                     State                            1                        0
                  GATHER_OPTIMIZER_STATISTICS      NO_GATHER_OPTIMIZER_STATISTICS   QKSFM_DBMS_STATS               GATHER_OPTIMIZER_STATISTICS         State                            1                        0
                  NO_AUTO_REOPTIMIZE               AUTO_REOPTIMIZE                  QKSFM_AUTO_REOPT               AUTO_REOPTIMIZE                     State                            1                        0
                  NO_GATHER_OPTIMIZER_STATISTICS   GATHER_OPTIMIZER_STATISTICS      QKSFM_DBMS_STATS               GATHER_OPTIMIZER_STATISTICS         State                            1                        0
                  USE_HIDDEN_PARTITIONS                                             QKSFM_PARTITION                USE_HIDDEN_PARTITIONS                      QBlock                    2                        0
                  WITH_PLSQL                                                        QKSFM_ALL                      WITH_PLSQL                          State                            1                        0
--------
      37

12.1.0.2 12.1.0.2 ADAPTIVE_PLAN                    NO_ADAPTIVE_PLAN                 QKSFM_ADAPTIVE_PLAN            ADAPTIVE_PLAN                       State                            1 16                    16
         12.1.0.2 ANSI_REARCH                      NO_ANSI_REARCH                   QKSFM_ANSI_REARCH              ANSI_REARCH                                QBlock                    2 16                    16
         12.1.0.2 ELIM_GROUPBY                     NO_ELIM_GROUPBY                  QKSFM_TRANSFORMATION           ELIM_GROUPBY                               QBlock                    2 16                    16
         12.1.0.2 INMEMORY                         NO_INMEMORY                      QKSFM_EXECUTION                INMEMORY                                   QBlock Object             6         64            64
         12.1.0.2 INMEMORY_PRUNING                 NO_INMEMORY_PRUNING              QKSFM_EXECUTION                INMEMORY_PRUNING                           QBlock Object             6         64            64
         12.1.0.2 NO_ADAPTIVE_PLAN                 ADAPTIVE_PLAN                    QKSFM_ADAPTIVE_PLAN            ADAPTIVE_PLAN                       State                            1 16                    16
         12.1.0.2 NO_ANSI_REARCH                   ANSI_REARCH                      QKSFM_ANSI_REARCH              ANSI_REARCH                                QBlock                    2 16                    16
         12.1.0.2 NO_ELIM_GROUPBY                  ELIM_GROUPBY                     QKSFM_TRANSFORMATION           ELIM_GROUPBY                               QBlock                    2 16                    16
         12.1.0.2 NO_INMEMORY                      INMEMORY                         QKSFM_EXECUTION                INMEMORY                                   QBlock Object             6         64            64
         12.1.0.2 NO_INMEMORY_PRUNING              INMEMORY_PRUNING                 QKSFM_EXECUTION                INMEMORY_PRUNING                           QBlock Object             6         64            64
         12.1.0.2 NO_USE_VECTOR_AGGREGATION        USE_VECTOR_AGGREGATION           QKSFM_VECTOR_AGG               USE_VECTOR_AGGREGATION                     QBlock                    2 16                    16
         12.1.0.2 NO_VECTOR_TRANSFORM              VECTOR_TRANSFORM                 QKSFM_VECTOR_AGG               VECTOR_TRANSFORM                           QBlock                    2 16                    16
         12.1.0.2 NO_VECTOR_TRANSFORM_DIMS         VECTOR_TRANSFORM_DIMS            QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_DIMS                             Object             4 16      64            80
         12.1.0.2 NO_VECTOR_TRANSFORM_FACT         VECTOR_TRANSFORM_FACT            QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_FACT                             Object             4 16      64            80
         12.1.0.2 USE_VECTOR_AGGREGATION           NO_USE_VECTOR_AGGREGATION        QKSFM_VECTOR_AGG               USE_VECTOR_AGGREGATION                     QBlock                    2 16                    16
         12.1.0.2 VECTOR_TRANSFORM                 NO_VECTOR_TRANSFORM              QKSFM_VECTOR_AGG               VECTOR_TRANSFORM                           QBlock                    2 16                    16
         12.1.0.2 VECTOR_TRANSFORM_DIMS            NO_VECTOR_TRANSFORM_DIMS         QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_DIMS                             Object             4 16      64            80
         12.1.0.2 VECTOR_TRANSFORM_FACT            NO_VECTOR_TRANSFORM_FACT         QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_FACT                             Object             4 16      64            80
                  RESERVOIR_SAMPLING                                                QKSFM_EXECUTION                RESERVOIR_SAMPLING                  State                            1                        0
--------

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
      19

12.2.0.1 12.2.0.1 BUSHY_JOIN                       NO_BUSHY_JOIN                    QKSFM_BUSHY_JOIN               BUSHY_JOIN                                 QBlock                    2 16                    16
         12.2.0.1 CONTAINERS                                                        QKSFM_ALL                      CONTAINERS                          State                            1                        0
         12.2.0.1 DIST_AGG_PROLLUP_PUSHDOWN        NO_DIST_AGG_PROLLUP_PUSHDOWN     QKSFM_PQ                       DIST_AGG_PROLLUP_PUSHDOWN                  QBlock                    2 16                    16
         12.2.0.1 ELIMINATE_SQ                     NO_ELIMINATE_SQ                  QKSFM_ELIMINATE_SQ             ELIMINATE_SQ                               QBlock                    2 16                    16
         12.2.0.1 NO_BUSHY_JOIN                    BUSHY_JOIN                       QKSFM_BUSHY_JOIN               BUSHY_JOIN                                 QBlock                    2 16                    16
         12.2.0.1 NO_DIST_AGG_PROLLUP_PUSHDOWN     DIST_AGG_PROLLUP_PUSHDOWN        QKSFM_PQ                       DIST_AGG_PROLLUP_PUSHDOWN                  QBlock                    2 16                    16
         12.2.0.1 NO_ELIMINATE_SQ                  ELIMINATE_SQ                     QKSFM_ELIMINATE_SQ             ELIMINATE_SQ                               QBlock                    2 16                    16
         12.2.0.1 NO_OR_EXPAND                     OR_EXPAND                        QKSFM_CBQT_OR_EXPANSION        OR_EXPAND                                  QBlock                    2 16                    16
         12.2.0.1 NO_USE_DAGG_UNION_ALL_GSETS      USE_DAGG_UNION_ALL_GSETS         QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         12.2.0.1 NO_USE_HASH_GBY_FOR_DAGGPSHD     USE_HASH_GBY_FOR_DAGGPSHD        QKSFM_ALL                      USE_HASH_GBY_FOR_DAGGPSHD                  QBlock                    2                        0
         12.2.0.1 NO_USE_PARTITION_WISE_DISTINCT   USE_PARTITION_WISE_DISTINCT      QKSFM_PARTITION                USE_PARTITION_WISE_DISTINCT                QBlock                    2                        0
         12.2.0.1 NO_USE_PARTITION_WISE_GBY        USE_PARTITION_WISE_GBY           QKSFM_PARTITION                USE_PARTITION_WISE_GBY                     QBlock                    2                        0
         12.2.0.1 ORDER_SUBQ                                                        QKSFM_TRANSFORMATION           ORDER_SUBQ                                 QBlock                    2 16                    16
         12.2.0.1 OR_EXPAND                        NO_OR_EXPAND                     QKSFM_CBQT_OR_EXPANSION        OR_EXPAND                                  QBlock                    2 16                    16
         12.2.0.1 USE_DAGG_UNION_ALL_GSETS         NO_USE_DAGG_UNION_ALL_GSETS      QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         12.2.0.1 USE_HASH_GBY_FOR_DAGGPSHD        NO_USE_HASH_GBY_FOR_DAGGPSHD     QKSFM_ALL                      USE_HASH_GBY_FOR_DAGGPSHD                  QBlock                    2                        0
         12.2.0.1 USE_PARTITION_WISE_DISTINCT      NO_USE_PARTITION_WISE_DISTINCT   QKSFM_PARTITION                USE_PARTITION_WISE_DISTINCT                QBlock                    2                        0
         12.2.0.1 USE_PARTITION_WISE_GBY           NO_USE_PARTITION_WISE_GBY        QKSFM_PARTITION                USE_PARTITION_WISE_GBY                     QBlock                    2                        0
                  DATA_VALIDATE                                                     QKSFM_EXECUTION                DATA_VALIDATE                       State                            1                        0
                  FRESH_MV                                                          QKSFM_MVIEWS                   FRESH_MV                            State                            1                        0
                  SQL_SCOPE                                                         QKSFM_COMPILATION              SQL_SCOPE                           State                            1                        0
                  XMLTSET_DML_ENABLE                                                QKSFM_ALL                      XMLTSET_DML_ENABLE                  State                            1                        0
--------
      22

18.1.0   18.1.0   ANSWER_QUERY_USING_STATS         NO_ANSWER_QUERY_USING_STATS      QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS                   QBlock                    2 16                    16
         18.1.0   NO_ANSWER_QUERY_USING_STATS      ANSWER_QUERY_USING_STATS         QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS                   QBlock                    2 16                    16
         18.1.0   NO_PUSH_HAVING_TO_GBY            PUSH_HAVING_TO_GBY               QKSFM_EXECUTION                PUSH_HAVING_TO_GBY                         QBlock                    2                        0
         18.1.0   NO_REORDER_WIF                   REORDER_WIF                      QKSFM_PARTITION                REORDER_WIF                                QBlock                    2                        0
         18.1.0   NO_USE_PARTITION_WISE_WIF        USE_PARTITION_WISE_WIF           QKSFM_PARTITION                USE_PARTITION_WISE_WIF                     QBlock                    2                        0
         18.1.0   PUSH_HAVING_TO_GBY               NO_PUSH_HAVING_TO_GBY            QKSFM_EXECUTION                PUSH_HAVING_TO_GBY                         QBlock                    2                        0
         18.1.0   REORDER_WIF                      NO_REORDER_WIF                   QKSFM_PARTITION                REORDER_WIF                                QBlock                    2                        0
         18.1.0   USE_PARTITION_WISE_WIF           NO_USE_PARTITION_WISE_WIF        QKSFM_PARTITION                USE_PARTITION_WISE_WIF                     QBlock                    2                        0
                  AV_CACHE                                                          QKSFM_EXECUTION                AV_CACHE                                   QBlock                    2                        0
                  CURRENT_INSTANCE                                                  QKSFM_ALL                      CURRENT_INSTANCE                    State                            1                        0
                  MEMOPTIMIZE_WRITE                                                 QKSFM_EXECUTION                MEMOPTIMIZE_WRITE                   State                            1                        0
                  PDB_LOCAL_ONLY                                                    QKSFM_DML                      PDB_LOCAL_ONLY                      State                            1                        0
                  SKIP_PROXY                                                        QKSFM_ALL                      SKIP_PROXY                          State                            1                        0
                  SUPPRESS_LOAD                                                     QKSFM_DDL                      SUPPRESS_LOAD                       State                            1                        0
                  SYSTEM_STATS                                                      QKSFM_ALL                      SYSTEM_STATS                        State                            1 16              256  272
--------
      15

19.1.0   19.1.0   NO_PQ_EXPAND_TABLE               PQ_EXPAND_TABLE                  QKSFM_TABLE_EXPANSION          PQ_EXPAND_TABLE                                   Object             4 16                    16
         19.1.0   NO_USE_SCALABLE_GBY_INVDIST      USE_SCALABLE_GBY_INVDIST         QKSFM_PQ                       USE_SCALABLE_GBY_INVDIST                   QBlock                    2                        0
         19.1.0   PQ_EXPAND_TABLE                  NO_PQ_EXPAND_TABLE               QKSFM_TABLE_EXPANSION          PQ_EXPAND_TABLE                                   Object             4 16                    16
         19.1.0   USE_SCALABLE_GBY_INVDIST         NO_USE_SCALABLE_GBY_INVDIST      QKSFM_PQ                       USE_SCALABLE_GBY_INVDIST                   QBlock                    2                        0
                  JSON_LENGTH                                                       QKSFM_EXECUTION                JSON_LENGTH                         State                            1                        0
                  QUARANTINE                                                        QKSFM_EXECUTION                QUARANTINE                          State                            1                        0
--------
       6

20.1.0   20.1.0   FORCE_JSON_TABLE_TRANSFORM       NO_JSON_TABLE_TRANSFORM          QKSFM_JSON_REWRITE             FORCE_JSON_TABLE_TRANSFORM          State                            1                        0
         20.1.0   NO_JSON_TABLE_TRANSFORM          FORCE_JSON_TABLE_TRANSFORM       QKSFM_JSON_REWRITE             FORCE_JSON_TABLE_TRANSFORM          State                            1                        0
         20.1.0   NO_SET_GBY_PUSHDOWN              SET_GBY_PUSHDOWN                 QKSFM_ALL                      SET_GBY_PUSHDOWN                           QBlock                    2 16                    16
         20.1.0   SET_GBY_PUSHDOWN                 NO_SET_GBY_PUSHDOWN              QKSFM_ALL                      SET_GBY_PUSHDOWN                           QBlock                    2 16                    16
                  ANALYTIC_VIEW_SQL                                                 QKSFM_COMPILATION              ANALYTIC_VIEW_SQL                          QBlock                    2                        0
                  DENORM_AV                                                         QKSFM_COMPILATION              DENORM_AV                                  QBlock                    2                        0
--------
       6

21.1.0   21.1.0   DAGG_OPTIM_GSETS                 NO_DAGG_OPTIM_GSETS              QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         21.1.0   HASHSET_BUILD                                                     QKSFM_EXECUTION                HASHSET_BUILD                              QBlock                    2 16                    16
         21.1.0   NO_DAGG_OPTIM_GSETS              DAGG_OPTIM_GSETS                 QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         21.1.0   NO_OBY_GBYPD_SEPARATE            OBY_GBYPD_SEPARATE               QKSFM_PQ                       OBY_GBYPD_SEPARATE                         QBlock                    2 16                    16
         21.1.0   NO_PQ_NONLEAF_SKEW               PQ_NONLEAF_SKEW                  QKSFM_PQ                       PQ_NONLEAF_SKEW                                   Object             4 16              256  272
         21.1.0   OBY_GBYPD_SEPARATE               NO_OBY_GBYPD_SEPARATE            QKSFM_PQ                       OBY_GBYPD_SEPARATE                         QBlock                    2 16                    16
         21.1.0   ORDER_KEY_VECTOR_USE                                              QKSFM_VECTOR_AGG               ORDER_KEY_VECTOR_USE                       QBlock                    2 16              256  272
         21.1.0   OSON_GET_CONTENT                                                  QKSFM_JSON                     OSON_GET_CONTENT                    State                            1                        0
         21.1.0   PQ_NONLEAF_SKEW                  NO_PQ_NONLEAF_SKEW               QKSFM_PQ                       PQ_NONLEAF_SKEW                                   Object             4 16              256  272
--------
       9


388 rows selected

2 Comments »

  1. […] spent on calls to dbms_stats over the stats retention period. System stats: report current values SQL Hints:  simple listing of v$sql_hint showing all the hints, their inverses, and the level(s) for which […]

    Pingback by Simple scripts | Oracle Scratchpad — March 7, 2022 @ 10:23 am GMT Mar 7,2022 | Reply

  2. […] If you want to see a complete list of hints, including the version where they appeared, and the version where they got into the “stored outline” code, then 11g gives you the view v$sql_hint. (Sample query with output from 19c) […]

    Pingback by Undocumented Hints | Oracle Scratchpad — April 2, 2022 @ 9:51 am BST Apr 2,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: