深入分析及性能调整学习教案

1、会计学1深入分析及性能深入分析及性能(xngnng)调整调整第一页，共100页。SQL语句静态部分和动态部分oracle对SQL语句进行了概括和抽象，将SQL语句提炼为两部分，一部分是SQL语句的静态部分，也就是SQL语句本身的关键词、所涉及的表名称以及表的列等。另一部分就是SQL语句的动态部分，也就是SQL语句中的值（即表里的数据(shj)）。很明显的，整个数据(shj)库中所包含的对象数量是有限的，而其中所包含的数据(shj)则是无限的。而正是这无限的数据(shj)导致了SQL语句的千变万化，也就是说在数据(shj)库运行的过程中，发生的所有SQL语句中，静态部分可以认为数量是有限的，而动

2、态部分则是无限的。而实际上，动态部分对解析的影响相比静态部分对解析的影响来说是微乎其微，也就是说通常情况下，对于相同的静态部分的SQL语句来说，不同的动态部分所产生的解析结果（执行计划）基本都是一样的。这也就为oracle提高解析SQL语句的效率提供了方向。 第1页/共100页第二页，共100页。共享池oracle会将用户提交来的SQL语句都缓存在内存(ni cn)中。每次处理新的一条SQL语句时，都会先在内存(ni cn)中查看是否有相同的SQL语句。如果相同则可以减少最重要的解析工作（也就是生成执行计划），从而节省了大量的资源；反之，如果没有找到相同的SQL语句，则必须重新从头到尾进行完整

3、的解析过程。这部分存放SQL语句的内存(ni cn)就叫做共享池（shared pool）。当然，shared pool里不仅仅是SQL语句，还包括管理shared pool的内存(ni cn)结构以及执行计划、控制信息等等内存(ni cn)结构。 第2页/共100页第三页，共100页。绑定变量当oracle在shared pool中查找相同的SQL语句的过程中，如果SQL语句使用了绑定变量（bind variable），那么就是比较SQL语句的静态部分，前面我们已经知道，静态部分是有限的，很容易就能够缓存在内存里，从而找到相同的SQL语句的概率很高。如果没有使用绑定变量，则就是比较SQL语句

4、的静态部分和动态部分，而动态部分的变化是无限的，因此这样的SQL语句很难被缓存在shared pool里。毕竟内存是有限的，不可能把所有的动态部分都缓存在shared pool里，即便能够缓存，管理这样一个无限大的shared pool也是不可能完成的任务。不使用绑定变量导致的直接结果就是，找到相同的SQL语句的概率很低，导致必须完整的解析SQL语句，也就导致消耗更多的资源。从这里(zhl)也可以看出，只有我们使用了绑定变量，才真正遵循了oracle引入shared pool的哲学思想，才能够更有效的利用shared pool第3页/共100页第四页，共100页。共享池：库缓存+字典缓存sha

5、red pool的大小由初始化参数shared_pool_size决定。10g以后可以不用设定该参数，而只需要指定sga_target，从而oracle将自动决定shared pool的大小尺寸。在一个(y )很高的层次上来看，shared pool可以分为库缓存（library cache）和数据字典缓存（dictionary cache）。Library cache存放了最近执行的SQL语句、存储过程、函数、解析树以及执行计划等。而dictionary cache则存放了在执行SQL语句过程中，所参照的数据字典的信息，包括SQL语句所涉及的表名、表的列、权限信息等。dictionary c

6、ache也叫做row cache，因为这里面的信息都是以数据行的形式存放的，而不是以数据块的形式存放的。对于dictionary cache来说，oracle倾向于将它们一直缓存在shared pool里，不会将它们交换出内存，因此我们不用对它们进行过多的关注。而library cache则是shared pool里最重要的部分，也是在shared pool中进进出出最活跃的部分，需要我们仔细研究。所以，我们在说到shared pool实际上就可以认为是在指library cache。 第4页/共100页第五页，共100页。shared pool的内存结构（逻辑结构）从一个逻辑层面来看，sha

7、red pool由library cache和dictionary cache组成。shared pool中组件(z jin)之间的关系可以用下图一来表示。从下面这个图中可以看到，当SQL语句（select object_id,object_name from sharedpool_test）进入library cache时，oracle会到dictionary cache中去找与sharedpool_test表有关的数据字典信息，比如表名、表的列等，以及用户权限等信息。如果发现dictionary cache中没有这些信息，则会将system表空间里的数据字典信息调入buffer cache

