oracle10g parallel execution並行執行與大池large pool使用之四
背景
在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資料庫效能分析與最佳化
中國聯通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-1810975/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 10g sga手動管理mmm 基於並行查詢parallel query如何配置large_pool_size大池Oracle 10g並行Parallel
- alter session enable parallel dml 使DML語句並行執行SessionParallel並行
- Oracle資料庫並行機制Parallel ExecutionOracle資料庫並行Parallel
- 並行（Parallel）並行Parallel
- oracle10g在sga手工管理mmm模式如何配置大池large_pool_size(一）Oracle模式
- 各種執行緒池實現，pool執行緒
- 執行緒池以及四種常見執行緒池執行緒
- Parallel 並行技術Parallel並行
- Oracle並行操作——並行查詢（Parallel Query）Oracle並行Parallel
- 使用11g dbms_parallel_execute執行並行更新（下）Parallel並行
- 使用11g dbms_parallel_execute執行並行更新（上）Parallel並行
- ORACLE ORA-00020與parallel並行OracleParallel並行
- Oracle Parallel ExecutionOracleParallel
- 簡單的執行緒池（四）執行緒
- Program Execution 程式的執行
- parallel: 一個簡單的並行執行Go迴圈的庫Parallel並行Go
- java--執行緒池--建立執行緒池的幾種方式與執行緒池操作詳解Java執行緒
- oracle large poolOracle
- Python執行緒池與程式池Python執行緒
- 執行緒與執行緒池的那些事之執行緒池篇（萬字長文）執行緒
- 並行閘道器 Parallel Gateway並行ParallelGateway
- 並行處理 Parallel Processing並行Parallel
- oracle的Parallel 並行技術OracleParallel並行
- 26、多執行緒與並行執行緒並行
- JDK提供的四種執行緒池JDK執行緒
- 常見的四種執行緒池執行緒
- java 四種執行緒池的使用Java執行緒
- Java四種執行緒池的使用Java執行緒
- 11G R2中的並行執行，dbms_parallel_execute並行Parallel
- oracle parallel並行_引數parameter_parallel_max_serverOracleParallel並行Server
- Android程式框架：執行緒與執行緒池Android框架執行緒
- Parallel Execution of SQL StatementsParallelSQL
- 課程 3: 執行緒與並行執行緒並行
- Java執行緒池二：執行緒池原理Java執行緒
- 並行執行並行
- 淺談執行緒池（上）：執行緒池的作用及CLR執行緒池執行緒
- 【Oracle】並行等待之PX Deq: Execution MsgOracle並行
- Java 執行緒池四種拒絕策略Java執行緒