LOB-part6-SecureFile Shared I/O Pool

在对LOB的性能进行优化的过程中会涉及到很多个具体的选项和参数,具体可以参考Oracle Documentmandalika在他的博客中也对性能优化进行了总结。本文主要是针对Shared I/O Pool size进行实验和测试。
Oracle数据库使用Shared I/O pool进行securefile lob的I/O操作,Shared I/O pool使用共享内存段,如果Shared I/O pool不够大或者没有充足的内存来支持SecureFile LOB的I/O操作的话,Oracle会使用PGA的以部门来支持SecureFile LOB的I/O操作,直到在Shared I/O pool有足够的内存,所以当数据库中发生了内存peak的时候,通过监控内存的使用合理的调整Shared I/O pool很重要。控制Shared I/O pool的隐含参数包括_shared_io_pool_size、__shared_io_pool_size_shared_iop_max_size。当没有使用SecureFile LOBs的时候,Shared IO Pool Size默认为0,当使用SecureFile LOB并第一次对SecureFile LOB插入数据之后,Shared IO Pool Size会自动进行设置,在11.2.0.4中测试过程中发现Shared I/O Pool size并没有像Bug 9915743中描述的那样是25% * buffer cache size,而是在0-25%这个区间来变化。
Shared IO Pool Size的最大数值是512M,这个数值由隐含参数_shared_iop_max_size控制,在11.2.0.4和12.1.0.2下均为512M,没有变化。
下面是跟shared_io_pool相关的隐含参数

NUM N_HEX           NAME                               VALUE             DESCRIPTION
--- --------------- ---------------------------------- ----------------- -------------------------------------------------
855   357           __shared_io_pool_size              0                 Actual size of shared IO pool
856   358           _shared_io_pool_size               0                 Size of shared IO pool
921   399           _shared_iop_max_size               536870912         maximum shared io pool size
922   39A           _shared_io_pool_buf_size           1048576           Shared IO pool buffer size
923   39B           _siop_flashback_scandepth          20                Shared IO pool flashback io completion scan depth
924   39C           _shared_io_pool_debug_trc          0                 trace kcbi debug info to tracefile

测试环境为11.2.0.4,分别针对11g使用memory_target(AMM)自动内存管理、ASMM内存管理和手动内存管理进行了测试。

AMM场景

SQL> show parameter memory
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 30G
memory_target                        big integer 30G
shared_memory_address                integer     0

SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 0

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;
NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.16395569
Redo Buffers                     52.6835938
Buffer Cache Size                     15488
Shared Pool Size                       2240
Large Pool Size                          64
Java Pool Size                          448
Streams Pool Size                         0
Shared IO Pool Size                       0
Granule Size                             64
Maximum SGA Size                 30582.8477
Startup overhead in Shared Pool  593.047401
Free SGA Memory Available             12288


创建含有securefile lob字段的表
create table t(id number,name clob) 
lob(name) store as securefile 
(tablespace lob_test disable storage in row nocache);

插入clob数据
SQL> insert into t values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
commit;
1 row created.
Commit complete.

SQL> 
SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.16395569
Redo Buffers                     52.6835938
Buffer Cache Size                     15488
Shared Pool Size                       2240
Large Pool Size                          64
Java Pool Size                          448
Streams Pool Size                         0
Shared IO Pool Size                     512   --上限
Granule Size                             64
Maximum SGA Size                 30582.8477
Startup overhead in Shared Pool  593.047401

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available             12288

Shared IO Pool已经自动设置成最大值512M,因为buffer cache size为15G。数据库的alert日志中出现了下面的信息
Shared IO Pool defaulting to 512MB. Trying to get it from Buffer Cache for process 27530.
Mon Oct 10 16:09:29 2016
Starting background process SMCO
Mon Oct 10 16:09:29 2016
SMCO started with pid=50, OS id=28996
SMCO进程在这个时候进行了工作。

SQL> col KSPPINM for a30
SQL> col KSPPSTVL for a10
SQL> col KSPPSTDVL for a10
SQL> 
SQL> select ksppinm,ksppstvl,ksppstdvl
  2  from x$ksppcv a,x$ksppi b
where a.indx=b.indx and b.ksppinm like '%shared_io%';  3  