8、内存，读取内存数据块里的数据字典内容，然后将这些读取出来的数据字典内容按照行的形式放入dictionary cache里，从而构造出dc_tables之类的对象。然后，再从dictionary cache中的行数据中取出有关的列信息放入library cache中。 第5页/共100页第六页，共100页。shared pool的内存结构（物理结构）从一个物理的层面来看，shared pool是由许多内存块组成，这些内存块通常称为chunk。Chunk是shared pool中内存分配的最小单位，一个chunk中的所有内存都是连续的。这些chunk可以分为四类，这四类可以从x$ksmsp（该视图

9、中的每个行都表示shared pool里的一个chunk）的ksmchcls字段看到： 1) free：这种类型的chunk不包含有效的对象，可以不受限制的被分配。 2) recr：意味着recreatable，这种类型的chunks里包含的对象可以在需要的时候被临时移走，并且在需要的时候重新创建(chungjin)。比如对于很多有关共享SQL语句的chunks就是recreatable的。3) freeabl：这种类型的chunks包含的对象都是曾经被session使用过的，并且随后会被完全或部分释放的。这种类型的chunks不能临时从内存移走，因为它们是在处理过程中间产生的，如果移走的话就

10、无法被重建。 4) perm：意味着permanent，这种类型的chunks包含永久的对象，大型的permanent类型的chunks也可能含有可用空间，这部分可用空间可以在需要的时候释放回shared pool里。 第6页/共100页第七页，共100页。当chunk属于free类型的时候，它既不属于library cache，也不属于dictionary cache。如果该chunk被用于存放SQL游标时，则该chunk进入library cache；同样，如果该chunk被用于存放数据字典的信息时，则该chunk进入dictionary cache。 在shared pool里，可用的c

11、hunk（free类型）会被串起来成为可用链表（free lists）或者也可以叫做buckets（一个可用链表也就是一个bucket）。我们可以使用下面的命令将shared pool的内容转储出来看看这些bucket。 alter session set events immediate trace name heapdump level 2; 然后(rnhu)打开产生的转储文件，找到“FREE LISTS”部分，可以发现类似如下图所示的内容。第7页/共100页第八页，共100页。每个bucket上挂的chunk的尺寸(ch cun)是不一样的，有一个递增的趋势。我们可以看到，每个bucke

12、t都有一个size字段，这个size就说明了该bucket上所能链接的可用chunk的大小尺寸(ch cun)。 第8页/共100页第九页，共100页。当一个进程需要shared pool里的一个chunk时，假设当前需要21个单位的空间，则该进程首先到符合所需空间大小的bucket（这里就是bucket 2）上去扫描，以找到一个尺寸(ch cun)最合适的chunk，扫描持续到bucket的最末端，直到找到完全符合尺寸(ch cun)的chunk为止。如果找到的chunk的尺寸(ch cun)比需要的尺寸(ch cun)要大，则该chunk就会被拆分成两个chunk，一个chunk被用来存放

13、数据，而另外一个则成为free类型的chunk，并被挂到当前该bucket上，也就是bucket 2上。然而，如果该bucket上不含有任何需要尺寸(ch cun)的chunk，那么就从下一个非空的bucket上（这里就是bucket 3）获得一个最小的chunk。如果在剩下的所有bucket上都找不到可用的chunk，则需要扫描已经使用的recreatable类型的chunk链表，从该链表上释放一部分的chunk出来，因为只有recreatable类型的chunk才是可以被临时移出内存的。第9页/共100页第十页，共100页。当某个chunk正在被使用时（可能是用户正在使用，也可能是使用了d

14、bms_shared_pool包将对象钉在shared pool里），该chunk是不能被移出内存(ni cn)的。比如某个SQL语句正在执行，那么该SQL语句所对应的游标对象是不能被移出内存(ni cn)的，该SQL语句所引用的表、索引等对象所占用的chunk也是不能被移出内存(ni cn)的。当shared pool中无法找到足够大小的所需内存(ni cn)时，报ORA-4031错。当出现4031错的时候，你查询v$sgastat里可用的shared pool空间时，可能会发现name为“free memory”的可用内存(ni cn)还足够大，但是为何还是会报4031错呢？事实上，在or

15、acle发出4031错之前，已经释放了不少recreatable类型的chunk了，因此会产生不少可用内存(ni cn)。但是这些可用chunk中，没有一个chunk是能够以连续的物理内存(ni cn)提供所需要的内存(ni cn)空间的，从而才会发出4031的错。第10页/共100页第十一页，共100页。对bucket的扫描、管理、分配chunk等这些(zhxi)操作都是在shared pool latch的保护下进行的。如果shared pool含有数量巨大的非常小的free类型的chunk的话，则扫描bucket时，shared pool latch会被锁定很长的时间，这也是8i以前的s

