【深入解析--eygle】 学习笔记 1.2 shared pool原理 Shared Pool是Oracle SGA设置中最复杂也是最重要的一部分内容,Oracle通过Shared Pool来实现SQL共享、减少代码硬解析等,从而提高数据库的性能。在某些版本中,如果设置不当,Shared Pool可能会极大影响数
【深入解析--eygle】 学习笔记
1.2 shared pool原理
Shared Pool是Oracle SGA设置中最复杂也是最重要的一部分内容,Oracle通过Shared Pool来实现SQL共享、减少代码硬解析等,从而提高数据库的性能。在某些版本中,如果设置不当,Shared Pool可能会极大影响数据库的正常运行。
在Oracle 7之前,Shared Pool并不存在,每个Oracle连接都有一个独立的Server进程与之相关联,Server进程负责解析和优化所有SQL和PL/SQL代码。典型的,在OLTP环境中,很多代码具有相同或类似的结构,反复的独立解析浪费了大量的时间以及资源,Oracle最终认识到这个问题,并且从PL/SQL开始尝试把这部分可共享的内容进行独立存储和管理,于是Shared Pool作为一个独立的SGA组件开始被引入,并且其功能和作用被逐渐完善和发展起来。
在这里注意到,Shared Pool最初被引入的目的,也就是它的本质功能在于实现共享。如果用户的系统代码是完全异构的(假设代码从不绑定变量,从不反复执行),那么就会发现,这时候Shared Pool完全就成为了一个负担,它在徒劳无功地进行无谓的努力:保存代码、执行计划等期待重用,并且客户端要不停的获取Latch,试图寻找共享代码,却始终一无所获。如果真是如此,那这是我们最不愿看到的情况,Shared Pool变得有害无益。当然这是极端,可是在性能优化中我们发现,大多数性能低下的系统都存在这样的通病:代码极少共享,缺乏或从不实行变量绑定。优化这些系统的根本方法就是优化代码,使代码(在保证性能的前提下)可以充分共享,减少无谓的反复硬/软解析。
实际上,Oracle引入Shared Pool就是为了帮助我们实现代码的共享和重用。了解了这一点之后,我们在应用开发的过程中,也应该有意识地?高自己的代码水平,以期减少数据库的压力。这应该是对开发人员最基本的要求。
Shared Pool主要由两部分组成,一部分是库缓存(Library Cahce),另一部分是数据字典缓存(Data Dictionary Cache)。Library Cache主要用于存储SQL语句、SQL语句相关的解析树、执行计划、PL/SQL程序块(包括匿名程序块、存储过程、包、函数等)以及它们转换后能够被Oracle执行的代码等,这部分信息可以通过v$librarycache视图查询;至于Data Dictionary Cache主要用于存放数据字典信息,包括表、视图等对象的结构信息,用户以及对象权限信息,这部分信息相对稳定,在Shared Pool中通过字典缓存单独存放,字典缓存的内容是按行(Row)存储的(其他数据通常按Buffer存储),所以又被称为Row Cache,其信息可以通过V$ROWCACHE查询。
17:44:15 sys@felix SQL>desc v$librarycache;
Name Null? Type
---------------------------- ---------------------------------
NAMESPACE VARCHAR2(64)
GETS NUMBER
GETHITS NUMBER
GETHITRATIO NUMBER
PINS NUMBER
PINHITS NUMBER
PINHITRATIO NUMBER
RELOADS NUMBER
INVALIDATIONS NUMBER
DLM_LOCK_REQUESTS NUMBER
DLM_PIN_REQUESTS NUMBER
DLM_PIN_RELEASES NUMBER
DLM_INVALIDATION_REQUESTS NUMBER
DLM_INVALIDATIONS NUMBER
17:44:40 sys@felix SQL>
17:44:40 sys@felix SQL>desc v$rowcache;
Name Null? Type
------------------------------ -------------------------------
CACHE# NUMBER
TYPE VARCHAR2(11)
SUBORDINATE# NUMBER
PARAMETER VARCHAR2(32)
COUNT NUMBER
USAGE NUMBER
FIXED NUMBER
GETS NUMBER
GETMISSES NUMBER
SCANS NUMBER
SCANMISSES NUMBER
SCANCOMPLETES NUMBER
MODIFICATIONS NUMBER
FLUSHES NUMBER
DLM_REQUESTS NUMBER
DLM_CONFLICTS NUMBER
DLM_RELEASES NUMBER
17:50:55 sys@felix SQL>
下图说明了Shared Pool各个部分协同工作以及与Buffer Cache的配合。
从Oracle Database 11g开始,在Shared Pool中划出了另外一块内存用于存储SQL查询的结果集,称为ResultCache Memory。以 前Shared Pool的主要功能是共享SQL,减少硬解析,从而?高性能,但是SQL共享之后,执行查询同样可能消耗大量的时间和资源,现在Oracle尝试将查询的结果集缓存起来,如果同一SQL或PL/SQL函数多次执行(特别是包含复杂运算的SQL), 那 么 缓 存 的查 询 结 果 可 以 直 接 返 回给用户,不需要真正去执行运算,这样就又为性能带来了极大的提升。
1.2.1 Oracle 11g 新特性:Result Cache
结果集缓存(Result Cache)是Oracle Database 11g新引入的功能,除了可以在服务器端缓存结果集(ServerResult Cache)之外,还可以在客户端缓存结果集(Client Result Cache)。
服务器端的Result Cache Memory由两部分组成:
(1) SQL Query Result Cache:存储SQL查询的结果集。
(2) PL/SQL Function Result Cache:用于存储PL/SQL函数的结果集。
Oracle通过一个新引入初始化参数result_cache_max_size来控制该Cache的大小。如果result_cache_max_size=0 则表示禁用该特性。参数result_cache_max_result 则控制单个缓存结果可以占总的ServerResult Cache大小的百分比。
09:47:20 sys@felix SQL>show parameter result_
NAME TYPE VALUE
---------------------------------------------------------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 1M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
09:48:09 sys@felix SQL>
上面显示的参数中result_cache_mode用于控制Server result cache的模式,该参数有3个可选设置。
(1) 设置auto:则优化器会自动判断是否将查询结果缓存。
(2) 设置manual:则需要通过查询提示result_cache来告诉优化器是否缓存结果。
(3) 设置force :则尽可能地缓存查询结果(通过提示no_result_cache可以拒绝缓存)
09:52:31 scott@felix SQL>create table felix asselect * from dba_objects;
Table created.
09:53:28 scott@felix SQL>alter systemflush SHARED_POOL;
System altered.
09:53:42 scott@felix SQL>alter system flushBUFFER_CACHE;
System altered.
09:54:06 scott@felix SQL>set autot on;
09:54:25 scott@felix SQL>select count(*) fromfelix;
COUNT(*)
----------
75613
Execution Plan
----------------------------------------------------------
Plan hash value: 2587295606
--------------------------------------------------------------------
| Id |Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 301 (1)| 00:00:04 |
| 1 | SORTAGGREGATE| | 1 | | |
| 2 | TABLE ACCESS FULL| FELIX | 63221 | 301 (1)| 00:00:04 |
--------------------------------------------------------------------
Note
-----
- dynamicsampling used for this statement (level=2)
Statistics
----------------------------------------------------------
70 recursive calls
0 db block gets
1167 consistent gets
1351 physical reads