调优3（share pool 调整）

首页 > 代码库 > 调优3（share pool 调整）

2024-07-04 07:35:32 223人阅读

第三章 share pool 调整

1、sga 组成部分

1）database buffer
2）redolog buffer
3）share pool ：共享池，存放最近最常使用的sql和plsql语句及解析计划和数据字典信息
---library cache：存放最近最常使用的sql及plsql 语句和执行计划、解析代码（采用LRU算法)
---Dictionary cache；存放最近最常使用的数据字典信息（表、字段、权限等）
---uga :用户全局区（如果server process 是专有模式，则uga 在pga 中分配；如果是共享模式，在没有启用large pool的情况下，uga是从share pool 分配）

2、share pool 的尺寸

share pool的大小有shared_pool_size 参数来设定（9i以前），在oralce 10g 后，启动内存自动管理后，这个参数指定的尺寸是share pool自动分配最小尺寸。

09:37:50 SYS@ test1>show parameter shared

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 20M

09:49:46 SYS@ test1>select name ,bytes/1024/1024 from v$sgainfo;
NAME BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size 1.16233444
Redo Buffers 2.8359375
Buffer Cache Size 168
Shared Pool Size 60

3、library cache
---采用LRU 算法，共享最近最常使用的sql及plsql 语句和执行计划、解析代码，为防止sql语句的再次解析

sql执行过程：

A)、parse(解析）
1）首先library cache扫描库缓存区，查看这个语句是否执行过，（对要访问的sql语句通过hash运算生成一个hash value，然后和libray cache 存储的hash value对比）如果发现，就从库缓存区调用此sql语句，去执行。如果没有，就从下一步开始
2）检查该语句的语法和语义及访问权限
在检查语义和权限时，要访问数据字典信息，先从数据字典缓存里去访问，如果数据字典缓存区没有，则从磁盘读取数据字典信息到dictionary cache。
3）对该语句访问的对象加lock，然后按照最小cost的优化原则，分析表并建立执行计划，并把执行计划编译后存放到library cache，以便共享

B)、bind ：绑定变量

C)、execute ：执行，按照执行计划去读取数据到buffer cache

D)、fetch ： server process 返回查询信息

----如果sql语句不能在library cache 里共享，在语句执行，都会做硬解析（hard parse），在oltp的数据库中，硬解析的比例应该不超过5%
如果sql 语句可以共享，则做软解析（soft parse），软解析在oltp的库中应该占 95%以上
但在OLAP或DSS类型的库，可以不考虑硬解析的比例

--------sql语句共享
10:03:38 SYS@ test1>conn scott/tiger
Connected.
10:03:44 SCOTT@ test1>select ename,sal,deptno from emp where empno=7788;

ENAME SAL DEPTNO
---------- ---------- ----------
SCOTT 3000 20

Elapsed: 00:00:00.04
10:04:10 SCOTT@ test1>select ENAME,SAL,DEPTNO from emp where empno=7788;

ENAME SAL DEPTNO
---------- ---------- ----------
SCOTT 3000 20

Elapsed: 00:00:00.02
10:04:28 SCOTT@ test1>

10:06:20 SYS@ test1>col sql_text for a50
10:06:30 SYS@ test1>r
1 select PARSE_CALLS ,HASH_VALUE,EXECUTIONS,sql_text from v$sqlarea
2* where lower(sql_text) like ‘select ename,sal,deptno from emp%‘

PARSE_CALLS HASH_VALUE EXECUTIONS SQL_TEXT
----------- ---------- ---------- --------------------------------------------------
1 3761118664 1 select ename,sal,deptno from emp where empno=7788
1 176422402 1 select ENAME,SAL,DEPTNO from emp where empno=7788

Elapsed: 00:00:00.02
10:06:31 SYS@ test1>

10:06:31 SYS@ test1>show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT ；游标共享默认是：精确匹配

