[20131025]一条sql语句的优化.txt

最近在优化一条sql语句，做一个测试例子测试看看。遇到一些问题记录一下:

1.建立环境：

SCOTT@test> @ver

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t (id1 number,id2 number,name varchar2(100) ) pctfree 99;
alter table t modify(id1  not null);
alter table t modify(id2  not null);

insert into t select rownum id,1e4-rownum+1 id2 ,lpad('x',100,'x') from dual connect by level commit ;
--这样建立了一张大表，记录10000行.注意这张表id1,id2没有等于0的记录。

create index i_t_id1 on t(id1);
create index i_t_id2 on t(id2);
exec dbms_stats.gather_table_stats(user,'T',cascade=>TRUE);

2.测试：
--注意：生产环境cursor_sharing=force。
--模拟语句如下：程序没有使用绑定变量，直接带数值的。

select * from t where (id1=440 or 440=0) and (id2=0 or 0=0);
select * from t where (id1=0 or 0=0) and (id2=9561 or 9561=0);
select * from t where (id1=440 or 440=0) and (id2=9561 or 9561=0);

--如果使用绑定变量，语句如下：
select * from t where (id1=:v1 or :v1=0) and (id2=:v2 or :v2=0);

-- 可以看出开发的本意是程序既可以查询id1字段也可以查询id2字段，一个很牛逼的设计!
-- 初始带入的参数都是0。

3.在cursor_sharing=exact的情况下：

SCOTT@test> select * from t where (id1=440 or 440=0) and (id2=0 or 0=0);

       ID1        ID2 NAME

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

       440       9561 xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

SCOTT@test> @dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID  627f4u46pmh2a, child number 0

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

select * from t where (id1=440 or 440=0) and (id2=0 or 0=0)

Plan hash value: 1183254286

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

| Id  | Operation                   | Name    | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |         |        |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T       |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_ID1 |      1 |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - access("ID1"=440)

SCOTT@test> select * from t where (id1=0 or 0=0) and (id2=9561 or 9561=0);

       ID1        ID2 NAME

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

       440       9561 xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

SCOTT@test> @dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID  avvjxv0a4dvs5, child number 0

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

select * from t where (id1=0 or 0=0) and (id2=9561 or 9561=0)

Plan hash value: 3974417878

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

| Id  | Operation                   | Name    | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |         |        |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T       |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_ID2 |      1 |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - access("ID2"=9561)

--可以发现cursor_sharing=exact的情况下，oracle知道0=0为真9561=0为假，这样 (id1=0 or 0=0) and (id2=9561 or 9561=0)
--实际变为id2=9561，这样的情况下可以使用i_t_id2索引。
--看看执行计划2 - access("ID2"=9561)就很容易明白。

4.在cursor_sharing=force的情况下：

SCOTT@test> select * from t where (id1=430 or 430=0) and (id2=0 or 0=0);

       ID1        ID2 NAME

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

       430       9571 xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

--注意我换了1个值，440=>430.原来的语句还是会使用索引。

SCOTT@test> @dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID  5ff9cdq6bmvgt, child number 1

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

select * from t where (id1=:"SYS_B_0" or :"SYS_B_1"=:"SYS_B_2") and

(id2=:"SYS_B_3" or :"SYS_B_4"=:"SYS_B_5")

Plan hash value: 1601196873

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

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |      |        |  2740 (100)|

|*  1 |  TABLE ACCESS FULL| T    |      1 |  2740   (1)|

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

Predicate Information (identified by operation id):

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

   1 - filter(((:SYS_B_1=:SYS_B_2 OR "ID1"=:SYS_B_0) AND

              (:SYS_B_4=:SYS_B_5 OR "ID2"=:SYS_B_3)))

--换成这样，oracle就瞎眼了，不知道里面的内在逻辑，仅仅选择全表扫描。


使用提示呢？

SCOTT@test> select /*+ index(t i_t_id1) */ * from t where (id1=430 or 430=0) and (id2=0 or 0=0);

       ID1        ID2 NAME

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

       430       9571 xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

SCOTT@test> @dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID  18buqaphf9pzt, child number 0

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

select /*+ index(t i_t_id1) */ * from t where (id1=:"SYS_B_0" or

:"SYS_B_1"=:"SYS_B_2") and (id2=:"SYS_B_3" or :"SYS_B_4"=:"SYS_B_5")

Plan hash value: 2057376682

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

| Id  | Operation                   | Name    | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |         |        | 10026 (100)|

|*  1 |  TABLE ACCESS BY INDEX ROWID| T       |      1 | 10026   (1)|

|   2 |   INDEX FULL SCAN           | I_T_ID1 |  10000 |    22   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter(((:SYS_B_1=:SYS_B_2 OR "ID1"=:SYS_B_0) AND

              (:SYS_B_4=:SYS_B_5 OR "ID2"=:SYS_B_3)))

--这样做INDEX FULL SCAN 扫描，效率不如全表扫描。

5.解决方法：
1.修改参数cursor_sharing=exact，这样可以彻底解决这样，实际上这样的类似的语句有许多处。
--补充1点，我个人认为如果现在开发的OLTP系统再没有使用并且合理的使用绑定变量，这样的项目可以讲就是一个豆腐渣工程！
--oracle相关优化的书籍对这个问题多次提到。不要简单以为修改参数cursor_sharing=force可以解决这个问题。

2.当然是修改程序代码。程序中不要出现or，并且改成绑定变量。

3.实际上还有一种方法就是加入提示 /*+ CURSOR_SHARING_EXACT */ ，这样就不会转换里面的常量数值，从而解决这个问题。

SCOTT@test> show parameter cursor_sharing

NAME                                 TYPE                                     VALUE

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

cursor_sharing                       string                                   FORCE

SCOTT@test> select /*+ CURSOR_SHARING_EXACT */ * from t where (id1=450 or 450=0) and (id2=0 or 0=0);

       ID1        ID2 NAME

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

       450       9551 xxxxxxxxxxxxxxxxxxxx

                      xxxxxxxxxxxxxxxxxxxx

SCOTT@test> @dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID  058w6jcj7twzr, child number 0

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

select /*+ CURSOR_SHARING_EXACT */ * from t where (id1=450 or 450=0)

and (id2=0 or 0=0)

Plan hash value: 1183254286

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

| Id  | Operation                   | Name    | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |         |        |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T       |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_ID1 |      1 |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - access("ID1"=450)