SQL语句执行过程到shared pool剖析

最新推荐文章于 2022-01-27 17:12:10 发布

wuli大大力

最新推荐文章于 2022-01-27 17:12:10 发布

阅读量962

点赞数

分类专栏： Oracle技术文章标签： Oracle 数据库 shared pool SQL执行过程

本文链接：https://blog.csdn.net/li450126014/article/details/18900251

版权

Oracle技术专栏收录该内容

16 篇文章 0 订阅

订阅专栏

一、SQL语句执行的过程

1、客户端输入SQL语句

2、SQL语句通过网络（如果应用和ORACELE不在同一台机器上）到达数据库实例

3、ServerProcess 接收SQL语句（更多server process详见“ORACLE服务器进程”）

● 解析：此过程会消耗很多资源——CPU、I/O

● 生成执行计划 →执行

● 从磁盘（数据文件）中读入必要的数据块到SGA的共享数据库缓冲区buffercache（该块不在缓冲区时）。

● 根据语句进行相应的操作。如果是删除或修改操作，则直接在内存中（buffer cache）进行修改。对于把buffer cache中的脏数据写回磁盘（dbf），和写日志等操作，serverprocess并不关心（因为serverprocess只负责与用户交互，提供用户最好的体验，所以日后的优化和性能监控大多关心server process），而是由分别由DBWR和LGWR后台程序进行。

● 将结果数据反馈给客户端

二、数据字典

Oracle中所有的数据字典可以在官方文档《Reference》中查看。

范例：创建T1表，在数据字典dba_tables中查找其所属的用户。

select OWNER,TABLE_NAME from dba_tables where TABLE_NAME like 'T1'；

OWNER TABLE_NAME

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

SYS T1

三、shared pool组成

主要的3块区域：free、library cache、dictionary cache（也叫row cache）。

了解以下两个概念：

● Library Cache：缓存最近被执行的SQL和PL/SQL的相关信息，即存放SQL语句的文本，分析后的代码及执行计划。实现常用语句的共享，使用LRU算法进行管理，由以下两个结构构成：Shared SQL area、Shared PL/SQL area；　　

● DataDictionary Cache：缓存最近被使用的数据库定义，即存放有关表,列和其它对象定义及权限。它包括关于数据库文件、表、索引、列、用户、权限以及其它数据库对象的信息。在语法分析阶段，Server Process访问数据字典中的信息以解析对象名和对存取操作进行验证。数据字典信息缓存在内存中有助于缩短响应时间。

范例：SGA中shared pool的以上三个区域的大小：

select * from v$sgastat where pool='shared pool' and name in ('free memory','library cache','row cache') ;

POOL NAME BYTES

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

shared pool library cache 7626040

shared pool free memory 66712768

shared pool row cache 7480368

四、硬解析和软解析

范例：从v$sysstat视图中查看当前软硬解析数目

SQL> col name format a25 --修改列显示宽度

SQL> select name,value from v$sysstat where name like 'parse%' ;

NAME VALUE

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

parse time cpu 664

parse time elapsed 1565

parse count (total) 33811

parse count (hard) 3079

parse count (failures) 11

Shared Pool中的两个概念：

● Chunk：shared pool 中的内存块。

