背景

  在ORACLE 10G并行查询情况下，如何使用大池，之前文章中如果配置parallel_automatic_tuning参数为TRUE，可使用大池；
在MOS 文章Parallel Execution: Large/Shared Pool and ORA-4031 (Doc ID 238680.1) ，配置隐含参数
也可以使用大池，本文即进行测试，加深了解。

结论

 1,在oracle10g下，sga采用自动管理模式，并行查询会从大池分配内存
 或者
   若配置_px_use_large_pool为true,并行查询也会使用大池内存
 或者
   或者parallel_automatic_tuning=true,并行查询也会使用大池内存
 2,并行查询会使用共享池中的px subheap，且此值一般不会变化，为65536BYTE
 3,并行查询px msg pool消耗内存大小与并行度及parallel_execution_message_size有关

测试

1,数据库版本
SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi


SQL> 


3，配置parallel_automatic_tuning为默认值FALSE，如此参数配置为TRUE，也可使用大池
SQL> show parameter parallel_auto


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_automatic_tuning            boolean     FALSE




4，隐含参数_PX_use_large_pool默认值为FALSE，如配置为TRUE，并行查询可以使用大池
NAME_1
--------------------------------------------------------------------------------
VALUE_1
--------------------------------------------------------------------------------
DESC1
--------------------------------------------------------------------------------
_PX_use_large_pool
FALSE
Use Large Pool as source of PX buffers


此参数为静态参数，必须重启库
SQL> alter system set "_px_use_large_pool"=true;
alter system set "_px_use_large_pool"=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified




SQL> alter system set "_px_use_large_pool"=true scope=spfile;


System altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.


Total System Global Area 1157627904 bytes
Fixed Size                  2095800 bytes
Variable Size             184550728 bytes
Database Buffers          939524096 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.


5,查询大池使用情况
SQL> select pool,name,bytes from v$sgastat where pool='large pool';


POOL         NAME                            BYTES
------------ -------------------------- ----------
large pool   PX msg pool                    262144
large pool   free memory                  16515072




6，运行并行查询
SQL> select pool,name,bytes from v$sgastat where pool='large pool';


POOL         NAME                            BYTES
------------ -------------------------- ----------
large pool   PX msg pool                    262144
large pool   free memory                  16515072


7,并行查询一般没有变化,可能是并行查询并行度不够
SQL> select /*+ parallel(a,2) */ count(a.a) from t_test a,t_test b;


[ora10g@seconary ~]$ ps -ef|grep -i --color p00
ora10g   14059     1  0 01:50 ?        00:00:00 ora_p000_ora10g
ora10g   14061     1  0 01:50 ?        00:00:00 ora_p001_ora10g
ora10g   14063     1  0 01:50 ?        00:00:00 ora_p002_ora10g
ora10g   14065     1  0 01:50 ?        00:00:00 ora_p003_ora10g
ora10g   14067     1  0 01:50 ?        00:00:00 ora_p004_ora10g


8，最大并行进程为20，最大并行进程为5
SQL> show parameter parallel_max_servers


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     20


parallel_min_servers                 integer     5


9，加大并行查询的并行度,加大了并行度并地查询就会使用大池了
SQL> select pool,name,bytes from v$sgastat where pool='large pool';


POOL         NAME                            BYTES
------------ -------------------------- ----------
large pool   PX msg pool                    262144 --PX MSG POOL子组件有增加
large pool   free memory                  45858816
large pool   KSFQ Buffers                  4210688


SQL> select /*+ parallel(a,22) */ count(a.a) from t_test a,t_test b;


COUNT(A.A)
----------
       100




SQL> select pool,name,bytes from v$sgastat where pool='large pool';


POOL         NAME                            BYTES
------------ -------------------------- ----------
large pool   PX msg pool                    327680
large pool   free memory                  45793280
large pool   KSFQ Buffers                  4210688       


--并行进程也随之发生了递增
SQL> host ps -ef|grep -i --color p00
ora10g   14059     1  0 01:50 ?        00:00:00 ora_p000_ora10g
ora10g   14061     1  0 01:50 ?        00:00:00 ora_p001_ora10g
ora10g   14063     1  0 01:50 ?        00:00:00 ora_p002_ora10g
ora10g   14065     1  0 01:50 ?        00:00:00 ora_p003_ora10g
ora10g   14067     1  0 01:50 ?        00:00:00 ora_p004_ora10g
ora10g   14274     1  0 01:58 ?        00:00:00 ora_p005_ora10g
ora10g   14276     1  0 01:58 ?        00:00:00 ora_p006_ora10g
ora10g   14278     1  0 01:58 ?        00:00:00 ora_p007_ora10g
ora10g   14280     1  0 01:58 ?        00:00:00 ora_p008_ora10g
ora10g   14282     1  0 01:58 ?        00:00:00 ora_p009_ora10g


10，继续测试，MOS上述文章说，如果配置AMM即SGA_TARGET=指定值，并行查询也会使用大池


