[20150705]使用CURSOR_SHARING_EXACT提示.txt
--生产系统有一条sql语句遇到性能问题,由于生产系统语句非常复杂,我拿测试用户scott的表作为例子来说明:
1.问题提出:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
语句如下:
SELECT *
FROM dept, emp
WHERE dept.deptno = EMP.DEPTNO
AND ( (0 = 0 AND dept.dname = 'SALES')
OR (1 = 0 AND dept.loc = ' ')
OR (1 = 0 AND emp.ename = ' ')
OR (1 = 0 AND emp.empno = 0)
OR (1 = 0 AND emp.hiredate = '0000/01/01')
OR (1 = 0 AND emp.sal = 0));
--我想大家看以上sql语句一定知道开发想实现的功能.这是另外一种风格的写法,真不知道开发为什么要这样写.
--通过控制前面的1=0或者0=0,来选择执行的路径.先来看看执行计划:
Plan hash value: 303751755
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 5 | 295 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_DEPT_DNAME | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_EMP_DEPTNO | 5 | | 0 (0)| |
| 6 | TABLE ACCESS BY INDEX ROWID | EMP | 5 | 195 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / DEPT@SEL$1
4 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
6 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DNAME"='SALES')
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
--注意看Predicate Information部分都忽略了.但是我们的程序存在着大量的文字变量(再次说明一下,对于一个OLTP系统,如果一个程序
--sql语句大量存在非绑定变量,可以将这种项目就是垃圾,因为这些已经在许多oracle优化的相关书籍上有介绍,开发不知实在不应该),也
--就是没有使用绑定变量,我在用户登录时已经将参数cursor_sharing设置为force,再看看看cursor_sharing = force的情况.
SCOTT@test01p> alter system flush shared_pool;
System altered.
SCOTT@test01p> alter session set cursor_sharing=force ;
Session altered.
Plan hash value: 4009592485
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| | | | |
| 1 | MERGE JOIN | | 1 | 59 | 5 (0)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |
|* 4 | FILTER | | | | | | | | |
|* 5 | SORT JOIN | | 14 | 546 | 3 (0)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 6 | TABLE ACCESS FULL | EMP | 14 | 546 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
6 - SEL$1 / EMP@SEL$1
Peeked Binds (identified by position):
--------------------------------------
15 - (VARCHAR2(30), CSID=852): '0000/01/01'
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(((:SYS_B_00=:SYS_B_01 AND "DEPT"."DNAME"=:SYS_B_02) OR (:SYS_B_03=:SYS_B_04 AND
"DEPT"."LOC"=:SYS_B_05) OR (:SYS_B_06=:SYS_B_07 AND "EMP"."ENAME"=:SYS_B_08) OR (:SYS_B_09=:SYS_B_10 AND
"EMP"."EMPNO"=:SYS_B_11) OR (:SYS_B_12=:SYS_B_13 AND "EMP"."HIREDATE"=:SYS_B_14) OR (:SYS_B_15=:SYS_B_16
AND "EMP"."SAL"=:SYS_B_17)))
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
--^_^,这回oracle傻眼了,不知道选择合理的执行计划.遇到这个问题最简单的方式就是开发修改代码加入提示CURSOR_SHARING_EXACT,这
--样sql语句不再做转换.保持原样,这种就可以选择合理的执行计划.加入提示/*+ CURSOR_SHARING_EXACT */看看:
--注意:我更换了查询条件.
SELECT /*+ CURSOR_SHARING_EXACT */*
FROM dept, emp
WHERE dept.deptno = EMP.DEPTNO
AND ( (1 = 0 AND dept.dname = ' ')
OR (1 = 0 AND dept.loc = ' ')
OR (1 = 0 AND emp.ename = ' ')
OR (0 = 0 AND emp.empno = 7499)
OR (1 = 0 AND emp.hiredate = '0000/01/01')
OR (1 = 0 AND emp.sal = 0));
Plan hash value: 2385808155
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS | | 1 | 59 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$1 / EMP@SEL$1
4 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=7499)
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
总结:
1.最好不要这样写.
2.如果这样写了,在使用cursor_sharing=force的情况下,可以加入提示/*+ CURSOR_SHARING_EXACT */.
3.另外我的测试不能使用sql打补丁的方式加入这种CURSOR_SHARING_EXACT提示.加入了无效.
