LibraryCache结构及内存管理[final]

建站服务器

Library cache是Shared pool的一部分，它几乎是Oracle内存结构中最复杂的一部分.

一 ， Library cache存放什么(存放的信息单元都叫做对象) ?

Library存放的信息单元都叫做对象，这些对象可以分为两类：

1. 存储对象
2. 过渡对象(游标Cursor,这里的游标指生成的可执行的对象， 运行相同SQL的多个进程可以共享该SQL产生的游标，节省内存。)

A. 用户提交的SQL
B. SQL语句相关的解析树
C. 执行计划
D. 用户提交的PL/SQL程序块(包括匿名程序块，procedure，packages，function等)
E. PL/SQL对象依赖的table,index,view等对象
F. 控制结构：lock,pin,dependency table 等

备注： LIBRARY CACHE的对象可以在V$DB_OBJECT_CACHE中找到，这个视图基于X$KGLOB。

二， SQL的解析及游标

SQL在解析阶段主要完成以下步骤 ：

1. 将父游标保存到Library Cache中 (父游标的概念参考后面的说明，这一步其实不包含
在解析过程中)

先将SQL转化为ASCII数值，然后对这些ASCII数值进行hash函数的运算生成hash value (10g还有唯一的SQL_ID)，运算后匹配library cache里的hash bucket (hash bucket简单来 讲是使用hash算法将进入library cache中的SQL 通过一个类似二维数组来表示，比如t[3][6], 每次查找时通过hash算法算出符合的bucket号，找到对应bucket，比如前面t[3][6]中的3号， 每个bucket后面会挂载所有满足hash算法的object handle, object handle会存储SQL名称 [对于SQL而言就是SQL文本]， namespace等) ，再匹配hash bucket上面的handle，也就是句柄， 如果匹配成功，那么去找子游标 (子游标的概念参考后面的说明，找到子游标那么直接执行， 如果子游标被交换出库缓存, 那么通过父游标信息重新构造reload一个子游标) , 如果不成功, 即不存在共享的父游标，就会在库缓存中分配一些内存(Chunk)，并将新产生的父游标保存进 库缓存，生成一个handle(对象句柄)，挂载hash bucket上。接下来进行硬解析。

2 . 包含VPD(虚拟专用数据库)的约束条件
虚拟专用数据库VPD详细信息见后备注。比如对于HR工资的查询，select salary from emp ; 如果设置VPD, 会隐含加入每个用户各自的账号，只能查看自己的，句子会变成类似： select salary from emp where name=\'susan\' ;

3. 对SQL语句进行文法检查，如果存在文法错误，则退出解析过程
确认sql语句是否正确书写(比如没有写from,select拼写错误等)，

4. 到数据字典校验SQL涉及的对象和列是否存在，不存在就退出解析过程，这个过程会加载 Dictionary Cache .

5. 将对象进行名称转换，比如将synonym 转换为实际的对象等。若转换失败则退出解析。

6. 检查发出SQL语句的用户(一般指连接用户)是否有访问SQL中引用的对象的权限，若没有则 退出解析。

7. 逻辑优化 -- 用一定的转换技巧(Transforming Queries,查询转换器)，生成语法语义上等同 的新的SQL语句。查询语句的形式会影响所产生的执行计划，查询转换器的作用就是改变查询语 句的形式以产生较好的执行计划。四种常见转换技术：视图合并（View Merging）、谓词推进 （Predicate Pushing）、非嵌套子查询（Subquery Unnesting）和物化视图的查询重写（Query
Rewrite with Materialized Views）。

详细可以参考以下文档及后面备注 ：　　
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i37745

8. 物理优化 -- 首先，生成与每个逻辑优化产生的sql语句有关的执行计划, 接着, 根据 数据字典找到相关的统计信息或者动态收集的统计信息，计算出与执行计划相关的开销。最后， 选中低开销的执行计划。涉及大量数学运算，所以这一步最消耗CPU资源。 子游标会在这一步 生成 ，执行计划,绑定变量及执行环境是子游标中的主要内容。　

9. 将子游标load到库缓存 -- 首先分配内存(Chunk)，然后将共享子游标存储进去，最后将它与父游标 关联，与子游标有关的关键内容是执行计划和执行环境，一旦保存到库缓存，父游标与子游标就可以 分别通过视图v$sqlarea和v$sql被具体化。