16、hared pool latch争用的主要原因。而如果增加shared pool尺寸的话，仅仅是延缓shared pool latch的争用，而到最后，就会因为小的free chunks的数量越来越多，争用也会越来越严重。而到了9i以后，由于大大增加了可用chunk链表（也就是bucket）的数量，同时，每个bucket所管理的可用chunk的尺寸递增的幅度非常小，于是就可以有效的将可用的chunk都均匀的分布在所有的bucket上。这样的结果就是每个bucket上所挂的free类型的chunk都不多，所以在查找可用chunk而持有shared pool latch的时间也可以缩短很多。第11

17、页/共100页第十二页，共100页。对于非常(fichng)大的对象，oracle会为它们单独从保留区域里分配空间，而不是从这个可用chunk链表中来分配空间。这部分空间的大小尺寸就是由初始化参数shared_pool_reserved_size决定的，缺省为shared_pool_size的5%，这块保留区域与正常的chunk的管理是完全分开的，小的chunk不会进入这块保留区域，而这块保留区域的可用chunk也不会挂在bucket上。这块保留区域的使用情况可以从视图v$shared_pool_reserved中看到，通常来说，该视图的request_misses字段显示了需要从保留区域的可

18、用链表上上获得大的chunk而不能获得的次数，该字段应该尽量为0。 第12页/共100页第十三页，共100页。library cache最主要的功能就是存放(cnfng)用户提交的SQL语句、SQL语句相关的解析树（解析树也就是对SQL语句中所涉及到的所有对象的展现）、执行计划、用户提交的PL/SQL程序块（包括匿名程序块、存储过程、包、函数等）以及它们转换后能够被oracle执行的代码等。为了对这些内存结构进行管理，还存放(cnfng)了很多控制结构，包括lock、pin、dependency table等。 library cache还存放(cnfng)了很多的数据库对象的信息，包括表、索

19、引等等。有关这些数据库对象的信息都是从dictionary cache中获得的。如果用户对library cache中的对象信息进行了修改，则这些修改会返回到dictionary cache中。第13页/共100页第十四页，共100页。在library cache中存放的所有的信息单元都叫做对象（object），这些对象可以分成两类：一类叫存储对象，也就是上面所说的数据库对象。它们是通过(tnggu)显式的SQL语句或PL/SQL程序创建出来的，如果要删除它们，也必须通过(tnggu)显示的SQL命令进行删除。这类对象包括表、视图、索引、包、函数等等；另一类叫做过渡对象，也就是上面所说的用户提

20、交的SQL语句或者提交的PL/SQL程序块等。这些过渡对象是在执行SQL语句或PL/SQL程序的过程中产生的，并缓存在内存里。如果实例关闭则删除，或者由于内存不足而被交换出去，从而被删除。第14页/共100页第十五页，共100页。父子游标当用户提交SQL语句或PL/SQL程序块到oracle的shared pool以后，在library cache中生成的一个可执行的对象，这个对象就叫做游标（cursor）。不要把这里的游标与标准SQL（ANSI SQL）的游标混淆起来了，标准SQL的游标是指返回多条记录的SQL形式，需要定义、打开、关闭(gunb)。下面所说到的游标如无特别说明，都是指lib

21、rary cache中的可执行的对象。游标是可以被所有进程共享的，也就是说如果100个进程都执行相同的SQL语句，那么这100个进程都可以同时使用该SQL语句所产生的游标，从而节省了内存。每个游标都是由library cache中的两个或多个对象所体现的，至少两个对象。一个对象叫做父游标（parent cursor），包含游标的名称以及其他独立于提交用户的信息。从v$sqlarea视图里看到的都是有关父游标的信息；另外一个或多个对象叫做子游标（child cursors），如果SQL文本相同，但是可能提交SQL语句的用户不同，或者用户提交的SQL语句所涉及到的对象为同名词等，都有可能生成不同的

22、子游标。因为这些SQL语句的文本虽然完全一样，但是上下文环境却不一样，因此这样的SQL语句不是一个可执行的对象，必须细化为多个子游标后才能够执行。子游标含有执行计划或者PL/SQL对象的程序代码块等。 第15页/共100页第十六页，共100页。HASH算法在介绍library cache的内部管理机制前，先简单介绍一下所谓的hash算法。 oracle内部在实现管理的过程中大量用到了hash算法。hash算法是为了能够进行快速查找定位所使用一种技术。所谓hash算法，就是根据要查找的值，对该值进行一定的hash算法后得出该值所在的索引号，然后进入到该值应该存在的一列(y li)数值列表（可以理

