Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › oracle11g自动并行的问题
发新帖
shentianyun

52

积分

0

好友

0

主题

oracle11g自动并行的问题

1#
发表于 2012-6-16 03:09:46 | 查看: 7511| 回复: 3
最近在看11g新特性，碰到个问题请教大神，望赐教~

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter cpu
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     1
parallel_threads_per_cpu             integer     2
resource_manager_cpu_allocation      integer     1

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_degree_limit                string      CPU
parallel_degree_policy               string      AUTO
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     40
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     16
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

SQL> conn sh/sh
Connected.

SQL> set autot traceonly exp
SQL> select sum(a.amount_sold) from sales a,sales b,sales c;
Execution Plan
----------------------------------------------------------
Plan hash value: 3928812053
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |     1 |     5 |    22T  (1)|999:59:59 |       |       |
|   1 |  SORT AGGREGATE                   |                 |     1 |     5 |            |          |       |       |
|   2 |   MERGE JOIN CARTESIAN            |                 |   775P|  3445P|    22T  (1)|999:59:59 |       |       |
|   3 |    MERGE JOIN CARTESIAN           |                 |   844G|  3931G|    24M  (1)| 01:55:25 |       |       |
|   4 |     PARTITION RANGE ALL           |                 |   918K|  4486K|   530   (3)| 00:00:01 |     1 |    28 |
|   5 |      TABLE ACCESS FULL            | SALES           |   918K|  4486K|   530   (3)| 00:00:01 |     1 |    28 |
|   6 |     BUFFER SORT                   |                 |   918K|       |    24M  (1)| 01:55:25 |       |       |
|   7 |      PARTITION RANGE ALL          |                 |   918K|       |    27   (0)| 00:00:01 |     1 |    28 |
|   8 |       BITMAP CONVERSION TO ROWIDS |                 |   918K|       |    27   (0)| 00:00:01 |       |       |
|   9 |        BITMAP INDEX FAST FULL SCAN| SALES_PROMO_BIX |       |       |            |          |     1 |    28 |
|  10 |    BUFFER SORT                    |                 |   918K|       |    22T  (1)|999:59:59 |       |       |
|  11 |     PARTITION RANGE ALL           |                 |   918K|       |    27   (0)| 00:00:01 |     1 |    28 |
|  12 |      BITMAP CONVERSION TO ROWIDS  |                 |   918K|       |    27   (0)| 00:00:01 |       |       |
|  13 |       BITMAP INDEX FAST FULL SCAN | SALES_PROMO_BIX |       |       |            |          |     1 |    28 |
---------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1

按照文档上来讲，对于这个查询肯定超过默认参数PARALLEL_DEGREE_POLICY设置的10s，应该会走并行的，此处为何还是提示为1.

SQL> select ename from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |    98 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

