Oracle数据库技术实用详解学习笔记：Oracle进程、Shared pool

连接Oracle数据库

用户在客户端启动了sqlplus，那么在客户端本地就会启动一个用户进程sqlplus，当连接到Oracle服务器并成功建立连接后，就会在服务器端生成一个对应的服务器进程，这个服务器进程就是用户进程的代理进程，代替客户端去执行各种命令并将结果返回。

用户在客户端输入命令，客户端的用户进程把命令发送到服务器端对应的服务器进程，由服务器进程代替用户进程去执行命令，并返回结果。用户进程不能直接访问数据库。

 

在oracle服务器上执行如下命令：

sqlplus   /nolog

connect   /   as sysdba

 

查看sqlplus的进程号xxx，这就是客户端进程号

ps   -ef  |  grep   sqlplus

找到以这个进程号xxx为父进程的进程，这个进程就是与客户端进程对应的服务器进程

ps   -ef  | grep   xxx

 

 

Shared Pool

oracle作为一个数据库管理系统，最主要的就是解析、执行SQL语句。

 

那为什么oracle需要shared pool 呢？

在专用链接模式下，每个客户端进程对应一个服务器进程，当客户端1输入一个sql语句，发送到服务器端，有服务器端进程1接收，先编译，没有把语句和编译后生成的执行计划保存在内存中，然后执行，执行完成后把结果集返回给客户。

如果客户端1又再次执行相同的语句，还得把相同的事情再做一遍，这样很浪费时间。于是，这次做了改进，首先会把语句和编译后的执行计划放到进程自己的内存，这样一旦发现相同的语句，就不用再次编译、生成执行计划了，就可以节省一点cpu时间。

这时客户端1再次发送了一个语句，这个语句和前2次执行的语句很相似，只是where 字段名='xxx'中的xxx不同而已，这次做了改进，把SQL语句分成两个部分：一个是sql语句的静态部分，也就是sql语句的关键字、所引用的表、字段等；一个是sql语句的动态部分，也就是字面值，比如这里的xxx。由于整个数据库所包含的的对象数量是有限的，而表中的数据，比如xxx，是无限的。正是由于这些无限的数据，导致了sql语句的千变万化，也就是说静态部分是有限的，而动态部分是无限的。

实际上，动态部分对于解析的影响，相对于静态部分对解析的影响来说，几乎可以忽略不计，所以对于静态部分一样，而动态部分不同的语句，产生的执行计划基本是一样的，除了由于表中列的数据分布极不均匀时，可能会导致产生不同的执行计划。

所以这次虽然语句不完全一样，但仍然可以不用再重复解析，再次节省了cpu时间。

 

如果再有一个客户端2，执行和客户端1一样的语句，那会怎么样？

由于这些语句和执行计划只是存储在每个服务器端进程自己的内存中，无法共享，所以还是得再编译一次，所以这次的改进就是，构建一个服务器进程都能访问的共享内存，这就是共享池shared pool。这时，每个服务器进程在编译了SQL后，就会把sql语句和执行计划都缓存在shared pool，这样就能节省更多的cpu资源了。

 

1、shared pool 的内存结构

 

那么既然有了这个共享池，也就是内存中的一个区域，那么它的结构是怎样的呢？

从物理上看，共享池由去多内存块构成，这些块就是chunk，是在共享池中内存分配的最小单位，一个chunk中的内存都是连续的。

这些chunk分成4类，而x$ksmsp视图中每条记录都表示当前在shared pool中的一个chunk，所以通过x$ksmsp的ksmchcls字段看到:

 

select   ksmchcls   from x$ksmsp

 

free：不包含有效对象，可以不受限制的分配。

recr：就是recreatable，这个chunk中的对象可以在需要的时候被临时移走，在需要时可以重建。

freeabl：这个chunk包含的对象是被session使用过的，在处理过程中产生的，接下来会全部或部分的释放，不能临时从内存移走，因为移走后不能重建。

perm：这种类型的chunk包含永久对象，大型的permanent类型的chunk也可能包含可用空间，这部分可用空间可以释放会shared pool。

 

那么这么多的chunk，是如何管理的呢？

