Oracle DBA 应知应会 -- CURSOR共享

CURSOR共享是共享池的重要功能之一,通过CURSOR共享，可以提高共享池的使用效率，减少SQL解析的开销，从总体上提高SQL执行的效率。如果一个SQL能够被解析一次，执行多次，那么就可以达到比较好的效果，减少分析的开销。这是OracleSQL共享的最高目标。
要实现CURSOR共享，首先要具备一定的机制，也就是说CURSOR的一些执行结构不能存放在程序的私有空间里，而需要存放在共享内存中。共享池中的Library Cache就是实现这种共享机制的载体。一个可共享的CURSOR，其可共享的部件是存放在LIBRARY CACHE中的，这样就实现了不同SESSION可以共享同一个SQL。
满足了存放在共享内存中的条件后，下一步就是要来判断哪些SQL是可以共享的。最简单的方法就是SQL语句完全相同的SQL是可以共享的，如何来判断SQL完全相同呢？如果对该SQL的语义语法进行全面解析，通过最终分解出的TOKEN来进行比较是最好的，能够对SQL进行全面的识别，但是这种识别方式的开销很大，Oracle采取了一个十分巧妙的方法来分辨不同的SQL。就是通过对SQL的文本进行计算，生成一个HASHVALUE，如果HASHVALUE不同，那么SQL肯定不同，如果HASHVALUE相同，就可能是可以共享的SQL。这种机制的实现十分简单，比较相同的SQL的开销也十分小。不过这种机制带来的问题是对SQL的书写要求较高，对于大小写，空格等有严格的书写要求，否则虽然语法语义完全相同的SQL，Oracle也会认为是不同的SQL。
基于上述原理，Oracle判断CURSOR共享的第一个原则是，可共享的CURSOR的SQL文本必须完全相同。一个CURSOR在执行前，首先对CURSOR的文本计算HASHVALUE，通过HASHVALUE在HASH BUCKET上找到相同的CURSOR，如果找到了相同的CURSOR，而且该CURSOR的所有的对象（包括SUBHEAP）都是VALID的，那么这个CURSOR在执行的时候，可以使用共享的SQL。如果某些对象已经被AGEOUT，那么这个CURSOR就需要进行软分析，将丢失的部分补充完整才能执行。
如果两条SQL的文本完全相同，是不是就一定能够共享呢？答案是否定的。比如有SCOTT和TIGER这两个SCHEMA，下面都有表的名字叫test.如果在这两个用户下都执行select 1 from tt where rownum<2 ,这两个SQL访问的表是不同的，因此这两个SQL是不应该共享的。Oracle在这种情况下是怎么处理的呢？首先，由于SQL文本完全相同，所以这2个SQL具有相同的SQL_ID和hashvalue，被认为是相同的SQL ,在v$sqlarea下可以看到如下的结果：
SQL_ID ADDRESS SQL_TEXT VERSION_COUNT
cpjnybv7021rv 1F7059E0 select 1 from tt where rownum<2 2
我们可以看到这条SQL的version count是2，也就是存在两个子CURSOR。接下来我们在v$sql中看到如下结果：

SQL_ID ADDRESS SQL_TEXT

cpjnybv7021rv 1F7059E0 select 1 from tt where rownum<2
cpjnybv7021rv 1F7059E0 select 1 from tt where rownum<2
可以看出，这2个SQL，被认为是同一个SQL，但是cpjnybv7021rv 包含2个子cursor。为什么会产生2个子cursor呢，通过v$sql_shared_cursor可以看到：
SQL> select sql_id,address,child_address,child_number,translation_mismatch from
2 v$sql_shared_cursor where sql_id='cpjnybv7021rv';
SQL_ID ADDRESS CHILD_AD CHILD_NUMBER T

cpjnybv7021rv 1F7059E0 231974B8 0 N
cpjnybv7021rv 1F7059E0 232BC270 1 Y
我们看到，第一个子CURSOR在这个视图中的所有mismatch都是N，第二个SQL由于translation_mismatch而导致不能共享，其原因是在做translation的时候发现相关的OBJECT不同。通过LIBRARY CACHE DUMP看(alter system set events 'immediate trace name library_cache level 10';)：
BUCKET 67323:
LIBRARY OBJECT HANDLE: handle=1f7059e0 mutex=1F705A94(2)
name=select 1 from tt where rownum<2
hash=1cd693ce0a964189cac69e5ece0106fb timestamp=12-11-2007 16:18:05
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
kkkk-dddd-llll=0001-0001-0001 lock=0 pin=0 latch#=3 hpc=0000 hlc=0000
lwt=1F705A3C[1F705A3C,1F705A3C] ltm=1F705A44[1F705A44,1F705A44]
pwt=1F705A20[1F705A20,1F705A20] ptm=1F705A28[1F705A28,1F705A28]
ref=1F705A5C[1F705A5C,1F705A5C] lnd=1F705A68[232C803C,1F705164]
DEPENDENCY REFERENCES:
reference latch flags

