题目:Oracle的cursor_sharing参数告诉Oracle数据库何时重用SQL语句,有Exact、force和similar三种设置,请阐述三种设置的区别,并通过一个例子距离说明三者之间的区别。最后请设计一个实际应用场景,执行一系列操作,说明这个参数设置的意义(提示,可以设计一个循环操作语句来验证),分别说明在什么情况下用何种参数设置。
答:
Cursor_sharing参数有3个值可以设置:
1)、EXACT:通常来说,exact值是Oracle推荐的,也是默认的,它要求SQL语句在完全相同时才会重用,否则会被重新执行硬解析操作。
2)、SIMILAR:similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时,才会被重新分析,否则将重用SQL。
3)、FORCE:force是在任何情况下,无条件重用SQL。
比如执行三条sql语句分别为:
Select * from dba_objects where object_id=7000;
Select * from dba_objects where object_id=7001;
Select * from dba_objects where object_id=7002;
在参数为exact的情况下,数据库将执行3次硬解析,因为每一条语句并不是完全相同,所以每一条语句都会被硬解析一次。
而在参数为force的情况下,Oracle对输入的SQL值,会将where条件取值自动替换为绑定变量。以后在输入相同的结构SQL语句时,会进行cursor sharing共享游标。所以该情况下只会对第一条语句执行1次硬解析,然后对后面两条语句执行软解析。
而在本例的情况下,因为谓词条件并不会影响到执行计划,所以结果和force是一样的,oracle执行一次硬解析,两次软解析。
合适的cursor_sharing参数设置能够让数据库在执行相似的sql操作时显著提高效率,从而提高数据库的性能。下面是不同参数设置下的oracle数据库执行1000条相似的select语句所花时间的对比。
测试方法:
让数据库在不同的参数设置下执行一个相同的sql脚本,观察执行完成所需要的时间。
测试使用的是oracle12 c数据库 用sys用户登入,在cdb$root中查询dba_objects表中的一千条纪录。
Sql脚本如下:
alter system flush shared_pool;//清空shared_pool
alter system flush buffer_cache;//清空buffer_cache
alter session set cursor_sharin