[20131025]一條sql語句的優化.txt

[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 <=1e4;
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)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-775123/，如需轉載，請註明出處，否則將追究法律責任。