23、解为一个二维数组）里，通过该索引号去找它应该属于哪一个列表。然后再进入所确定的列表里，对其中所含有的值，进行一个一个的比较，从而找到该值。这样就避免了对整个数值列表进行扫描才能找到该值，这种全扫描的方式显然要比hash查找方式低效很多。其中，每个索引号对应的数值列在oracle里都叫做一个hash bucket。第16页/共100页第十七页，共100页。我们来列举一个最简单的hash算法。假设我们的数值列表最多可以有10个元素，也就是(jish)有10个hash buckets，每个元素最多可以包含20个数值。则对应的二维数组就是(jish)t1020。我们可以定义hash算法为n MOD 1

24、0。通过这种算法，可以将所有进入的数据均匀放在10个hash bucket里面，hash bucket编号从0到9。比如，我们把1到100都通过这个hash函数均匀放到这10个hash bucket里，当查找32在哪里时，只要将32 MOD 10等于2，这样就知道可以到2号hash bucket里去找，也就是(jish)到t220里去找，2号hash bucket里有10个数值，逐个比较2号hash bucket里是否存在32就可以了。第17页/共100页第十八页，共100页。library cache就是使用多个hash bucket来管理(gunl)的，其hash算法当然比我们前面列举的要

25、复杂多了。每 个hash bucket后面都串连着多个句柄（该句柄叫做library cache object handle），这些句柄描述了library cache里的对象的一些属性，包括名称、标记、指向对象所处的内存地址的指针等。可以用下图来描述library cache的整体结构。 第18页/共100页第十九页，共100页。当一条SQL语句进入library cache的时候，先将SQL文本转化为对应ASCII数值，然后对该这些ASCII数值进行hash函数的运算，传入函数的是SQL语句的名称（name，对于SQL语句来说其name就是SQL语句的文本）以及命名空间（namespace

26、，对于SQL语句来说是“SQL AREA”，表示共享游标。可以从视图v$librarycache里找到所有(suyu)的namespace）。运用hash函数后得到一个值，该值就是hash bucket的号码，从而该SQL语句被分配到该号的hash bucket里去。实际上，hash bucket就是通过串连起来的对象句柄才体现出来的，它本身是一个逻辑上的概念，是一个逻辑组，而不像对象是一个具体的实体。oracle根据shared_pool_size所指定的shared pool尺寸自动计算hash buckets的个数，shared pool越大，则可以挂载的对象句柄就越多。第19页/共10

27、0页第二十页，共100页。当某个进程需要处理某个对象时，比如处理一条新进入的SQL语句时，它会对该SQL语句应用hash函数算法，以决定其所在的hash bucket的编号，然后进入该hash bucket进行扫描并比较。有可能会发生该对象的句柄存在，但是句柄所指向的对象已经被交换出内存的情况出现。这时对应的对象必须被再次装载（reload）。也可能该对象的句柄都不存在，这时进程必须重新构建一个对象句柄挂到hash bucket上，然后再重新装载对象。SQL语句相关的对象有很多（最直观的就是SQL语句的文本），这些对象都存放在library cache里，它们都通过句柄来访问。可以(ky)把l

28、ibrary cache理解为一本书，而SQL语句的对象就是书中的页，而句柄就是目录，通过目录可以(ky)快速定位到指定内容的页。第20页/共100页第二十一页，共100页。对象句柄存放了对象的名称（name）、对象所属的命名空间（namespace）、有关对象的一些标记（比如对象是否为只读、为本地对象还是远程对象、是否被pin在内存中等等）以及有关对象的一些统计信息等。而且，对象句柄中还存放了当前正在lock住和pin住该对象的用户列表、以及当前正在等待lock和pin该对象的用户列表。对象句柄中存放的最重要(zhngyo)的内容就是指向Heap 0对象的指针了。Heap 0用来存放与对象有

29、直接关系的一些信息，比如对象类型、对象相关的表（比如依赖表、子表等）、指向对象的其他数据块的指针（这些数据块指向了实际存放SQL文本、PL/SQL代码、错误信息等的大内存块，这些大内存块依次叫做Heap 1、2、3、4等）等信息。 第21页/共100页第二十二页，共100页。Heap是通过调用服务器进程进行分配的，任何对象都具有(jyu)heap 0，至于还应该分配哪些其他的heap则是由对象的类型决定的，比如SQL游标具有(jyu)heap 1和 6，而PL/SQL程序包则具有(jyu)heap 1、2、3和4。按照heap的使用情况，oracle会在SGA（library cache）、P

