Shared pool深入分析及性能调整(一)
(2012-04-06 12:14:25)
1. shared pool的概念
oracle数据库作为一个管理数据的产品,必须能够认出用户所提交的管理命令(通常叫做SQL语句),从而进行响应。认出的过程叫做解析SQL语句的过程,响应的过程叫做执行SQL语句的过程。解析的过程是一个相当复杂的过程,它要考虑各种可能的异常情况,比如SQL语句涉及到的对象不存在、提交的用户没有权限等等。而且,还需要考虑如何执行SQL语句,采用什么方式去获取数据等。解析的最终结果是要产生oracle自己内部的执行计划,从而指导SQL的执行过程。可以看到,解析的过程是一个非常消耗资源的过程。因此,oracle在解析用户提交的SQL语句的过程中,如果对每次出现的新的SQL语句,都按照标准过程完整的从头到尾解析一遍的话,效率太低,尤其随着并发用户数量的增加、数据量的增加,数据库的整体性能将直线下降。
oracle对SQL语句进行了概括和抽象,将SQL语句提炼为两部分,一部分是SQL语句的静态部分,也就是SQL语句本身的关键词、所涉及的表名称以及表的列等。另一部分就是SQL语句的动态部分,也就是SQL语句中的值(即表里的数据)。很明显的,整个数据库中所包含的对象数量是有限的,而其中所包含的数据则是无限的。而正是这无限的数据导致了SQL语句的千变万化,也就是说在数据库运行的过程中,发生的所有SQL语句中,静态部分可以认为数量是有限的,而动态部分则是无限的。而实际上,动态部分对解析的影响相比静态部分对解析的影响来说是微乎其微,也就是说通常情况下,对于相同的静态部分的SQL语句来说,不同的动态部分所产生的解析结果(执行计划)基本都是一样的。这也就为oracle提高解析SQL语句的效率提供了方向。
oracle会将用户提交来的SQL语句都缓存在内存中。每次处理新的一条SQL语句时,都会先在内存中查看是否有相同的SQL语句。如果相同则可以减少最重要的解析工作(也就是生成执行计划),从而节省了大量的资源;反之,如果没有找到相同的SQL语句,则必须重新从头到尾进行完整的解析过程。这部分存放SQL语句的内存就叫做共享池(shared pool)。当然,shared pool里不仅仅是SQL语句,还包括管理shared pool的内存结构以及执行计划、控制信息等等内存结构。
当oracle在shared pool中查找相同的SQL语句的过程中,如果SQL语句使用了绑定变量(bind variable),那么就是比较SQL语句的静态部分,前面我们已经知道,静态部分是有限的,很容易就能够缓存在内存里,从而找到相同的SQL语句的概率很高。如果没有使用绑定变量,则就是比较SQL语句的静态部分和动态部分,而动态部分的变化是无限的,因此这样的SQL语句很难被缓存在shared pool里。毕竟内存是有限的,不可能把所有的动态部分都缓存在shared pool里,即便能够缓存,管理这样一个无限大的shared pool也是不可能完成的任务。不使用绑定变量导致的直接结果就是,找到相同的SQL语句的概率很低,导致必须完整的解析SQL语句,也就导致消耗更多的资源。从这里也可以看出,只有我们使用了绑定变量,才真正遵循了oracle引入shared pool的哲学思想,才能够更有效的利用shared pool。
shared pool的大小由初始化参数shared_pool_size决定。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。
2.shared pool的内存结构
从一个逻辑层面来看,shared pool由library cache和dictionary cache组成。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中。
从一个物理的层面来看,shared pool是由许多内存块组成,这些内存块通常称为chunk。Chunk是shared pool中内存分配的最小单位,一个chunk中的所有内存都是连续的。这些chunk可以分为四类,这四类可以从x$ksmsp(该视图中的每个行都表示shared pool里的一个chunk)的ksmchcls字段看到:
1) free:这种类型的chunk不包含有效的对象,可以不受限制的被分配。
2) recr:意味着recreatable,这种类型的chunks里包含的对象可以在需要的时候被临时移走,并且在需要的时候重新创建。比如对于很多有关共享SQL语句的chunks就是recreatable的。
3) freeabl:这种类型的chunks包含的对象都是曾经被session使用过的,并且随后会被完全或部分释放的。这种类型的chunks不能临时从内存移走,因为它们是在处理过程中间产生的,如果移走的话就无法被重建。
4) perm:意味着permanent,这种类型的chunks包含永久的对象,大型的permanent类型的chunks也可能含有可用空间,这部分可用空间可以在需要的时候释放回shared pool里。
当chunk属于free类型的时候,它既不属于library cache,也不属于dictionary cache。如果该chunk被用于存放SQL游标时,则该chunk进入library cache;同样,如果该chunk被用于存放数据字典的信息时,则该chunk进入dictionary cache。
在shared pool里,可用的chunk(free类型)会被串起来成为可用链表(free lists)或者也可以叫做buckets(一个可用链表也就是一个bucket)。我们可以使用下面的命令将shared pool的内容转储出来看看这些bucket。
alter session set events 'immediate trace name heapdump level 2';
然后打开产生的转储文件,找到“FREE LISTS”部分,可以发现类似如下图二所示的内容。
图二
这是在9i下产生的bucket列表,9i以前的可用chunk的管理方式是不一样的。我们可以看到,可用的chunk链表(也就是bucket)被分成了254个,每个bucket上挂的chunk的尺寸是不一样的,有一个递增的趋势。我们可以看到,每个bucket都有一个size字段,这个size就说明了该bucket上所能链接的可用chunk的大小尺寸。
当一个进程需要shared pool里的一个chunk时,假设当前需要21个单位的空间,则该进程首先到符合所需空间大小的bucket(这里就是bucket 2)上去扫描,以找到一个尺寸最合适的chunk,扫描持续到bucket的最末端,直到找到完全符合尺寸的chunk为止。如果找到的chunk的尺寸比需要的尺寸要大,则该chunk就会被拆分成两个chunk,一个chunk被用来存放数据,而另外一个则成为free类型的chunk,并被挂到当前该bucket上,也就是bucket 2上。然而,如果该bucket上不含有任何需要尺寸的chunk,那么就从下一个非空的bucket上(这里就是bucket 3)获得一个最小的chunk。如果在剩下的所有bucket上都找不到可用的chunk,则需要扫描已经使用的recreatable类型的chunk链表,从该链表上释放一部分的chunk出来,因为只有recreatable类型的chunk才是可以被临时移出内存的。当某个chunk正在被使用时(可能是用户正在使用,也可能是使用了dbms_shared_pool包将对象钉在shared pool里),该chunk是不能被移出内存的。比如某个SQL语句正在执行,那么该SQL语句所对应的游标对象是不能被移出内存的,该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会被锁定很长的时间,这也是8i以前的shared pool latch争用的主要原因。而如果增加shared pool尺寸的话,仅仅是延缓shared pool latch的争用,而到最后,就会因为小的free chunks的数量越来越多,争用也会越来越严重。而到了9i以后,由于大大增加了可用chunk链表(也就是bucket)的数量,同时,每个bucket所管理的可用chunk的尺寸递增的幅度非常小,于是就可以有效的将可用的chunk都均匀的分布在所有的bucket上。这样的结果就是每个bucket上所挂的free类型的chunk都不多,所以在查找可用chunk而持有shared pool latch的时间也可以缩短很多。
对于非常大的对象,oracle会为它们单独从保留区域里分配空间,而不是从这个可用chunk链表中来分配空间。这部分空间的大小尺寸就是由初始化参数shared_pool_reserved_size决定的,缺省为shared_pool_size的5%,这块保留区域与正常的chunk的管理是完全分开的,小的chunk不会进入这块保留区域,而这块保留区域的可用chunk也不会挂在bucket上。这块保留区域的使用情况可以从视图v$shared_pool_reserved中看到,通常来说,该视图的request_misses字段显示了需要从保留区域的可用链表上上获得大的chunk而不能获得的次数,该字段应该尽量为0。
2.1 library cache概述
library cache最主要的功能就是存放用户提交的SQL语句、SQL语句相关的解析树(解析树也就是对SQL语句中所涉及到的所有对象的展现)、执行计划、用户提交的PL/SQL程序块(包括匿名程序块、存储过程、包、函数等)以及它们转换后能够被oracle执行的代码等。为了对这些内存结构进行管理,还存放了很多控制结构,包括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程序块到oracle的shared pool以后,在library cache中生成的一个可执行的对象,这个对象就叫做游标(cursor)。不要把这里的游标与标准SQL(ANSI SQL)的游标混淆起来了,标准SQL的游标是指返回多条记录的SQL形式,需要定义、打开、关闭。下面所说到的游标如无特别说明,都是指library cache中的可执行的对象。游标是可以被所有进程共享的,也就是说如果100个进程都执行相同的SQL语句,那么这100个进程都可以同时使用该SQL语句所产生的游标,从而节省了内存。每个游标都是由library cache中的两个或多个对象所体现的,至少两个对象。一个对象叫做父游标(parent cursor),包含游标的名称以及其他独立于提交用户的信息。从v$sqlarea视图里看到的都是有关父游标的信息;另外一个或多个对象叫做子游标(child cursors),如果SQL文本相同,但是可能提交SQL语句的用户不同,或者用户提交的SQL语句所涉及到的对象为同名词等,都有可能生成不同的子游标。因为这些SQL语句的文本虽然完全一样,但是上下文环境却不一样,因此这样的SQL语句不是一个可执行的对象,必须细化为多个子游标后才能够执行。子游标含有执行计划或者PL/SQL对象的程序代码块等。
在介绍library cache的内部管理机制前,先简单介绍一下所谓的hash算法。
oracle内部在实现管理的过程中大量用到了hash算法。hash算法是为了能够进行快速查找定位所使用一种技术。所谓hash算法,就是根据要查找的值,对该值进行一定的hash算法后得出该值所在的索引号,然后进入到该值应该存在的一列数值列表(可以理解为一个二维数组)里,通过该索引号去找它应该属于哪一个列表。然后再进入所确定的列表里,对其中所含有的值,进行一个一个的比较,从而找到该值。这样就避免了对整个数值列表进行扫描才能找到该值,这种全扫描的方式显然要比hash查找方式低效很多。其中,每个索引号对应的数值列在oracle里都叫做一个hash bucket。
我们来列举一个最简单的hash算法。假设我们的数值列表最多可以有10个元素,也就是有10个hash buckets,每个元素最多可以包含20个数值。则对应的二维数组就是t[10][20]。我们可以定义hash算法为n MOD 10。通过这种算法,可以将所有进入的数据均匀放在10个hash bucket里面,hash bucket编号从0到9。比如,我们把1到100都通过这个hash函数均匀放到这10个hash bucket里,当查找32在哪里时,只要将32 MOD 10等于2,这样就知道可以到2号hash bucket里去找,也就是到t[2][20]里去找,2号hash bucket里有10个数值,逐个比较2号hash bucket里是否存在32就可以了。
library cache就是使用多个hash bucket来管理的,其hash算法当然比我们前面列举的要复杂多了。每
个hash bucket后面都串连着多个句柄(该句柄叫做library cache object handle),这些句柄描述了library cache里的对象的一些属性,包括名称、标记、指向对象所处的内存地址的指针等。可以用下图一来描述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里去。实际上,hash bucket就是通过串连起来的对象句柄才体现出来的,它本身是一个逻辑上的概念,是一个逻辑组,而不像对象是一个具体的实体。oracle根据shared_pool_size所指定的shared pool尺寸自动计算hash buckets的个数,shared pool越大,则可以挂载的对象句柄就越多。
当某个进程需要处理某个对象时,比如处理一条新进入的SQL语句时,它会对该SQL语句应用hash函数算法,以决定其所在的hash bucket的编号,然后进入该hash bucket进行扫描并比较。有可能会发生该对象的句柄存在,但是句柄所指向的对象已经被交换出内存的情况出现。这时对应的对象必须被再次装载(reload)。也可能该对象的句柄都不存在,这时进程必须重新构建一个对象句柄挂到hash bucket上,然后再重新装载对象。SQL语句相关的对象有很多(最直观的就是SQL语句的文本),这些对象都存放在library cache里,它们都通过句柄来访问。可以把library cache理解为一本书,而SQL语句的对象就是书中的页,而句柄就是目录,通过目录可以快速定位到指定内容的页。
对象句柄存放了对象的名称(name)、对象所属的命名空间(namespace)、有关对象的一些标记(比如对象是否为只读、为本地对象还是远程对象、是否被pin在内存中等等)以及有关对象的一些统计信息等。而且,对象句柄中还存放了当前正在lock住和pin住该对象的用户列表、以及当前正在等待lock和pin该对象的用户列表。对象句柄中存放的最重要的内容就是指向Heap 0对象的指针了。Heap 0用来存放与对象有直接关系的一些信息,比如对象类型、对象相关的表(比如依赖表、子表等)、指向对象的其他数据块的指针(这些数据块指向了实际存放SQL文本、PL/SQL代码、错误信息等的大内存块,这些大内存块依次叫做Heap 1、2、3、4等)等信息。
Heap是通过调用服务器进程进行分配的,任何对象都具有heap 0,至于还应该分配哪些其他的heap则是由对象的类型决定的,比如SQL游标具有heap 1和 6,而PL/SQL程序包则具有heap 1、2、3和4。按照heap的使用情况,oracle会在SGA(library cache)、PGA或UGA中分配heap,但是heap 0始终都是在library cache中进行分配的。如果所请求的heap已经在SGA中分配了,则不会在PGA中再次分配heap。Heap是由一个或多个chunk组成的,这些chunk可以是分散的分布在library cache中的,不需要连续分布。
如上图三中所看到的heap 0实际上是指heap 0的句柄,其中包含的对象包括:
1) object type:library cache中的对象类型包括:表、视图、索引、同名词等等。每个对象只能有一个object type,根据object type将对象归类到不同的namespace里。一个object type对应一个namespace,但是一个namespace可能对应多个object type。这样的话,查找一个对象时,只要在该对象所属的namespace中去找就可以了。比较常见的namespace包括:
a) SQL AREA:也可以叫做CRSR,表示shared cursor,存放共享的SQL语句。
b) TABLE/PROCEDURE:存放的object type包括:table、view、sequence、synonym、 procedure的定义、function的定义以及package的定义。
c) BODY:存放procedure的实际代码、function的实际代码以及package的实际代码。
d) TRIGGER:存放的object type为trigger。
e) INDEX:存放的object type为index。
2) object name:对象名称由三部分组成:
a) Schema的名称,对于共享游标(SQL语句或PL/SQL程序块)来说为空。
b) 对象名称。分为两种情况:对于共享游标(SQL语句或PL/SQL程序块)来说,其对象名称就是SQL的语句本身;而对于其他对象(比如表、视图、索引等)就是其在数据字典中的名称。
c) Database link的名称。这是可选的,如果是本地对象,则为空。
这样,对象的名称的格式为:SCHEMA.NAME@DBLINK。比如,可以为hr.employees@apac.com,也可以为hr.employees等。
3) flags:flags主要用来描述对象是否已经被锁定。对象具有三种类型的flag:
a) public flag:表示对象上没有锁定(pin)或者latch。
b) status flag:表示对象上存在锁定(pin),说明对象正在被创建或删除或修改等。
c) specitial flag:表示对象上存在library cache latch。
4) tables:对每个对象,都会维护以下一串tables中的若干个:
a) dependency table:含有当前对象所依赖的其他对象。比如一个视图可能会依赖其组成的多个表、一个存储过程可能依赖其中所调用的其他存储过程、一个游标可能依赖其中所涉及到的多个表等。Dependency table中的每个条目都指向一块物理内存,该物理内存中含有当前对象所依赖的对象的句柄。
b) child table:含有当前对象的子对象,只有游标具有child table。Child table中的每个条目都指向一个可执行的SQL命令所对应的句柄。
c) translation table:包含当前对象所引用的名称是如何解释为oracle底层对象的名称,只有游标具有translation table。
d) authorization table:包含该对象上所对应的权限,一个条目对应一个权限。
e) access table:对于dependency table中的每一个条目,都会在access table中存在对应的一个或多个条目。比如,假设对象A依赖对象B,那么在A的dependency table和access table中都会存在一个条目指向B。位于access table中的指向B的条目说明了对B具有什么样的访问类型,从而也就说明了用户要执行A则必须具有对B的权限。
f) read-only dependency table:类似于dependency table,但是存放只读的对象。
g) schema name table:包含authorization table中的条目所属的schema。
5) data blocks:对象的其他信息会存放在不同的heap中,为了找到这些heap,会在heap 0中存放多个(最多16个,但是这16个data block不会都用到)data blocks结构,每个data block含有指向这些实际heap内存块的指针。
除了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 8–11:根据对象的不同而使用的子heap。
我们可以通过查询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;
2.2 转储library cache
Shared pool深入分析及性能调整(一)
(2012-04-06 12:14:25)1) free:这种类型的chunk不包含有效的对象,可以不受限制的被分配。
2) recr:意味着recreatable,这种类型的chunks里包含的对象可以在需要的时候被临时移走,并且在需要的时候重新创建。比如对于很多有关共享SQL语句的chunks就是recreatable的。
3) freeabl:这种类型的chunks包含的对象都是曾经被session使用过的,并且随后会被完全或部分释放的。这种类型的chunks不能临时从内存移走,因为它们是在处理过程中间产生的,如果移走的话就无法被重建。
4) perm:意味着permanent,这种类型的chunks包含永久的对象,大型的permanent类型的chunks也可能含有可用空间,这部分可用空间可以在需要的时候释放回shared pool里。
在shared pool里,可用的chunk(free类型)会被串起来成为可用链表(free lists)或者也可以叫做buckets(一个可用链表也就是一个bucket)。我们可以使用下面的命令将shared pool的内容转储出来看看这些bucket。
alter session set events 'immediate trace name heapdump level 2';
然后打开产生的转储文件,找到“FREE LISTS”部分,可以发现类似如下图二所示的内容。
个hash bucket后面都串连着多个句柄(该句柄叫做library cache object handle),这些句柄描述了library cache里的对象的一些属性,包括名称、标记、指向对象所处的内存地址的指针等。可以用下图一来描述library cache的整体结构。
1) object type:library cache中的对象类型包括:表、视图、索引、同名词等等。每个对象只能有一个object type,根据object type将对象归类到不同的namespace里。一个object type对应一个namespace,但是一个namespace可能对应多个object type。这样的话,查找一个对象时,只要在该对象所属的namespace中去找就可以了。比较常见的namespace包括:
a) SQL AREA:也可以叫做CRSR,表示shared cursor,存放共享的SQL语句。
b) TABLE/PROCEDURE:存放的object type包括:table、view、sequence、synonym、 procedure的定义、function的定义以及package的定义。
c) BODY:存放procedure的实际代码、function的实际代码以及package的实际代码。
d) TRIGGER:存放的object type为trigger。
e) INDEX:存放的object type为index。
2) object name:对象名称由三部分组成:
a) Schema的名称,对于共享游标(SQL语句或PL/SQL程序块)来说为空。
b) 对象名称。分为两种情况:对于共享游标(SQL语句或PL/SQL程序块)来说,其对象名称就是SQL的语句本身;而对于其他对象(比如表、视图、索引等)就是其在数据字典中的名称。
c) Database link的名称。这是可选的,如果是本地对象,则为空。
这样,对象的名称的格式为:SCHEMA.NAME@DBLINK。比如,可以为hr.employees@apac.com,也可以为hr.employees等。
3) flags:flags主要用来描述对象是否已经被锁定。对象具有三种类型的flag:
a) public flag:表示对象上没有锁定(pin)或者latch。
b) status flag:表示对象上存在锁定(pin),说明对象正在被创建或删除或修改等。
c) specitial flag:表示对象上存在library cache latch。
4) tables:对每个对象,都会维护以下一串tables中的若干个:
a) dependency table:含有当前对象所依赖的其他对象。比如一个视图可能会依赖其组成的多个表、一个存储过程可能依赖其中所调用的其他存储过程、一个游标可能依赖其中所涉及到的多个表等。Dependency table中的每个条目都指向一块物理内存,该物理内存中含有当前对象所依赖的对象的句柄。
b) child table:含有当前对象的子对象,只有游标具有child table。Child table中的每个条目都指向一个可执行的SQL命令所对应的句柄。
c) translation table:包含当前对象所引用的名称是如何解释为oracle底层对象的名称,只有游标具有translation table。
d) authorization table:包含该对象上所对应的权限,一个条目对应一个权限。
e) access table:对于dependency table中的每一个条目,都会在access table中存在对应的一个或多个条目。比如,假设对象A依赖对象B,那么在A的dependency table和access table中都会存在一个条目指向B。位于access table中的指向B的条目说明了对B具有什么样的访问类型,从而也就说明了用户要执行A则必须具有对B的权限。
f) read-only dependency table:类似于dependency table,但是存放只读的对象。
g) schema name table:包含authorization table中的条目所属的schema。
5) data blocks:对象的其他信息会存放在不同的heap中,为了找到这些heap,会在heap 0中存放多个(最多16个,但是这16个data block不会都用到)data blocks结构,每个data block含有指向这些实际heap内存块的指针。
除了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 8–11:根据对象的不同而使用的子heap。
我们可以通过查询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;
2.2 转储library cache
Shared pool深入分析及性能调整(二)
(2012-04-06 12:39:40) 2.2 转储library cache
oracle提供了命令可以对library cache中的内容进行转储。于是我们可以对library cache进行转储,从而对上面所说的library cache的内容进行验证。
ALTER SESSION SET EVENTS 'immediate trace name library_cache level N';
这里的N可以取的值分别为:
1 转储library cache的统计信息
2 转储hash表的汇总信息
4 转储library cache object的基本信息
8 转储library cache object的详细信息
16 转储heap size的信息
32 转储heap的详细信息
在测试之前,我们先创建一个测试表,然后再显示该表的数据。从而在library cache中放入一些数据。
SQL> create table sharedpool_test as select * from dba_objects where rownum<10;
SQL> select object_id,object_name from sharedpool_test;
以level 1转储整个library cache。
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 1';
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 563 0.815 2717 0.916 15 0
TABL/PRCD/TYPE 403 0.730 568 0.653 0 0
BODY/TYBD 2 0.000 2 0.000 0 0
……………………
打开4#文件,然后直接查找“select object_id,object_name from sharedpool_test”,因为我们前面说到过,对于SQL语句来说,整个SQL语句的文本就是library cache object的名称。于是,我们可以发现类似下图四所示的内容:
SQL> select sql_text from v$sql where hash_value=to_number('541cf4c2','xxxxxxxx');
SQL_TEXT
--------------------------------------------------------------------------------
select object_id,object_name from sharedpool_test
我们可以看到类似下图六所示的内容。从name列中可以看到,该对象正是sharedpool_test表,同时该表所在的schema为COST。而且从type为TABL也可以看到,对象sharedpool_test是一个表。
转储dictionary cache
这里的N可以取的值分别为:
1 转储dictionary cache的统计信息 ;
2 转储hash表的汇总信息 ;
8 转储dictionary cache中的对象的结构信息;
ROW CACHE HASH TABLE: cid=0 ht=66BD90B0 size=32
…………
ROW CACHE HASH TABLE: cid=1 ht=66BD78B0 size=256
…………
ROW CACHE HASH TABLE: cid=22 ht=66DA5590 size=512
…………
3.1解析SQL语句的过程
为了将用户写的可读的SQL文本转化为oracle认识的且可执行的语句,这个过程就叫做解析过程。
解析分为硬解析和软解析。当一句SQL第一次被执行时必须进行硬解析。
当客户端发出一条SQL语句(也可以是一个存储过程或者一个匿名PL/SQL块)进入shared pool时
(注意,我们从前面已经知道,oracle对这些SQL不叫做SQL语句,而是称为游标(cursor)。因为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。然后,进行硬解析过程。硬解析包括以下几个步骤:
1) 对SQL语句进行语法检查,看是否有语法错误。比如没有写from等。如果有,则退出解析过程。
2) 到数据字典里校验SQL语句涉及的对象和列是否都存在。如果不存在,则退出解析过程。
3) 将对象进行名称转换。比如将同名词翻译成实际的对象等。如果转换失败,则退出解析过程。
4) 检查游标里用户是否具有访问SQL语句里所引用的对象的权限。如果没有权限,则退出解析过程。
5) 通过优化器创建一个最优的执行计划。这一步是最消耗CPU资源的。
6) 将该游标所产生的执行计划、SQL文本等装载进library cache的若干个heap中。
在硬解析的过程中,进程会一直持有library cach latch,直到硬解析结束。硬解析结束以后,会为该SQL产生两个游标,一个是父游标,另一个是子游标。父游标里主要包含两种信息:SQL文本以及优化目标(optimizer goal)。父游标在第一次打开时被锁定,直到其他所有的session都关闭该游标后才被解锁。当父游标被锁定的时候是不能被交换出library cache的,只有在解锁以后才能被交换出library cache,这时该父游标对应的所有子游标也被交换出library cache。子游标包括游标所有的信息,比如具体的执行计划、绑定变量等。前面图四中看到的CHILDREN部分就是子游标所对应的handle的信息。子游标随时可以被交换出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语句完全相同,但是所引用的对象属于不同的schema时,都会创建一个新的子游标。
如果在bucket中找到了该SQL语句,则说明该SQL语句以前运行过,于是进行软解析。软解析是相对于硬解析而言的,如果解析过程中,可以从硬解析的步骤中去掉一个或多个的话,这样的解析就是软解析。软解析分为以下三种类型。
1) 第一种是某个session发出的SQL语句与library cache里其他session发出的SQL语句一致。这时,该解析过程中可以去掉硬解析中的5和6这两步,但是仍然要进行硬解析过程中的2、3、4步骤:也就是表名和列名检查、名称转换和权限检查。
2) 第二种是某个session发出的SQL语句与library cache里该同一个session之前发出的SQL语句一致。这时,该解析过程中可以去掉硬解析中的2、3、5和6这四步,但是仍然要进行权限检查,因为可能通过grant改变了该session用户的权限。
3) 第三种是当设置了初始化参数session_cached_cursors时,当某个session对相同的cursor进行第三次访问时,将在该session的PGA里创建一个标记,并且该游标即使已经被关闭也不会从library cache中交换出去。这样,该session以后再执行相同的SQL语句时,将跳过硬解析的所有步骤。这种情况下,是最高效的解析方式,但是会消耗很大的内存。
我们先来举一个例子说明如果在解析过程中发生语法或语义错误时,在shared pool中是怎样体现的。
SQL> select object_type fromm sharedpool_test111;
ORA-00942: 表或视图不存在
然后我们以level 16转储library cache,并打开转储文件,找到相应的部分,如下图八所示。可以看到,
该SQL语句在语法上是错误的(from写成了fromm),oracle仍然在shared pool中为其分配了一个chunk,然后该chunk进入library cache,并在library cache中分配了一个bucket,同时也生成了heap 0,但是该heap 0中不存在相应的一些如dependency table等table的部分,以及data block的部分。我看到有些资料上说SQL语句是先进行语法分析,如果通过语法分析以后,则应用hash函数生成hash值,然后再去shared pool中分配chunk。实际上从这个实例已经可以看出,这个说法是错误的。oracle始终都是先对SQL生成hash值(不论该SQL语法上是否正确),再根据hash值到对应的可用chunk链表(也就是bucket)里分配chunk,然后进入语法解析等解析过程。
图八
我们再举一个例子来说明解析正确的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
然后,我们以level 16来转储library cache。可以看到如下图九所示的内容。很明显的看到,子游标的
部分包含两条记录,这也就说明该SQL语句产生了两个子游标。虽然我们从SQL文本上看,前后两次执行的SQL语句是一样的。只有绑定变量的类型发生了改变,第一次是number型,而第二次是varchar2型。可正是这数据类型的变化导致了该SQL语句的执行计划不能得到共享,从而产生了两个子游标。这时,我们根据子游标的两个handle:6757f358和674440fc找到对应的heap 0的话,就可以看到这两个heap 0中所记录的heap 6是两个完全不同的内存块,这也说明前后两次执行SQL并没有真正得到共享。
图九
我们还可以根据该SQL的hash值(f390fb6f)来看看动态性能视图里是如何表现的。
SQL> select to_number('f390fb6f','xxxxxxxx') from dual;
TO_NUMBER('F390FB6F','XXXXXXXX
------------------------------
4086365039
SQL> select sql_text,version_count from v$sqlarea where hash_value=4086365039;
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 hash_value=4086365039;
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参数等)等。
oracle提供了多个library cache latch(这样,每个library cache latch都称为子latch)来保护library cache中的bucket。这些子latch的数量由一个隐藏参数决定:_kgl_latch_count。该参数缺省值为大于等于系统中CPU个数的最小的素数。比如在一个具有4个CPU的生产环境中,library cache latch的个数为5,如下所示。但是oracle内部(9i版本)规定了library cache latch的最大个数为67,即便将这个隐藏参数设置为100,library cache latch的数量也还是67个。
SQL> select x.ksppinm, y.ksppstvl, x.ksppdesc
2 from x$ksppi x , x$ksppcv y
3 where x.indx = y.indx
4 and x.ksppinm like '\_%' escape '\'
5 and ksppinm like '%kgl_latch_count%'
6 ;
KSPPINM KSPPSTVL KSPPDESC
-------------------- ---------- ----------------------------------------
_kgl_latch_count 5 number of library cache latches
具体到每个bucket应该由哪个子latch来管理,则是通过下面这个函数来确定的。
latch号=mod(bucket号,latch的数量)
假如还是按照上面的例子,对于bucket 64367来说,假设当前系统具有37个library cache latch,那么会使用24(mod(64367,37)=24)号latch来保护挂在该bucket上的句柄。正是由于这样的算法,可能会导致所有的子latch不能在library cache里的整个bucket链条上均匀分布,有可能出现某个或某几个子latch非常繁忙,而有些子latch则非常空闲。至于如何判断以及解决,可以见下面shared pool的优化部分。
我们来做两个测试,分别来模拟一下lock和pin。来看看lock和pin是如何控制library cache里的对象的。试验的思路很简单,第一,打开一个session(sess #1)创建一个存储过程,该过程只做一件事情,就是通过调用dbms_lock.sleep进行等待。并在sess #1中调用该存储过程;第二,打开第二个session(sess #2),重新编译该存储过程;第三,打开第三个session(sess #3),删除该存储过程;第四,打开第四个session(sess #4)进行监控。根据前面对lock和pin的描述,我们可以预见,sess #2将等待library cache pin。而sess #3会等待library cache lock。
试验过程如下,在试验的过程中,不断以level 16转储library cache以更深入的观察lock和pin的变化,以下按照时间顺序排列:
sess #1
SQL> create or replace procedure lock_test
2 is
3 begin
4 sys.dbms_lock.sleep(5000);
5 end;
6 /
SQL> exec lock_test;
sess #4,转储出来的文件编号为F1。
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';
sess #2
SQL> select sid from v$mystat where rownum=1;
SID
----------
9
SQL> alter procedure lock_test compile; --这时该命令停住了。
sess #4,转储出来的文件编号为F2。
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';
sess #3
SQL> select sid from v$mystat where rownum=1;
SID
----------
10
SQL> drop procedure lock_test; --这时该命令也停住了
sess #4,转储出来的文件编号为F3。
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';
SQL> select sid,event from v$session_wait where sid in(9,10);
SID EVENT
---------- ----------------------------------------------------------------
9 library cache pin
10 library cache lock
从监控的结果看到,正如我们所预料的,编译存储过程的sess #2(sid为9)正在等待library cache pin,而删除存储过程的sess #3(sid为10)正在等待library cache lock。在转储出来的文件中,我们主要关存储过程lock_test本身在library cache中的变化。在F1中,我们可以看到如下图十的内容。注意其中的lock为N,pin为S。由于sess #1正在执行lock_test存储过程,需要读取该handle所对应的heap里的内容,因此以null模式lock住句柄,同时以share模式pin住了heap。这时该对象句柄可以被其他进程以任何模式锁定,但是该句柄对应的heap只能被其他进程以share模式pin,而不能以exclusive模式pin。
图十
我们打开发出编译命令以后生成的F2,找到与图十同样的部分,如下图十一所示。由于sess #2会对存储过程lock_test进行编译,因此需要重新刷新该对象的heap中的信息。所以需要以exclusive模式lock住该对象的句柄,同时以exclusive模式pin住该对象的heap。这时,由于当前句柄上存在null模式的lock,因此sess #2申请exclusive的lock能够成功,但是由于当前该句柄对应的heap上已经存在share的pin,因此申请exclusive的pin时,必须等待,这时体现为sess #2等待library cache pin。
至于如何诊断以及解决这两个等待事件的话,可以见下面shared pool的优化部分。
Shared pool深入分析及性能调整(三)
(2012-04-06 13:44:07)4.1 共享SQL语句
该存储过程有两个参数,第一个参数表示要钉在内存里的对象的名字,第二个参数表示要钉住的对象的类型,缺省为P,表示存储过程、包、函数,如果要钉住某个SQL语句,则需要设置该参数为C。对于存储过程、包、函数、触发器以及序列(sequence)等对象,在调用该存储过程时,使用名字对其进行引用。比如:DBMS_SHARED_POOL.KEEP('SALES.PKG_SALES'),这样就将位于SALES下的包PKG_SALES给钉在内存里了。;而对于某条单独的SQL语句来说,则需要使用地址和hash值对其进行引用,地址和hash值可以从v$sqlarea里的address和hash_value列获得,比如对于我们前面测试的SQL语句来说,address就是图二中的handle值,也就是'6758CDBC,hash_value就是图二中的541cf4c2,转换为十进制就是1411183810,则我们将其钉在内存里:DBMS_SHARED_POOL.KEEP('6758CDBC,1411183810',’C’)。如果我们要取消钉住的话,则调用DBMS_SHARED_POOL.UNKEEP即可,该过程的参数与KEEP相同。
4.4.1 library cache latch
还是按照上面我们模拟这两个等待事件的例子。这时我们查看v$session_wait,发现出现这两个等待。
同样,我们可以使用下面的SQL语句来显示哪个session正持有lock或者pin,而哪个session又在等待lock或者pin。
注意,我们不能获得正在等待lock的session所发出的SQL语句,因为该session都还没
有获得library cache对象句柄的控制权,就更谈不上将SQL语句写入heap里了,所以我们无法获得该session所发出的SQL语句。
因此,我们只能显示哪个session正在等待lock。
4 w.kgllktype lock_or_pin, 5 w.kgllkhdl address,
6 decode(h.kgllkmod, 0 , ' None ' , 1 , ' Null ' , 2 , ' Share ' , 3 , ' Exclusive ' , ' Unknown ' )
mode_held, 7 decode(w.kgllkreq, 0 , ' None ' , 1 , ' Null ' , 2 , ' Share ' , 3 , ' Exclusive ' , ' Unknown ' )
mode_requested 8 from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 9
where (((h.kgllkmod != 0 ) and (h.kgllkmod != 1 ) 10 and ((h.kgllkreq = 0 ) or (h.kgllkreq = 1 ))) 11
and (((w.kgllkmod = 0 ) or (w.kgllkmod = 1 )) 12 and ((w.kgllkreq != 0 ) and (w.kgllkreq != 1 )))) 13
and w.kgllktype = h.kgllktype 14 and w.kgllkhdl = h.kgllkhdl 15 and w.kgllkuse = w1.saddr 16
and h.kgllkuse = h1.saddr; WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN ADDRESS MODE_HELD MODE_REQUESTED
-- ------------- --------------- ----------- -------- --------- --------------
9 8 Pin 675C3078 Share Exclusive 10 9 Lock 675C3078 Exclusive Exclusive
SQL> select session_id,owner,type,mode_held,mode_requested
2 from dba_ddl_locks where owner='COST' and name='LOCK_TEST';
SESSION_ID OWNER TYPE MODE_HELD MODE_REQUESTED
---------- ------------ -------------------- --------- --------------
8 COST Table/Procedure/Type Null None
9 COST Table/Procedure/Type Exclusive None
10 COST Table/Procedure/Type None Exclusive