在上周恩墨微信大讲堂的讨论中，几个有趣的视图跃入我们的视野，可以分享给大家。
在Oracle 11g中，新增的视图V$SQL_HINT记录了Oracle数据库中的可用Hint及其历史。
如果串起来，这几个视图的关系极大：V$SQL_FEATURE，V$SQL_FEATURE_HIERARCHY，V$SQL_HINT，通过这几个视图可以了解Oracle的SQL特性，继承关系，以及可用Hint及启用版本等。
v$sql_hint这个视图来自于底层的x$qksht表，其创建语句如下：
SELECT INST_ID,
TOKEN_QKSHTSYROW,
FID_QKSHTSYROW,
CLASS_QKSHTSYROW,
INVERSE_QKSHTSYROW,
LEVEL_QKSHTSYROW,
PROPS_QKSHTSYROW,
VERSION_QKSHTSYROW,
VERSION_OL_QKSHTSYROW,
CON_ID
FROM x$qksht
在11.2.0.3版本中，有273个Hint:
SQL> select count(*) from v$sql_hint;
COUNT(*)
----------
273
在不同版本引入的数量如下：
SQL> select substr(version,1,2),count(*) from v$sql_hint
2 group by substr(version,1,2);
SUBS COUNT(*)
---- ----------
11 66
10 80
9. 39
8. 88
在12c的版本中，查询统计如下。
SQL> select substr(version,1,2),count(*) from v$sql_hint
group by substr(version,1,2);
SUBS COUNT(*)
---- ----------
11 70
12 76
10 80
9. 39
8. 85
在V$SQL_HINT中还有一个有趣的字段：INVERSE - 显示一个提示的反向提示。通过这个对照关系也可以帮助我们更好的理解Hints。
比如，Shared的反向提示就有NOPARALLEL、NO_PARALLEL的变革历史。
SQL> select NAME,INVERSE,VERSION FROM v$sql_hint where INVERSE='SHARED';
NAME INVERSE VERSION
-------------------- ---------- -------------------------
NOPARALLEL SHARED 8.1.0
NO_PARALLEL SHARED 10.1.0.3
这些提示对应的SQL_FEATURE可以进一步的说明，NO_PARALLEL是QKSFM_CBO 相关：
SQL> select sql_feature,name,version FROM v$sql_hint where INVERSE='SHARED';
SQL_FEATURE NAME VERSION
------------------------------ -------------------- -------------------------
QKSFM_PARALLEL NOPARALLEL 8.1.0
QKSFM_CBO NO_PARALLEL 10.1.0.3
在V$SQL_HINT视图中，字段SQL_FEATURE显示了SQL特点信息，这个内容就可以从V$SQL_FEATURE中获得更详细的注释。
SQL> select SQL_FEATURE,DESCRIPTION from v$SQL_FEATURE where sql_feature='QKSFM_CBO';
SQL_FEATURE DESCRIPTION
-------------------- --------------------------------------------------
QKSFM_CBO SQL Cost Based Optimization
通过V$SQL_FEATURE_HIERARCHY 又可以将SQL特点的继承关系树构造出来：
SQL_FEATURE DESCRIPTION
------------------------------------------------------- --------------------------------------------------
ALL A Universal Feature
ANSI_REARCH ANSI rearcihtecture
COMPILATION SQL COMPILATION
CBO SQL Cost Based Optimization
ACCESS_PATH Query access path
AND_EQUAL Index and-equal access path
BITMAP_TREE Bitmap tree access path
FULL Full table scan
INDEX Index
INDEX_ASC Index (ascending)
INDEX_COMBINE Combine index for bitmap access
INDEX_DESC Use index (descending)
INDEX_FFS Index fast full scan
INDEX_JOIN Index join
INDEX_RS_ASC Index range scan
INDEX_RS_DESC Index range scan descending
INDEX_SS Index skip scan
INDEX_SS_ASC Index skip scan ascending
INDEX_SS_DESC Index skip scan descending
SORT_ELIM Sort Elimination Via Index
ADAPTIVE_PLAN Adaptive plans
AUTO_REOPT Automatic reoptimization
PERF_FEEDBACK Performance feedback
STATS_FEEDBACK Statistics feedback
BUSHY_JOIN bushy join
CBQT Cost Based Query Transformation
CBQT_OR_EXPANSION Cost Based OR Expansion
CVM Complex View Merging
DIST_PLCMT Distinct Placement
JOINFAC Join Factorization
JPPD Join Predicate Push Down
PLACE_GROUP_BY Group-By Placement
PULL_PRED pull predicates
STAR_TRANS Star Transformation
TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop
TABLE_EXPANSION Table Expansion
UNNEST unnest query block
VECTOR_AGG Vector Transformation
CURSOR_SHARING Cursor sharing
DML DML
JOIN_METHOD Join methods
USE_CUBE Cube join
USE_HASH Hash join
USE_MERGE Sort-merge join
USE_MERGE_CARTESIAN Merge join cartesian
USE_NL Nested-loop join
USE_NL_WITH_INDEX Nested-loop index join
JOIN_ORDER Join order
OPT_MODE Optimizer mode
ALL_ROWS All rows (optimizer mode)
CHOOSE Choose (optimizer mode)
FIRST_ROWS First rows (optimizer mode)
OR_EXPAND OR expansion
OUTLINE Outlines
PARTIAL_JOIN Partial Join Evaluation
PARTITION Partition
PQ Parallel Query
PARALLEL Parallel table
PQ_DISTRIBUTE PQ Distribution method
PQ_MAP PQ slave mapper
PQ_REPLICATE PQ replicate small tables
PX_JOIN_FILTER Bloom filtering for joins
STAR_TRANS Star Transformation
TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop
STATS Optimizer statistics
CARDINALITY Cardinality computation
COLUMN_STATS Basic column statistics
CPU_COSTING CPU costing
DBMS_STATS Statistics gathered by DBMS_STATS
DYNAMIC_SAMPLING Dynamic sampling
DYNAMIC_SAMPLING_EST_CDN Estimate CDN using dynamic sampling
GATHER_PLAN_STATISTICS Gather plan statistics
INDEX_STATS Basic index statistics
OPT_ESTIMATE Optimizer estimates
TABLE_STATS Basic table statistics
DATA_SECURITY_REWRITE XS Data Security Rewrite
QUERY_REWRITE query rewrite with materialized views
RBO SQL Rule Based Optimization
SQL_CODE_GENERATOR SQL Code Generator
SQL_MANAGEMENT_BASE SQL Management base
SQL_PLAN_MANAGEMENT SQL Plan Management
TRANSFORMATION Query Transformation
CBQT Cost Based Query Transformation
CBQT_OR_EXPANSION Cost Based OR Expansion
CVM Complex View Merging
DIST_PLCMT Distinct Placement
JOINFAC Join Factorization
JPPD Join Predicate Push Down
PLACE_GROUP_BY Group-By Placement
PULL_PRED pull predicates
STAR_TRANS Star Transformation
TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop
TABLE_EXPANSION Table Expansion
UNNEST unnest query block
VECTOR_AGG Vector Transformation
CLUSTER_BY_ROWID Cluster By Rowid Transformation
GROUPING_SET_XFORM Grouping Set Transformation
HEURISTIC Heuristic Query Transformation
CNT Count(col) to count(*)
COALESCE_SQ coalesce subqueries
CSE Common Sub-Expression Elimination
CVM Complex View Merging
DECORRELATE View Decorrelation
ELIMINATE_SQ eliminate subqueries
FILTER_PUSH_PRED Push filter predicates
FULL_OUTER_JOIN_TO_OUTER Join Conversion
GBYEL Group-by Elimination
JPPD Join Predicate Push Down
OBYE Order-by Elimination
OLD_PUSH_PRED Old push predicate algorithm (pre-10.1.0.3)
OUTER_JOIN_TO_ANTI Join Conversion
OUTER_JOIN_TO_INNER Join Conversion
PRED_MOVE_AROUND Predicate move around
SET_TO_JOIN Transform set operations to joins
SVM Simple View Merging
TABLE_ELIM Table Elimination
UNNEST unnest query block
USE_CONCAT Or-optimization
XML_REWRITE XML Rewrite
CHECK_ACL_REWRITE Check ACL Rewrite
COST_XML_QUERY_REWRITE Cost Based XML Query Rewrite
XMLINDEX_REWRITE XMLIndex Rewrite
ZONEMAP Zone Maps
DDL DDL
EXECUTION SQL EXECUTION
STATS_ONLOAD Statistics on load
VECTOR_PROC Vector Processing
WINDOW_CONSOLIDATOR WINDOW CONSOLIDATOR
Oracle的数据库内部功能自解析越来越完善和全面了。