30、GA或UGA中分配heap，但是heap 0始终都是在library cache中进行分配的。如果所请求的heap已经在SGA中分配了，则不会在PGA中再次分配heap。Heap是由一个或多个chunk组成的，这些chunk可以是分散的分布在library cache中的，不需要连续分布。第22页/共100页第二十三页，共100页。1) object type：library cache中的对象类型包括：表、视图、索引、同名词等等。每个对象只能有一个object type，根据object type将对象归类到不同的namespace里。一个object type对应一个namespace，但

31、是一个namespace可能对应多个object type。这样的话，查找一个对象时，只要在该对象所属的namespace中去找就可以了。比较常见的namespace包括： a) SQL AREA：也可以叫做CRSR，表示shared cursor，存放共享的SQL语句。 b) TABLE/PROCEDURE：存放的object type包括：table、view、sequence、synonym、 procedure的定义(dngy)、function的定义(dngy)以及package的定义(dngy)。 c) BODY：存放procedure的实际代码、function的实际代码以及pa

32、ckage的实际代码。 d) TRIGGER：存放的object type为trigger。 e) INDEX：存放的object type为index。 第23页/共100页第二十四页，共100页。2) object name：对象名称由三部分组成： a) Schema的名称，对于共享游标（SQL语句或PL/SQL程序块）来说为空。 b) 对象名称。分为两种情况：对于共享游标（SQL语句或PL/SQL程序块）来说，其对象名称就是SQL的语句本身；而对于其他对象（比如(br)表、视图、索引等）就是其在数据字典中的名称。 c) Database link的名称。这是可选的，如果是本地对象，则为空

33、。 这样，对象的名称的格式为：SCHEMA.NAMEDBLINK。比如(br)，可以为，也可以为hr.employees等。 第24页/共100页第二十五页，共100页。3) flags：flags主要用来描述对象是否已经被锁定。对象具有(jyu)三种类型的flag： a) public flag：表示对象上没有锁定（pin）或者latch。 b) status flag：表示对象上存在锁定（pin），说明对象正在被创建或删除或修改等。 c) specitial flag：表示对象上存在library cache latch。第25页/共100页第二十六页，共100页。4) tables：对每

34、个对象，都会维护以下一串tables中的若干个： a) dependency table：含有当前对象所依赖的其他对象。比如一个视图可能会依赖其组成的多个表、一个存储过程可能依赖其中所调用的其他存储过程、一个游标可能依赖其中所涉及到的多个表等。Dependency table中的每个条目(tiom)都指向一块物理内存，该物理内存中含有当前对象所依赖的对象的句柄。 b) child table：含有当前对象的子对象，只有游标具有child table。Child table中的每个条目(tiom)都指向一个可执行的SQL命令所对应的句柄。 c) translation table：包含当前对象所

35、引用的名称是如何解释为oracle底层对象的名称，只有游标具有translation table。 d) authorization table：包含该对象上所对应的权限，一个条目(tiom)对应一个权限。 e) access table：对于dependency table中的每一个条目(tiom)，都会在access table中存在对应的一个或多个条目(tiom)。比如，假设对象A依赖对象B，那么在A的dependency table和access table中都会存在一个条目(tiom)指向B。位于access table中的指向B的条目(tiom)说明了对B具有什么样的访问类型，从而

36、也就说明了用户要执行A则必须具有对B的权限。 f) read-only dependency table：类似于dependency table，但是存放只读的对象。 g) schema name table：包含authorization table中的条目(tiom)所属的schema。 第26页/共100页第二十七页，共100页。5) data blocks：对象的其他(qt)信息会存放在不同的heap中，为了找到这些heap，会在heap 0中存放多个（最多16个，但是这16个data block不会都用到）data blocks结构，每个data block含有指向这些实际heap内

37、存块的指针。 除了heap 0以外，还有11个heap，根据对象的不同进行分配，并存放了不同的内容： 1) Heap 1：存放PL/SQL对象的源代码。 2) Heap 2：存放PL/SQL对象的解析树，这有个好听的名字： DIANA。 3) Heap 3：存放PL/SQL对象的伪代码。 4) Heap 4：存放PL/SQL对象的基于硬件的伪代码。 5) Heap 5：存放了编译时的错误信息。 6) Heap 6：存放了共享游标对象的SQL文本。 7) Heap 7：可用空间。 8) Heaps 811：根据对象的不同而使用的子heap。 第27页/共100页第二十八页，共100页。我们(w

38、men)可以通过查询v$db_object_cache来显示library cache中有哪些对象被缓存，以及这些对象的大小尺寸。比如，我们(w men)可以用下面的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) namefrom v$db_object_cacheorder by shar

