SQL语句是一种描述性的语言。Oracle接受一个SQL之后,要进行一系列的解析parse操作,最终生成真正操作的步骤——执行计划。Oracle对一个SQL进行第一次硬解析(hard parse)之后,就将执行计划缓存在SGA的library cache中。
进行执行计划缓存的目的就是进行执行计划共享,这样期望在下次输入相同SQL的时候能够共享该计划,减少由于硬解析带来的Library Cache Lock和Library Cache Pin。但是实际中,真正实现SQL语句执行计划共享是一件很复杂的事情,要满足很多现实条件。本系列将从SQL语句游标共享的角度,讨论几种SQL游标不能共享的场景。
1、 环境准备
首先,我们准备一个简单的实验环境。
SQL> 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 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
我们选择Oracle 10gR2的环境。同时,在sys的用户schema下,存在一个数据表t。我们本系列中的大部分实验都是围绕这个数据表进行的。
2、父子游标的cursor sharing
为了增加本系列的可理解性。在本部分中稍稍介绍Oracle父子游标机制和共享机制。
ü 当Oracle接受到一个全新的SQL时,要进行硬解析hard parse,形成执行计划。执行计划对应的shared cursor本质上就是SGA上的一个地址区域。保存执行计划是以父游标parent cursor和子游标child cursor两部分进行保存;
ü 父游标parent cursor对应的就是SQL语句字面相同的SQL与之对应。对每一个父游标而言,Oracle都会分配一个系统唯一的sql_id与之对应。新SQL进行shared pool寻找可共享的shared cursor的时候,首先定位到的就是父游标parent cursor;
ü 子游标child cursor是绑定在父游标上形成多对一关系的执行计划。由于优化器模式等原因,一个父游标会对应多个子游标。每个子游标中携带的才是真正的执行计划;
ü 在Oracle内部视图上,v$sqlarea视图表示的是父游标信息,v$sql视图中表示的是子游标信息;
3、 SQL字面值差异
两个SQL如果语义相同,是否可以实现游标共享吗?首先,我们执行两个SQL语句。
SQL> select /*+ demo_1 */count(*) from t;
COUNT(*)
----------
18015
SQL> select /*+ demo_1 */count(*) from T;
COUNT(*)
----------
18015
两个SQL语句的差异只是在大小写拼写上。我们观察父子游标情况。
SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address from v$sqlarea where sql_text like 'select /*+ demo_1 */%';
SQL_TEXT SQL_ID OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS
----------------------------------- ------------- -------------- ---------- --------------- --------
select /*+ demo_1 */count(*) from t 1vp29y1g9zjs2 ALL_ROWS 1587529474 2966233522 6967139C
select /*+ demo_1 */count(*) from T 2kjp0bmmn7jsg ALL_ROWS 3879978767 2966233522 69670434
从结果看,Oracle为两个略微差异的SQL语句生成了不同的父游标。在v$sqlarea中,有两行记录与之对应。但是,我们注意到两个父游标对应的plan_hash_value值相同,说明虽然两个游标没有实现共享,但是游标生成的执行计划内容上是相同的。
结论:在Oracle中,要实现游标共享的前提条件,就是输入SQL的字面值100%相同,不允许有任何包括大小写的差异。
4、 SQL指定对象相同(1)
SQL字面相同,那么SQL语句一定可能共享吗?我们说,不同用户schema下对应相同的名称。这样就可能出现相同的SQL语句通过不同的用户发出,对应不同对象的情况。下面我们进行试验。
SQL> col owner for a10;
SQL> col object_name for a20;
SQL> select owner, object_name, object_id from dba_objects where object_name='T';
OWNER OBJECT_NAME OBJECT_ID
---------- -------------------- ----------
SYS T 54216
SCOTT T 54136
sys用户和scott用户下,存在两个相同名称T的对象。但是本质上肯定是两个对象。
//当前用户为sys
SQL> show user;
User is "SYS"
SQL> select /*+ demo_2 */ count(*) from t;
COUNT(*)
----------
18015
//切换到scott用户
SQL> conn scott/tiger@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> select /*+ demo_2 */ count(*) from t;
COUNT(*)
----------
0
两个SQL字面完全一样,此时作为相同的SQL语句,在Oracle library cache中是如何呢?
SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_2 */%';
SQL_TEXT SQL_ID OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS EXECUTIONS VERSION_COUNT
----------------------------------- ------------- -------------- ---------- --------------- -------- ---------- -------------
select /*+ demo_2 */ count(*) from 6d100h31dw99u ALL_ROWS 3269338426 2966233522 69654C70 2 2
t
我们在父游标中只看到了一个父游标信息,同时executions为2表示该SQL被执行过两次,同实际相同。而version_count为2说明两次执行,虽然可以共享父游标,但是在子游标层面上,变为两个子游标,进而是两个执行计划。
SQL> select sql_id, PARSING_SCHEMA_NAME, child_number from v$sql where sql_id='6d100h31dw99u';
SQL_ID PARSING_SCHEMA_NAME CHILD_NUMBER
------------- ------------------------------ ------------
6d100h31dw99u SYS 0
6d100h31dw99u SCOTT 1
结论:两个schema下发出的SQL,如果环境相同,SQL字面值相同,在shared pool中共享父游标,对应不同的子游标。执行计划不能共享。
此时,如果我们进一步想,如果两个SQL字面值相同,而且对应相同的对象,会怎么样呢?
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-703624/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-703624/