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

相关文章

来自专栏飞扬的花生

jsencrypt参数前端加密c#解密

      写程序时一般是通过form表单或者ajax方式将参数提交到服务器进行验证,如何防止提交的请求不被抓包后串改,虽然无法说绝对安全却给非法提交提高了难度...

3859
来自专栏张善友的专栏

LINQ via C# 系列文章

LINQ via C# Recently I am giving a series of talk on LINQ. the name “LINQ via C...

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

c#实现打印功能

2652
来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3095
来自专栏魂祭心

原 canvas绘制clock

4034
来自专栏转载gongluck的CSDN博客

cocos2dx 打灰机

#include "GamePlane.h" #include "PlaneSprite.h" #include "BulletNode.h" #include...

5346
来自专栏陈仁松博客

ASP.NET Core 'Microsoft.Win32.Registry' 错误修复

今天在发布Asp.net Core应用到Azure的时候出现错误InvalidOperationException: Cannot find compilati...

4818
来自专栏闻道于事

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

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

6708
来自专栏一个爱瞎折腾的程序猿

sqlserver使用存储过程跟踪SQL

USE [master] GO /****** Object: StoredProcedure [dbo].[sp_perfworkload_trace_s...

2000
来自专栏张善友的专栏

Miguel de Icaza 细说 Mix 07大会上的Silverlight和DLR

Mono之父Miguel de Icaza 详细报道微软Mix 07大会上的Silverlight和DLR ,上面还谈到了Mono and Silverligh...

2697

扫码关注云+社区