-------可以看出，以上两个sql语句大小写不一致，会导致sql无法共享，所以在开发阶段代码的编写过程中，必须有严格的编写规范。

---通过bind variable 实现共享

14:43:28 SYS@ test1>conn scott/tiger
Connected.
14:43:33 SCOTT@ test1>select ename,sal,deptno from emp where deptno=10;

Elapsed: 00:00:00.09
14:43:48 SCOTT@ test1>select ename,sal,deptno from emp where deptno=20;

Elapsed: 00:00:00.02
14:43:54 SCOTT@ test1>var num number
14:48:17 SCOTT@ test1>exec :num:=20;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
14:48:32 SCOTT@ test1>select ename,sal,deptno from emp where deptno=:num;

Elapsed: 00:00:00.02
14:48:42 SCOTT@ test1>exec :num:=30;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
14:48:48 SCOTT@ test1>select ename,sal,deptno from emp where deptno=:num;

Elapsed: 00:00:00.01
14:48:52 SCOTT@ test1>

14:45:37 SYS@ test1>col sql_text for a50
14:45:46 SYS@ test1>r
1 select SQL_TEXT,HASH_VALUE,EXECUTIONS,PARSE_CALLS from v$sqlarea
2* where sql_text like ‘select ename,sal,deptno from emp%‘

SQL_TEXT HASH_VALUE EXECUTIONS PARSE_CALLS
-------------------------------------------------- ---------- ---------- -----------
select ename,sal,deptno from emp where deptno=20 4176389791 1 1
select ename,sal,deptno from emp where deptno=10 1088798058 1 1

Elapsed: 00:00:00.03
14:45:46 SYS@ test1>/

Elapsed: 00:00:00.03
14:48:56 SYS@ test1>

4、library cache 调优目标：
-----让大部分执行过的sql和plsql 语句都能共享，提高命中率，减少语句的再次解析的次数（硬解析）
1）保证用户执行的语句可以被共享（在oltp的库中，尽量使用绑定变量）
2）有足够的空间保证语句的共享

------查看share pool的空闲空间
10:33:26 SYS@ test1>select * from v$sgastat
10:33:32 2 where pool =‘shared pool‘ and name like ‘%free memory%‘;

POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 18716444

------通过oracle advisory 设定share pool 的尺寸
Shared Pool Advisory DB/Inst: TEST1/test1 End Snap: 12

Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
44 .8 9 1,558 111 1.0 26 1.2 36,144
52 1.0 16 2,518 115 1.0 22 1.0 37,492
60 1.2 23 3,759 116 1.0 21 1.0 37,874

1）share pool size： share pool 的大小
2）sp size factr：估算值和实际值的比率，如果是1，那么share pool size就是当前share pool的实际尺寸
3）est lc size： library cache 的大小
4）est lc mem obj：在library cache 存放对象的数量
5）time saved ：目标存放的时间
6）est lc tim saved factr ：影响对象在library cache 存放时间的因子（因子：发生的可能性）

----在调整shared pool 尺寸时，主要考虑，est lc tim saved factr因子的变化
7）est lc mem obj hits ：library cache存放的（语句、解析代码、执行计划）的命中次数

3）避免共享语句无效（invalidations），再次解析

-------library cache 优化参数

v$librarycache
描述了librery cache的性能和活动指标。
Name Type Nullable Default Comments
------------------------- ------------ -------- ------- --------
NAMESPACE VARCHAR2(15) Y library cache中各种对象名称分类
GETS NUMBER Y 在解析过程中library cache中寻找的次数
GETHITS NUMBER Y 在解析过程中library cache中寻找并获取次数
GETHITRATIO NUMBER Y 在解析过程中library cache中命中的比率
PINS NUMBER Y 在执行过程中library cache中寻找的次数
PINHITS NUMBER Y 在执行过程中library cache中寻找并获取次数
PINHITRATIO NUMBER Y 在执行过程中library cache中命中的比率
RELOADS NUMBER Y 被访问对象从磁盘读取到内存的次数
INVALIDATIONS NUMBER Y 被访问对象结构发生变化导致hard parse的次数

