背景

本文主要學習在啟用並行查詢情況下，如何配置大池。

結論

1,在oracle10g中要在並行查詢中使用大池，必須配置parallel_automatic_tuning=true
2,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 concept
oracle administator guide
oracle dataware guide
oralce performance guide
http://blog.csdn.net/gxftry1st/article/details/23035967

個人簡介

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/

oracle 10g sga手動管理mmm 基於並行查詢parallel query如何配置large_pool_size大池

背景

結論

測試

參考資料

個人簡介

相關文章