低于10秒的查询，应该是会根据阀值执行串行计划。
2#
发表于 2012-6-16 12:10:47
ODM TEST:
  1. SQL> show parameter parallel

  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. fast_start_parallel_rollback         string      LOW
  6. parallel_adaptive_multi_user         boolean     TRUE
  7. parallel_automatic_tuning            boolean     FALSE
  8. parallel_degree_limit                string      CPU
  9. parallel_degree_policy               string      MANUAL
  10. parallel_execution_message_size      integer     16384
  11. parallel_force_local                 boolean     FALSE
  12. parallel_instance_group              string
  13. parallel_io_cap_enabled              boolean     FALSE
  14. parallel_max_servers                 integer     80
  15. parallel_min_percent                 integer     0

  17. NAME                                 TYPE        VALUE
  18. ------------------------------------ ----------- ------------------------------
  19. parallel_min_servers                 integer     0
  20. parallel_min_time_threshold          string      AUTO
  21. parallel_server                      boolean     FALSE
  22. parallel_server_instances            integer     1
  23. parallel_servers_target              integer     32
  24. parallel_threads_per_cpu             integer     2
  25. recovery_parallelism                 integer     0
  26. SQL>
  27. SQL> alter system set parallel_degree_policy=AUTO;

  29. System altered.

  31. SQL>

  33. SQL>
  34. SQL>
  35. SQL>
  36. SQL> alter system set parallel_min_time_threshold=10;

  38. System altered.



  42. SQL> conn sh/sh
  43. Connected.
  44. SQL> set autot traceonly exp
  45. SQL>  select sum(a.amount_sold) from sales a,sales b,sales c;

  47. Execution Plan
  48. ----------------------------------------------------------
  49. Plan hash value: 3928812053

  51. ---------------------------------------------------------------------------------------------------------------------
  52. | Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  53. ---------------------------------------------------------------------------------------------------------------------
  54. |   0 | SELECT STATEMENT                  |                 |     1 |     5 |    22T  (1)|999:59:59 |       |       |
  55. |   1 |  SORT AGGREGATE                   |                 |     1 |     5 |            |          |       |       |
  56. |   2 |   MERGE JOIN CARTESIAN            |                 |   775P|  3445P|    22T  (1)|999:59:59 |       |       |
  57. |   3 |    MERGE JOIN CARTESIAN           |                 |   844G|  3931G|    24M  (1)| 82:43:36 |       |       |
  58. |   4 |     PARTITION RANGE ALL           |                 |   918K|  4486K|   530   (3)| 00:00:07 |     1 |    28 |
  59. |   5 |      TABLE ACCESS FULL            | SALES           |   918K|  4486K|   530   (3)| 00:00:07 |     1 |    28 |
  60. |   6 |     BUFFER SORT                   |                 |   918K|       |    24M  (1)| 82:43:30 |       |       |
  61. |   7 |      PARTITION RANGE ALL          |                 |   918K|       |    27   (0)| 00:00:01 |     1 |    28 |
  62. |   8 |       BITMAP CONVERSION TO ROWIDS |                 |   918K|       |    27   (0)| 00:00:01 |       |       |
  63. |   9 |        BITMAP INDEX FAST FULL SCAN| SALES_PROMO_BIX |       |       |            |          |     1 |    28 |
  64. |  10 |    BUFFER SORT                    |                 |   918K|       |    22T  (1)|999:59:59 |       |       |
  65. |  11 |     PARTITION RANGE ALL           |                 |   918K|       |    27   (0)| 00:00:01 |     1 |    28 |
  66. |  12 |      BITMAP CONVERSION TO ROWIDS  |                 |   918K|       |    27   (0)| 00:00:01 |       |       |
  67. |  13 |       BITMAP INDEX FAST FULL SCAN | SALES_PROMO_BIX |       |       |            |          |     1 |    28 |
  68. ---------------------------------------------------------------------------------------------------------------------

  70. Note
  71. -----
  72.    - automatic DOP: skipped because of IO calibrate statistics are missing
  73.    
  74.    
  75.    
  76.    GATHER IO calibrate STATISTICS
  77.    
  78.    
  79.    
  80.     set serveroutput on;
  81.    DECLARE
  82.       lat  INTEGER;
  83.       iops INTEGER;
  84.       mbps INTEGER;
  85.     BEGIN
  86.     -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
  87.        DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 15, iops, mbps, lat);
  88.   
  89.       DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  90.      DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
  91.      dbms_output.put_line('max_mbps = ' || mbps);
  92.    end;
  93.    /
  94.    
  95. max_iops = 78
  96. latency  = 12
  97. max_mbps = 49
  98.    
  99.    
  100. SQL>  select sum(a.amount_sold) from sales a,sales b,sales c;

  102. Execution Plan
  103. ----------------------------------------------------------
  104. Plan hash value: 3928812053

  106. ---------------------------------------------------------------------------------------------------------------------
  107. | Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  108. ---------------------------------------------------------------------------------------------------------------------
  109. |   0 | SELECT STATEMENT                  |                 |     1 |     5 |    22T  (1)|999:59:59 |       |       |
  110. |   1 |  SORT AGGREGATE                   |                 |     1 |     5 |            |          |       |       |
  111. |   2 |   MERGE JOIN CARTESIAN            |                 |   775P|  3445P|    22T  (1)|999:59:59 |       |       |
  112. |   3 |    MERGE JOIN CARTESIAN           |                 |   844G|  3931G|    24M  (1)| 00:48:58 |       |       |
  113. |   4 |     PARTITION RANGE ALL           |                 |   918K|  4486K|   530   (3)| 00:00:01 |     1 |    28 |
  114. |   5 |      TABLE ACCESS FULL            | SALES           |   918K|  4486K|   530   (3)| 00:00:01 |     1 |    28 |
  115. |   6 |     BUFFER SORT                   |                 |   918K|       |    24M  (1)| 00:48:58 |       |       |
  116. |   7 |      PARTITION RANGE ALL          |                 |   918K|       |    27   (0)| 00:00:01 |     1 |    28 |
  117. |   8 |       BITMAP CONVERSION TO ROWIDS |                 |   918K|       |    27   (0)| 00:00:01 |       |       |
  118. |   9 |        BITMAP INDEX FAST FULL SCAN| SALES_PROMO_BIX |       |       |            |          |     1 |    28 |
  119. |  10 |    BUFFER SORT                    |                 |   918K|       |    22T  (1)|999:59:59 |       |       |
  120. |  11 |     PARTITION RANGE ALL           |                 |   918K|       |    27   (0)| 00:00:01 |     1 |    28 |
  121. |  12 |      BITMAP CONVERSION TO ROWIDS  |                 |   918K|       |    27   (0)| 00:00:01 |       |       |
  122. |  13 |       BITMAP INDEX FAST FULL SCAN | SALES_PROMO_BIX |       |       |            |          |     1 |    28 |
  123. ---------------------------------------------------------------------------------------------------------------------

  125. Note
  126. -----
  127.    - automatic DOP: Computed Degree of Parallelism is 1
  128.   
  129.    
  130.    SQL> alter session set events '10053 trace name context forever,level 1';

  132. Session altered.

  134. SQL> explain plan for select sum(a.amount_sold) from sales a,sales b,sales c;

  136. Explained.


  139. 10053  解释了 为什么 DOP 选择


  142. kkeCostToTime: using io calibrate stats
  143. maxmbps=49(MB/s) maxpmbps=66(MB/s)
  144. block_size=8192 mb_io_count=4 mb_io_size=30247 (bytes)
  145. tot_io_size=0(MB) time=0(ms)

  147. AUTO DOP PLANS EVALUATION
  148. ***************************************
  149. Compilation completed with Dop: 1.
  150.   Cost_io:  0.00  Cost_cpu: 380000.00
  151.   Card:     1.00  Bytes:    305.00
  152.   Cost:     0.02  Est_time:  0ms
  153. kkopqSetDopReason: Reason why we chose this DOP is: no expensive parallel operation.
  154. No parallel scan operations
  155. kkopqCombineDop: Dop:1 Hint:no
  156. Query: compute:yes forced:no  computedDop:2 forceDop:0
  157. kkopqSetDopReason: Reason why we chose this DOP is: parallel threshold.
  158. Serial phase is inexpensive (0), stick to it.
  159. Costing completed. DOP chosen: 1.
  160. AutoDop: kkopqSetMaxDopInCursorEnv:In the Cursor Session Env, max DOP is set to 43



  164.     Reason why we chose this DOP is: no expensive parallel operation.
  165.     Reason why we chose this DOP is: parallel threshold.
