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

相关文章

来自专栏芋道源码1024

数据库[分库分表]中间件 Sharding-JDBC 源码分析 —— SQL 解析(四)之插入SQL

本文主要基于 Sharding-JDBC 1.5.0 正式版 1. 概述 2. InsertStatement 3. #parse() 3.1 #parseI...

4167
来自专栏大内老A

T-SQL Enhancement in SQL Server 2005[上篇]

较之前一版本,SQL Server 2005可以说是作出了根本性的革新。对于一般的编程人员来说,最具吸引力的一大特性就是实现了对CLR的寄宿,使我们可以使用任意...

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

通过shell脚本得到数据字典的信息 (r2笔记72天)

在平时的工作中,可能需要查询一些数据字典的信息,比如数据字典对应的基表信息,可以得到更多数据库内部的一些详细信息。 比如user_objects这个数据字典视图...

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

和Null有关的函数(r3笔记第48天)

关于null相关的函数在日常的工作中还有比较实用的,可能会碰到各种和Null校验相关的情况,大体有以下几种。 case when decode nvl nvl...

31812
来自专栏文渊之博

初识SQL Server2017 图数据库(一)

背景:   图数据库对于表现和遍历复杂的实体之间关系是很有效果的。而这些在传统的关系型数据库中尤其是对于报表而言很难实现。如果把传统关系型数据库比做火车的话,那...

2768
来自专栏文渊之博

T-SQL基础--TOP

理解TOP子句 众所周知,TOP子句可以通过控制返回行的数量来影响查询。 我们知道TOP子句能很容易的满足返回指定行数的子集,接下来有一些例子来展示什么情况下使...

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

探索ASH 第一篇

老是在用ASH,对它的依赖感觉已经大于AWR,昨天心血来潮,想看看ash视图里面是怎么样的,过程也算曲折,不过也算抛砖引玉。 先看看v$active_sessi...

4278
来自专栏Jerry的SAP技术分享

使用ABAP(ADBC)和Java(JDBC)连接SAP HANA数据库

在表DBCON里维护一条记录,指向HANA数据库。con_ENV里填入HANA数据库的主机名和端口号。如vmXXXX:30015

4123
来自专栏java一日一条

数据库性能优化之SQL语句优化

在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着...

1182
来自专栏SAP最佳业务实践

SAP S/4 HANA新变化-FI数据模型

With the installation of SAP Simple Finance, on-premise edition totals and appli...

4267

扫码关注云+社区