背景
本文主要学习在启用并行查询情况下，如何配置大池。结论
1,在oracle10g中要在并行查询中使用大池，必须配置parallel_automatic_tuning=true2,parallel_automatic_tuning默认值为FALSE，且是一个已经废弃的参数
3,在并行查询中使用大池的是子组件PX msg pool
4,至于配置合理的大池，可以参考如下计算公式，含义为计算大池的可用空间率，如果此值一直偏小，可以考虑加大大池
select
(select bytes from v$sgastat where pool='large pool' and name='free memory')
/
(select sum(bytes) from v$sgastat where pool='large pool')
from dual;
5,并行配置的一些参数的优先级要高于SQL的并行HINT，而并行HINT的优先级要高于并行会话的配置，比如并行DML会话或者并行DDL会话或者并行查询会话模式
测试
1,查看大池配置
SQL> show parameter large_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 48M
2，查看并行相关的参数
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 20
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recovery_parallelism integer 0
3,创建测试表
SQL> create table t_test(a int,b int);
Table created.
SQL> insert into t_test select level,level from dual connect by level<=1000000;
1000000 rows created.
SQL> commit;
Commit complete.
4，当前会话启用并行查询模式
SQL> alter session force parallel query;
Session altered.
5,可见若当前会话启用并行查询模式，则所属SQL会使用并行查询
SQL> select count(1) from t_test;
Execution Plan
----------------------------------------------------------
Plan hash value: 4081899150
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 270 (4)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 950K| 270 (4)| 00:00:04 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T_TEST | 950K| 270 (4)| 00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
2242 consistent gets
2072 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
6，可见是否并行hint要优先于并行查询会话模式的设备
SQL> select /*+ noparallel (t_test) */ count(1) from t_test;
Execution Plan
----------------------------------------------------------
Plan hash value: 2253469265
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 487 (4)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_TEST | 950K| 487 (4)| 00:00:06 |
---------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
57 recursive calls
36 db block gets
2160 consistent gets
0 physical reads
5532 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
7，如果不启用并行查询参数，还会启用并行查询吗,如果关闭并行查询参数，即使开启查询查询模式或并行查询HINT，仍旧不会启用并行查询
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 20
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recovery_parallelism integer 0
SQL> alter system set parallel_max_servers=0;
System altered.
SQL> show parameter parallel_max_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 0
SQL> select count(1) from t_test;
Execution Plan
----------------------------------------------------------
Plan hash value: 2253469265
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 487 (4)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_TEST | 950K| 487 (4)| 00:00:06 |
---------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
30 recursive calls
120 db block gets
2173 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
8，再看看在DML或DDL中如何使用并行查询
SQL> create table t_test(a int,b int);
Table created.
SQL> insert into t_test select level,level from dual connect by level<=100000;
100000 rows created.
SQL> commit;
Commit complete.
可见在create as 中可以使用并行查询
SQL> create table t_test1 as select * from t_test;
Table created.
SQL> select sql_id,sql_text from v$sql where lower(sql_text) like '%create table t_test1 as select * from t_test%';
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
az8v6cnjbtnhy
create table t_test1 as select * from t_test
SQL> select * from table(dbms_xplan.display_cursor('az8v6cnjbtnhy'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID az8v6cnjbtnhy, child number 0
-------------------------------------
create table t_test1 as select * from t_test
Plan hash value: 3770611746
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 70 (100)| | | | |
| 1 | LOAD AS SELECT | | | | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 87364 | 2218K| 32 (4)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 87364 | 2218K| 32 (4)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T_TEST | 87364 | 2218K| 32 (4)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
26 rows selected.
SQL>
DML也可以使用并行DML
SQL> delete from t_test;
100000 rows deleted.
SQL> select * from table(dbms_xplan.display_cursor('7zpf4902mxt6x'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7zpf4902mxt6x, child number 0
-------------------------------------
delete from t_test
Plan hash value: 1068266099
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | 31 (100)| | | | |
| 1 | DELETE | T_TEST | | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 87364 | 31 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 87364 | 31 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| T_TEST | 87364 | 31 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
26 rows selected.
SQL>
在DELETE中的SELECT也可以使用并行查询
SQL> delete from t_test where a in (select a from t_test);
100000 rows deleted.
SQL> select sql_id,sql_text from v$sql where lower(sql_text) like '%delete from t_test where a in (select a from t_test)%';
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4bwbx5srqc0g8
delete from t_test where a in (select a from t_test)
SQL> select * from table(dbms_xplan.display_cursor('4bwbx5srqc0g8'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4bwbx5srqc0g8, child number 0
-------------------------------------
delete from t_test where a in (select a from t_test)
Plan hash value: 1527085197
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 65 (100)| | | | |
| 1 | DELETE | T_TEST | | | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 87364 | 2218K| 65 (7)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 4 | HASH JOIN SEMI | | 87364 | 2218K| 65 (7)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 87364 | 1109K| 31 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 6 | TABLE ACCESS FULL | T_TEST | 87364 | 1109K| 31 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 8 | PX RECEIVE | | 87364 | 1109K| 31 (0)| 00:00:01 | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 87364 | 1109K| 31 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 87364 | 1109K| 31 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL| T_TEST | 87364 | 1109K| 31 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"="A")
6 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
34 rows selected.
SQL>
至于其它的UPDATE或INSERT不再测试，同理
9,用如下视图也可以获知大池的空闲率，如果一直空闲率很低，表明大池配置不足，可以考虑加大大池大小，当前无大配置配置信息，是因为未启用大池
SQL> col metric_name for a50
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select begin_time,end_time,metric_name,value,metric_unit from v$metric where lower(metric_name) like '%pool%';
BEGIN_TIME END_TIME METRIC_NAME VALUE METRIC_UNIT
------------------- ------------------- -------------------------------------------------- ---------- ----------------------------------------------------------------
2015-09-22 06:11:15 2015-09-22 06:12:15 Shared Pool Free % 60.6116411 % Free/Total
2015-09-22 06:11:15 2015-09-22 06:12:15 Streams Pool Usage Percentage 0 % Memory allocated / Size of Streams pool
2015-09-22 06:12:30 2015-09-22 06:12:45 Shared Pool Free % 60.5681776 % Free/Total
SQL>
SQL> alter system set disk_asynch_io=false scope=spfile;
System altered.
SQL> alter system set dbwr_io_slaves=2 scope=spfile;
System altered
使用了大池仍未从v$metric体现出来
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
POOL NAME MB
------------ -------------------------- ----------
large pool free memory 43.9804688
large pool KSFQ Buffers 4.01953125
SQL>
SQL>
SQL> select begin_time,end_time,metric_name,value,metric_unit from v$metric where lower(metric_name) like '%pool%';
BEGIN_TIM END_TIME METRIC_NAME VALUE METRIC_UNIT
--------- --------- -------------------------------------------------- ---------- ----------------------------------------------------------------
22-SEP-15 22-SEP-15 Shared Pool Free % 71.9869346 % Free/Total
22-SEP-15 22-SEP-15 Streams Pool Usage Percentage 0 % Memory allocated / Size of Streams pool
22-SEP-15 22-SEP-15 Shared Pool Free % 71.8579992 % Free/Total
从现存的度量中，确实没有这个度量指标
SQL> col metric_name for a30
SQL> select group_name,metric_name,metric_unit from v$metricname where lower(metric_name) like '%free%';
GROUP_NAME METRIC_NAME METRIC_UNIT
-------------------------------------------------- ------------------------------ ----------------------------------------------------------------
System Metrics Long Duration Shared Pool Free % % Free/Total
System Metrics Short Duration Shared Pool Free % % Free/Total
经过查阅官方手册，可见直接关于大池的度量指标，而是一个间接经过换算的指标，不过从这儿也可以知道，如果大池可用空闲率一直很低，可以考虑加大大池
Data Source
((Free/Total)*100) where:
Free: select sum(decode(name,'free memory',bytes)) from v$sgastat where pool = 'large pool'
Total: select sum(bytes) from v$sgastat where pool = 'large pool'
10，继续学习如何在并行查询中配置大池
SQL> conn scott/system
Connected.
SQL> alter session force parallel query;
Session altered.
SQL> create table t_test(a int,b int);
Table created.
SQL> insert into t_test select level,level from dual connect by level<=100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from t_test;
COUNT(1)
----------
100000
可见仍未使用大池
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
POOL NAME MB
------------ -------------------------- ----------
large pool free memory 48
肯定是某些参数未配置，所以没有使用大池，经过查阅官方手册,好像是说parallel_automatic_tuning在进行配置方可使用大池
并且还要官方文档说此参数已经废弃不用了
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 20
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recovery_parallelism integer 0
看来此参数确实无用了
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 352322888 bytes
Database Buffers 771751936 bytes
Redo Buffers 31457280 bytes
Database mounted.
Database opened.
虽然启动库报错，不过这下终于并行查询使用大池了
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
POOL NAME MB
------------ -------------------------- ----------
large pool PX msg pool .375
large pool free memory 47.625
从官方文档可知parallel_automatic_tuning与如下参数也有关系，此参数用于在多用户行令并行执行性能最佳，默认值为TRUE
SQL> show parameter PARALLEL_ADAPTIVE_MULTI_USER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user boolean TRUE
反过来我想下，如果我再次把参数parallel_automatic_tuning恢复为FALSE默认值，并行查询应该不会使用大池吗,可见不会使用大池
SQL> show parameter parallel_automatic_tuning
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_automatic_tuning boolean FALSE
SQL> alter session force parallel query;
Session altered.
SQL> select count(1) from t_test;
COUNT(1)
----------
400000
SQL> select pool,name,bytes/1024/1024 as mb from v$sgastat where pool='large pool';
POOL NAME MB
------------ -------------------------- ----------
large pool free memory 48
参考资料
oracle conceptoracle administator guide
oracle dataware guide
oralce performance guide
http://blog.csdn.net/gxftry1st/article/details/23035967
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1806660/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1806660/
889
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