v$sql中通过child_number,hash_value,address来确定一个子游标，而v$sqlarea通过address和hash_value可以确定一个父游标； 而从10g过后，通过sql_id就能确定一个游标； 查找是否有共享的父游标
和硬解析是两个不同的过程，父游标共享与否和硬解析没有直接关系，子游标的共享状态决定软硬解析 。

备注：
----------------------------------------------------------------------------------

Namespace:
使用hash算法对SQL语句对应的ASCII进行运算时，传入函数的参数有SQL语句名称及namespace(可通过v$librarycache查询到各种不同的namespace，对于SQL而言值为SQL AREA) .

VPD虚拟专用数据库的详细信息：
http://www.oracle.com/technology/global/cn/pub/articles/10gdba/week14_10gdba.html

SQL Parsing Flow Diagram [ID 32895.1]
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=32895.1

解析过程中的逻辑优化部分的查询转换器 ---

从Oracle 8i开始就有四种转换技术：视图合并（View Merging）、谓词推进（Predicate Pushing）、非嵌套子查询（Subquery Unnesting）和物化视图的查询重写（Query Rewrite with Materialized Views）。

视图合并：如果SQL语句中含有视图，经分析后会把视图放在独立的“视图查询块”中，每个视图会产生一个视图子计划，当为整个语句产生执行计划时，视图子计划会被直接拿来使用而不会照顾到语句的整体性，这样就很容易导致不良执行计划的生成。视图合并就是为了去掉“视图查询块”，将视图合并到一个整体的查询块中，这样就不会有视图子计划产生，执行计划的优良性得到提升。

谓词推进：不是所有的视图都能够被合并，对于那些不能被合并的视图Oracle会将相应的谓词推进到视图查询块中，这些谓词通常是可索引的或者是过滤性较强的。

非嵌套子查询：子查询和视图一样也是被放于独立查询块中的，查询转换器会将绝大多数子查询转换为连接从而合并为同一查询块，少量不能被转换为连接的子查询，会将它们的子计划安照一个高效的方式排列。

物化视图的查询重写：当query_rewrite_enabled=true时，查询转换器寻找与该查询语句相关联的物化视图，并用物化视图改写该查询语句。

----------------------------------------------------------------------------------

三， 父游标与子游标

部分内容参考：
http://www.oraclefans.cn/forum/showblog.jsp?rootid=5553
http://www.itpub.net/thread-1362874-1-1.html (问题)

在硬解析的过程中，进程会一直持有library cache latch，直到硬解析结束。硬解析过程会为该SQL产生两个游标，一个是父游标，另一个是子游标。

父游标和子游标相关问题的讨论：
http://www.itpub.net/thread-1362874-1-1.html

父游标(parent cursor) ---

当用户A发出一条SQL后，Oracle会根据SQL文本内容生成hash value(10g还有唯一的SQL_ID)，对比库缓存中的hash value, 以便能够快速找到Shared pool中已经存在的相同SQL。如果找不到，则Oracle会为这个SQL创建一个parent cursor和一个child cursor。

父游标里主要包含两种信息：SQL文本以及优化目标（optimizer goal）。从v$sqlarea视图中看到的都是有关父游标的信息，v$sqlarea中的每一行代表了一个parent cursor, 比如SQL文本对应字段SQL_TEXT, 优化目标（optimizer goal）对应后面的RUNTIME_MEM，EXECUTIONS，CPU_TIME, DISK_READS, BUFFER_GETS 等等 。

父游标在第一次打开时被锁定，直到其他所有的session都关闭游标后才被解锁。当父游标被锁定的时候它是不能被交换出library cache的，只有在解锁以后才能被交换出library cache，这时该父游标对应的所有子游标也被交换出library cache。

一个CURSOR的结构包括PARENT CURSOR和CHILD CURSOR，每个CURSOR至少包含一个CHILD CURSOR。这个CURSOR通过HASHVALUE来区别，每个PARENT CURSOR至少包含一个HEAP0，里面存放环境、状态和绑定变量的信息。每个PARENT CURSOR至少有一个CHILD CURSOR 。handle其实就是存放的父游标，真正的执行计划是存放在子游标上的，也就是heap6上。