● Chain：在Shared pool中，可用的chunk (free类型,即Free Chunks) 会被串起来称为可用链表(这里称为Chain，但多数资料称为free list），空闲内存块都是由空闲列表（free list）统一管理、分配的。每个空闲的chunk都会属于也只属于一个空闲列表。空闲列表上的chunk的大小范围是bucket来划分的。我们也可以把bucket视为一种索引，使Oracle在查找空闲块时，先定位所需的空闲块在哪个bucket的范围内，然后在相应的空闲列表中查找。

在free区域和librarycache中chunk都是串起来挂在chain上。解析SQL语句时，先从free（此SQL语句没有执行计划时）中遍历合适的chunk，然后serverprocess将sql text转化为ASCII码，对ASCII码进行hash运算，产生hash bucket号，存放于刚才找到的chunk中，之后将chunk中有内容（或者说被修改了的）部分划分出来挂在library cache中，而如果chunk还有剩余，则其剩余部分（碎片）就继续挂在free区域合适的位置。

（关于library cache及其管理参见“library cache” ；

关于参数CURSOR_SHARING的详解见《Reference》）

● x$ksmsp视图的简单应用

因为x$ksmp中存放着所有的chunk的记录。所以该视图的行数即当前chunk的个数。

范例：验证chunk的增加

SQL> select count(*) from x$ksmsp;

COUNT(*)

----------

15693

select count(*) from dba_objects;

COUNT(*)

----------

50317

select count(*) from x$ksmsp;

COUNT(*)

----------

15754

通过观察可以发现，执行SQL语句会产生很多的chunk。

五、SQL共享，绑定变量

范例：验证SQL是否共享

SQL> select /*hello*/ count(*) from t1 where name=1;

select /*hello*/ count(*) from t1 where name=2;

select /*hello*/ count(*) from t1 where name= 1; -- 多一个空格

SQL> select sql_id,sql_text,executions from v$sql where sql_text like '%hello%';

SQL_ID SQL_TEXT EXECUTIONS

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

ab0uwntzqx6sx select sql_id,sql_text,executions from v$sql where sql_ 1

text like '%hello%'

9y5vj2r7z2khy select /*hello*/ count(*) from t1 where name= 1 1

ay7yww74z6wjx select /*hello*/ count(*) from t1 where name=1 1

8szsary053x4d select /*hello*/ count(*) from t1 where name=2 1

经过验证可以发现，3条sql语句产生了3个不同的SQL_ID，说明它们并没有实现共享，而是各自都执行了一次！要达到SQL共享必须SQL语句完全相同——包括空格、大小写、数值、回车符等等。如果没有共享，则必须执行硬解析。所以为了可以共享SQL语句，应该做到以下两点：

1、统一书写风格

2、使用绑定变量

范例：通过绑定变量到达SQL语句共享

先通过以下语句将Shared_pool里面的缓存清空，注意使用此语句必须十分谨慎，因为清空缓存意味着清空了所有SQL共享，在之后的操作中必定会进行大量的硬解析。

altersystem flush shared_pool;

SQL> declare v_sql varchar2(50);

2 begin for i in 1..10000 loop

3 v_sql := 'insert /*hello*/ into t1 values (:1)';

4 execute immediate v_sql using i;

5 end loop;

6 commit;

7 end;

8 /

SQL> select sql_id,sql_text,executions from v$sql where sql_text like '%hello%';

SQL_ID SQL_TEXT EXECUTIONS

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

1vx8fabpy9dwv select sql_id,sql_text,executions from v$sql where sql_ 1

text like '%hello%'

5wsw3kg839stc declare v_sql varchar2(50); begin for i in 1..10000 loo 1

p v_sql := 'insert /*hello*/ into t1 values (:1)'; exec

ute immediate v_sql using i; end loop; commit; end;

f0na2j20ngh92 insert /*hello*/ into t1 values (:1) 10000

通过观察发现，插入这条语句执行了10000次，说明该SQL语句已共享。

（关于绑定变量的实验参考“oracle中的软解析和硬解析”）

六、找出没有共享的SQL语句

可参考以下语句：

select sql_fulltext from v$sql where execution=1 andsql_text like '&from t1%';

但应该注意到此处不一定能说明该语句没有共享，有可能该语句就只执行了一次而已。或可以参考：

select sql_fulltext from v$sql where execution=1 order bysql_text;

可以把结果数据输出到文件进行观察分析，步骤如下：

SQL> spool 1.lst

SQL> Select sql_fulltext from v$sql where executions=1 order by sql_text;

SQL> sool off

SQL> exit

$ ls

可以发现外面多了一个1.lst文件，可以用各种阅读器整理后查看。

七、解析命中率

library命中率：

SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;

SUM(PINHITS)/SUM(PINS)*100

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

89.705768

rowcache命中率：

SQL> select sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcache where gets>0;

SUM(GETS) SUM(GETMISSES) 100*SUM(GETS-GETMISSES)/SUM(GETS)

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

181955 6197 96.5942129

实际生产中命中率一般在99%以上，对于查看数据库命中率，需要在数据库运行一段时间后再分析才比较准确。

八、4031错误

出现4031错误的一般原因：大量的硬解析而导致碎片的增多（小而多的chunk），或者大量的硬解析突然后又出现很大的SQL语句，由于存在不到合适的chunk而导致此错误。

解决方案：

1、刷新shared_pool

清空shared_pool缓存，使用此方法需十分慎重，而且治标不治本

● alter system flush shared_pool;

2、共享SQL

将cursor_sharing的值改为FORCE，意味着强制使用绑定变量。（关于cursor_sharing详见“Oracle cursor_sharing 参数详解”）

SQL> alter system set cursor_sharing='FORCE'; -- 默认值为EXACT

SQL> show parameter cursor_sharing

NAME TYPE VALUE

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

cursor_sharing string force

3、强行缓存。

通过查找分析，将大的SQL语句强行缓存到缓存里面，而且其永远不会被置换出来。

步骤如下（未经检验）：

select *

from v$db_object_cache

where sharable_men > 10000

and (type = 'PACKAGE' or type = 'PACKAGE BODY'or type = 'FUNCTION' or type = 'PROCEDURE')

and kept = 'NO' ;

执行dbms_shared_pool.keep('对象名');

DBMS_SHARED_POOL

@?/rdbms/admin/dbmspool.sql

根据ORACLE对shared_pool的管理来设置“强行缓存”。ORACLE对shared_pool管理时，如果在free区域中剩下的都是很小的chunk，而此时又遇到大SQL时，ORACLE会在library cache中把使用频率少的大SQL缓存“挤”出去，然后把这块的缓存空间重新挂回free区域中。但此时还有可能再次遇到问题，如果被“挤”出来的SQL语句再次被执行，而在free区域和library cache中再也找不到合适的缓存时，就会发生4031错误。

对此，解决的办法就是，通过对library cache中的chunk进行排序，把最大的前几个chunk强行放到缓存中，这样就可以避免了4031错误。

4、保留区

保留区（shared_pool_reserved，也叫保留池）是专门用于存放shared_pool中大对象的空间。如果对象大于特定的值，就会向shared_pool_reserved请求空间，但是如果在shared_pool_reserved中也找不到空间的话就会出现4031错误。

SQL>select request_misses from v$shared_pool_reserved;

REQUEST_MISSES

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

SQL>show parameter shared_pool_reserved

NAME TYPE VALUE

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

shared_pool_reserved_size biginteger 9856614

关于REQUEST_MISSES参数官方说明是：

Number oftimes the reserved list did not have a free piece of memory to satisfy therequest, and started flushing objects from the LRU list.(无法满足查找保留区空闲内存块请求，需要从LRU列表中清出对象的次数。)

（更多参考“共享池中保留池的调整(shared_pool_reserved_size)”）

5、shared_pool扩容

因为在ORACLE中我们不能对shared_pool里面的各个区域进行空间的调整，所以只能通过增加shared_pool的空间从而让ORACLE自动调整各个区域的空间。

调整shared_pool空间需注意，如果将shared_pool空间调大，那么ORACLE会立刻调大其空间；但是如果将其调小，ORACLE并不会立刻将shared_pool的空间调小，而是运行一段时间后，如果确定shared_pool并不需要那么大的空间时，才将其调小。

范例：动态调整shared_pool大小

查看shared_pool调整前的大小：0M，即由SGA_TARGET自动调整

SQL> show parameter shared_pool_size

NAME TYPE VALUE

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

shared_pool_size big integer 0

虽然此处shared_pool的大小为0M，但实际shared_pool并不可能是0M，通过查看v$sga_dynamic_components数据字典可以发现其值。

SQL> select component,current_size from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE

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

shared pool 201326592

large pool 4194304

java pool 4194304

streams pool 0

DEFAULT buffer cache 385875968

KEEP buffer cache 0

RECYCLE buffer cache 0

DEFAULT 2K buffer cache 0

DEFAULT 4K buffer cache 0

DEFAULT 8K buffer cache 0

DEFAULT 16K buffer cache 0

0DEFAULT 32K buffer cache 0

ASM Buffer Cache 0

13 rows selected.

通过观察，我们可以发现，当前shared_pool的大小是200M左右。

下面来动态调整shared_pool的大小，但其值并不大于200M，看能否成功。

SQL> alter system set shared_pool_size=20M scope=both ;

SQL> show parameter shared_pool_size

NAME TYPE VALUE

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

shared_pool_size big integer 20M

SQL> select component,current_size from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE

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

shared pool 201326592

large pool 4194304

java pool 4194304

streams pool 0

DEFAULT buffer cache 385875968

KEEP buffer cache 0

RECYCLE buffer cache 0

DEFAULT 2K buffer cache 0

DEFAULT 4K buffer cache 0

DEFAULT 8K buffer cache 0

DEFAULT 16K buffer cache 0

0DEFAULT 32K buffer cache 0

ASM Buffer Cache 0

13 rows selected.

可以发现如果shared_pool大小调整为小于当前的大小，ORACLE并不会立刻将其值调小，但如果调大呢？

SQL> alter system set shared_pool_size=300M scope=both ;

SQL> show parameter shared_pool_size

NAME TYPE VALUE

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

shared_pool_size big integer 300M

SQL> select component,current_size from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE

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

shared pool 314572800

large pool 4194304

java pool 4194304

streams pool 0

DEFAULT buffer cache 272629760

KEEP buffer cache 0

RECYCLE buffer cache 0

DEFAULT 2K buffer cache 0

DEFAULT 4K buffer cache 0

DEFAULT 8K buffer cache 0

DEFAULT 16K buffer cache 0

DEFAULT 32K buffer cache 0

ASM Buffer Cache 0

13 rows selected.

可以发现，此时shared_pool已经调大了。此时，问题又来了，在ORACLE 10g中，SGA是自动管理的，这样的好处是可以动态分配SGA中组件的内存的大小，不必造成内存浪费。而SGA的大小可以有动态参数SGA_TARGET设置，但是如果这个参数设置的大小大于物理内存则有可能出现错误，所以ORACLE还引入了另外一个参数：SGA_MAX_SIZE，这个参数限制SGA_TARGET的大小不能大于它。（更多详见“SGA_MAX_SIZE与SGA_TARGET”）