206b0c68 0 [60]
20642c5c 0 [60]
LIBRARY OBJECT: object=206b138c
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle

0 206b1318 206b0fcc 231974b8
1 206b1318 206b112c 232bc270
DATA BLOCKS:
data# heap pointer status pins change whr

0 2316d4bc 206b1424 I/P/A/-/- 0 NONE 00
BUCKET 67323 total object count=1
从TRACE上我们可以看出，这个PARENET CURSOR的文本就是select 1 from tt where rownum<2，这个CURSOR包含了2个子CURSOR，其中一个子CURSOR的HANDLE地址是231974B8,我们来看看这个子CURSOR：
LIBRARY OBJECT HANDLE: handle=231974b8 mutex=2319756C(0)
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=3 hpc=0000 hlc=0000
lwt=23197514[23197514,23197514] ltm=2319751C[2319751C,2319751C]
pwt=231974F8[231974F8,231974F8] ptm=23197500[23197500,23197500]
ref=23197534[206B0FCC,206B0FCC] lnd=23197540[23197540,23197540]
CHILD REFERENCES:
reference latch flags

206b0fcc 0 CHL[02]
LIBRARY OBJECT: object=206b0b2c
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags

0 20759070 20758d70 230c63b8 14 DEP[01]
READ ONLY DEPENDENCIES: count=1 size=16
dependency# table reference handle flags

0 206b0ee8 206b0c68 1f7059e0 /ROD/KPP[60]
ACCESSES: count=1 size=16
dependency# types

0 0009
TRANSLATIONS: count=1 size=16
original final

230c63b8 230c63b8
DATA BLOCKS:
data# heap pointer status pins change whr

0 23202038 206b0c7c I/-/A/-/- 0 NONE 00
6 20758ca4 201a185c I/-/A/-/- 0 NONE 00
这个子CURSOR的依赖关系列表中有一个对象的HANDLE是230c63b8，我们再来看看230c63b8对应的对象到底是什么：
BUCKET 99929:
LIBRARY OBJECT HANDLE: handle=230c63b8 mutex=230C646C(0)
name=SCOTT.TT
hash=dcfddf221c9799c3b07c3d16af4b8659 timestamp=12-11-2007 16:16:45
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0701 lock=N pin=0 latch#=1 hpc=0002 hlc=0002
lwt=230C6414[230C6414,230C6414] ltm=230C641C[230C641C,230C641C]
pwt=230C63F8[230C63F8,230C63F8] ptm=230C6400[230C6400,230C6400]
ref=230C6434[230C6434,230C6434] lnd=230C6440[232B9660,1F6575D8]
DEPENDENCY REFERENCES:
reference latch flags

20478ce0 2 DEP[01]
20758d70 0 DEP[01]
LOCK OWNERS:
lock user session count mode flags

20f9e5e4 2373238c 23733654 0 N [4000]
20fc86cc 2373238c 2373238c 0 N [4000]
LIBRARY OBJECT: object=20758884
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr

0 2324da64 2075891c I/-/A/-/- 0 NONE 00
8 20758aac 206b7298 I/-/A/-/- 0 NONE 00
9 20758b44 204cf62c I/-/A/-/- 0 NONE 00
10 20758b94 2073e4e0 I/-/A/-/- 0 NONE 00
BUCKET 99929 total object count=1
从name上我们看到这个对象是表SCOTT.TT，再来看看第二个子CURSOR：232bc270的信息：
LIBRARY OBJECT HANDLE: handle=232bc270 mutex=232BC324(0)
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=3 hpc=0000 hlc=0000
lwt=232BC2CC[232BC2CC,232BC2CC] ltm=232BC2D4[232BC2D4,232BC2D4]
pwt=232BC2B0[232BC2B0,232BC2B0] ptm=232BC2B8[232BC2B8,232BC2B8]
ref=232BC2EC[206B112C,206B112C] lnd=232BC2F8[232BC2F8,232BC2F8]
CHILD REFERENCES:
reference latch flags

206b112c 0 CHL[02]
LIBRARY OBJECT: object=20642b20
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags

0 2059d22c 2059cf2c 1f708c18 14 DEP[01]
READ ONLY DEPENDENCIES: count=1 size=16
dependency# table reference handle flags