在共享池中，可用的chunk，也就是free型的，会被串联起来组成可用链表free list，也就是一个bucket。

一共有编号为0-254共255个bucket，每个bucket上的chunk的大小不同，有一个递增的趋势。同时每个bucket都有一个size字段，说明了这个bucket上链接的free型的chunk的大小。

 

那么如何找到free型的chunk呢？

当某个服务器进程需要一个chunk时，会首先到符合所需空间size的bucket上扫描，一直扫描直到链表的末端，直到找到完全符合大小的chunk。如果找到的chunk比需要的大，那么把chunk拆分成2个chunk，一个用来存放数据，一个则成为free型的chunk，并挂到当前bucket上。

如果在这个bucket上没有找到所需大小的chunk，那么可以从下一个非空的bucket上获取一个最小的chunk。如果在剩余的所有bucket上都找不到所需chunk，由于recreatable类型的chunk是可以被临时移出内存，这时可以扫描已经使用的recreatable类型的chunk链表，从链表上释放一些chunk。

 

那么如果找不到free型的chunk，会怎样呢？

当某个chunk正在使用时，或者是通过dbms_shared_pool包将对象pin在共享池中时，这个chunk是不能被移出内存的。比如某个sql语句正在执行，那么这个sql语句所使用的chunk，以及这个sql语句所引用的表、索引等对象所占用的chunk，是不能被移出内存。

所以，当在shared pool中无法找到足够大小的所需内存时，就会报：ora-4031错误消息。

这时我们可以通过 v$sgastat 查询shared pool空间时，可能会发现name为“free memory”的还很大，那为什么还会报错呢？

其实，在oracle在报错之前，已经尝试释放了不少chunk，这样一来确实也产生了不少free型的内存了，但这些chunk中，没有一个chunk能以连续的物理内存(可能有2个chunk所占的内存大小能满足需求，但是这2个chunk并不连续)，来提供所需的内存空间，所以才会发出4031错误。本质上的原因就是，多次分配后，导致了太多的内存碎片。

 

在对bucket的扫描、分配、拆分过程中，如何保证数据的一致性？

这里需要引入一个概念，就是latch，轻量级的锁，闩锁，这是一个底层的对象，用来保护对某个内存块的并发访问。

当进程有个要往一个内存块写入数据，这时还有其他的进程也想往里面写数据，如果不加控制，可能会使数据错乱。这时这个latch就能派上用场了，每个进程要往内存块写数据时必须要先获取latch，写完后释放latch，而latch的获取是串行化的，一次只能有一个进程获得latch。不过获取和释放latch的速度很快，所以才是轻量级的锁。

那么在管理shared pool中的chunk时，就用了latch，都是shared pool latch的保护下进行的。当shared pool中存在大量很小的chunk时，在扫描可用chunk时，shared pool latch就会被服务器进程持有很长时间，这在oracle 8i以前是shared pool latch争用的主要原因，如果增大shared pool的大小，那么这能是延缓产生问题的时间，到最后，会产生更多的非常小的chunk，争用也会越来越严重。

到了oracle 9i后，通过增加bucket的个数，同时每个bucket所管理的chunk的尺寸递增的幅度非常小，这样就可以把chunk均匀的分布在所有的bucket上，每个bucket上可用chunk都不多（shared pool大小相同的情况下，bucket多了，那么每个bucket中的chunk自然就少了），于是在扫描某个bucket，查找满足需要的chunk时，自然持有shared pool latch的时间就会缩短很多。

 

那么对于超大对象，又从何处分配呢？

超大对象不会从可用chunk链表中分配，而是通过设置的初始化参数 shared_pool_reserved_size ，决定这部分空间的大小尺寸，默认为shared_pool_size 的 5%。这个区域和正常的chunk管理是分开的，也就是说小的chunk不会进入到这个保留区域，而这个保留区域的可用chunk也不会挂到bucket上。

通过v$shared_pool_reserved 视图的request_misses 字段，显示了不能从保留区域获取足够大chunk的总次数，这个值应该为0：

select  request_misses   from  v$shared_pool_reserved

 

不过除了上面这些问题外，还有个问题，就是当释放可用空间时，是否会合并？是否还是放到原来的bucket中呢？

 

