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 条评论
登录 后参与评论

相关文章

来自专栏吴小龙同學

Android ListView addHeaderView

最近在调试我的个人大湿说app,发现在4.2系统下直接崩溃了,看log,原来是ListView addHeaderView和setAdapter方法调用顺利不同...

2818
来自专栏跟着阿笨一起玩NET

datagridview显示行号

另一种也是在这个事件下面下面 用了个for循环来做index+1的单元格赋值,效率太慢了。

60
来自专栏吴伟祥

RBAC模型与Shiro简单的实例介绍 原

RBAC是基于角色的访问控制(Role-Based Access Control )在 RBAC  中,权限与角色相关联,用户通过成为适当角色的成员而得到这些角...

604
来自专栏前端架构

程序猿的字体选择

gitHub地址:https://github.com/adobe-fonts/source-code-pro

862
来自专栏闻道于事

js登录滑动验证,不滑动无法登陆

js的判断这里是根据滑块的位置进行判断,应该是用一个flag判断 <%@ page language="java" contentType="text/html...

3278
来自专栏数据小魔方

R语言可视化——多系列柱形图(条形图)与分面组图美化技巧!

今天跟大家分享多系列与分面组图的美化技巧! 昨天讲的关于多序列柱形图与条形图美化技巧,其实还漏掉了一些一点儿。 当数据序列比较多的时候,特别是超过四个以后,还用...

3497
来自专栏deepcc

jQuery ui datepicker 日历转中文

31411
来自专栏Android干货

Android项目实战(八):列表右侧边栏拼音展示效果

2725
来自专栏智能计算时代

Best Bluemix Content

image.png Watson Services for Bluemix Sample Apps: Watson Films App (Video) Wats...

2474
来自专栏Hongten

java编程_socket_套接字_网络编程_简易的GUI多线程聊天程序

===============================================================

371

扫描关注云+社区