39、able_mem desc)where size_rank select object_type fromm sharedpool_test111; ORA-00942: 表或视图不存在 然后我们以level 16转储library cache，并打开转储文件，找到相应的部分，可以看到， 该SQL语句在语法上是错误的（from写成了fromm），oracle仍然(rngrn)在shared pool中为其分配了一个chunk，然后该chunk进入library cache，并在library cache中分配了一个bucket，同时也生成了heap 0，但是该heap 0中不存在相应的一些如d

40、ependency table等table的部分，以及data block的部分。oracle始终都是先对SQL生成hash值（不论该SQL语法上是否正确），再根据hash值到对应的可用chunk链表（也就是bucket）里分配chunk，然后进入语法解析等解析过程。 第54页/共100页第五十五页，共100页。我们再举一个例子来说明解析(ji x)正确的SQL语句的过程。如下所示。另个使用(shyng)了绑定变量的SQL语句，语句名字完全一样，但是绑定变量的数据类型不一致。第55页/共100页第五十六页，共100页。我们以level 16来转储library cache。可以看到如图所示的内

41、容。很明显的看到，子游标的 部分(b fen)包含两条记录，这也就说明该SQL语句产生了两个子游标。虽然我们从SQL文本上看，前后两次执行的SQL语句是一样的。只有绑定变量的类型发生了改变，第一次是number型，而第二次是varchar2型。可正是这数据类型的变化导致了该SQL语句的执行计划不能得到共享，从而产生了两个子游标。这时，我们根据子游标的两个handle： 33e12868和33e393c0找到对应的heap 0的话，就可以看到这两个heap 0中所记录的heap 6是两个完全不同的内存块，这也说明前后两次执行SQL并没有真正得到共享。 第56页/共100页第五十七页，共100页。

42、从记录父游标的视图v$sqlarea的version_count列可以看到，该SQL语句有2个子游标。而从记录子游标的视图v$sql里可以看到，该SQL文本确实有两条记录，而且它们(t men)的SQL文本所处的地址（address列）也是一样的，但是子地址（child_address）却不一样。这里的子地址实际就是子游标所对应的heap 0的句柄。 由此我们也可以看到，存在许多因素可能导致SQL语句不能共享。常见的因素包括， SQL文本大小写不一致、SQL语句的绑定变量的类型不一致、SQL语句涉及到的对象名称虽然一致但是位于不同的schema下、SQL的优化器模式不一致（比如添加提示、修改了

43、optimizer_mode参数等）等。 第57页/共100页第五十八页，共100页。library cache的并发控制(latch,lock,pin)为什么需要并发控制？由于library cache是可以被所有进程同时访问并修改的，因此就必然存在一个并发控制的问题。比如对于前面我们举例子来说，第一次使用number类型的绑定变量执行的SQL语句产生的游标挂在bucket 22544上。而当我们第二次使用varchar2类型的绑定变量再次执行该SQL语句时，oracle需要访问该bucket 22544上的句柄，发现不能共用执行计划时，还要修改该句柄，向CHILDREN部分添加一条指向另外

44、一个子游标的句柄。在很多进程并发执行的情况下，那么当修改挂在bucket 22544上的句柄的时候，必须防止其他进程同时修改所访问的句柄。因为如果不防止这种情况的出现，那么假如这时正好也有一个进程也是使用varchar2类型的绑定变量执行该相同的SQL时，那么也会向CHILDREN部分添加一条子游标的记录，那么CHILDREN部分就会出现两条varchar2的执行计划，而实际上这两个执行计划是一样的，是完全可以合二为一的，这样也就达不到SQL共享的目的(md)。同时还要考虑，当oracle在向某个heap（比如heap 0和heap 6）填入SQL文本、执行计划等数据的过程中，要防止该heap

45、又被其他进程分配掉的情况出现。如果不防止的话，那这个heap的数据就被两个进程同时写，那里面的数据一定是混乱而无法使用的了。第58页/共100页第五十九页，共100页。为了有效的解决上面所说的并发性的问题，oracle使用三种结构来完成对library cache的并发控制：lock、pin和library cache latch。简单来说，进程如果要访问或者修改library cache里的对象，首先必须获得library cache latch，然后获得handle上的lock，最后获得heap上的pin，访问或修改结束以后，释放pin、lock和latch。lock是落在library