PARENT CURSOR是由一个handle和一个object组成，可以通过在库缓存hash table中的hash value查找到handle, 而object 包含了指向它的每个 child cursor的指针 。

V$SQLAREA中version_count看到一个父游标对应多少个子游标，对应关系是靠hash_value及adress(SQL文本的地址)联系的，V$SQL中相同SQL文本的不同子游标，hash_value及adress是相同的，但是子地址child_address却不一样，这里的子地址实际就是子游标所对应的Heap0的句柄(handel)。 V$SQL中的hild_number编号从0开始，同样SQL文本(父游标共享)不同的child_number对应不同的child_address 。Oracle10g版本下V$SQL中有有3个字段bind_data,
optimizer_env , optimizer_env_hash_value 应该是用于决定取哪个子游标的字段。不过9i 中v$sql中没有这些字段，具体如何查找到子游标的参考讨论 ：
http://www.itpub.net/thread-1362874-1-1.html

子游标 (Child Cursors) ---

子游标包括游标所有的信息，比如具体的执行计划、绑定变量等。子游标随时可以被交换出library cache，当子游标被交换出library cache时，oracle可以利用父游标的信息重新构建出一个子游标来，这个过程叫reload。 子游标具体的个数可以从v$sqlarea的version_count字段体现出来。而每个具体的子游标则全都在v$sql里体现。可以使用下面的方式来确定reload的比率：
SELECT 100*sum(reloads)/sum(pins) Reload_Ratio FROM v$librarycache;

一个父游标可以对应多个子游标。当具体的绑定变量的值与上次的绑定变量的值有较大差异（比如上次执行的绑定变量的值的长度是6位，而这次执行的绑定变量的值的长度是200 位）时或者当SQL语句完全相同，但是所引用的对象属于不同的schema时，或执行SQL的环境不同(优化器模式不一样)， 都会创建一个新的子游标。

关于子游标新建和reload 的区别，如果所有版本的子游标都不能被共享，那么会创建一个新的子游标 (new create) ，这种情况指的就是 environment 或bind var 长度不一样等 导致的情况 ；而reload 指的是父游标可以共享， 同样的子游标 (environment 或bind var 等都一样)原来已经存在于library cache, 因为某种原因被aged out出去，而现在需要它了，要重新装载回来。

每个child cursor也是由一个handle和一个object构成. child object 又是由两个heap即heap0及heap6 组成，其中Heap0包含所有的针对SQL语句每个版本的标示信息(比如Environment, Statistics, Bind Variables等，比如绑定变量值不同的长度可能导致sql解析版本的不同; Child cursors are also called versions. )，Heap6包含执行计划 。

Child cursor包含了SQL的metadata，也就是使这个SQL可以执行的所有相关信息，如OBJECT和权限，优化器设置，执行计划等。v$sql中的每一行表示了一个child cursor，根据hash value和address与parent cursor 关联。child cursor有自己的address，即V$SQL.CHILD_ADDRESS。如果有多个child cursor，则表示parent cursor有多个版本，v$sqlarea中的version_count字段就会纪录下来。

每种类型的dml语句都需要如下阶段：

Create a Cursor 创建游标
Parse the Statement 解析语句
Bind Any Variables 绑定变量
Run the Statement 运行语句
Close the Cursor 关闭游标

四， 硬解析与软解析，软软解析，RELOAD

硬解析 ---
首先了解父游标共享的条件 ：

1. 字符级的比较, 要求文本完全一致
SQL语句必须完全相同，select * from emp; 和select * from emp; 是不一样的。不能共享。

2. 必须使用相同名称的绑定变量(其实就是文本字符不一致)，比如
select age from pepoo where name=:var_p
select age from pepoo where name=:var_f
(即使在运行的时候赋予这两个不同名称的绑定变量一样的值，也不能通向父游标)

从SQL解析过程可以看出，父游标是否共享是发生在硬解析之前，所以父游标是否能共享和硬解析没有关系，不过父游标不能共享则一定是硬解析，硬解析的整个过程见上面的第二节 。但是父游标共享了不一定就是软解析。能否避免硬解析，还要看子游标 。

---------------------------------------------------------
父游标共享已经讨论过，这里讨论子游标共享的几种情况 (假设CURSOR_SHARING=EXACT )：

