暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle Hints - 先知的提示

盖国强 2015-10-12
413

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


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


最后修改时间:2020-05-08 17:04:30
文章转载自盖国强,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论