SQL走错索引

由于各种各样的原因导致SQL走错了索引，如果是统计信息问题那么重新收集统计信息，如果是索引失效，那么重建索引，使用outline/profile固定执行计划等等，本文介绍使用HINT及手工调整索引的cluster factor值快速调整SQL走的索引：

首先验证索引的状态统计信息，以及哪个索引的选择性好：
[code] Number Empty Chain Average Global Sample Date
TABLE_NAME of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
A_BIND_TEST 1,002,017 31,71 0 0 17 YES 60,409 03-29-2016
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID 1,000,010 .00000100 1 0 60,409 03-29-2016
NAME 3 .33333333 1 0 6,189 03-29-2016
AGE 1 1.00000000 1 0 6,189 03-29-2016
ADDR 2 .50000000 1 0 6,189 03-29-2016
SQL>
Index Leaf Distinct Number AV Av Cluster Date
Name BLV Blks Keys of Rows LEA Data Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
IND_ABT_ID_AGE 2 2,652 999,023 1,001,220 1 1 3,194 03-29-2016
IND_ABT_ID_ADDR 2 3,352 999,023 1,001,220 1 1 3,196 03-29-2016
SQL>
Index Column Col Column
Name Name Pos Details
------------------------------ ------------------------------ ---- ------------------------
IND_ABT_ID_ADDR ID 1 NUMBER(22)
IND_ABT_ID_ADDR ADDR 2 VARCHAR2(30)
IND_ABT_ID_AGE ID 1 NUMBER(22)
IND_ABT_ID_AGE AGE 2 NUMBER(22)

select * from A_BIND_TEST a where id=100 and age=38 and addr='BeiJing';

SQL> select id,addr,count(*) from A_BIND_TEST where id=100 and age=38 and addr='BeiJing' group by id,addr;

ID ADDR COUNT(*)
---------- ------------------------------ ----------
100 BeiJing 100

SQL> select id,age,count(*) from A_BIND_TEST where id=100 and age=38 group by id,age;

ID AGE COUNT(*)
---------- ---------- ----------
100 38 2189[/code]
从上面可以看出，根据IND_ABT_ID_ADDR复合索引字段筛选出来的结果集明显要小很多，所以SQL应该走IND_ABT_ID_ADDR索引，通过使用HINT指定索引进一步验证：
[code]select /*+ index(a IND_ABT_ID_AGE) */* from A_BIND_TEST a where id=100 and age=38 and addr='BeiJing';
select /*+ index(a IND_ABT_ID_ADDR) */* from A_BIND_TEST a where id=100 and age=38 and addr='BeiJing';

SQL> select /*+ index(a IND_ABT_ID_AGE) */* from A_BIND_TEST where id=100 and age=38 and addr='BeiJing';

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2733777954

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| A_BIND_TEST | 1 | 17 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ABT_ID_AGE | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ADDR"='BeiJing')
2 - access("ID"=100 AND "AGE"=38)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
2362 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed

SQL>
SQL> select /*+ index(a IND_ABT_ID_ADDR) */* from A_BIND_TEST a where id=100 and age=38 and addr='BeiJing';

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 397368058

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| A_BIND_TEST | 1 | 17 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ABT_ID_ADDR | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("AGE"=38)
2 - access("ID"=100 AND "ADDR"='BeiJing')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
2362 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed[/code]
从上面可以看出执行统计信息看到，的确走IND_ABT_ID_ADDR索引的逻辑读要低一半，当然这种情况需要去改业务SQL，大多数情况下不允许临时调整业务代码。

还可以手工通过调整索引cluster factor的值，然后将内存中SQL的执行计划置为失效，重新解析生成正确的执行计划。
索引扫描COST的计算公式如下：
[code]B*TREE INDEX RANGE SCAN (IRS)

(Workload statistics are gathered)
(CPU Costing is enabled)

IRS Cost = I/O Cost + CPU Cost

Note:
I/O Cost = Index Access I/O Cost + Table Access I/O Cost
Index Access I/O Cost = LVLS + CEIL(#LB * ix_sel)
Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filters)