第一种是A发出的原始SQL语句和其他用户B之前发出的SQL文本一模一样，父亲游标可以共享，但是因为优化器环境设置不同( OPTIMIZER_MISMATCH)， 绑定变量的值的长度在第二次执行的时候发生显著的变化(BIND_MISMATCH) , 授权关系不匹配(AUTH_CHECK_MISMATCH ) 或者 基础物件转换不匹配(TRANSLATION_MISMATCH) 等导致子游标不能共享，需要新生成一个子游标 。 这与SQL共享(即游标共享)是有关系的 。 这种情况下的执行计划可能不同，也可能相同(我们可以通过plan_hash_value看出);
这里因为除SQL TEXT之外的其他条件不符合，所以reload 也不会发生 。子游标就是new create and load，应该是硬解析 。具体的mismatch可以查询 V$SQL_SHARED_CURSOR . ；

例如：

--窗口1执行

sys/SYS>alter session set optimizer_mode=all_rows;
Session altered.

sys/SYS>select * from tt;
no rows selected

sys/SYS>alter session set optimizer_mode=first_rows_10;
Session altered.

sys/SYS>select * from tt;
no rows selected

--窗口2执行

sys/SYS>select hash_value,sql_text,executions,VERSION_COUNT from
v$sqlarea where sql_text like \'%from tt\';

HASH_VALUE SQL_TEXT EXECUTIONS VERSION_COUNT
---------- ---------------------------------------- ---------- -------------
3762890390 select * from tt 2 2

sys/SYS>select HASH_VALUE,child_number,sql_text from v$sql where sql_text like \'%from tt\';

HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ----------------------------------------
3762890390 0 select * from tt
3762890390 1 select * from tt

可以看到，SQL文本是完全相同的，所以两个子游标共享了一个父游标。但是由于optimizer_mode的不同，所以生成了2个子游标。如果产生了子游标，那么说明肯定产生了某种mismatch，如何来查看是何种原因产生了mismatch，要通过v$sql_shared_cursor。