46、cache里的对象句柄上的，用来管理并发性。按照前面所说的例子，当多个进程同时修改bucket 22544上的句柄的时候，只有一个进程能够获得该句柄上的lock，其他进程必须等待（体现的等待事件就是library cache lock）。同时，尝试获得某个句柄上的lock也是将游标句柄对象加载到shared pool里的唯一方式。也就是说，当客户端发出某个SQL语句时，oracle对该SQL语句运用(ynyng)hash函数生成hash值，然后到该hash值所对应的library cache的bucket里试图找到对应的句柄并lock该句柄时，如果发现该句柄不存在（可能是由于该SQL语句是一条

47、全新的SQL语句，或者以前该SQL语句执行过但是现在被交换出了library cache），则会将该SQL游标所对应的句柄加载到library cache里。 第59页/共100页第六十页，共100页。目前有三种lock模式，分别是：share、exclusive和null。如果某个进程只是要读取句柄里的信息时，会对该句柄添加share模式的lock，比如当编译某个存储过程时，进程会去读取该存储过程所引用的子存储过程等，这时其他进程可以对该子存储过程添加share和null模式的lock；如果某个进程需要修改对象里的信息时，就会对该句柄添加exclusive模式的lock，比如删除某个存储过程

48、就会添加exclusive模式的lock，这时其他进程只能对该相同(xin tn)的句柄添加null模式的lock；null模式的lock比较特殊，在任何可以执行的对象上（比如存储过程、视图、函数等等）都拥有该null模式的lock。你可以随意打破该模式的lock，这时拥有该LOCK的对象就失效了，需要重新编译。当SQL开始解析时，或获得null模式的lock，然后会一直加在该对象上，直到某些会引起对象失效的DDL发生在对象或对象所依赖的其他对象上，这时该lock被打破。当发生null模式的lock时，其他进程可以对该相同(xin tn)的句柄添加任何模式的lock。Null模式的锁主要是为了

49、解决对象的依赖性问题。第60页/共100页第六十一页，共100页。pin则是落在heap上的，用来防止多个进程同时更新同一个heap。pin的优先级比lock要低，获得pin之前必须先获得lock。同样按照前面所说的例子，当第二次使用varchar2类型的绑定变量执行相同的SQL语句时，该进程首先会获得bucket 22544的句柄上的lock，根据该句柄里所记录的heap发现不能共用时，到shared pool中分配可用的chunk作为heap（包括heap 0和heap 6等）的空间，并获得该heap上的pin，然后在句柄里添加一条子游标记录，以指向所分配的heap 0的句柄。当pin住了

50、heap以后，进程就向heap中写入数据(shj)，结束以后释放pin，最后释放lock。当某个进程获得了句柄上的lock，但是不能pin住该句柄所对应的heap时，该进程就必须等待（体现的等待事件就是library cach pin）。与lock相同，当进程试图pin住某个heap但是发现该heap不存在时，就会同时将该heap加载到library cache里同时pin住它。第61页/共100页第六十二页，共100页。pin有两种模式：share和exclusive。当某个进程只需要(xyo)读取heap中的信息时，会对该heap执行share模式的pin。如果进程需要(xyo)修改hea

51、p时，则会先对该heap执行share模式的pin以便对heap进行错误和安全检查，通过以后，再对该heap执行exclusive模式的pin，从而对该heap进行修改。 第62页/共100页第六十三页，共100页。从上面对lock和pin的描述中可以看出，lock本身不是一个原子的操作，也就是说要完成lock需要执行一系列的操作步骤（包括pin住heap等）。因此为了(wi le)防止lock的过程被其他进程打破，oracle使用library cache latch来管理lock。也就是说，如果某个进程在进行lock之前，必须先获得library cache latch，如果不能获得该la

52、tch，就必须等待。当lock过程结束以后，释放该latch。 注意：latch锁住的是整个的bucket。注意：latch是串行的，latch的单位是bucket。首先获得bucket上的latch，然后去获得Handle上的Lock，最后(zuhu)去pin 住Heap。当Lock和Pin完成以后，oracle会释放latch。因此latch持有的过程就是为了获取lock或者pin，因此latch不是长时间持有，因为它是串行的。第63页/共100页第六十四页，共100页。oracle提供了多个library cache latch（这样，每个library cache latch都称为子l

