17.和优化相关的hint

最新推荐文章于 2024-09-28 09:34:36 发布

weixin_33923762

最新推荐文章于 2024-09-28 09:34:36 发布

阅读量87

点赞数

文章标签：数据结构与算法数据库

原文链接：http://blog.51cto.com/chenhuican/1329179

版权

一、和优化器相关的hint

oracle允许在系统级别、会话级别、SQL语句级别设定优化器的类型：

A 系统级别：alter system set optimizer_mode=all_rows

B 会话级别：alter session set optimizer_mode=all_rows

C SQL语句级别：HINT

all_rows和first_rows默认是用CBO优化器，显示的告诉优化器当前使用那种模式来作SQL语句的解析。

案例：

全局参数：

SQL> show parameter opt

NAME TYPE VALUE

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

optimizer_mode string ALL_ROWS

但是个别语句用first_rows。

SQL> select /*+ first_rows(20) */ * from t a where a.id>20; －－用返回结果的前20行的执行计划来运行查询sql的需求

Execution Plan

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

Plan hash value: 3772518221

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

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

| 0 | SELECT STATEMENT | | 20 | 360 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 360 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_PK | 80 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."ID">20)

用返回结果的前20行的执行计划来运行查询sql的需求，这种情况反应的执行计划是不真实的。除非查询的结果是小于20行的。

所以在应用中，为了保证查询和计划的匹配，一般都返回的结果是小于等于20行的。如果有需要，再临时用全部的数据生成执行计划的，但是查询全部数据的需求量小。

SQL> select /*+ rule */ * from t a;

Execution Plan

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

Plan hash value: 1601196873

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| T |

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

Note

-----

- rule based optimizer used (consider using cbo)

数据迁移完之后，尤其从9i迁移到10g，存在问题：优化器从RBO到CBO，但是因为RBO下很稳定，暂时用RBO，需要所有的SQL都加上这个提示。

数据迁移,10g到10g以上的版本，CBO－CBO，将性能数据用export方法导出到表，将表迁移，再目标库上用import方法导入。

二、和访问路径相关的hint

会直接影响SQL的执行计划，所以在使用的时候要格外小心，特别是代码中。

这种hint对于DBA来说，分析SQL性能的时候用处很大，DBA可以使用不同的SQL使用不同的hint得到不同的执行计划，通过比较不同的执行计划的效率来分析当前SQL语句的性能。比如，是否合适的创建了索引，是否合理的做了性能数据的收集。

1、full hint /*+ full(t) */

2、index hint ——如果所强制走索引可能导致错误的查询结果的时候，hint会被忽略。

3、强制不走索引。

SQL> select * from t a where a.id<20;

Execution Plan

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

Plan hash value: 3772518221

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

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

| 0 | SELECT STATEMENT | | 19 | 342 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 19 | 342 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_PK | 19 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."ID"<20)

SQL> select /*+ no_index(a t_pk)*/ * from t a where a.id<20; －－避开走索引会选择全表扫

Execution Plan

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 19 | 342 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 19 | 342 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("A"."ID"<20)

使用场合：

1、假如这个表上所有的SQL需求，大部分是走索引效率高的，个别SQL需要全表扫，此时，针对的个别的SQL可以用这个提示忽略索引。

2、CBO是自动选择的，随着系统的运行，某此分析完之后，由于数据的变化，性能数据不匹配了，来不及更新性能数据，执行计划就错了，需要临时加一个不用索引而让plan正确。

4、索引降序扫index desc hint

场合：查询的时候一般情况下查询最有可能的是近几天生成的数据，插入数据的时候，表的主键是ID值按照升序排列的，此时查询数据扫索引的时候，从大往小扫，很容易能扫到要的数据。

SQL> select /*+ index_desc(a t_pk)*/ * from t a where a.id<20;

Execution Plan

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

Plan hash value: 725076093

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

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

| 0 | SELECT STATEMENT | | 19 | 342 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 19 | 342 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN DESCENDING| T_PK | 19 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."ID"<20)

filter("A"."ID"<20)

SQL> select * from t a where a.id<20;

Execution Plan

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

Plan hash value: 3772518221

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

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

| 0 | SELECT STATEMENT | | 19 | 342 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 19 | 342 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_PK | 19 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."ID"<20)

5、index_combine HINT