11:26:33 SYS@ test1>alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.14
11:26:42 SYS@ test1>select ename,sal,deptno from scott.emp;

11:26:54 SYS@ test1>select namespace,pins,reloads,invalidations from v$librarycache;

NAMESPACE PINS RELOADS INVALIDATIONS
--------------- ---------- ---------- -------------
SQL AREA 18677 342 0

11:27:31 SYS@ test1>analyze table scott.emp compute statistics;

Table analyzed.

Elapsed: 00:00:00.53
11:28:19 SYS@ test1>select ename,sal,deptno from scott.emp;

11:28:29 SYS@ test1>select namespace,pins,reloads,invalidations from v$librarycache;

NAMESPACE PINS RELOADS INVALIDATIONS
--------------- ---------- ---------- -------------
SQL AREA 19247 369 2

-----由于对emp 做了分析，则和此对象有关的sql语句在library cache里面的共享就会被标记为invalid，所以再次执行此sql 语句会reload ，发生硬解析；所以对表的分析
无需频繁，只是在表结构和字段上的数据发生严重变化的时候，再做表分析

---在稳定运行的oracle db 中，reload 的比率应该低于1%
11:39:34 SYS@ test1>select sum(pins) "Executions",sum(reloads) "cache Misses" ,sum(reloads)/sum(pins) from v$librarycache;

Executions cache Misses SUM(RELOADS)/SUM(PINS)
---------- ------------ ----------------------
27343 813 .029733387

Elapsed: 00:00:00.18

11:58:12 SYS@ test1>select gets,gethits,gethitratio,pinhitratio ,namespace from v$librarycache
11:59:37 2 where namespace =‘SQL AREA‘;

GETS GETHITS GETHITRATIO PINHITRATIO NAMESPACE
---------- ---------- ----------- ----------- ---------------
1047 133 .127029608 .959151938 SQL AREA

Elapsed: 00:00:00.02

12:02:00 SCOTT@ test1>begin
12:02:05 2 for i in 1..100000 loop
12:02:14 3 execute immediate ‘insert into t1 values (‘||i||‘)‘ ;
12:02:51 4 end loop;
12:02:54 5 end;
12:02:56 6 /

PL/SQL procedure successfully completed.
-----在insert 过程不使用绑定变量，语句被解析10万次，执行10万次

11:59:42 SYS@ test1>select gets,gethits,gethitratio,pinhitratio ,namespace from v$librarycache
12:04:38 2 where namespace =‘SQL AREA‘;

GETS GETHITS GETHITRATIO PINHITRATIO NAMESPACE
---------- ---------- ----------- ----------- ---------------
101220 158 .001560956 .217968611 SQL AREA

Elapsed: 00:00:00.04

-------导致gethitratio 在解析过程中命中的比率下降
导致pinhitratio 在执行过程中过的命中率下降
gets 的次数增加了10万次以上

-------查看sql 执行所分配的内存空间
12:14:21 SYS@ test1>select ename,sal,deptno from scott.emp where deptno=30;

Elapsed: 00:00:00.05
12:14:44 SYS@ test1>select sql_text ,SHARABLE_MEM from v$sqlarea
12:15:17 2 where sql_text like ‘select ename,sal,deptno from scott.emp%‘;

SQL_TEXT SHARABLE_MEM
-------------------------------------------------- ------------
select ename,sal,deptno from scott.emp where deptn 12633
o=:num

select ename,sal,deptno from scott.emp where deptn 8667
o=30

Elapsed: 00:00:00.02

--------库缓存的命中率应保持在95%，否则应考虑增大shared_pool_size

SELECT SUM(pins) "Executions",SUM(reloads) "CacheMisses while Executing",

ROUND((SUM(pins)/(SUM(reloads)+SUM(pins)))*100,2)