53、atch）来保护library cache中的bucket。这些子latch的数量由一个隐藏参数决定：_kgl_latch_count。该参数缺省值为大于等于系统中CPU个数的最小的素数。比如在一个具有4个CPU的生产环境中，library cache latch的个数为5，如下所示。但是oracle内部(nib)规定了library cache latch的最大个数为67，即便将这个隐藏参数设置为100，library cache latch的数量也还是67个。 第64页/共100页第六十五页，共100页。具体到每个bucket应该由哪个子latch来管理，则是通过下面这个函数来确定的。 l

54、atch号=mod(bucket号,latch的数量) 假如还是按照上面的例子，对于bucket 64367来说，假设当前系统具有37个library cache latch，那么会使用24（mod(64367,37)=24）号latch来保护挂在该bucket上的句柄。正是由于这样的算法，可能会导致所有的子latch不能在library cache里的整个bucket链条(lintio)上均匀分布，有可能出现某个或某几个子latch非常繁忙，而有些子latch则非常空闲。至于如何判断以及解决，可以见下面shared pool的优化部分。 第65页/共100页第六十六页，共100页。我们来做两

55、个测试，分别来模拟一下lock和pin。来看看lock和pin是如何控制library cache里的对象的。实验的思路很简单第一，打开一个session（sess #1）创建一个存储过程，该过程只做一件事情，就是通过调用dbms_lock.sleep进行(jnxng)等待。并在sess #1中调用该存储过程；第二，打开第二个session（sess #2），重新编译该存储过程；第三，打开第三个session（sess #3），删除该存储过程；第四，打开第四个session（sess #4）进行(jnxng)监控。根据前面对lock和pin的描述，我们可以预见，sess #2将等待librar

56、y cache pin。而sess #3会等待library cache lock。 实验过程如下，在实验的过程中，不断以level 16转储library cache以更深入的观察lock和pin的变化，以下按照时间顺序排列：第66页/共100页第六十七页，共100页。Session 1#：建立存储过程(guchng)，执行存储过程(guchng) Session 4#，dump，编号(bin ho)F1第67页/共100页第六十八页，共100页。sess #2sess #4 dump，编号(bin ho)F2sess #3 第68页/共100页第六十九页，共100页。sess #4，转储出

57、来(ch li)的文件编号为F3从监控的结果看到，正如我们所预料的，编译存储(cn ch)过程的sess #2（sid为153）正在等待library cache pin，而删除存储(cn ch)过程的sess #3（sid为148）正在等待library cache lock。第69页/共100页第七十页，共100页。在转储出来的文件中，我们主要关存储过程lock_test本身在library cache中的变化。在F1中，我们可以看到如下图的内容。注意其中的lock为N，pin为S。由于sess #1正在执行lock_test存储过程，需要读取该handle所对应的heap里的内容，因此以

58、null模式lock住句柄，同时以share模式pin住了heap。这时该对象句柄可以被其他进程以任何(rnh)模式锁定，但是该句柄对应的heap只能被其他进程以share模式pin，而不能以exclusive模式pin。 第70页/共100页第七十一页，共100页。在转储出来的文件中，我们主要关存储过程lock_test本身在library cache中的变化。在F1中，我们可以看到如图的内容。注意其中的lock为N，pin为S。由于sess #1正在执行lock_test存储过程，需要读取该handle所对应的heap里的内容，因此以null模式lock住句柄，同时以share模式pin住

59、了heap。这时该对象句柄可以被其他进程(jnchng)以任何模式锁定，但是该句柄对应的heap只能被其他进程(jnchng)以share模式pin，而不能以exclusive模式pin。第71页/共100页第七十二页，共100页。我们打开发出编译命令以后生成的F2，找到图中部分。由于sess #2会对存储过程(guchng)lock_test进行编译，因此需要重新刷新该对象的heap中的信息。所以需要以exclusive模式lock住该对象的句柄，同时以exclusive模式pin住该对象的heap。这时，由于当前句柄上存在null模式的lock，因此sess #2申请exclusive的l

60、ock能够成功，但是由于当前该句柄对应的heap上已经存在share的pin，因此申请exclusive的pin时，必须等待，这时体现为sess #2等待library cache pin。 第72页/共100页第七十三页，共100页。我们发出删除命令以后，很明显的，要删除存储过程lock_test，sess #3必须以exclusive模式获得lock_test句柄的lock。而这时该句柄上已经存在了exclusive模式的lock，于是这时sess #3只有等待sess #2所添加的exclusive模式的lock释放以后才能(cinng)继续进行。体现在v$session_wait等相关