KSPPINM                        KSPPSTVL   KSPPSTDVL
------------------------------ ---------- ----------
__shared_io_pool_size          536870912  512M
_shared_io_pool_size           0          0
_shared_iop_max_size           536870912  512M
_shared_io_pool_buf_size       1048576    1M
_shared_io_pool_debug_trc      0          0
_shared_io_set_value           FALSE      FALSE
通过查询内部视图看到__shared_io_pool_size的当前值为512M,而_shared_io_pool_size为0.
接下来查看一下内存的变化情况
SQL> set linesize 200
SQL> SELECT component, min_size, max_size, current_size, last_oper_type
  2  FROM v$sga_dynamic_components
WHERE last_oper_type != 'STATIC';  3  

COMPONENT                                  MIN_SIZE   MAX_SIZE CURRENT_SIZE LAST_OPER_TYP
---------------------------------------- ---------- ---------- ------------ -------------
shared pool                              2214592512 2348810240   2348810240 GROW
DEFAULT buffer cache                     1.5703E+10 1.6375E+10   1.5703E+10 SHRINK
Shared IO Pool                                    0  536870912    536870912 GROW

发现shared IO Pool进行了Resize。接下来重新设置一下内存的总和。
SQL> alter system set memory_max_target = 2048M scope=spfile sid = '*';
alter system set memory_target = 2048M scope=spfile sid = '*';

System altered.

SQL> insert into t values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
commit;
1 row created.

SQL> 

Commit complete.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15048981
Redo Buffers                     20.6953125
Buffer Cache Size                       608
Shared Pool Size                        512
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                         0
Shared IO Pool Size                     112
Granule Size                             16
Maximum SGA Size                 2038.84766

测试一下修改db_cache_size是否会影响Shared IO Pool的大小,从下面的结果看到并没有改变,依然是112M大小。
SQL> ALTER SYSTEM SET db_cache_size = 270426368 sid = 'sid1';

System altered.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15048981
Redo Buffers                     20.6953125
Buffer Cache Size                       608
Shared Pool Size                        512
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                         0
Shared IO Pool Size                     112
Granule Size                             16
Maximum SGA Size                 2038.84766
Startup overhead in Shared Pool  444.048134

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available               832

12 rows selected.


接下来修改memory_target的大小观察变化

SQL> alter system set memory_target = 1440M scope=both sid = '*';

System altered.

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 2G
memory_target                        big integer 1440M
shared_memory_address                integer     0
SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15048981
Redo Buffers                     20.6953125
Buffer Cache Size                       592
Shared Pool Size                        512
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                         0
Shared IO Pool Size                      96
Granule Size                             16
Maximum SGA Size                 2038.84766
Startup overhead in Shared Pool  444.048134

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available               848

12 rows selected.

Shared IO Pool Size在memory_target变化之后自动进行了收缩操作,但是alert告警日志中并没有记录。

SQL> alter system set memory_target = 1940M scope=both sid = '*';

System altered.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15048981
Redo Buffers                     20.6953125
Buffer Cache Size                       592
Shared Pool Size                        512
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                         0
Shared IO Pool Size                      96
Granule Size                             16
Maximum SGA Size                 2038.84766
Startup overhead in Shared Pool  444.048134

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available               848

SQL> insert into t values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
commit;
1 row created.

SQL> 

Commit complete.

SQL> 
SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15048981
Redo Buffers                     20.6953125
Buffer Cache Size                       592
Shared Pool Size                        512
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                         0
Shared IO Pool Size                      96
Granule Size                             16
Maximum SGA Size                 2038.84766
Startup overhead in Shared Pool  444.048134

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available               848

再次扩大memory_target的总和,Shared IO Pool Size并没有扩大。

SQL> SELECT component, min_size, max_size, current_size, last_oper_type
FROM v$sga_dynamic_components
  2    3  WHERE last_oper_type != 'STATIC';

COMPONENT                                  MIN_SIZE   MAX_SIZE CURRENT_SIZE LAST_OPER_TYP
---------------------------------------- ---------- ---------- ------------ -------------
shared pool                               301989888  536870912    536870912 SHRINK
large pool                                 33554432   33554432     33554432 SHRINK
java pool                                  33554432   33554432     33554432 SHRINK
DEFAULT buffer cache                      520093696  721420288    520093696 GROW
Shared IO Pool                                    0  117440512    100663296 SHRINK
从内存动态视图获取到的数据看到Shared IO Pool只进行了SHRINK操作。

