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/
1328
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