从中找出依赖的对象1f708c18，我们再来看看1f708c18对应的对象：
BUCKET 68671:
LIBRARY OBJECT HANDLE: handle=1f708c18 mutex=1F708CCC(0)
name=T2.TT
hash=b6efdd23a914ea2ea1b8ffeb7d170c3f timestamp=12-11-2007 16:16:15
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0701 lock=N pin=0 latch#=2 hpc=0002 hlc=0002
lwt=1F708C74[1F708C74,1F708C74] ltm=1F708C7C[1F708C7C,1F708C7C]
pwt=1F708C58[1F708C58,1F708C58] ptm=1F708C60[1F708C60,1F708C60]
ref=1F708C94[1F708C94,1F708C94] lnd=1F708CA0[1F705910,1F708B48]
DEPENDENCY REFERENCES:
reference latch flags

207a6154 2 DEP[01]
2059cf2c 0 DEP[01]
LOCK OWNERS:
lock user session count mode flags

2125a5dc 2373491c 23733654 0 N [4000]
212345d0 2373491c 2373491c 0 N [4000]
LIBRARY OBJECT: object=2059ca40
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr

0 2324abc0 2059cad8 I/-/A/-/- 0 NONE 00
8 2059cc68 2059c8fc I/-/A/-/- 0 NONE 00
9 2059cd00 2059c10c I/-/A/-/- 0 NONE 00
10 2059cd50 2059bd14 I/-/A/-/- 0 NONE 00
BUCKET 68671 total object count=1
这个对象是T2.TT，这两张表是完全不同的，所以SQL不能共享。接下来我们要考虑一下，除了这种情况，还有哪些不可共享的原因呢？从v$sql_shared_cursor的字段中就可以看出Mismatch的各种原因。
另外一种典型的SQL不能共享的条件是两个SQL的执行计划不同。如果2个SQL虽然相同，但是由于参数的不同，必须使用不同的执行计划，那么这种SQL最好还是不要共享。因为SQL共享带来的好处可能只是执行了错误的执行计划的几百分之一。从这方面来看，我们也不能片面的强调SQL共享，而忽略了由于SQL共享带来的问题。
举个简单的例子，比如TA表有个字段是STATUS，其中99%的值都是END,只有1%的字段是BEGIN，我们的大多数程序都是每次读取值为BEGIN的行，然后处理，处理结束后STATUS编程END，只有少量的统计操作需要统计END值的行的数量，这种情况下，如果我们可以通过使用柱状图，优化器就能够判断出使用合理的执行计划。这种情况下，就不能使用绑定变量。如果使用了绑定变量，那么如果是早期版本，优化器不会使用柱状图，而使用缺省的选择性值来判断，如果是9i或者以后的数据库，bind value peeking技术可以通过使用柱状图来选择较好的执行计划，但是bind value peeking只是在SQL第一次被执行的时候，执行HARD PARSE的时候进行，后面的所有SQL由于使用了绑定变量，会被认为是安全的，这条SQL会使用共享的CURSOR，因此就不会进行peeking，这个SQL可能有2种执行计划，走索引或者全表扫描，而选择哪种执行计划，完全取决于做HARD PARSE的那个SQL的绑定变量的值，而无法使优化器做出最佳的选择。在这种情况下，共享SQL的代价就太大了。所以说SQL共享是优化的手段，而不是优化的目标，千万不能为了优化而优化。对于不同的变量值，希望使用不同的执行计划，而且走错了执行计划，会大幅度增加SQL开销的情况下，就要慎用绑定变量了。这个时候可能不使用绑定变量对系统整体性能的改善最有利。在9I中，缺省情况下做表分析是不采集柱状图的，在10g中柱状图的采集是缺省的。
不过10g和9i的bind peeking技术还是存在一定的局限性。对于使用了绑定变量的SQL，bind peeking就往往无法达到较好的效果了。因此Oracle 11g引入了一种新的CURSOR共享机制Adaptive Cursor Sharing(ACS)，首先引入ACS的稳定版本是11.1.0.6。ACS的目的是给了更好的在共享SQL和SQL执行时的资源消耗之间达成一个平衡。其基本思想就是对于使用了绑定变量的SQL，不会盲目的使用一个共享的执行计划，而是根据其绑定变量值的选择性的不同，分为几个组，每组使用不同的执行计划。ACS的出现，使绑定变量的使用没有了顾虑，可以有效的提高此类SQL的效率。
ACS也存在一些副作用，首先由于每次SQL执行都需要分析绑定变量，这增加了SQL PARSE的开销。同时也加大了CURSOR不能共享的机会，增加了硬分析的比例。另外一个CURSOR中不能共享的版本的数量也会大大增加，这样也影响了LIBRARY CACHE的性能。