2、shared pool的逻辑结构： Library cache 和 Dictionary cache 

上面主要是从物理角度分析共享池，下面主要从逻辑的角度来分析共享池。

共享池主要由 library cache和dictionary cache组成。

library cache主要存储：用户提交的sql语句，sql语句相关的解析树（对sql语句中涉及的所有对象的展现），执行计划，用户提交的pl/sql程序块（匿名程序块、存储过程、包、函数），以及他们转换后的可执行代码。

为了对这些进行管理，library cache 中还存放了很多控制结构：lock，pin，dependency table 等。

此外，还存放了很多数据库对象的信息：表、索引等。这些对象很多都从dictionary cache中获取的。

 

上面说了不少，那么library cache到底是由哪些对象构成的呢？

在library cache 中存放的所有信息都称为对象，这些对象可以分成2类：

一个是存储对象，也就是上面提到的数据库对象，这些对象是通过显式的sql语句或pl/sql程序创建的，也必须通过显示的sql命令进行删除，这类对象包含了表、视图、索引、包、函数等。

一个是过度对象，也就是上面所说的用户提交的sql语句、提交的匿名pl/sql程序块等，这些过渡对象是在执行sql语句或pl/sql的过程中产生，并缓存在内存中，直到关闭实例或由于内存不足而被swap out，从而被删除。

 

那么过渡对象到底是什么？

当用户提交代码到shared pool后，会在library cache中生成一个可执行对象，就是游标。游标可以被所有进程共享，假如有100个进程都执行相同的语句，那么这100个进程都可以公用这个sql语句所产生的游标，从而节省了很多内存。

每个游标都是由至少2个对象构成：

一个是父游标，包含了：sql文本(游标的名称)、其他独立于提交用户的信息在v$sqlarea中的都是父游标。

一个是子游标，如果sql文本相同，但可能提交sql的用户不同，或提交的sql语句引用了同义词，那么可能会生成不同的子游标。虽然这些sql语句的文本都一样，但是上下文环境却不一样，所以这样的语句只有细化为多个子游标，最终会形成多个不同的执行对象，也就是多个不同的执行计划或pl/sql程序代码。

 

library cache中的对象是如何管理的？

学过数据结构的都知道，程序 = 数据结构 + 算法，oracle也是一个程序。在管理library cache时，数据结构是一个数组，数组中的每个元素就是一个bucket，这个bucket是一个链表，把对象句柄串联起来，这些对象句柄描述了对象的一些属性，包括：名称、标记、指向对象的指针。

oracle通过hash算法来管理这个数据结构。当一个sql语句进入library cache时，先把sql文本转化为ascii码，然后对这些ascii码进行hash运算，也就是：返回值 = hash(sql语句的ascii码，命名空间)，这里的命名空间对于sql语句来说就是“SQL  AREA”，表示共享游标。经过hash运算后得到的返回值，其实就是hash bucket 的号码，也就是数组的序号，那么这个sql语句就被分配到这个hash bucket里。

当某个进程处理一个新的sql语句时，会对这个sql语句应用hash函数，取得hash bucket号，然后到这个bucket中进行扫描，看是否有相同的sql语句。如果找到了相同的sql语句，有可能对象句柄所指向的对象已经被换出内存，那么这个对象必须再次装载reload；也可能找不到相同的语句，也就是对象句柄不存在，也就是这个语句是第一次执行，这时进程就必须重新建一个对象句柄，挂到bucket上，然后加载相应的对象。

这些管理操作，都必须用library cache latch。

 

那么对象句柄里到底存储了那些信息？

对象句柄存放了对象的名称，对象所属的命名空间，有关对象的标记(比如对象是否只读、是本地对象还是远程对象，是否被pin在内存中)，对象的统计信息。其中存放的最重要的内容就是，指向Heap 0对象的指针，Heap 0中存放了与对象直接有关系的信息，比如，对象类型、对象相关的表、实际的执行计划、执行pl/sql的机器码，Heap 0 是由一个活多个chunk组成的，这些chunk可能是离散的分布在library cache中的，而不一定是连续分布的。

