5.2 深入Shared Pool
Oracle数据库作为一个管理数据的产品，必须能够认出用户所提交的管理命令（通常叫做SQL语句），从而进行响应。认出的过程叫做解析SQL语句的过程，响应的过程叫做执行SQL语句的过程。解析是一个相当复杂的过程，它要考虑各种可能的异常情况，比如SQL语句涉及的对象不存在、提交的用户没有权限等。而且，还需要考虑如何执行SQL语句，采用什么方式去获取数据等。解析的最终结果是要产生Oracle自己内部的执行计划，从而指导SQL的执行过程。可以看到，解析是一个非常消耗资源的过程。因此，Oracle在解析用户提交的SQL语句的过程中，如果对每次出现的新的SQL语句都按照标准过程完整地从头到尾解析一遍的话，效率太低。尤其随着并发用户数量的增加、数据量的增加，数据库的整体性能将直线下降。
Oracle对SQL语句进行了概括和抽象，将SQL语句提炼为两部分。一部分是SQL语句的静态部分，也就是SQL语句本身的关键词、所涉及的表名称以及表的列名等。另一部分是SQL语句的动态部分，也就是SQL语句中的字面值（literal value，即有关表里的数据部分，比如where name='hsj'中，hsj就是SQL语句中的字面值，而where name就是SQL语句中的静态部分）。很明显，整个数据库中所包含的对象数量是有限的，而表中所包含的数据则是无限的。而正是这无限的数据导致了SQL语句的千变万化，也就是说，在数据库处理的所有SQL语句中，静态部分可以认为数量是有限的，而动态部分则是无限的。而实际上，动态部分对解析的影响相比于静态部分对解析的影响来说是微乎其微，也就是说通常情况下，对于相同的静态部分的SQL语句来说，不同的动态部分所产生的解析结果（执行计划）基本都是一样的（除非表里的数据分布极其不均匀，则有可能导致不同的动态部分产生不同的执行计划）。这也就为Oracle提高解析SQL语句的效率提供了方向。
Oracle会将用户提交来的SQL语句都缓存在内存中。每次处理新的一条SQL语句时，都会先在内存中查看是否有相同的SQL语句。如果相同则可以减少最重要的解析工作（也就是生成执行计划），从而节省了大量的CPU资源；反之，如果没有找到相同的SQL语句，则必须重新从头到尾进行完整的解析。这部分存放SQL语句的内存就叫做共享池。当然，shared pool里不仅仅是SQL语句，还包括执行计划、PL/SQL代码、PL/SQL程序的机器码、管理shared pool的内存结构、控制信息等内容。
如果SQL语句使用了绑定变量（bind variable），也就是用一个变量来替代SQL中的字面值。那么Oracle在shared pool中查找到相同的SQL语句的概率相对就很大。比如：
select c1 from t1 where c2=1;
select c1 from t1 where c2=2;
select c1 from t1 where c2=3;
这里的1、2、3就是SQL中的字面值。如果写成：
select c1 from t1 where c2=:v1;
这里的v1就是绑定变量，用来替代上面的1、2、3。解析时使用绑定变量，而在具体执行SQL时，才将字面值传入。这时，解析就是比较SQL语句的静态部分。前面我们已经知道，静态部分是有限的，很容易就能够缓存在内存里，从而找到相同的SQL语句的概率很高。如果没有使用绑定变量，则就是比较SQL语句的静态部分和动态部分，而动态部分的变化是无限的，因此这样的SQL语句很难被缓存在shared pool里。毕竟内存是有限的，不可能把所有的动态部分都缓存在shared pool里。不使用绑定变量导致的直接结果就是，找到相同的SQL语句的概率较低，导致必须完整地解析SQL语句，也就导致消耗更多的资源。从这里也可以看出，只有我们使用了绑定变量，才真正遵循了Oracle引入shared pool的根本思路，才能够更有效地利用shared pool。
shared pool的大小由初始化参数shared_pool_size决定。Oracle 10g以后可以不用设定该参数，而只需要指定sga_target，从而由Oracle自动决定shared pool的大小尺寸。
在一个很高的层次上来看，shared pool可以分为库缓存（library cache）和数据字典缓存（dictionary cache）。Library cache存放了最近执行的SQL语句、存储过程、函数、解析树以及执行计划等。而dictionary cache则存放了在执行SQL语句过程中，所参照的数据字典的信息，包括SQL语句所涉及的表名、表的列、权限信息等。dictionary cache里面的信息都是以数据行的形式存放的，而不是以数据块的形式存放的，因此也叫做row cache。对于dictionary cache来说，Oracle倾向于将它们一直缓存在shared pool里，不会将它们交换出内存，因此我们不用对它们进行过多的关注。而library cache则是shared pool里最重要的部分，也是在shared pool中进进出出最活跃的部分，需要我们仔细研究。所以，我们在说到shared pool实际上就可以认为是在指library cache。
5.2.1 shared pool的内存结构
从一个物理的层面来看，shared pool由许多内存块组成，这些内存块通常称为chunk。chunk是shared pool中内存分配的最小单位，一个chunk中的所有内存都是连续的。
当chunk属于可用类型的时候，它既不属于library cache，也不属于dictionary cache。如果该chunk被用于存放SQL相关的数据时，则该chunk就属于library cache；同样，如果该chunk被用于存放数据字典的信息时，则该chunk就属于dictionary cache。
这些chunk可以分为四类，这四类可以从x$ksmsp（该视图中的每条记录都表示当前在shared pool里的一个chunk）的ksmchcls字段看到。
⊙ free：这种类型的chunk不包含有效的对象，可以不受限制地被分配。
⊙ recr：意味着recreatable，这种类型的chunk里包含的对象可以在需要的时候被临时移走，并且在需要的时候重新创建。比如对于很多有关共享SQL语句的chunk就是recreatable的。
⊙ freeabl：这种类型的chunk包含的对象都是曾经被session使用过的，并且随后会被完全或部分释放。这种类型的chunk不能临时从内存移走，因为它们是在处理过程中间产生的，如果移走的话就无法被重建。
⊙ perm：意味着permanent，这种类型的chunk包含永久的对象，大型的permanent类型的chunk也可能含有可用空间，这部分可用空间可以在需要的时候释放回shared pool里。
在shared pool里，可用的chunk（free类型）会被串起来成为可用链表（free list）或者也可以叫做bucket（一个可用链表也就是一个bucket）。其结构如图5-1所示：
从图5-1中可以看到，可用的chunk链表（也就是bucket）被分成了254个，每个bucket上挂的chunk的尺寸是不一样的，有一个递增的趋势。同时每个bucket都有一个size字段，这个size就说明了该bucket上所能链接的可用chunk的大小尺寸。
图5-1 shared pool可用内存结构图
当某个进程需要shared pool里的一个chunk时，则该进程首先到符合所需空间大小的bucket上去扫描，以找到一个尺寸最合适的chunk，扫描持续到bucket的最末端，直到找到完全符合尺寸的chunk为止。如果找到的chunk的尺寸比需要的尺寸要大，则该chunk就会被拆分成两个chunk，一个chunk被用来存放数据，而另外一个则成为free类型的chunk，并被挂到当前该bucket上。
然而，如果该bucket上不含有任何需要尺寸的chunk，那么就从下一个非空的bucket上获得一个最小的chunk。如果在剩下的所有bucket上都找不到可用的chunk，则需要扫描已经使用的recreatable类型的chunk链表，从该链表上释放一部分的chunk，因为只有recreatable类型的chunk才是可以被临时移出内存的。
当某个chunk正在被使用时（可能是用户正在使用，也可能是使用了dbms_shared_pool包将对象钉在shared pool里），该chunk是不能被移出内存的。比如某个SQL语句正在执行，那么该SQL语句所使用的chunk是不能被移出内存的，该SQL语句所引用的表、索引等对象所占用的chunk也是不能被移出内存的。当shared pool中无法找到足够大小的所需内存时，就会发出ORA-4031的错误消息。当出现4031错误的时候，我们查询v$sgastat里可用的shared pool空间时，可能会发现name为“free memory”的可用内存还足够大，但是为何还是会报4031错呢？事实上，在Oracle发出4031错之前，已经释放了不少recreatable类型的chunk了，因此会产生不少可用内存。但是这些可用chunk中，没有一个chunk能够以连续的物理内存提供所需要的内存空间，从而才会发出4031的错误。
对bucket的扫描、管理、分配chunk等这些操作都是在shared pool latch的保护下进行的。如果shared pool含有数量巨大的非常小的free类型的chunk，则在扫描bucket时，shared pool latch会被锁定很长的时间，这也是Oracle 8i以前的shared pool latch争用的主要原因。而如果增加shared pool尺寸的话，仅仅是延缓shared pool latch的争用，而到最后，就会因为小的free类型的chunk的数量越来越多，争用也会越来越严重。而到了Oracle 9i以后，由于大大增加了可用chunk链表（也就是bucket）的数量，同时，每个bucket所管理的可用chunk的尺寸递增的幅度非常小，于是就可以有效地将可用的chunk都均匀分布在所有的bucket上。这样的结果就是每个bucket上所挂的free类型的chunk都不多，所以在查找可用chunk而持有shared pool latch的时间也可以缩短很多。
注 意 所谓latch，就是轻量级的锁。它是非常底层的对象，用来保护对某个内存块的并发访问。比如某个进程要往一个内存块里写数据，这时如果还有其他进程也要往该内存块里写数据的话，如果不控制并发性，则多个进程写入的数据会互相覆盖。因此，为了不让这种情况出现，Oracle引入了latch，每个进程往内存块里写数据之前必须获得latch，写完以后释放latch。而latch的获取是串行化的，一次只有一个进程能够获得latch。获取和释放latch的速度很快，因此叫轻量级锁。有关latch的详细解释，请参考第10章。
从一个逻辑层面来看，shared pool由library cache和dictionary cache组成。shared pool中组件之间的关系可以用图5-2来表示。
图5-2 shared pool逻辑结构
从上图中可以看到，当SQL语句（比如select object_id,object_name from sharedpool_test）进入library cache时，Oracle会到dictionary cache中去找与sharedpool_test表有关的数据字典信息，比如表名、表的列等，以及用户权限等信息。如果发现dictionary cache中没有这些信息，则会将system表空间里的数据字典信息调入buffer cache内存，读取内存数据块里的数据字典内容，然后将这些读取出来的数据字典内容按照行的形式放入dictionary cache里，从而构造出dc_tables之类的对象。然后，再从dictionary cache的行数据中取出有关的列信息放入library cache中。
对于非常大的对象，我们可以让Oracle为它们单独从保留区域里分配空间，而不是从这个可用的chunk链表中来分配空间。通过设置初始化参数shared_pool_reserved_size，决定这部分空间的大小尺寸，默认为shared_pool_size的5%。这块保留区域与正常chunk管理是完全分开的，小的chunk不会进入这块保留区域，而这块保留区域的可用chunk也不会挂在bucket上。这块保留区域的使用情况可以从视图v$shared_pool_reserved中看到，通常来说，该视图的request_misses字段显示了无法从保留区域里获得足够大的chunk的总次数，该字段应该尽量为0。
5.2.2 library cache和dictionary cache概述
library cache最主要的功能就是存放用户提交的SQL语句、SQL语句相关的解析树（解析树也就是对SQL语句中所涉及的所有对象的展现）、执行计划、用户提交的PL/SQL程序块（包括匿名程序块、存储过程、包、函数等）以及它们转换后能够被Oracle执行的代码等。为了对这些内存结构进行管理，library cache中还存放了很多控制结构，包括lock、pin、dependency table等。
library cache也存放了很多的数据库对象的信息，包括表、索引等。有关这些数据库对象的信息都是从dictionary cache中获得的。如果用户对library cache中的对象信息进行了修改，比如为表添加了一个列等，则这些修改会返回到dictionary cache中。
在library cache中存放的所有信息单元都叫做对象（object），这些对象可以分成两类：一类叫存储对象，也就是上面所说的数据库对象。它们是通过显式的SQL语句或PL/SQL程序创建出来的，如果要删除它们，也必须通过显式的SQL命令进行删除。这类对象包括表、视图、索引、包、函数等；另一类叫做过渡对象，也就是上面所说的用户提交的SQL语句或者提交的PL/SQL匿名程序块等。这些过渡对象是在执行SQL语句或PL/SQL程序的过程中产生的，并缓存在内存里。如果实例关闭则删除，或者由于内存不足而被交换出去，从而被删除。
当用户提交SQL语句或PL/SQL程序块到shared pool以后，会在library cache中生成一个可执行的对象，这个对象就叫做游标（cursor）。不要把这里的游标与标准SQL（ANSI SQL）的游标混淆起来了，标准SQL里的游标是指返回多条记录的SQL形式，需要定义、打开、关闭。下面所说到的游标如无特别说明，都是指library cache中的可执行的对象。游标是可以被所有进程共享的，也就是说如果100个进程都执行相同的SQL语句，那么这100个进程都可以共用该SQL语句所产生的游标，从而节省了内存。
每个游标都是由library cache中的两个或多个对象所体现的，至少两个对象。一个对象叫做父游标（parent cursor），包含游标的名称以及其他独立于提交用户的信息。从v$sqlarea视图里看到的都是有关父游标的信息；另外一个或多个对象叫做子游标（child cursor），如果SQL文本相同，但是可能提交SQL语句的用户不同，或者用户提交的SQL语句所涉及的对象为同名词等，都有可能生成不同的子游标。因为这些SQL语句的文本虽然完全一样，但是上下文环境却不一样，因此这样的SQL语句不是一个可执行的对象，必须细化为多个子游标后才能够执行。子游标含有执行计划或者PL/SQL对象的程序代码块等。
在介绍library cache的内部管理机制前，先简单介绍一下所谓的hash算法。
Oracle内部在实现管理的过程中大量用到了hash算法。hash算法是为了能够进行快速查找定位所使用一种技术。所谓hash算法，就是根据要查找的值，对该值运用一定的hash函数后得出该值所在的索引号。进入索引号所对应的一列数值列表（可以理解为一个二维数组）里，然后再对其中所含有的值进行逐个比较，从而找到该值。这样就避免了对整个数值列表进行扫描才能找到该值，这种全扫描的方式显然要比hash查找方式低效很多。其中，每个索引号对应的数值列在Oracle里都叫做一个hash bucket。
我们来列举一个最简单的hash算法。假设我们的数值列表最多可以有10个元素，也就是有10个hash bucket，每个bucket最多可以包含10个数值。则对应的二维数组就是t⑩⑩。我们可以定义hash算法为n MOD 10。通过这种算法，可以将所有进入的数据均匀放在10个hash bucket里面，hash bucket编号从0到9。比如，我们把1到100都通过这个hash函数均匀放到这10个hash bucket里，当查找32在哪里时，只要将32 MOD 10等于2，这样就知道32必定位于2号hash bucket里，于是到t②⑩里去找，2号hash bucket里有10个数值，逐个比较2号hash bucket里是否存在32就可以了。这里可以看出，为了找到32这个值，我们总共需要比较11次（1+10）。如果不使用hash算法，而采用遍历扫描的方式，则需要比较100次。
library cache就是使用多个hash bucket来管理的，其hash算法当然比我们前面列举的要复杂多了。每个hash bucket后面都串连着多个句柄（该句柄叫做library cache object handle），这些句柄描述了library cache里的对象的一些属性，包括名称、标记、指向对象所处的内存地址的指针等。实际上，hash bucket就是通过串连起来的对象句柄才体现出来的，它本身是一个逻辑上的概念，是一个逻辑组，而不像对象是一个具体的实体。Oracle根据shared_pool_size所指定的shared pool尺寸自动计算hash buckets的个数，shared pool越大，则可以挂载的对象句柄就越多。
可以使用图5-3来描述library cache的结构。
图5-3 library cache结构图
当一条SQL语句进入library cache的时候，先将SQL文本转化为对应ASCII数值，然后对这些ASCII数值进行hash函数的运算，传入函数的参数包括SQL语句的名称（name，对于SQL语句来说其name就是SQL语句的文本）以及命名空间（namespace，对于SQL语句来说是“SQL AREA”，表示共享游标。可以从视图v$librarycache里找到所有的namespace）。运用hash函数后得到一个值，该值就是hash bucket的号码，从而该SQL语句被分配到该号的hash bucket里去。
当某个进程需要处理某个对象时，比如处理一条新进入的SQL语句时，它会对该SQL语句应用hash函数算法，以决定其所在的hash bucket的编号，然后进入该hash bucket进行扫描以确定是否存在相同的SQL语句。有可能会发生该对象的句柄存在，但是句柄所指向的对象已经被交换出内存的情况出现。这时对应的对象必须被再次装载（reload）。也可能该对象的句柄都不存在，也就是说该条SQL语句是第一次被执行，这时进程必须重新构建一个对象句柄挂到hash bucket上，然后再重新装载对象。SQL语句相关的对象有很多（最直观的就是SQL语句的文本），这些对象都存放在library cache里，它们都通过句柄来访问。可以把library cache理解为一本书，而SQL语句的对象就是书中的页，而句柄就是目录，通过目录可以快速定位到指定内容的页。
对象句柄存放了对象的名称（name）、对象所属的命名空间（namespace）、有关对象的一些标记（比如对象是否为只读、为本地对象还是远程对象、是否被pin在内存中等）以及有关对象的一些统计信息等。其中存放的最重要的内容应该就是指向Heap 0对象的指针了。Heap 0用来存放与对象有直接关系的一些信息，比如对象类型、对象相关的表、实际的执行计划、执行PL/SQL的机器码等。Heap是由一个或多个chunk组成的，这些chunk可以是分散的分布在library cache中的，不需要连续分布。
我们可以通过查询视图v$db_object_cache来显示library cache中有哪些对象被缓存，以及这些对象的大小尺寸。比如，我们可以用下面的SQL语句来显示每个namespace中，大小尺寸排在前3名的对象：
select ＊
from (select row_number() over(partition by namespace order by sharable_mem desc) size_rank,
namespace,
sharable_mem,
substr(name, 1, 50) name
from v$db_object_cache
order by sharable_mem desc)
where size_rank <= 3
order by namespace, size_rank;
而dictionary cache则是专门用来存放SYS schema所拥有的对象的内存区域。使用dictionary cache时以行为单位，而不像其他比如buffer cache以数据块为单位，因此dictionary cache也叫做row cache。构造dictionary cache的目的是为了加快解析SQL语句的速度，因为dictionary cache里存放了所有表的定义、Storage信息、用户权限信息、约束定义、回滚段信息、表的统计信息等。基本上我们不需要过多地关注它。
Oracle里是没有初始化参数来控制library cache和dictionary cache应该占多大的内存的，我们只能控制shared pool的大小。因为前面说过，shared pool里的一个可用chunk，如果存放了数据字典的信息，那么它就属于dictionary cache。否则，如果存放了SQL文本或执行计划等信息，则它就属于library cache。有时某chunk原先可能放的是SQL文本，后来由于内存不足被数据字典信息所覆盖，则该chunk就从library cache变成了dictionary cache。所以我们不能单独控制library cache和dictionary cache各是多大。
5.2.3 解析SQL语句的过程
为了将用户写的SQL文本转化为Oracle认识的且可执行的语句，这个过程就叫做解析过程。解析分为硬解析和软解析。一条SQL语句在第一次被执行时必须进行硬解析。
当客户端发出一条SQL语句（也可以是一个存储过程或者一个匿名PL/SQL块）进入shared pool时（注意，我们从前面已经知道，Oracle对这些SQL不叫做SQL语句，而是称为游标。因为Oracle在处理SQL时，需要很多相关的辅助信息，这些辅助信息与SQL语句一起组成了游标），Oracle首先将SQL文本转化为ASCII值，然后根据hash函数计算其对应的hash值（hash_value）。根据计算出的hash值到library cache中找到对应的bucket，然后比较bucket里是否存在该SQL语句。
如果不存在，则需要按照我们前面所描述的，获得shared pool latch，然后在shared pool中的可用chunk链表（也就是bucket）上找到一个可用的chunk，之后释放shared pool latch。在获得了chunk以后，这块chunk就可以认为是进入了library cache。接下来，进行硬解析过程。硬解析包括以下几个步骤。
①对SQL语句进行文法检查，看是否有文法错误。比如没有写from、select拼写错误等。如果存在文法错误，则退出解析过程。
②到数据字典里校验SQL语句涉及的对象和列是否都存在。如果不存在，则退出解析过程。这个过程会加载dictionary cache。
③将对象进行名称转换。比如将同名词翻译成实际的对象等。比如select * from t中，t是一个同名词，指向hr．t1，于是Oracle将t转换为hr．t1。如果转换失败，则退出解析过程。
④检查发出SQL语句的用户是否具有访问SQL语句里所引用的对象的权限。如果没有权限，则退出解析过程。
⑤通过优化器创建一个最优的执行计划。这个过程会根据数据字典里记录的对象的统计信息，来计算最优的执行计划。这一步牵涉大量数学运算，是最消耗CPU资源的。
⑥将该游标所产生的执行计划、SQL文本等装载进library cache的heap中。
在硬解析的过程中，进程会一直持有library cache latch，直到硬解析结束为止。硬解析结束以后，会为SQL语句产生两个游标，一个是父游标，另一个是子游标。父游标里主要包含两种信息：SQL文本以及优化目标（optimizer goal）。父游标在第一次打开时被锁定，直到其他所有的session都关闭该游标后才被解锁。当父游标被锁定的时候是不能被交换出library cache的，只有在解锁以后才能被交换出library cache。父游标被交换出内存时，父游标对应的所有子游标也被交换出library cache。子游标包括游标所有的信息，比如具体的执行计划、绑定变量等。子游标随时可以被交换出library cache，当子游标被交换出library cache时，Oracle可以利用父游标的信息重新构建出一个子游标来，这个过程叫reload。可以使用下面的方式来确定reload的比率：
select 100＊sum(reloads)/sum(pins) Reload_Ratio from v$librarycache;
一个父游标可以对应多个子游标。子游标具体的个数可以从视图v$sqlarea的version_count字段体现出来。而每个具体的子游标则全都在视图v$sql里体现。当具体绑定变量的值与上次绑定变量的值有较大差异（比如上次执行的绑定变量值的长度是6位，而这次执行绑定变量的值的长度是200位）时或者当SQL语句完全相同，但是所引用的表属于不同的用户时，都会创建一个新的子游标。
如果在bucket中找到了该SQL语句，则说明该SQL语句以前运行过，于是进行软解析。软解析是相对于硬解析而言的，如果解析过程中，可以从硬解析的步骤中去掉一个或多个的话，这样的解析就是软解析。软解析分为以下三种类型。
⊙ 第一种是某个session发出的SQL语句与library cache里其他session发出的SQL语句一致。这时，该解析过程中可以去掉硬解析中的⑤和⑥，但是仍然要进行硬解析过程中的②、③、④，也就是表名和列名检查、名称转换和权限检查。
⊙ 第二种是某个session发出的SQL语句是该session之前发出的曾经执行过的SQL语句。这时，该解析过程中可以去掉硬解析中的②、③、⑤和⑥这四步，但是仍然要进行权限检查，因为可能通过grant改变了该session用户的权限。
⊙ 第三种是当设置了初始化参数session_cached_cursors时，当某个session第三次执行相同的SQL时，则会把该SQL语句的游标信息转移到该session的PGA里。这样，该session以后再执行相同的SQL语句时，会直接从PGA里取出执行计划，从而跳过硬解析的所有步骤。这种情况下，是最高效的解析方式，但是会消耗很大的内存。
我们举一个例子来说明解析SQL语句的过程。在该测试中，绑定变量名称相同，但是变量类型不同时，所出现的解析情况。如下所示。
首先，执行下面的命令，清空shared pool里所有的SQL语句：
SQL> alter system flush shared_pool;
然后，定义一个数值型绑定变量，并为该绑定变数赋一个数值型的值以后，执行具体的查询语句。
SQL> variable v_obj_id number;
SQL> exec :v_obj_id := 4474;
SQL> select object_id,object_name from sharedpool_test where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
---------- ---------------------------
4474 AGGXMLIMP
接下来，定义一个字符型的绑定变量，变量名与前面相同，为该绑定变数赋一个字符型的值以后，执行相同的查询：
SQL> variable v_obj_id varchar2(10);
SQL> exec :v_obj_id := '4474';
SQL> select object_id,object_name from sharedpool_test where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
---------- ---------------------------
4474 AGGXMLIMP
然后我们到视图v$sqlarea里找到该SQL的父游标的信息，并到视图v$sql里找该SQL的所有子游标的信息。
SQL> select sql_text,version_count from v$sqlarea where sql_text like ‘%sharedpool_test%’;
SQL_TEXT VERSION_COUNT
------------------------------------------------------------------------------------
select object_id,object_name from sharedpool_test where object_id=:v_obj_id 2
SQL> select sql_text,child_address,address from v$sql where sql_text like
‘%sharedpool_test%’;
SQL_TEXT CHILD_ADDRESS
ADDRESS
-----------------------------------------------------------------------------
select object_id,object_name from sharedpool_test where object_id=:v_obj_id 6757F358
676B6D08
select object_id,object_name from sharedpool_test where object_id=:v_obj_id 674440FC
676B6D08
从记录父游标的视图v$sqlarea的version_count列可以看到，该SQL语句有2个子游标。而从记录子游标的视图v$sql里可以看到，该SQL文本确实有两条记录，而且它们的SQL文本所处的地址（ADDRESS列）也是一样的，但是子地址（CHILD_ADDRESS）却不一样。这里的子地址实际就是子游标所对应的heap 0的句柄。
由此我们也可以看到，存在许多因素可能导致SQL语句不能共享。常见的因素包括SQL文本大小写不一致、SQL语句的绑定变量的类型不一致、SQL语句涉及的对象名称虽然一致但是位于不同的schema下、SQL的优化器模式不一致（比如添加提示、修改了optimizer_mode参数等）等。
5.2.4 设置shared pool
对于设置shared pool的大小来说，没有一个通用的、普遍适用的值，不同的系统负载需要不同大小的shared pool来管理。通常我们在设置shared pool时，应该遵循“不要太大、也不要太小”的原则，设置一个初始的值，然后让系统正常运行一段时间，在这段时间里，对shared pool的使用情况进行观察监控，最后根据系统的负载得出一个在当前负载下比较合理的值。注意，这里只是说明是在当前负载下，如果随着系统的不断升级，导致负载发生一个比较大的变化，这时又需要对shared pool重新监控并做出相应的调整了。
一般来说，设置1GB以上的shared pool不会给性能带来明显的提高，相反，这将给Oracle管理shared pool以及监控shared pool的过程中会带来较多的麻烦。我们可以在系统上线时，设置shared pool为SGA的10%，但是不要超过1GB，让系统正常运行一段时间，我们可以借助Oracle 9i以后所引入的顾问（advisory）来帮助我们判断shared pool的设置是否合理。
只要将初始化参数statistics_level设置为typical（默认值）或all，就能启动对shared pool的建议功能，如果设置为basic，则关闭建议功能。使用如下的SQL语句显示Oracle所建议的shared pool的大小。
SQL> SELECT shared_pool_size_for_estimate “SP”, estd_lc_size “EL”, estd_lc_memory_objects
“ELM”,
2 estd_lc_time_saved “ELT”, estd_lc_time_saved_factor as “ELTS”,
3 estd_lc_memory_object_hits as “ELMO”
4 FROM v$shared_pool_advice;
SP EL ELM ELT ELTS ELMO
---------------- ---------- --------- -------- ------------
128 135 12223 8566 0.9993 2980874
160 166 15809 8567 0.9994 2981291
192 197 19167 8570 0.9998 2982322
224 228 22719 8572 1 2982859
256 259 27594 8572 1 2982906
288 292 31436 8572 1 2982917
320 323 36157 8572 1 2982920
352 354 40371 8572 1 2982929
384 385 45019 8572 1 2982937
416 389 46099 8572 1 2982937
448 389 46099 8572 1 2982937
480 389 46099 8572 1 2982937
512 389 46099 8572 1 2982937
第一列表示Oracle所估计的shared pool的尺寸值，其他列表示在该估计的shared pool大小下所表现出来的指标值，具体含义可以参见Oracle的联机帮助。我们主要关注estd_lc_time_saved_factor列的值，当该列值为1时，表示再增加shared pool的大小对性能的提高没有意义。对于上例来说，当shared pool为224MB时，达到最佳大小。对于设置比224MB更大的shared pool来说，就是浪费空间，没有更多的好处了。
我们还可以借助v$shared_pool_advice来观察在不同的shared pool尺寸情况下的响应时间（单位是秒）各是多少，如下所示。
SQL> SELECT 'Shared Pool' component,
2 shared_pool_size_for_estimate estd_sp_size,
3 estd_lc_time_saved_factor parse_time_factor,
4 CASE
5 WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN
6 0
7 ELSE
8 current_parse_time_elapsed_s + adjustment_s
9 END response_time
10 FROM (SELECT shared_pool_size_for_estimate,
11 shared_pool_size_factor,
12 estd_lc_time_saved_factor,
13 a.estd_lc_time_saved,
14 e.VALUE / 100 current_parse_time_elapsed_s,
15 c.estd_lc_time_saved -a.estd_lc_time_saved adjustment_s
16 FROM v$shared_pool_advice a,
17 (SELECT ＊ FROM v$sysstat WHERE NAME = 'parse time elapsed') e,
18 (SELECT estd_lc_time_saved
19 FROM v$shared_pool_advice
20 WHERE shared_pool_size_factor = 1) c);
COMPONENT ESTD_SP_SIZE PARSE_TIME_FACTOR RESPONSE_TIME
-----------------------------------------------------
Shared Pool 128 0.9993 252.82
Shared Pool 160 0.9994 251.82
Shared Pool 192 0.9998 248.82
Shared Pool 224 1 246.82
Shared Pool 256 1 246.82
Shared Pool 288 1 246.82
Shared Pool 320 1 246.82
Shared Pool 352 1 246.82
Shared Pool 384 1 246.82
Shared Pool 416 1 246.82
Shared Pool 448 1 246.82
Shared Pool 480 1 246.82
Shared Pool 512 1 246.82
如果是Oracle 9i之前的版本，没有顾问的话，则可以在系统运行过程中，观察shared pool的统计信息以及等待事件来判断shared pool是否合理。
如果设置了共享服务器（Shared Server）的连接模式，则注意要配置large poo（l通过设置large_pool参数）。如果不设置large pool，session的PGA会有一部分在shared pool里进行分配，从而加重shared pool的负担。