Oracle SQL Hints --- Statistics Data And Optimizer Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 04:57:59

Share to 

Statistics Data Hints CARDINALITY         Usage: CARDINALITY([<@Block>] [<Table>] <Cardinality Size>) Description: Specify the cardinality size of specified object of query block for the optimizer. HELLODBA.COM>exec sql_explain('select /*+ FULL(T) CARDINALITY(@"SEL$1" 10) */* from T_TABLES T, T_USERS u where t.owner=u.username', 'TYPICAL');   ------------------------------------------------------------------------------- | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |          |    10 |  3530 |    31   (4)| 00:00:01 | |*  1 |  HASH JOIN         |          |  2696 |   929K|    31   (4)| 00:00:01 | |   2 |   TABLE ACCESS FULL| T_USERS  |    31 |  3472 |     3   (0)| 00:00:01 | |   3 |   TABLE ACCESS FULL| T_TABLES |  2696 |   634K|    28   (4)| 00:00:01 | ------------------------------------------------------------------------------- CPU_COSTING Usage: CPU_COSTING                   Description: Instructs the optimizer to consider CPU cost when optimizing. HELLODBA.COM>alter session set "_optimizer_cost_model"=io;   Session altered.   HELLODBA.COM>exec sql_explain('select /*+CPU_COSTING*/* from t_users, t_tables','TYPICAL OUTLINE');   --------------------------------------------------------------------------------- | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |          | 83576 |    28M|   835   (3)| 00:00:09 | |   1 |  MERGE JOIN CARTESIAN|          | 83576 |    28M|   835   (3)| 00:00:09 | |   2 |   TABLE ACCESS FULL  | T_USERS  |    31 |  3472 |     3   (0)| 00:00:01 | |   3 |   BUFFER SORT        |          |  2696 |   634K|   832   (4)| 00:00:09 | |   4 |    TABLE ACCESS FULL | T_TABLES |  2696 |   634K|    27   (4)| 00:00:01 | --------------------------------------------------------------------------------- NO_CPU_COSTING Usage: NO_CPU_COSTING               Description: Prevents the optimizer to consider CPU cost when optimizing. HELLODBA.COM>exec sql_explain('select /*+NO_CPU_COSTING*/* from t_users','TYPICAL OUTLINE'); … … Note -----    - cpu costing is off (consider enabling it) DBMS_STATS Usage: DBMS_STATS                   Description: Used in the internal statement generated by DBMS_STAS when gathering statistic data, it will instruct the optimizer that the statement is for gathering statistics data only, should not be performed an extra process, e.g. auto tuning. Demo (Below statement was abstracted from the trace file of table statistics data gathering): select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */ count(*)  from "DEMO"."T_OBJECTS" sample block (  9.1911764706,1) t DYNAMIC_SAMPLING Usage: DYNAMIC_SAMPLING([<@Block>] [<Table>] <Level>) Description: Specify the sampling level of specified table of query block, from 0 to 10. HELLODBA.COM>exec dbms_stats.delete_table_stats('DEMO', 'T_OBJECTS_DUMMY');   PL/SQL procedure successfully completed.   HELLODBA.COM>exec sql_explain('select /*+ DYNAMIC_SAMPLING(3) */* from t_objects_dummy o, t_users u where o.owner=u.username', 'TYPICAL'); ... ... Note -----    - dynamic sampling used for this statement (level=3) DYNAMIC_SAMPLING_EST_CDN Usage: DYNAMIC_SAMPLING_EST_CDN([<@Block>] <Table>) Description: Force the optimizer to perform dynamic sampling even if there is statistics data of the specified table. HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO', 'T_OBJECTS_DUMMY');   PL/SQL procedure successfully completed.   HELLODBA.COM>exec sql_explain('select /*+ DYNAMIC_SAMPLING(3) DYNAMIC_SAMPLING_EST_CDN(O) */* from t_objects_dummy o, t_users u where o.owner=u.username', 'TYPICAL'); ... ...   Note -----    - dynamic sampling used for this statement (level=3) GATHER_PLAN_STATISTICS Usage: GATHER_PLAN_STATISTICS       Description: Instructs the SQL executor to gather the running statistics data of the execution plan. HELLODBA.COM>exec sql_explain('select /*+qb_name(M) GATHER_PLAN_STATISTICS*/* from t_tables t, t_users u where t.owner=u.username', 'BASIC LAST ALLSTATS', FALSE);   ------------------------------------------------------------------------------------------------------- | Id |Operation          |Name    |Starts |E-Rows |A-Rows |   A-Time  |Buffers |OMem |1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------- |*  1| HASH JOIN         |        |     1 |  2069 |  2071 |00:00:00.01|   2082 | 767K| 767K| 1161K (0)| |   2|  TABLE ACCESS FULL|T_USERS |     1 |    43 |    43 |00:00:00.01|      7 |      |    |         | |   3|  TABLE ACCESS FULL|T_TABLES|     1 |  2071 |  2071 |00:00:00.01|   2075 |      |    |         | ------------------------------------------------------------------------------------------------------- NO_STATS_GSETS Usage: NO_STATS_GSETS               Description: Instructs the SQL executor not gather the running statistics of the internal statement generated by the Grouping Sets query. HELLODBA.COM>SELECT /*+gather_plan_statistics*/ owner, object_type, count(object_id) obj_cnt FROM t_objects o GROUP BY GROUPING SETS (owner, object_type); ... ... 67 rows selected.   HELLODBA.COM>SELECT /*+gather_plan_statistics NO_STATS_GSETS*/ owner, object_type, count(object_id) obj_cnt FROM t_objects o GROUP BY GROUPING SETS (owner, object_type); ... ... 67 rows selected.   HELLODBA.COM>select substr(sql_text,1, 50), sharable_mem, executions, buffer_gets from v$sql where sql_text like 'SELECT /*+gather_plan_statistics%';   SUBSTR(SQL_TEXT,1,50)                                              SHARABLE_MEM EXECUTIONS BUFFER_GETS ------------------------------------------------------------------ ------------ ---------- ----------- SELECT /*+gather_plan_statistics*/ owner, object_t                         38421          1    4034 SELECT /*+gather_plan_statistics NO_STATS_GSETS*/                          38428          1    1490 OPT_ESTIMATE Usage: OPT_ESTIMATE([<@Block>] <Object Type> <Object> <Data to be adjusted>=<Number>) Description: Instructs the optimizer to use the adjusted object statistics data. <Object Type> could be QUERY_BLOCK/TABLE/INDEX_FILTER/INDEX_SCAN/INDEX_SKIP_SCAN/JOIN; <Object> could be <@Block>/<Table>/<Index of the Table>/JOIN(<Join Object1> <Join Object2>); <Data to be adjusted> could be ROWS/SCALE_ROWS/MIN/MAX. OPT_ESTIMATE/COLUMN_STATS/INDEX_STATS/TABLE_STATS hints are normally used as a part of SQL Profiler data. HELLODBA.COM>exec sql_explain('SELECT /*+QB_NAME(M) OPT_ESTIMATE(INDEX_SCAN U T_USERS_PK ROWS=8)*/ * from t_users u where user_id<:A','TYPICAL');   ------------------------------------------------------------------------------------------ | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT            |            |     2 |   224 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_USERS    |     2 |   224 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_USERS_PK |     8 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------ COLUMN_STATS Usage: COLUMN_STATS(<Table> <字段> <SCALE|NULL> [<Data1 to be adjusted >=<Number 1> ...]) Description: Instructs the optimizer to use the adjusted column statistics data. SCALE|NULL indicates the optimizer to scale the statistics data or not. <Data to be adjusted> could be length, distinct, nulls, min and max. HELLODBA.COM>exec sql_explain('select /*+qb_name(M) OPT_ESTIMATE(TABLE O scale_rows=721) COLUMN_STATS(t_objects, OBJECT_NAME, scale, length=666666) */object_name from t_objects o', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------------- | Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |                |    51M|  4958M|   185   (2)| 00:00:02 | |   1 |  INDEX FAST FULL SCAN| T_OBJECTS_IDX8 |    51M|  4958M|   185   (2)| 00:00:02 | --------------------------------------------------------------------------------------- INDEX_STATS Usage: INDEX_STATS(<Table>  <Index> <SCALE|NULL> [<Data1 to be adjusted>=<Number 1> ...]) Description: Instructs the optimizer to use the adjusted index statistics data. SCALE|NULL indicates the optimizer to scale the statistics data or not. <Data to be adjusted> could be blocks, index_rows, keys and clustering_factor. HELLODBA.COM>exec sql_explain('select /*+qb_name(M) index(o (object_id)) OPT_ESTIMATE(TABLE O scale_rows=721) INDEX_STATS(t_objects, t_objects_pk, scale, clustering_factor=66666) */object_name from t_objects o where object_id < 10000', 'TYPICAL OUTLINE');   -------------------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |  7010K|   200M|  9023   (1)| 00:01:31 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS    |  7010K|   200M|  9023   (1)| 00:01:31 | |*  2 |   INDEX RANGE SCAN          | T_OBJECTS_PK |  9723 |       |    20   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- TABLE_STATS Usage: TABLE_STATS(<Table> <SCALE|NULL> [<Data1 to be adjusted >=<Number 1> ...])