我们可以通过视图v$db_object_cache来查看有哪些对象被缓存，这些对象占用多大的空间，下面的语句显示在每个namespace中，大小尺寸排在前3的对象：

select *
from
(
select row_number() over(partition by namespace order by sharable_mem desc) as size_rank,
       namespace,
       sharable_mem,
       substr(name,1,50) sql_stmt
from v$db_object_cache
order by sharable_mem desc
)
where size_rank <=3
order by namespace,size_rank;

上面主要是library cache，那么dictionary cache 主要是起上面作用，两者之间又有上面联系呢？

dictionary cache 是一个专门存放sys schema 下的对象的内存区域，里面存放了：所有表的定义、存储信息、权限信息、约束定义、回滚段信息、表的统计信息等，dictionary cache是以行为单位来构造的，也称为row cache。之所以构造dictionary cache的目的是为了加快解析sql语句的速度。

 

在上面提到了，在library中存放了很多数据库对象的信息：表、索引等。这些对象很多都从dictionary cache中获取的，比如语句：select  object_id，object_name   from  objects，在编译时，需要到dictionary cache 中去找objects 表相关的数据字典的信息，比如表、列名、以及权限信息。如果在dictionary cache中找不到，那么就会从system表空间中，把数据字典信息加载到内存中的 buffer cache中，然后把这些内存中的数据字典信息读出来，按照行的形式存放到 dictionay cache，从而构造出字典信息，再从dictionary cache的行数据中取出表的列信息存放入 library cache中。

在oracle中没有初始化参数来控制library cache 和 dictionary cache应该占多大的内存，我们只能控制shared pool的大小。shared pool中的一个可用chunk，如果存放了数据字典的cache，那么它就属于dictionary cache。如果存放了sql文本或执行计划，那么它就属于library cache。有时候，某个chunk原先存放的是sql文本，但由于内存不足，被数据字典信息所覆盖，则这个chunk就从library cache变成了dictionary cache，所以去控制两者的大小，显然也没什么意义。

 

 3、解析SQL语句的过程之硬解析

要执行sql语句，先要解析sql语句，解析sql语句的过程其实就是把sql文本转化为oracle可以执行的代码。解析过程分为：硬解析、软解析。

一条语句在第一次执行时，必须进行硬解析。

当发送一条sql语句进入shared pool时，首先会把sql文本转化为ascii值，然后传入hash函数，取得相应的hash值hash_value，根据hash值到library cache中找到对应的bucket，比较bucket里是否存在这个的sql语句。

如果不存在，那么需要获取shared pool latch，然后在shared pool的可用列表free list中查找一个可用的chunk，再释放latch，在有了chunk后，可以认为是进入了library cache了，那么接下来，就进行硬解析，包括如下步骤：

a、对sql语句进行文法检查，看是否有文法错误。如果有错误，则退出解析。

b、到数据字典校验sql语句引用的对象、列是否存在，这个过程会加载dictionary cache。如果不存在，则退出。

c、把对象进行名称转换，比如，把同义词转化为实际的对象。如果转化失败，则退出。

d、检查发出sql语句的用户，是否有权限，访问sql语句所引用的对象。如果没有权限，则退出。

e、优化器根据数据字典里记录的对象的统计信息，来计算最优的执行计划，会涉及大量的数学运算，是最消耗cpu资源的。

f、将sql文本、执行计划等载入到library cache的heap中。

 

在整个硬解析的过程中，进程一直持有library cache latch，直到解析结束。

解析后会产生两个游标：

一个是父游标，包含了：sql文本、优化目标。父游标在第一次打开时被锁定，知道其他所有会话都关闭这个父游标后，才会被解锁。当父游标被锁定时是不能被交换出library cache的，只有解锁后才能被交换出去，在交换出去时，相应的子游标也会交换出去。

一个是子游标，包含了：包含了游标所有的信息，比如，具体的执行计划、绑定变量。当子游标被交换出去时，可以利用父游标的信息重新构建一个子游标，也就是再次加载reload。可用如下的方式，计算reload的比率：

SELECT  sum(reloads) / sum(pins) * 100 FROM v$librarycache

这里的视图v$librarycache记录了各个namespace的各种统计信息。

