ITPub博客

首页 > 数据库 > Oracle > oracle 10g sga手动管理mmm 基于并行查询parallel query如何配置large_pool_size大池

oracle 10g sga手动管理mmm 基于并行查询parallel query如何配置large_pool_size大池

原创 Oracle 作者:wisdomone1 时间:2015-09-22 22:18:48 0 删除 编辑

背景

  本文主要学习在启用并行查询情况下,如何配置大池。


结论

  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/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-1806660/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2150
  • 访问量
    11877505