Oracle SQL提示含义与示例 --- 统计数据和优化器提示

日期： 2013-01-08 04:57:59

分享到

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。

统计数据提示

CARDINALITY

语法：CARDINALITY([<@查询块>] [<表>] <基数>)

描述：指定查询块或对象的基数大小

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

语法：CPU_COSTING

描述：指示优化器在代价估算时考虑CPU代价

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

语法：NO_CPU_COSTING

描述：禁止优化器在代价估算时考虑CPU代价

HELLODBA.COM>exec sql_explain('select /*+NO_CPU_COSTING*/* from t_users','TYPICAL OUTLINE');

… …

Note

-----

   - cpu costing is off (consider enabling it)

DBMS_STATS

语法：DBMS_STATS

描述：出现在DBMS_STATS包收集统计数据的递归调用语句上，告诉优化器该语句是收集统计数据用的，不做额外处理（如自动调优）

示例（以下是对DBMS_STATS收集表统计数据过程跟踪得到的语句）：

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

语法：DYNAMIC_SAMPLING([<@查询块>] [<表>] <采用级别>)

描述：指定查询块或者表的采样级别，从0～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

语法：DYNAMIC_SAMPLING_EST_CDN([<@查询块>] <表>)

描述：指示优化器对已经存在统计数据的表也进行动态采样；

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

语法：GATHER_PLAN_STATISTICS

描述：指示SQL执行器在运行SQL时收集语句的统计数据；

示例：

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

语法：NO_STATS_GSETS

描述：不将对集合分组查询过程中产生的内部递归语句的运行统计数据计算在原语句的统计数据内。

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

语法：OPT_ESTIMATE([<@查询块>] <对象类型> <对象> <调整数据>=<数字>)

描述：指示优化器采用调整的对象的统计数据。其中，对象类型可以为QUERY_BLOCK/TABLE/INDEX_FILTER/INDEX_SCAN/INDEX_SKIP_SCAN/JOIN；相应对象为<@查询块>/<表>/<索引所在表索引>/JOIN(<关联对象1> <关联对象2>)；调整数据可以为ROWS/SCALE_ROWS/MIN/MAX。OPT_ESTIMATE/COLUMN_STATS/INDEX_STATS/TABLE_STATS提示通常是用于的SQL优化配置的辅助数据。

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

语法：COLUMN_STATS(<表> <字段> <SCALE|NULL> [<调整统计数据1>=<数字1> ...])

描述：指