sys/SYS>select kglhdpar, address,auth_check_mismatch, translation_mismatch,OPTIMIZER_MISMATCH
2 from v$sql_shared_cursor
3 where kglhdpar in
4 ( select address
5 from v$sql
6 where sql_text like \'%from tt\');

KGLHDPAR ADDRESS A T O
-------- -------- - - -
89BB8948 89BB83CC N N N
89BB8948 89BB5E78 N N Y　

可以看到OPTIMIZER_MISMATCH列第二行的值为Y，这说明了正是由于optimizer_mode的不同而产生了子游标。最后，父游标和子游标的意义何在？其实一切都是为了共享。以减少再次解析的资源浪费。

第二种是A发出的原始SQL语句和与在shared pool 中的SQL文本一模一样，父游标可以共享，子游标不存在所谓的mismatch , 目前也存在于库缓存中，可以共享子游标，那么应该是软解析 。

第三种，父游标可以共享， 不同的是，子游标本来是可以共享的，但是目前被交换出(aged out)库缓存，这时会reload 子游标，也就是利用父游标的信息重新构造出一个子游标 ，Oracle已经知道应该共享哪个子游标，只是它暂时被交换出库缓存， reload应该不属于硬解析，是否属于软解析呢 ？虽然被aged out 出库缓存，但是可能某个地方会记录这个子游标的一些信息，而不需要重新生成子游标的相关信息(比如执行计划等), 而只需要reload (reload的具体过程是什么还需要研究) 。

查找是否有共享的父游标和硬解析是两个不同的过程，父游标共享与否和硬解析没有直接关系， 子游标的共享状态决定软硬解析 。

---------------------------------------------------------

从性能的角度来看，尽量避免硬解析，为什么？
第一： 因为逻辑优化(Transforming Queries)和物理优化(选择最优执行计划)都非常依赖CPU的操作。
第二： 需要分配内存来将父游标与子游标保存到库缓存中。由于库缓存是在所有的会话之间共享，
库缓存中的内存分配必须是串行执行。

软解析，软软解析 ---

软解析是相对于硬解析而言的，其实只要在hash bucket里可以匹配对应的SQL文本(算一次get)，那么就是软解析，说明之前运行过该sql，其实sql执行期间只要一个或多个步骤可以跳过，那么我们就可以定位为软解析。如果这个SQ语句没有被找到，就进行硬解析。软解析有三种类型：

A. 第一种是某session发出的SQL语句与在library cache里其他session发出的SQL一致，父游标和子游标都可以共享，逻辑优化(Transforming Queries)，和物理优化(选择最优执行计划)及将这些信息装载到库缓存的heap中 这几个步骤可以省略，表名，列名，名称转化及权限检查还是需要的。

B. 第二种是某session发出的SQL是该session之前发出的曾经执行过的SQL。这时，解析过程只需要进行文法检查及权限检查。

C. 第三种是当设置了session_cached_cursors时，当某个session第三次执行相同的SQL时，则会把该SQL的游标信息转移到该session的PGA中。这样，该session以后再执行相同的SQL语句时，会直接从PGA里取出执行计划，跳过硬解析的所有步骤，这是高效的解析方式，但是会消耗很大的内存。俗称为软软解析 。

Reload ---

关于子游标新建和reload 的区别，如果各版本的子游标都不能被共享，那么会创建一个新的子游标 (new create) ，这种情况指的就是 environment 或绑定变量长度不一样等 导致的情况。 而reload 指的是父游标可以共享，同样的子游标 (执行计划，environment 或bind var 等都一样)原来已经存在于library cache, 因为某种原因被aged out出去，而现在需要它了，要重新装载回来 (Oracle数据库可能在某个地方保存了原来相同的子游标信息)。

在Hash bucket中查找SQL，如果有的话就算作是一次get，并查找这个SQL语句的执行计划，如果执行计划已经不存在了(age out)或者是存在但不可用(Invalidation)，那么就必须对这条sql语句重新装载，这就叫reload，如果执行计划存在并且可用的话，oracle就执行这句话，这就叫做execution

五， 绑定变量(Bind Variables)

优点： 共享游标，减少硬解析

绑定变量分级 --

前面说到执行环境的变化比如绑定变量定义的类型大小不同会导致生成不同的游标，为了使游标的数量不至于太多，产生了这个功能。此功能将变量的长度分为4个级别，0-32字节，33-128字节，129-2000字节，>2000字节 这四个等级。不用说，同一个绑定变量(长度)的变化，最多能生成4个游标。

缺点： 绑定变量也有缺点。缺点就是，相对于字面量而言，会减弱查询优化器的功能。

比如：
select count(*) from t where id > 10;
select count(*) from t where id > 99999;
根据id值10,99999和表的统计信息，查询优化器可能会选择全表扫描或者索引扫描，是合理的。

使用了绑定变量，优化器会忽略他们的具体值，从而生成相同的执行计划。为了解决这个问题，
oracle9i引入了绑定变量窥测(bind variable peeking)的功能。

绑定变量窥测的优点，就是窥测绑定变量的值，把它们当做字面量来使用。这样的好处，就是能获得最优查询路径，比如是选择全表扫描还是索引扫描。

绑定变量窥测也有缺点，即生成的执行计划依赖第一次生成执行计划时所提供的值。举例来说，就是如果第一次是全扫描，以后永远都是全表扫描了。这个方法对于非OLTP系统的缺点非常明显，因为一个绑定变量集可能返回的结果集只包含几百行的数据，而另一套绑定变量可能返回几百万行数据，因此，Oracle建议保留CURSOR_SHARING作为该初始化参数的默认值，以强制产生一个新的更有效的执行计划 (cursor_sharing的详细解释见后面)。

那么如何避免这个缺点呢？只有升级到oracle11g了。
oracle11g引用一个新功能，自适应游标共享(ACS)。这个功能就是根据绑定变量的值，可以为相同的sql语句，生成不同子游标，及不同的执行计划。ACS使用了两个新的度量机制：绑定敏感度和绑定感知。具体可以参考Oracle11g文档。

什么时候不使用绑定变量？
批量任务处理，报表生成，运用OLAP的数据仓库，因为这种大型的查询时间较长，一次新的硬解析相对于这个查询时间不算什么, 所以不用绑定变量没有什么影响 。如果使用绑定变量，10g或以前的版本，一旦第一次执行时绑定变量第一次提供的值如果是小范围的，那么可能是索引扫描，但是第二次可能是数据仓库典型的大时间范围的查询，需要全表扫描，但是还是沿用了前面的索引扫描，这样导致性能下降。OLTP类型大多数是小量密集的操作，所以使用绑定变量时相对最优的执行计划比较稳定 。

在我们不使用where等条件判断时我们就要尽量使用绑定变量（比如普通insert操作），没理由不使用绑定变量； 而涉及到基数选择性判断时我们应该尽量避免使用绑定变量，因为在物理优化阶段的绑定变量窥测遇到较大负面风险。

也可以参考下面的两种建议：

如果sql处理的数据较少, 解析时间显然比执行时间多很多了，那么我们应该尽量使用绑定变量，这种适用于 OLTP（联机事务处理系统）；
而如果是数据仓库类型的数据库，我们对绑定变量的使用就应该慎重了，因为这时的执行时间有可能远远大于解析时间，解析时间相对于执行时间近乎可以忽略，所以这时应该尽量不使用绑定变量。

参数CURSOR_SHARING ---

oracle是为了满足一些以前开发的程序，里面有大量的相似的statement，没有很好的使用绑定变量，但是重写有不现实的情况下使用的一个参数。并且oracle也不建议修改这个参数。保持默认即可。

语法 CURSOR_SHARING = { SIMILAR | EXACT | FORCE } ，默认值为 EXACT

EXACT --
仅仅允许绝对一样的SQL语句共享同样的游标。当一个SQL语句解析的时候，首先到shared pool区查看是否有完全一样的语句存在，如果不存在(其实此时是找不到共享的父游标)，就执行hard parse .

SIMILAR --
如果在shared pool中无法找到完全一样的语句的时候，就会在shared pool进行一次新的查找，就是查找和当前要解析的语句相似的SQL语句。 similar语句就是除了value of some literal不同，别的地方都相同的语句。比如下面：
select * from a where age=2;
select * from a where age=5;
如果在shared pool中查找到这样的语句，就会做下一步的检查，看shared pool中缓存的这个语句的execution plan是否适合当前解析的语句，如果适合，就会使用shared pool的语句，而不去做hard parse。

FORCE --
强制将字面值不一样的但是其他方面是一样的SQL语句共享游标。如果cursor_sharing设置为force的时候，当在shared pool中发现了similar statement之后，就不会再去检查执行计划了，而直接使用在shared pool中的这个语句了。

将cursor_sharing设置为force实际上是危险的。这会可能形成suboptimal的执行计划。比如对于一个范围查找的语句，比如select * from a where a>10 and a<20这样类型的语句，缓存中的语句的执行计划可能对于正在解析的语句就是不适合的，不是最优的执行计划。这样看起来是减少了解析的时间，但是大大增大了execution的时间。

什么时候需要修改这个参数呢？需要满足以下的条件：
一个是由于大量的shared pool hitmis影响了用户的响应时间（就是当前的shared pool无法满足共享sql语句存储的需要），如果没有这个问题，那么设置这个参数，可能会造成更糟糕的性能。这个参数仅仅只是减少parse的时间。另外一个就是在现有程序中有大量的similar statement，可通过设置这个参数来获得相对比较好的性能。

---------------------------------------------------------------
关于cursor_sharing = similar的测试 ：
http://www.wangchao.net.cn/bbsdetail_60551.html

若存在object_id的 histograms ,则每次是不同的 值 的时候都产生硬解析 ,若不存在 histograms ，则不产生硬解析 。换句话说，当表的字段被分析过存在histograms的时候，similar 的表现和exact一样，当表的字段没被分析不存在histograms的时候,similar的表现和force一样。这样避免了一味地如force一样转换成变量形式，因为有hostograms的情况下转换成变量之后就容易产生错误的执行计划，没有利用上统计信息。而exact呢，在没有histograms的情况下也要分别产生硬解析，这样的话，由于执行计划不会受到数据分布的影响（因为没有统计信息）重新解析是没有实质意义的。而similar则综合了两者的优点。

备注： cursor_sharing=force or similar时,在9205以下的版本BUG不少 。
---------------------------------------------------------------

Library cache内部机制详解 参考：

http://www.hellodba.net/2010/07/oracle-library-cache.html