Oracle Hints - 先知的提示

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

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

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2015-10-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏xingoo, 一个梦想做发明家的程序员

Oracle基础知识-sqlplus使用

Oracle的sql*plus是与oracle进行交互的客户端工具。 在sql*plus中,可以运行sql*plus命令与sql*plus语句。 我们通常所说的...

20210
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(35)-文章发布系统②-构建项目

注:阅读本文,需要阅读本系列的之前文章 代码生成器下载地址(文章开头处)  接下来我们建立数据库的表和各层的代码 我们只需要两张表,文章列表(MIS_Artic...

1889
来自专栏杨建荣的学习笔记

CPU 100%负载的性能优化分析(r7笔记第40天)

今天收到报警邮件,提示在短时间内DB time有了很大的抖动。报警邮件如下: ZABBIX-监控系统: ------------------------...

3274
来自专栏流媒体人生

基于S3C6410和ffmpeg的视频加速示例

764
来自专栏你不就像风一样

Mysql字段名与保留字冲突导致的异常解决

642
来自专栏白驹过隙

MySQL - MySQL++在c++11环境下接口设计

985
来自专栏杨建荣的学习笔记

巧用shell生成数据库检查脚本 (74天)

在生产环境中需要部署大量的数据变更。对于新增的表,需要注意权限和同义词等。但是手动去检查这些变更是否生效就很麻烦。而且也不易维护,比如写好了一个脚本,可能在过一...

2957
来自专栏云知识学习

kubernetes 基础集群排障

在排错过程中,kubectl 是最重要的工具,通常也是定位错误的起点。这里也列出一些常用的命令,在后续的各种排错过程中都会经常用到。

72112
来自专栏乐沙弥的世界

使用 DBMS_REPAIR 修复坏块

       对于Oracle数据块物理损坏的情形,在我们有备份的情况下可以直接使用备份来恢复。对于通过备份恢复,Oracel为我们提供了很多种方式,冷备,基于...

682
来自专栏白驹过隙

MySQL - MySQL++在c++11环境下接口设计

33510

扫码关注云+社区