ASMM场景

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3G
sga_target                           big integer 2G

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15293884
Redo Buffers                     16.1210938
Buffer Cache Size                      1424
Shared Pool Size                        528
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                         0
Shared IO Pool Size                       0
Granule Size                             16
Maximum SGA Size                 3058.27734
Startup overhead in Shared Pool  469.596489

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available              1024

12 rows selected.

SQL> insert into t values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
commit;
1 row created.

SQL> 

Commit complete.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15293884
Redo Buffers                     16.1210938
Buffer Cache Size                      1424
Shared Pool Size                        528
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                         0
Shared IO Pool Size                     320
Granule Size                             16
Maximum SGA Size                 3058.27734
Startup overhead in Shared Pool  469.596489

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available              1024

Shared IO Pool Size的大小为320M。

SQL> SELECT component, min_size, max_size, current_size, last_oper_type
  2  FROM v$sga_dynamic_components
  3  WHERE last_oper_type != 'STATIC';

COMPONENT                                  MIN_SIZE   MAX_SIZE CURRENT_SIZE LAST_OPER_TYP
---------------------------------------- ---------- ---------- ------------ -------------
shared pool                               452984832  553648128    553648128 GROW
DEFAULT buffer cache                     1157627904 1577058304   1157627904 SHRINK
Shared IO Pool                                    0  335544320    335544320 GROW   --grow!

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15293884
Redo Buffers                     16.1210938
Buffer Cache Size                      1392
Shared Pool Size                        528
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                        32
Shared IO Pool Size                     320
Granule Size                             16
Maximum SGA Size                 3058.27734
Startup overhead in Shared Pool  469.596489

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available              1024

12 rows selected.

SQL> 
SQL> 
SQL> 
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3G
sga_target                           big integer 2G

修改sga_target的大小,观察到Shared IO Pool Size大小变化了。
SQL> alter system set sga_target = 1560M scope=both sid = '*';

System altered.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15293884
Redo Buffers                     16.1210938
Buffer Cache Size                       912
Shared Pool Size                        528
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                        32
Shared IO Pool Size                      96
Granule Size                             16
Maximum SGA Size                 3058.27734
Startup overhead in Shared Pool  469.596489

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available              1504

12 rows selected.

SQL> SELECT component, min_size, max_size, current_size, last_oper_type
  2  FROM v$sga_dynamic_components
  3  WHERE last_oper_type != 'STATIC';

COMPONENT                                  MIN_SIZE   MAX_SIZE CURRENT_SIZE LAST_OPER_TYP
---------------------------------------- ---------- ---------- ------------ -------------
shared pool                               452984832  553648128    553648128 SHRINK
large pool                                 33554432   33554432     33554432 SHRINK
java pool                                  33554432   33554432     33554432 SHRINK
streams pool                                      0   33554432     33554432 SHRINK
DEFAULT buffer cache                      855638016 1577058304    855638016 SHRINK
Shared IO Pool                                    0  335544320    100663296 SHRINK

6 rows selected.

重新增大sga_target,Shared IO Pool Size没有重新进行resize,保持在96M大小。
SQL> alter system set sga_target = 2560M scope=both sid = '*';

System altered.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.15293884
Redo Buffers                     16.1210938
Buffer Cache Size                      1904
Shared Pool Size                        528
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                        32
Shared IO Pool Size                      96
Granule Size                             16
Maximum SGA Size                 3058.27734
Startup overhead in Shared Pool  469.596489

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available               512

SQL> SELECT component, min_size, max_size, current_size, last_oper_type
  2  FROM v$sga_dynamic_components
  3  WHERE last_oper_type != 'STATIC';

COMPONENT                                  MIN_SIZE   MAX_SIZE CURRENT_SIZE LAST_OPER_TYP
---------------------------------------- ---------- ---------- ------------ -------------
shared pool                               452984832  553648128    553648128 SHRINK
large pool                                 33554432   33554432     33554432 SHRINK
java pool                                  33554432   33554432     33554432 SHRINK
streams pool                                      0   33554432     33554432 SHRINK
DEFAULT buffer cache                      855638016 1895825408   1895825408 GROW
Shared IO Pool                                    0  335544320    100663296 SHRINK

SQL> select ksppinm,ksppstvl,ksppstdvl
  2  from x$ksppcv a,x$ksppi b
  3  where a.indx=b.indx and b.ksppinm like '%shared_io%';