"HitRatio, %" FROM V$LIBRARYCACHE;

Executions CacheMisses while Executing HitRatio, %
---------- --------------------------- -----------
778784 1303 99.83

4）避免碎片产生：（当调入大的对象时如存储过程、函数、触发器等，如果library cache里没有连续的可用大的空间时，会产生碎片）
---将需求大的内存空间的plsql语句，放入保留池

13:14:42 SYS@ test1>show parameter reserv

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 3M

--保留池的大小一般在share pool size 的10-50%

----将经常调用的大对象保留在library cache里，避免大的对象在多次load后，产生碎片
13:36:31 SYS@ test1>r
1 select * from v$db_object_cache
2 where sharable_mem >100000
3 and (type=‘PACKAGE‘ OR type=‘PACKAGE BODY‘ OR TYPE=‘FUNCTION‘ OR TYPE=‘PROCEDURE‘)
4 AND KEPT=‘NO‘
5*

OWNER NAME DB_LINK NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEP
---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---
CHILD_LATCH INVALIDATIONS
----------- -------------
SYS STANDARD TABLE/PROC PACKAGE 438620 3 0 3 0 NO
EDURE
1 0

11:33:31 SYS@ test1 >col owner for a10
11:33:38 SYS@ test1 >col name for a20
11:33:42 SYS@ test1 >col type for a20
11:33:48 SYS@ test1 >r
1 select owner,name,TYPE,SHARABLE_MEM,KEPT from v$db_object_cache
2 where sharable_mem >100000
3 and (type=‘PACKAGE‘ OR type=‘PACKAGE BODY‘ OR TYPE=‘FUNCTION‘ OR TYPE=‘PROCEDURE‘)
4* AND KEPT=‘NO‘

OWNER NAME TYPE SHARABLE_MEM KEP
---------- -------------------- -------------------- ------------ ---
SYS STANDARD PACKAGE 565652 NO
SYS DBMS_BACKUP_RESTORE PACKAGE BODY 115736 NO
SYS DBMS_BACKUP_RESTORE PACKAGE 344488 NO
SYS DBMS_RCVMAN PACKAGE BODY 521940 NO
SYS DBMS_RCVMAN PACKAGE 310188 NO
SYS PRVT_ADVISOR PACKAGE 123264 NO
SYS PRVT_HDM PACKAGE BODY 104100 NO
SYS PRVT_ADVISOR PACKAGE BODY 123380 NO

8 rows selected.

Elapsed: 00:00:00.01
11:33:48 SYS@ test1 >

----将standard package 驻留到library cache

13:36:32 SYS@ test1>execute dbms_shared_pool.keep(‘STANDARD‘);

PL/SQL procedure successfully completed.

11:53:47 SYS@ test1>select * from v$db_object_cache
11:54:10 2 where sharable_mem >100000
11:54:10 3 and (type=‘PACKAGE‘ OR type=‘PACKAGE BODY‘ OR TYPE=‘FUNCTION‘ OR TYPE=‘PROCEDURE‘)
11:54:10 4 AND KEPT=‘NO‘;

no rows selected

-----保留池（reserved pool）

1、当一些大的对象需要分配连续的大的空间时，oracle 可以用保留池来存放这些对象的语句代码和解析信息
2、保留池的大小：
shared_pool_reserved_size 2936012，一般为shared_pool SIZE 的5%-10%，最多不能超过50%

11:57:06 SYS@ test1>desc v$shared_pool_reserved
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FREE_SPACE NUMBER
AVG_FREE_SIZE NUMBER
FREE_COUNT NUMBER
MAX_FREE_SIZE NUMBER
USED_SPACE NUMBER
AVG_USED_SIZE NUMBER
USED_COUNT NUMBER
MAX_USED_SIZE NUMBER
REQUESTS NUMBER
REQUEST_MISSES NUMBER
LAST_MISS_SIZE NUMBER
MAX_MISS_SIZE NUMBER
REQUEST_FAILURES NUMBER
LAST_FAILURE_SIZE NUMBER
ABORTED_REQUEST_THRESHOLD NUMBER
ABORTED_REQUESTS NUMBER
LAST_ABORTED_SIZE NUMBER

