【cursor_sharing】cursor_sharing参数设置exact,similar,force的区别
Oracle中为了提高sql的执行效率,需要减少硬解析,实现shared cursor共享,最常见的方法是使用绑定变量,但很多时候由于各种原因
Oracle中为了提高sql的执行效率,需要减少硬解析,实现shared cursor共享,最常见的方法是使用绑定变量,但很多时候由于各种原因未能在开发初期使用绑定变量,对于减少硬解析的目的,退而求其次地方法是设置cursor_sharing.
1.准备环境
实验环境
BALLON@PROD> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
使用脚本插入数据后:
BALLONTT@PROD> desc t;
Name Null? Type
----------------------- -------- ----------------
ID VARCHAR2(5)
NAME NUMBER(38)
BALLONTT@PROD> select id,count(*) from t group by id;
ID COUNT(*)
----- ----------
d 6
a 10000
b 20000
c 20
BALLONTT@PROD> create index ind_id on t(id);
Index created.
2.取值为exact时(默认):
BALLONTT@PROD> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
cursor_sharing string EXACT
BALLONTT@PROD> select count(*) from t where;
COUNT(*)
----------
20000
BALLONTT@PROD> select count(*) from t where;
COUNT(*)
----------
6
BALLONTT@PROD> select sql_text,sql_id,version_count,executions from v$sqlarea
where sql_text like 'select count(*) from t where id=%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ------------- ----------
select count(*) from t where b0gfs7u9r55rv 1 1
select count(*) from t where fqurbumy7bsg6 1 1
可以看到两条查询语句没有使用绑定变量,有各自对应的sql_id,子游标数均为1个。两个sql查询没有任何关系。
查看两次sql的执行计划:
BALLONTT@PROD>select * from table(dbms_xplan.
display_cursor('b0gfs7u9r55rv',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID b0gfs7u9r55rv, child number 0
-------------------------------------
select count(*) from t where
Plan hash value: 3666266488
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IND_ID | 14 | 28 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
第一次查询利用了索引。
BALLONTT@PROD> select * from table(dbms_xplan.display_cursor('fqurbumy7bsg6',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fqurbumy7bsg6, child number 0
-------------------------------------
select count(*) from t where
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T | 19783 | 39566 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
第二次查询利用了全表扫描
我们在更直观地来看一下两次sql查询后的硬解析统计情况:
BALLONTT@PROD> select count(*) from t where;
COUNT(*)
----------
10000
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 2133
parse time elapsed 4463
parse count (total) 54889
parse count (hard) 6579(硬解析数目)
parse count (failures) 52
BALLONTT@PROD> select count(*) from t where;
COUNT(*)
----------
20
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 2134
parse time elapsed 4464
parse count (total) 54895
parse count (hard) 6580(硬解析数目)
parse count (failures) 52
硬解析数目再次加1
因为数据的巨大差异性,,导致了对两次查询有不同的执行计划,这也说明在cursor设置为exact时,两条sql语句如果存在一点不同,就不会共享cursor,而进行两次硬解析。
3.设置为force时
Oracle对输入的SQL值,会将where条件取值自动替为绑定变量。以后在输入相同的结构SQL语句时,会进行cursor sharing共享游标
BALLONTT@PROD> alter system set cursor_sharing=force;
System altered.
BALLONTT@PROD> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------- ------------ -------
cursor_sharing string FORCE
清除一下share pool中已缓存的cursor
BALLONTT@PROD> alter system flush shared_pool;
System altered.
查看硬解析情况:
BALLONTT@PROD> select count(*) from t where;
COUNT(*)
----------
20000
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
------------------------------------- ----------
parse time cpu 2163
parse time elapsed 4506
parse count (total) 55097
parse count (hard) 6668
parse count (failures) 52
BALLONTT@PROD> select count(*) from t where;
COUNT(*)
----------
6
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
----------------------------------------- ----------
parse time cpu 2164
parse time elapsed 4507
parse count (total) 55101
parse count (hard) 6669
parse count (failures) 52
硬解析加1了,这不应该呀!!
BALLONTT@PROD> select sql_text,child_number from v$sql where sql_text like 'select count(*) from t where id%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
select count(*) from t where 0
select count(*) from t where 0
可以看到并没有使用绑定变量,force的设置没有生效。
解决办法:应在设置cursor_sharing前,执行两次下面语句:
alter system flush shared_pool;
BALLONTT@PROD> alter system flush shared_pool;
System altered.
BALLONTT@PROD> alter system flush shared_pool;
System altered.
BALLONTT@PROD> alter system set cursor_sharing=force;
System altered.
设置好了,接着进行sql测试
BALLONTT@PROD> select count(*) from t where;
COUNT(*)
----------
6
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
-------------------------------------- ----------
parse time cpu 2216
parse time elapsed 4572
parse count (total) 55867
parse count (hard) 6910
parse count (failures) 55
BALLONTT@PROD> select count(*) from t where;
COUNT(*)
----------
20000
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
--------------------------------------- ----------
parse time cpu 2216
parse time elapsed 4572
parse count (total) 55869
parse count (hard) 6910
parse count (failures) 55
硬解析的次数没有发生变化
BALLONTT@PROD> select sql_text,sql_id,version_count,executions from v$sqlarea
2 where sql_text like 'select count(*) from t where%';
SQL_TEXT SQL_ID VERSION_COUN EXECUTIONS
---------------------------------------------- ------------- ------------- -------------
select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 1 2
可以看到两次查询使用了绑定变量,将谓词的值用sys_B_0代替。该语句执行了两次,有一个child cursor(子游标)。
在来看一下两次查询语句的执行计划:
BALLONTT@PROD> select * from table(dbms_xplan.display_cursor('g82ztj8p3q174',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID g82ztj8p3q174, child number 0
-------------------------------------
select count(*) from t where id=:"SYS_B_0"
Plan hash value: 3666266488
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IND_ID | 6 | 24 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
两次的查询使用了同一个执行计划:索引扫描。这就是force的设置。
对与参数cusor_sharing设置为force时,根据实验,我们可以得出下列结论:
上一篇: static变量与方法
下一篇: 请教这个php程式为什么会出错