那么父游标和子游标的对应关系又是怎样的呢？

一个父游标对应多个子游标，父游标对应v$sqlarea，而子游标的个数对应v$sqlarea的version_count字段，每个子游标则全部都在 v$sql。当绑定变量的值长度从6位变为200时，或者虽然sql语句相同，但引用的表属于不同用户时，都会创建一个新的子游标。

 

4、解析SQL语句的过程之软解析

接着上面，如果在bucket中找到了sql语句，那说明这个sql语句之前就运行过，于是进行软解析，软解析相对于硬解析而言，解析过程少了几个步骤，所以也能节省不少cpu资源。

软解析分为3种：

一种是某个session发出的sql语句已经在library cache里，这时仍然需要进行表名列名的检查、名称转换、权限检查。

一种是某个session发出的sql语句是之前就发送过的sql语句，这时需要进行权限检查。

一种是设置了session_cached_cursors，当某个session第三次执行相同的sql时，会把这个sql语句的游标信息，转移到这个session的PGA里。以后再次执行相同的语句，会直接从PGA里取出执行计划，从而跳过硬解析的全过程，当然这也是最高效的解析方式，消耗的cpu较少，但消耗的内存就很大，因为在这个session的PGA中存储的游标，只适合于这个session，就算其他session有相同的语句，也没办法使用，因为这个游标是这个会话私有的。

 

下面是一个例子，说明解析过程：

craete TABLE test_t(ID NUMBER);

inset INTO TEST_T SELECT 1 FROM DUAL;

inert INTO TEST_T SELECT 2 FROM DUAL;

 

--0.清空shared pool里所有的sql语句
ALTER SYSTEM flush shared_pool;


--1.定义绑定变量、赋值，再执行查询
variable v_id NUMBER;

EXEC :v_id := 1;

SELECT * FROM test_t
WHERE id = :v_id;


--定义绑定变量、赋值，再执行查询
variable v_id VARCHAR2(10);

EXEC :v_id := '1';

SELECT * FROM test_t
WHERE id = :v_id;


--2.version_count为2，因为数据类型不同，导致产生了2个子游标
SELECT sql_text,
       version_count
FROM v$sqlarea
WHERE sql_text LIKE '%test_t%';


--显示2个子游标
SELECT SQL_text,
       child_address,
       address
FROM v$sql
WHERE sql_text LIKE '%test_t%';




--3.定义绑定变量、赋值，再执行查询
variable v_id NUMBER;

EXEC :v_id := 3;

SELECT * FROM test_t
WHERE id = :v_id;


--version_count还是为2，因为数据类型相同，所以没有产生子游标
SELECT sql_text,
       version_count
FROM v$sqlarea
WHERE sql_text LIKE '%test_t%';

 

从上面的例子可以看出，由于数据类型不一致，会导致产生新的子游标，另外，用户不同、sql的优化模式不同，都会产生新的子游标。

 

5、设置shared pool

设置共享池的原则是，在系统上线时，设置一个初始值，一般为SGA的10%，但不超过1GB，让系统正常运行一段时间，通过监控获取系统的负载情况，然后选择一个合理的值，也可以根据顾问来帮助我们判断shared pool的设置是否合理。

通过将初始化参数设置statistics_level 设置为typical 或 all，就可以启动对shared pool的建议，如果设置为basic，就是关闭。通过下面的语句可以显示shared pool 的建议大小，当estd_lc_time_saved_factor为1时，表示再增加shared pool的大小时，也不会对提高性能有帮助：

SELECT shared_pool_size_for_estimate,  --共享池预计大小MB
       estd_lc_size,                   --预估的library cache大小
       estd_lc_memory_objects,          --预估的library cache中的内存对象个数
       
       estd_lc_time_saved,             --预估的library cache所节省的解析时间
       estd_lc_time_saved_factor,      --节省解析时间的因子
       estd_lc_memory_object_hits      --预估的library cache的内存对象的命中次数
FROM v$shared_pool_advice a

需要注意的是，如果设置了共享服务器的连接模式，会用large pool，如果不配置large pool ，则session的PGA会有一部分在shared pool里进行分配，从而加重shared pool 的负担。