eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle SQL多版本VERSION_COUNT产生的可能性 | Blog首页 | 2015 Oracle Openworld 精彩预告 »

Oracle SQL Hint的视图解析 V$SQL_HINT

在上周恩墨微信大讲堂的讨论中,几个有趣的视图跃入我们的视野,可以分享给大家。

在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 TYPECHECKING SQL TYPECHECKING Oracle的数据库内部功能自解析越来越完善和全面了。

历史上的今天...
    >> 2009-10-12文章:
    >> 2008-10-12文章:
    >> 2006-10-12文章:
    >> 2005-10-12文章:
    >> 2004-10-12文章:
           Use RMAN to Manage BackupSet

无觅

By eygle on 2015-10-12 16:40 | Comments (0) | Oracle12c/11g | SQL.PLSQL | 3185 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com