HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle SQL Hints --- Statistics Data And Optimizer Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

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

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

 

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> ...])

Description: Instructs the optimizer to use the adjusted table statistics data. SCALE|NULL indicates the optimizer to scale the statistics data or not. <Data to be adjusted> could be blocks and rows.

HELLODBA.COM>exec sql_explain('select /*+qb_name(M) OPT_ESTIMATE(TABLE t_objects scale_rows=0.1) TABLE_STATS(t_objects,scale, blocks=10 rows=1000) */* from t_objects', 'TYPICAL OUTLINE');

 

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |  4765 |   567K|  1666   (1)| 00:00:07 |

|   1 |  TABLE ACCESS FULL| T_OBJECTS |  4765 |   567K|  1666   (1)| 00:00:07 |

-------------------------------------------------------------------------------

Optimizer Hints

NUM_INDEX_KEYS

Usage: NUM_INDEX_KEYS([<@Block>] <Table>  <Index> <Index Key Number>)

Description: Instructs the optimizer to how many index keys when performing INLIST ITERATOR operation.

HELLODBA.COM>exec sql_explain('SELECT /*+ NUM_INDEX_KEYS(o T_OBJECTS_IDX8 2) */* FROM t_objects o WHERE owner = :A AND object_name IN (:B1, :B2, :B3)', 'BASIC OUTLINE PREDICATE');

… …

 

Outline Data

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      NUM_INDEX_KEYS(@"SEL$1" "O"@"SEL$1" "T_OBJECTS_IDX8" 2)

      INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("T_OBJECTS"."OWNER"

              "T_OBJECTS"."OBJECT_NAME" "T_OBJECTS"."SUBOBJECT_NAME"

              "T_OBJECTS"."OBJECT_ID" "T_OBJECTS"."DATA_OBJECT_ID"

              "T_OBJECTS"."OBJECT_TYPE" "T_OBJECTS"."CREATED" "T_OBJECTS"."STATUS"))

      OUTLINE_LEAF(@"SEL$1")

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("OWNER"=:A AND ("OBJECT_NAME"=:B1 OR "OBJECT_NAME"=:B2 OR

              "OBJECT_NAME"=:B3))

BIND_AWARE

Usage: BIND_AWARE

Description: Instructs the optimizer to be aware the changes of bind variable value, and can auto tune SQL using extended cursor sharing consequently.

HELLODBA.COM>var owner varchar2(30)

HELLODBA.COM>exec :owner := 'DEMO';

 

PL/SQL procedure successfully completed.

 

HELLODBA.COM>select /*+bind_aware*/* from t_objects where owner = :owner;

... ...

 

HELLODBA.COM>select sql_id, sql_text, is_bind_aware from v$sql where sql_text like 'select /*+bind_aware*/%';

 

SQL_ID        SQL_TEXT                                                       IS_BIND_AWARE

------------- -------------------------------------------------------------- -------------

6asc1cwrbw925 select /*+bind_aware*/* from t_objects where owner = :owner    Y

NO_BIND_AWARE

Usage: NO_BIND_AWARE               

Description: Prevents the optimizer to be aware the changes of bind variable value, and will not  auto tune SQL using extended cursor sharing consequently.

HELLODBA.COM>select /*+no_bind_aware*/* from t_objects where owner = :owner;

... ...

 

HELLODBA.COM>select sql_id, sql_text, is_bind_aware from v$sql where sql_text like 'select /*+no_bind_aware*/%';

 

SQL_ID        SQL_TEXT                                                       IS_BIND_AWARE

------------- -------------------------------------------------------------- -------------

586x9p08ag5f3 select /*+no_bind_aware*/* from t_objects where owner = :owner N

CURSOR_SHARING_EXACT

Usage: CURSOR_SHARING_EXACT        

Description: Instructs the optimizer match the statement exactly when using shared cursor, and it will not convert the explicit data to a bind variable.

HELLODBA.COM>show parameter cursor_sharing

 

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

cursor_sharing                       string                           SIMILAR

HELLODBA.COM>select count(*) from t_objects o where owner = 'DEMO';

… …

 

HELLODBA.COM>select sql_text from v$sql where sql_text like 'select count(*) from t_objects%';

 

SQL_TEXT

------------------------------------------------------------------------------------------------

select count(*) from t_objects o where owner = :"SYS_B_0"

 

 

HELLODBA.COM>select count(*) from t_objects o where owner = 'DEMO';

… …

 

HELLODBA.COM>select sql_text from v$sql where sql_text like 'select /*+CURSOR_SHARING_EXACT*/count(*) from t_objects%';

 

SQL_TEXT

-------------------------------------------------------------------------------------------------------

select /*+CURSOR_SHARING_EXACT*/count(*) from t_objects o where owner = 'DEMO'

DML_UPDATE

Usage: DML_UPDATE

Description: Used in the internal statement (UPD_JOININDEX) when updating Bitmap Join Index.

FBTSCAN

Usage: FBTSCAN

Description: Instructs the optimizer to query the Flashback Table instead of the base table when