告诉优化器要强制使用位图索引，当访问这个索引会使结果集不正确的时候，会忽略这个hint

SQL> select /*+ index_combine(t t_pk) */ * from t; 等同于 /*+ index(t t_pk)*/

Execution Plan

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

Plan hash value: 1399892806

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

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

| 0 | SELECT STATEMENT | | 99 | 1782 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 99 | 1782 | 2 (0)| 00:00:01 |

| 2 | INDEX FULL SCAN | T_PK | 99 | | 1 (0)| 00:00:01 |

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

SQL> select /*+ index(t t_pk) */ * from t;

Execution Plan

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

Plan hash value: 1399892806

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

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

| 0 | SELECT STATEMENT | | 99 | 1782 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 99 | 1782 | 2 (0)| 00:00:01 |

| 2 | INDEX FULL SCAN | T_PK | 99 | | 1 (0)| 00:00:01 |

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

SQL> select * from t;

Execution Plan

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 99 | 1782 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 99 | 1782 | 3 (0)| 00:00:01 |

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

创建位图索引

SQL> alter table t drop constraint t_pk;

Table altered.

SQL> create bitmap index bitind_id on t(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select /*+ index_combine(t bitind_id) */ * from t;

Execution Plan

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

Plan hash value: 48670168

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

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

| 0 | SELECT STATEMENT | | 99 | 1782 | 22 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 99 | 1782 | 22 (0)| 00:00:01 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |

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

SQL> select /*+ index(t bitind_id) */ * from t;

Execution Plan

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

Plan hash value: 48670168

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

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

| 0 | SELECT STATEMENT | | 99 | 1782 | 22 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 99 | 1782 | 22 (0)| 00:00:01 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |

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

结论：index和index_combine是等价的。

SQL> select count(1) from t where id<15; －－作count查询容易用到位图索引。

Execution Plan

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

Plan hash value: 408230478

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

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

| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

| 2 | BITMAP CONVERSION COUNT | | 14 | 42 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - filter("ID"<15)

6、index_ffs hint

index fast full scan 当访问这个索引会使结果集不正确的时候会忽略这个索引。

SQL> begin

2 for i in 100..100000 loop

3 insert into t values(i,'XXXX');

4 end loop;

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select id from t where id>99;

Execution Plan

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

Plan hash value: 1176281950

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

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

| 0 | SELECT STATEMENT | | 99902 | 487K| 48 (7)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| T_IND | 99902 | 487K| 48 (7)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID">99)

SQL> select id from t where id<=99;

Execution Plan

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

Plan hash value: 422821423

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

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

| 0 | SELECT STATEMENT | | 99 | 495 | 2 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| T_IND | 99 | 495 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("ID"<=99)

原理：

FULL INDEX SCAN——查找索引的root块、branch块、leaf块，然后从第一个leaf块开始，沿着双向链表，所读取的索引键值块是有顺序的，排序的。

fast full index scan——按照索引块在磁盘上的存储顺序来扫描的，包括位图块、root块、branch块都可能读到。读取完后索引是不排序的。每次读取16个块。

SQL> show parameter multi

NAME TYPE VALUE

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

db_file_multiblock_read_count integer 16 －－多块读方式下一次读取16个块。一般是2的n次方

同等条件下，ffs方式性能要优于full scan方式。

SQL> drop table t1;

Table dropped.

SQL> create table t1 as select * from all_objects where object_id is not null;

Table created.

SQL> create index t1_ind on t1(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select count(1) from t1;

Execution Plan

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

Plan hash value: 2264155217

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

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

| 0 | SELECT STATEMENT | | 1 | 23 (5)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| T1_IND | 40913 | 23 (5)| 00:00:01 |

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

SQL> select /*+ no_index_ffs(t1 t1_ind) */ count(1) from t1;

Execution Plan

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

Plan hash value: 3083331628

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

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

| 0 | SELECT STATEMENT | | 1 | 93 (2)| 00:00:02 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| T1_IND | 40913 | 93 (2)| 00:00:02 | －－说明ffs方式性能高于fs方式

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

SQL> drop table t1;

Table dropped.

SQL> create table t1 as select * from all_objects where object_id is not null and rownum<=100;

Table created.

SQL> alter table t1 add constraint t1_pk primary key(object_id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select object_id from t1;

Execution Plan

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

Plan hash value: 1683850221

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

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

| 0 | SELECT STATEMENT | | 100 | 400 | 1 (0)| 00:00:01 |

| 1 | INDEX FULL SCAN | T1_PK | 100 | 400 | 1 (0)| 00:00:01 |

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

SQL> select count(object_id) from t1;

Execution Plan

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

Plan hash value: 1213398864

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

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

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| T1_PK | 100 | 1 (0)| 00:00:01 |

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

SQL> select count(1) from t1;

Execution Plan

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

Plan hash value: 1213398864

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

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

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| T1_PK | 100 | 1 (0)| 00:00:01 |

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

总结：

1、对于数据量巨大的表，用ffs方式更快

2、对于数据量相对较小的表，用fs方式更快。

SQL> select /*+ index_ffs(t1 t1_pk) */ count(1) from t1;

Execution Plan

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

Plan hash value: 1018460547

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

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

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| T1_PK | 100 | 2 (0)| 00:00:01 |

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

7、index_join hint

索引关联，当谓词中引用的列上都有索引的时候，可以通过索引关联的方式访问数据。

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index t_ind on t(object_id);

Index created.

SQL> create bitmap index t_bm on t(object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select /*+ index_join(t t_ind t_bm) */ * from t where object_id<100 and object_name='DBMS_REGISTER';

no rows selected

8、索引跳跃扫 index_ss hint 关于联合索引的扫描方式

当在一个联合索引中，某些谓词条件不在联合索引第一列的时候，可通过索引跳跃扫的方式获得数据。当联合索引第一列重复率很高的时候，使用索引跳跃扫效率会比较高。

SQL> drop table t;

Table dropped.

SQL> create table t as select 1 id,object_name from dba_objects;

Table created.

SQL> insert into t select 2 id,object_name from dba_objects;

50602 rows created.

SQL> insert into t select 3 id,object_name from dba_objects;

50602 rows created.

SQL> insert into t select 4 id,object_name from dba_objects;

50602 rows created.

SQL> commit;

Commit complete.

SQL> create index ind_t on t(id,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> set autotrace trace exp

SQL> select * from t where object_name='TEST'; －－过滤条件落在了第二列上。

Execution Plan

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

Plan hash value: 3688940926

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

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

| 0 | SELECT STATEMENT | | 7 | 189 | 6 (0)| 00:00:01 |

|* 1 | INDEX SKIP SCAN | IND_T | 7 | 189 | 6 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("OBJECT_NAME"='TEST')

filter("OBJECT_NAME"='TEST')

SQL> select * from t where id=2; －－过滤条件放在第一列上用不到索引

Execution Plan

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 50602 | 1334K| 230 (4)| 00:00:03 |

|* 1 | TABLE ACCESS FULL| T | 50602 | 1334K| 230 (4)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID"=2)

如果第一列重复率不高，还用这种方法，是否能skip scan，不能的，但是能用到索引范围扫。

如果创建索引的时候，create index ind_t on t(object_name,id);怎样写SQL效率会高？－－将过滤条件落在第一列上。

SQL> drop index ind_t;

Index dropped.

SQL> create index ind_t on t(object_name,id); －－第一列用索引条件较好

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select * from t where object_name='TEST';

Execution Plan

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

Plan hash value: 3131770069

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

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

| 0 | SELECT STATEMENT | | 7 | 189 | 3 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IND_T | 7 | 189 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("OBJECT_NAME"='TEST')

SQL> select /*+ index_ss(t ind_t) */ * from t where object_name='TEST'; －－强制索引跳越扫

Execution Plan

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

Plan hash value: 3688940926

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

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

| 0 | SELECT STATEMENT | | 7 | 189 | 6 (0)| 00:00:01 |

|* 1 | INDEX SKIP SCAN | IND_T | 7 | 189 | 6 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("OBJECT_NAME"='TEST')

总结：

1、当创建联合索引的时候，如果其中的一列重复率低，且谓词条件也在这列上，考虑将这个列作为联合索引的第一列。用索引范围扫。

2、当创建联合索引的时候，谓词条件在某一列上，但是这个列用索引条件比较好，此时想用好索引，可以找另外一列重复率较高的列作为联合索引的第一列，谓词列落在联合索引的第二列上，此时用跳跃扫。

3、如果一列的重复率低，但是谓词不在这一列上，。

转载于:https://blog.51cto.com/chenhuican/1329179

weixin_33923762

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