12:02:05 SYS@ test1>select FREE_SPACE,USED_SPACE,REQUESTS from v$shared_pool_reserved;

FREE_SPACE USED_SPACE REQUESTS
---------- ---------- ----------
2767544 0 6

----在plsql的代码中不要使用大的匿名块，可以用存储过程、函数或更小的匿名块代替
----如果server process 是share 模式，启用large pool 将uga 放入large pool

5、dictionary cache调优目标

减少读取目标在cache 未命中的比率（miss）

在dictionary cache 数据以行的方式存放，所以dictionary cache也叫row cache
13:49:20 SYS@ test1>select PARAMETER,gets,GETMISSES,round((GETMISSES/GETS*100),2) "Misses %" from v$rowcache
13:49:59 2 where PARAMETER like ‘%objects%‘;

PARAMETER GETS GETMISSES Misses %
-------------------------------- ---------- ---------- ----------
dc_objects 204709 745 .36

Elapsed: 00:00:00.02
13:50:14 SYS@ test1>

--对于常用对象的misses < 2%,整体的< 15% ,否则考虑增加share pool的大小
Elapsed: 00:00:00.02
13:50:14 SYS@ test1>select sum(gets),sum(getmisses),round((sum(getmisses)/sum(gets)*100),2) "Misses %" from v$rowcache
13:53:19 2 ;

SUM(GETS) SUM(GETMISSES) Misses %
---------- -------------- ----------
439436 8641 1.97

11:58:07 SYS@ test1 >select PARAMETER,sum(gets),sum(getmisses),round((sum(getmisses)/sum(gets)*100),2) "Misses %" from v$rowcache
11:58:32 2 where gets <>0
11:58:37 3 group by PARAMETER;

PARAMETER SUM(GETS) SUM(GETMISSES) Misses %
-------------------------------- ---------- -------------- ----------
dc_free_extents 15 5 33.33
dc_tablespaces 1469 17 1.16
dc_awr_control 42 1 2.38
dc_object_grants 4 2 50
dc_histogram_data 1192 348 29.19
dc_rollback_segments 640 34 5.31
dc_sequences 2 2 100
dc_segments 625 135 21.6
dc_objects 6018 515 8.56
dc_histogram_defs 1998 849 42.49
dc_users 1651 7 .42
outstanding_alerts 22 11 50
dc_files 15 15 100
dc_global_oids 34 12 35.29
global database name 785 1 .13

15 rows selected.

Elapsed: 00:00:00.01
11:58:45 SYS@ test1 >

---------uga

1、当server process 是专有模式，uga 在PGA 中分配
2、当server process 是share 模式，默认在share pool 分配；如启用了large pool，则在large pool 分配

6、large pool

1）在server process 是共享模式时，分配uga 空间
2）并行操作
3）备份恢复及rman 备份恢复
4）启动server process 异步I/O （dbwr_io_slaves）

dbwr 写：

1、异步:当后台进程在写脏块时，前台应用还可以继续进行
2、同步：在后台进程写脏块时，前台应用必须要等待脏块写完才能继续下面的应用（串行操作）

---默认unix/linux系统都支持异步I/O

如果操作系统不支持，oracle可以模拟启动异步从进程 salve I/O

13:53:25 SYS@ test1>show parameter syn

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
disk_asynch_io boolean TRUE
tape_asynch_io boolean TRUE

----默认操作系统都支持异步I/O,如果不支持，可以通过oracle 模拟异步I/O进程
13:58:22 SYS@ test1>show parameter dbwr

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
dbwr_io_slaves integer 0

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 调优3（share pool 调整）

调优3（share pool 调整）

看完仍有疑问？有类似问题直接问程序猿