CPU Cost = ROUND(#CPUCycles/cpuspeed/1000/sreadtim)[/code]
其中CLUF即为cluster factor的值，其实可以手工调整公式中的任意值来达到目的，！另外需要注意的是调整之后需要确认是否影响到其他SQL！。
简单测试一下通过调整CLUF值的方法，创建测试表和索引：
[code]SQL> select * from v$version
2 ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

create table t_clstfct_test as select * from dba_objects;
create index IND_TCT_OID on t_clstfct_test(object_id);
create index IND_TCT_ONAME on t_clstfct_test(object_name);

Index Leaf Distinct Number AV Av Cluster Date
Name BLV Blks Keys of Rows LEA Data Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
IND_TCT_OID 1 154 69,547 69,547 1 1 1,772 03-30-2016
IND_TCT_ONAME 2 339 38,504 69,547 1 1 37,834 03-30-2016[/code]
从上面可以看出，IND_TCT_OID索引的CLUF值比IND_TCT_ONAME索引的CLUF值要低很多，再默认情况下IND_TCT_OID索引的选择性会高一些，也就是在两个字段同事出现在where条件后会走IND_TCT_OID索引：
[code]SQL> select * from table(dbms_xplan.display_cursor('','','advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9mnapsj9sjhyb, child number 1
-------------------------------------
select count(*) from t_clstfct_test where OBJECT_ID=100 and OBJECT_NAME='XXXX'

Plan hash value: 379601504

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 79 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T_CLSTFCT_TEST | 6 | 474 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_TCT_OID | 11 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T_CLSTFCT_TEST@SEL$1
3 - SEL$1 / T_CLSTFCT_TEST@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_CLSTFCT_TEST"@"SEL$1" ("T_CLSTFCT_TEST"."OBJECT_ID"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_NAME"='XXXX')
3 - access("OBJECT_ID"=100)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]
3 - "T_CLSTFCT_TEST".ROWID[ROWID,10]

Note
-----
- dynamic sampling used for this statement

51 rows selected.[/code]
接下来我们通过手工设置两个索引的CLUF值，并通过赋权的操作让内存中的执行计划失效：
[code]exec DBMS_STATS.SET_INDEX_STATS('AWEN','IND_TCT_OID',clstfct=>100000);
exec DBMS_STATS.SET_INDEX_STATS('AWEN','IND_TCT_ONAME',clstfct=>100);

Index Leaf Distinct Number AV Av Cluster Date
Name BLV Blks Keys of Rows LEA Data Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
IND_TCT_OID 1 154 69,547 69,547 1 1 100,000 03-30-2016
IND_TCT_ONAME 2 339 38,504 69,547 1 1 100 03-30-2016

SQL> GRANT SELECT on awen.T_CLSTFCT_TEST to system;

Grant succeeded.[/code]
再次运行SQL，发现SQL已经走了IND_TCT_ONAME索引：
[code]SQL> select count(*) from t_clstfct_test where OBJECT_ID=100 and OBJECT_NAME='XXXX';

COUNT(*)
----------
0

SQL> select * from table(dbms_xplan.display_cursor('','','advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8wnqf3d192ram, child number 0
-------------------------------------
select count(*) from t_clstfct_test where OBJECT_ID=100 and OBJECT_NAME='XXXX'

Plan hash value: 2253870163

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 79 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T_CLSTFCT_TEST | 6 | 474 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_TCT_ONAME | 11 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T_CLSTFCT_TEST@SEL$1
3 - SEL$1 / T_CLSTFCT_TEST@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_CLSTFCT_TEST"@"SEL$1" ("T_CLSTFCT_TEST"."OBJECT_NAME"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"=100)
3 - access("OBJECT_NAME"='XXXX')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]
3 - "T_CLSTFCT_TEST".ROWID[ROWID,10]

Note
-----
- dynamic sampling used for this statement

51 rows selected.[/code]
这里需要注意的是，再调整完之后EXPLAIN的执行计划变了，但是实际的执行计划缺没变，这是因为SQL沿用了内存中的执行计划，直到下次SQL重新硬解析才有可能改变执行计划。关于让SQL重新解析的方法有很多，也就是将SQL对应共享词中的share cursor置为失效，常见的方法有如下几种：
1、收集统计信息时指定no_invalidate=false
no_invalidate=false会让内存中的执行计划失效，重新解析：
[code]exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'AWEN',TABNAME => 'A_BIND_TEST',DEGREE => 5, CASCADE => TRUE,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'for all columns size 1',no_invalidate=>false);[/code]
2、对象上执行DDL
常见的就是赋权和添加注释，这种方法最快
[code]grant select on table to system;

comment on table table_name is 'tab_comments';
comment on column table.column_name is 'col_comments';

select * from dba_table_coments where table_name='TABLE_NAME';[/code]
3、直接使用dbms_shared_pool.purge清除内存中的执行计划
[code]select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where sql_id in ('0nxb3h8d3n3jp')--('awcbdcc4hdxj5', '5k0hn1ku734dg');

exec dbms_shared_pool.purge('00000026F954FD78,2001276846', 'c');[/code]