ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131025]一条sql语句的优化.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-10-28 08:42:29 0 删除 编辑
[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2455
  • 访问量
    6259300