复制代码
CBO 认为 DOP =1 才是合理的  Serial phase is inexpensive (0), stick to it.   Costing completed. DOP chosen: 1.

回复 只看该作者 道具 举报

3#
发表于 2012-6-16 15:15:23
MORE EXAMPLE :


SQL> SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>
SQL> set autotrace trace exp;
SQL> set linesize 140 pagesize 1400
SQL>  select count(*)  from large_mac;

Execution Plan
----------------------------------------------------------
Plan hash value: 1764901164

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 | 27784   (1)| 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 |           |    19M| 27784   (1)| 00:00:04 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| LARGE_MAC |    19M| 27784   (1)| 00:00:04 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 3
   
   
  SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

SQL> explain plan for select count(*)  from large_mac;

Explained.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/s01/orabase/diag/rdbms/g11r23/G11R23/trace/G11R23_ora_28215.trc



AUTO DOP PLANS EVALUATION
***************************************
Compilation completed with Dop: 1.
  Cost_io:  74878.00  Cost_cpu: 4870769552.48
  Card:     1.00  Bytes:    0.00
  Cost:     75109.71  Est_time:  32828ms
kkopqCombineDop: Dop:3 Hint:no
Query: compute:yes forced:no  computedDop:3 forceDop:0
Serial plan is expensive enough to be a candidate for parallelism (75110)
Signal reparse with DOP 3.
*****************************
Number of Compilations tried: 1
*****************************



