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> ...]) 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用performing Flashbask query. This hint may affect the query result. HELLODBA.COM>var scn number HELLODBA.COM>begin   2    select dbms_flashback.get_system_change_number into :scn from dual;   3  end;   4  /   PL/SQL procedure successfully completed.   HELLODBA.COM>insert into t_tables(owner, table_name) values('NONE', 'NOTHING');   1 row created.   HELLODBA.COM>commit;   Commit complete.   HELLODBA.COM>SELECT /*+ QB_NAME(V) FBTSCAN FULL(S) */ count(SYS_FBT_INSDEL) FROM T_TABLES as of SCN :SCN S;   COUNT(SYS_FBT_INSDEL) ---------------------                    67   HELLODBA.COM>SELECT /*+ QB_NAME(V) FULL(S) */ count(SYS_FBT_INSDEL) FROM T_TABLES as of SCN :SCN S;   COUNT(SYS_FBT_INSDEL) ---------------------                     0 ALL_ROWS Usage: ALL_ROWS                      Description: Instructs the optimizer to optimize the statement with a goal of best throughput HELLODBA.COM>exec sql_explain('select /*+ all_rows */* from t_objects o where rownum <= 10', 'TYPICAL OUTLINE');   -------------------------------------------------------------------------------- | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |           |    10 |  1220 |  1670   (1)| 00:00:07 | |*  1 |  COUNT STOPKEY     |           |       |       |            |          | |   2 |   TABLE ACCESS FULL| T_OBJECTS | 47585 |  5669K|  1670   (1)| 00:00:07 | -------------------------------------------------------------------------------- FIRST_ROWS Usage: FIRST_ROWS([<Row Number>]) Description: instructs the optimizer to optimize a statement block with a goal o fast response to return the first n rows. HELLODBA.COM>exec sql_explain('select /*+ first_rows(10) */* from t_objects o', 'TYPICAL OUTLINE');   ------------------------------------------------------------------------------- | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |           |    10 |  1220 |     5   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| T_OBJECTS |    10 |  1220 |     5   (0)| 00:00:01 | ------------------------------------------------------------------------------- RULE Usage: RULE                         Description: Instructs the optimizer to choose the best execution based on rules. HELLODBA.COM>exec sql_explain('select /*+ rule */* from t_objects o where object_id > 0', 'TYPICAL OUTLINE'); … …   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       … …       RBO_OUTLINE       … …       END_OUTLINE_DATA   */ CHOOSE Usage: CHOOSE                       Description: Instructs the optimizer to choose the optimizing mode according to current environment. HELLODBA.COM>exec dbms_stats.delete_table_stats('DEMO', 'T_OBJECTS_DUMMY');   PL/SQL procedure successfully completed.   HELLODBA.COM>exec sql_explain('select /*+ choose */* from t_objects_dummy o where rownum <= 10', 'BASIC NOTE'); … … Note -----    - rule based optimizer used (consider using cbo) ORDERED_PREDICATES Usage: ORDERED_PREDICATES           Description: Instructs the optimizer to analyze the non-index accessing predictions in a special order. If two predications matched the same rule, they will be analyzed in the order of their appearance in the WHERE clause. 1.       Predication without user-defined function/type and sub query. 2.       Predication with user-defined function/type as well as related statistics data 3.       Predication without user-defined function/type, without related statistics data. 4.       Predication not appearing in WHERE clause, which may be generated by query transformation. 5.       Predication with sub query. Demo (Compare the cost of below two execution plans) HELLODBA.COM>exec sql_explain('select /*+ full(o) */* from t_objects o, t_tables t where o.owner = t.owner and o.object_name = t.table_name and exists (select 1 from t_tables t where o.object_name=t.table_name) and o.owner in (select username from t_users) and to_char(object_id)=:B and object_type = :A', 'TYPICAL');   -------------------------------------------------------------------------------------------- | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |             |     1 |   372 |   297   (3)| 00:00:03 | |   1 |  NESTED LOOPS                |             |       |       |            |          | |   2 |   NESTED LOOPS               |             |     1 |   372 |   297   (3)| 00:00:03 | |   3 |    NESTED LOOPS SEMI         |             |     1 |   131 |   296   (3)| 00:00:03 | |   4 |     NESTED LOOPS             |             |     1 |   110 |   295   (3)| 00:00:03 | |*  5 |      TABLE ACCESS FULL       | T_OBJECTS   |     1 |   100 |   295   (3)| 00:00:03 | |*  6 |      INDEX UNIQUE SCAN       | T_USERS_UK  |     1 |    10 |     0   (0)| 00:00:01 | |*  7 |     INDEX RANGE SCAN         | T_TABLES_PK |  1097 | 23037 |     1   (0)| 00:00:01 | |*  8 |    INDEX UNIQUE SCAN         | T_TABLES_PK |     1 |       |     0   (0)| 00:00:01 | |   9 |   TABLE ACCESS BY INDEX ROWID| T_TABLES    |     1 |   241 |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------------   HELLODBA.COM>exec sql_explain('select /*+ full(o) ORDERED_PREDICATES */* from t_objects o, t_tables t where o.owner = t.owner and o.object_name = t.table_name and exists (select 1 from t_tables t where o.object_name=t.table_name) and o.owner in (select username from t_users) and to_char(object_id)=:B and object_type = :A', 'TYPICAL');   -------------------------------------------------------------------------------------------- | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |             |     1 |   372 |   298   (3)| 00:00:03 | |   1 |  NESTED LOOPS                |             |       |       |            |          | |   2 |   NESTED LOOPS               |             |     1 |   372 |   298   (3)| 00:00:03 | |   3 |    NESTED LOOPS SEMI         |             |     1 |   131 |   297   (3)| 00:00:03 | |   4 |     NESTED LOOPS             |             |     1 |   110 |   296   (3)| 00:00:03 | |*  5 |      TABLE ACCESS FULL       | T_OBJECTS   |     1 |   100 |   296   (3)| 00:00:03 | |*  6 |      INDEX UNIQUE SCAN       | T_USERS_UK  |     1 |    10 |     0   (0)| 00:00:01 | |*  7 |     INDEX RANGE SCAN         | T_TABLES_PK |  1097 | 23037 |     1   (0)| 00:00:01 | |*  8 |    INDEX UNIQUE SCAN         | T_TABLES_PK |     1 |       |     0   (0)| 00:00:01 | |   9 |   TABLE ACCESS BY INDEX ROWID| T_TABLES    |     1 |   241 |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- SKIP_EXT_OPTIMIZER Usage: SKIP_EXT_OPTIMIZER           Description: Instructs the optimizer skip the extended optimizer. SKIP_UNQ_UNUSABLE_IDX Usage: SKIP_UNQ_UNUSABLE_IDX([<@Block>] <Table> [<Index1> ...]) or SKIP_UNQ_UNUSABLE_IDX([<@Block>] <Table> [(<Index1 Columns>) ...]) Description: Instructs the optimizer to skip the UNUSABLE indexes. HELLODBA.COM>alter index t_tables_pk unusable;   Index altered.   HELLODBA.COM>select /*+ index(t t_tables_pk) */count(1) from t_tables t; select /*+ index(t t_tables_pk) */count(1) from t_tables t * ERROR at line 1: ORA-01502: index 'DEMO.T_TABLES_PK' or partition of such index is in unusable state   HELLODBA.COM>select /*+ SKIP_UNQ_UNUSABLE_IDX(t) index(t t_tables_pk) */count(1) from t_tables t;     COUNT(1) ----------       2696