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.