---原有SGA半自动管理的参数配置
SQL> show parameter sga_target


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 1104M
SQL> show parameter db_cache


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      OFF
db_cache_size                        big integer 512M
SQL> show parameter _pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 48M
olap_page_pool_size                  big integer 0
shared_pool_size                     big integer 208M
streams_pool_size                    big integer 0


11,启用SGA全自劝管理
SQL> alter system set db_cache_size=0;


System altered.


SQL> alter system set large_pool_size=0;


System altered.


SQL> alter system set shared_pool_size=0;


System altered.


SQL> show parameter _pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0




SQL> alter system set "_px_use_large_pool"=false scope=spfile;


System altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.


Total System Global Area 1157627904 bytes
Fixed Size                  2095800 bytes
Variable Size             285214024 bytes
Database Buffers          838860800 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.


12,运行并行查询前


SQL> select pool,name,bytes from v$sgastat where pool='large pool';


POOL         NAME                            BYTES
------------ -------------------------- ----------
large pool   PX msg pool                    262144
large pool   free memory                  16515072


13，运行平行查询后，可见在SGA_TARGET配置为自动管理模式，并行查询也会使用大池


SQL> conn scott/system
Connected.
SQL> select /*+ parallel(a,22) */ count(a.a) from t_test a,t_test b;


large pool   PX msg pool                    327680
large pool   free memory                  16449536


SQL> select pool,name,bytes from v$sgastat where pool='large pool';


POOL         NAME                            BYTES
------------ -------------------------- ----------
large pool   PX msg pool                    327680
large pool   free memory                  16449536


14,并行查询也会使用共享池中px subheap的子组件而占用内存
SQL> select pool,name,bytes from v$sgastat where pool='large pool' or (pool='shared pool'  and name='PX subheap');


POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  PX subheap                      65336
large pool   PX msg pool                    327680


可见每次数据库启动会固定为并行查询分配内存
SQL> alter system flush shared_pool;


System altered.


SQL> select pool,name,bytes from v$sgastat where pool='large pool' or (pool='shared pool'  and name='PX subheap');


POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  PX subheap                      65336
large pool   PX msg pool                    327680
large pool   free memory                  16449536




large pool   free memory                  16449536




SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.


Total System Global Area 1157627904 bytes
Fixed Size                  2095800 bytes
Variable Size             285214024 bytes
Database Buffers          838860800 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.


SQL> select pool,name,bytes from v$sgastat where pool='large pool' or (pool='shared pool'  and name='PX subheap');


POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  PX subheap                      65336
large pool   PX msg pool                    262144
large pool   free memory                  16515072


15,如下参数加大可以提升并行执行的性能，但会消耗更多的物理内存，一般此值足够了


SQL> show parameter parallel_execution_message_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size      integer     2152


测试加大此值与并行执行消耗大池或共享池的关系
SQL> alter system set parallel_execution_message_size=2048 scope=spfile;
alter system set parallel_execution_message_size=2048 scope=spfile
*
ERROR at line 1:
ORA-00068: invalid value 2048 for parameter parallel_execution_message_size,
must be between 2148 and 65535




SQL> alter system set parallel_execution_message_size=2148 scope=spfile;


System altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.


Total System Global Area 1157627904 bytes
Fixed Size                  2095800 bytes
Variable Size             285214024 bytes
Database Buffers          838860800 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.
SQL> show parameter parallel_execu


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size      integer     2152




SQL> select pool,name,bytes from v$sgastat where pool='large pool' or (pool='shared pool'  and name='PX subheap');


POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  PX subheap                      65336
large pool   PX msg pool                    262144
large pool   free memory                  16515072

参考资料：
 Parallel Execution: Large/Shared Pool and ORA-4031 (文档 ID 238680.1)

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

服务过的客户：

中国电信

中国移动

中国联通

中国电通

国家电网

四川达州商业银行

湖南老百姓大药房

山西省公安厅

中国邮政

北京302医院     

河北廊坊新奥集团公司

 项目经验:

中国电信3G项目AAA系统数据库部署及优化
      中国联通4G数据库性能分析与优化

中国联通CRM数据库性能优化

中国移动10086电商平台数据库部署及优化

湖南老百姓大药房ERR数据库sql优化项目

四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

北京高铁信号监控系统RAC数据库部署及优化

河南宇通客车数据库性能优化

中国电信电商平台核心采购模块表模型设计及优化

中国邮政储蓄系统数据库性能优化及sql优化

北京302医院数据库迁移实施

河北廊坊新奥data guard部署及优化

山西公安厅身份证审计数据库系统故障评估

国家电网上海灾备项目4 node rac+adg 
       贵州移动crm及客服数据库性能优化项目
       贵州移动crm及客服务数据库sql审核项目
       深圳穆迪软件有限公司数据库性能优化项目

联系方式：

手机：18201115468

qq   :   305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900    

itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/