parse 后发现Serial Plan中的 IO COST很高， 考虑使用并行 DOP=3




ODM KNOWLEDGE:

When automatic degree of parallelism (Auto DOP) is on the Oracle Optimizer will
Automatically decide the DOP for a statement based on the resource requirements
of the statement. Any statement that can be parallelized is a candidate for AUTO DOP.
You can see the DOP that the optimizer came up with in the notes section of the
explain plan (see below)
There are two init.ora parameters that control auto DOP
PARALLEL_DEGREE_POLICY and PARALLEL_MIN_TIME_THRESHOLD.
PARALLEL_DEGREE_POLICY controls whether or not auto DOP will be used.
By default Auto DOP is switch off for backward compatibility (MANUAL). To
Enable Auto DOP for everything set the parameter to AUTO. It is recommended
that DW users choose the LIMITED setting. It will apply auto DOP only for statements
where at least one table is decorated with PARALLEL clause.
AUTO_DEGREE_POLICY:
o        MANUAL - reverts to Oracle Database 10g behavior (Default).
o        LIMITED - auto DOP applied only to stmts that contain tables or indexes decorated explicitly with the parallel clause with or without explicit DOP
o        AUTO  - automatic PQ for all statements.  

PARALLEL_MIN_TIME_THREADHOLD: the execution time, as estimated by the optimizer,
above which a statement is considered for automatic PQ and automatic derivation of DOP.
By default this is set to AUTO which means 30sec.

When a SQL statement is executed it will be hard parsed and a serial plan will be developed
The expected elapse time of that plan will be examined. If the expected Elapse time is
Less than PARALLEL_MIN_TIME_THRESHOLD  then the query will execute serially.

If the expected Elapse time is greater than PARALLEL_MIN_TIME_THRESHOLD  then the plan
Will be re-evaluated to run in parallel and the optimizer will determine the ideal DOP. The
Optimizer automatically determines the DOP based on the resource required for all scan operations
(full table scan, index fast full scan and so on)

However, the optimizer will cap the actual DOP for a statement with the default DOP
(paralllel_threads_per_cpu X CPU_COUNT X INSTANCE_COUNT), to ensure parallel
Processes do not flood the system.


Controlling Auto DOP
Controlled by two init.ora parameters:
PARALLEL_DEGREE_POLICY
Controls whether or not auto DOP will be used
Default is MANUAL which means no Auto DOP
Set to AUTO to enable auto DOP
PARALLEL_MIN_TIME_THRESHOLD
Controls which statements are candidate for parallelism
Default is 30 seconds



How Auto DOP Works

Statement with an elapse time estimate of less than PARALLEL_MIN_TIME_THRESHOLD will run serial
Statement above threshold are candidate for parallelism
Maximum DOP controlled by PARALLEL_DEGREE_LIMIT
Default value is
PARALLEL_THREADS_PER_CPU X CPU_COUNT
Actual DOP = MIN(PARALLEL_DEGREE_LIMIT, ideal DOP)

回复 只看该作者 道具 举报

4#
发表于 2012-6-16 21:01:26
色勒斯清,Many thx!

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 21:02 , Processed in 0.055690 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569