如果根据执行计划判断sql的并行度
大家好，想请问大家一个问题，如何根据执行计划判断出这条sql的并行度。通过hint知道并行度，但是并行度改变了执行计划未改变，想知道为什么？
并行度的优先级别从高到低：
Hint->alter session force parallel->表， 索引 上的设定-> 系统参数
是不是即使hint指定了并行度，cbo还是会根据cost选择并行度
--系统版本：
$ uname -a
Linux rhel5.com 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
--数据库版本
$ ss
SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 26 21:16:59 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
S
SQL> set autot trace exp stat
----hint指定并行度为2
SQL> select /*+parallel(epm01 4)*/DEPARTMENT_ID,sum(salary) from epm01 group by DEPARTMENT_ID;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3575007831
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2782 | 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 107 | 2782 | 3 (34)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 107 | 2782 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 107 | 2782 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 107 | 2782 | 3 (34)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 107 | 2782 | 3 (34)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 107 | 2782 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| EPM01 | 107 | 2782 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
783 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
----hint指定并行度为2
SQL> select /*+parallel(epm01 2)*/DEPARTMENT_ID,sum(salary) from epm01 group by DEPARTMENT_ID;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3575007831
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2782 | 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 107 | 2782 | 3 (34)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 107 | 2782 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 107 | 2782 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 107 | 2782 | 3 (34)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 107 | 2782 | 3 (34)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 107 | 2782 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| EPM01 | 107 | 2782 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
16 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
783 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
对了，还有当时session的设置
parallel_max_servers integer 40
parallel_min_servers integer 2
SQL> select DEGREE from dba_tables where table_name='EPM01';
DEGREE
----------------------------------------
1 我认为并不能，
1、 在最近的版本中引入了较多 运行时的指标来影响并行
2、 计划中某一部分使用了并行，不代表整个计划都是如此并行的
所以即便 能体现出一个 ”计算值“的并行度， 也未必是真实的并行度。
可以通过 10053 、_px_trace、 监控等方式来实际监视并行度。 Liu Maclean(刘相兵 发表于 2014-11-27 12:02 static/image/common/back.gif
我认为并不能，
1、 在最近的版本中引入了较多 运行时的指标来影响并行
谢谢刘大回复
页:
[1]