KSPPINM                                  KSPPSTVL   KSPPSTDVL
---------------------------------------- ---------- ----------
__shared_io_pool_size                    100663296  96M   -AMM模式下
_shared_io_pool_size                     0          0
_shared_iop_max_size                     536870912  512M
_shared_io_pool_buf_size                 1048576    1M
_shared_io_pool_debug_trc                0          0
_shared_io_set_value                     FALSE      FALSE

手动内存管理场景

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.14846039
Redo Buffers                     8.48046875
Buffer Cache Size                       496
Shared Pool Size                        544
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                        32
Shared IO Pool Size                       0
Granule Size                             16
Maximum SGA Size                 1194.63281
Startup overhead in Shared Pool  443.402855

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available                48

12 rows selected.

SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 496M
SQL> 
SQL> show parameter shared_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 28521267
shared_pool_size                     big integer 544M

插入记录观察内存变化
SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.14846039
Redo Buffers                     8.48046875
Buffer Cache Size                       496
Shared Pool Size                        544
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                        32
Shared IO Pool Size                      80
Granule Size                             16
Maximum SGA Size                 1194.63281
Startup overhead in Shared Pool  443.402855

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available                48

修改db_cache_size的大小
SQL> ALTER SYSTEM SET db_cache_size = 335544320 sid = 'sid1';    --320M

System altered.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.14846039
Redo Buffers                     8.48046875
Buffer Cache Size                       400   --400M = 320 + 80
Shared Pool Size                        544
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                        32
Shared IO Pool Size                      80
Granule Size                             16
Maximum SGA Size                 1194.63281
Startup overhead in Shared Pool  443.402855

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available               144

12 rows selected.

SQL> ALTER SYSTEM SET db_cache_size = 367001600 sid = 'sid1';

System altered.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.14846039
Redo Buffers                     8.48046875
Buffer Cache Size                       432 = 352 + 80
Shared Pool Size                        544
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                        32
Shared IO Pool Size                      80
Granule Size                             16
Maximum SGA Size                 1194.63281
Startup overhead in Shared Pool  443.402855

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available               112

12 rows selected.

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 352M

SQL> ALTER SYSTEM SET db_cache_size = 314572800 sid = 'sid1';

System altered.

SQL> select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                     MB
-------------------------------- ----------
Fixed SGA Size                   2.14846039
Redo Buffers                     8.48046875
Buffer Cache Size                       384 = 80 + 304
Shared Pool Size                        544
Large Pool Size                          32
Java Pool Size                           32
Streams Pool Size                        32
Shared IO Pool Size                      80
Granule Size                             16
Maximum SGA Size                 1194.63281
Startup overhead in Shared Pool  443.402855

NAME                                     MB
-------------------------------- ----------
Free SGA Memory Available               160

12 rows selected.

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 304M

通过设置db_cache_size的值发现shared io pool size并没有变化,但是buffer cache size在sga的内存视图中显示的数值是实际的db_cache_size + shared io pool size。

SQL> SELECT component, min_size, max_size, current_size, last_oper_type
  2  FROM v$sga_dynamic_components
  3  WHERE last_oper_type != 'STATIC';

COMPONENT                                                          MIN_SIZE   MAX_SIZE CURRENT_SIZE LAST_OPER_TYP
---------------------------------------------------------------- ---------- ---------- ------------ -------------
DEFAULT buffer cache                                              318767104  520093696    318767104 SHRINK
Shared IO Pool                                                            0   83886080     83886080 GROW

SQL> select ksppinm,ksppstvl,ksppstdvl
  2  from x$ksppcv a,x$ksppi b
  3  where a.indx=b.indx and b.ksppinm like '%shared_io%';

KSPPINM                                  KSPPSTVL   KSPPSTDVL
---------------------------------------- ---------- ----------
__shared_io_pool_size                    0          0
_shared_io_pool_size                     83886080   80M
_shared_iop_max_size                     536870912  512M
_shared_io_pool_buf_size                 1048576    1M
_shared_io_pool_debug_trc                0          0
_shared_io_set_value                     FALSE      FALSE
这次的查询结果中shared io pool size的大小是参数_shared_io_pool_size来控制,并且shared io pool size的大小没有随着db_cache_size的变化修改而变化。
此条目发表在LOB, Memory分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s