实验下并行FTS 时候 COST 变化（传统COST，CUP_COSTING COST）
并且简单看下并行FTS原理
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table t1 (a int);
表已创建。
SQL> ed
已写入 file afiedt.buf
1 declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i);
5 end loop;
6* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> execute dbms_stats.gather_table_stats('SYS','T1');
PL/SQL 过程已成功完成。
SQL> select blocks from user_tables where table_name='T1';
BLOCKS
----------
20
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter db_file_mu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
_optimizer_cost_model CHOOSE
_table_scan_cost_plus_one TRU
SQL> select distinct sid from v$mystat;
SID
----------
157
SQL> select sid,qcsid,degree,server# from v$px_session where qcsid=157;
未选定行
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';
会话已更改。
SQL> select count(*) from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------
-------------------------------------------------------------------
SQL> select /*+ parallel(t1,2) */ count(*) from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | T
Q |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| | |
| 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 | | 10000 | 3 (0)| 00:00:01 | Q1,
00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 10000 | 3 (0)| 00:00:01 | Q1,
00 | PCWP | |
--------------------------------------------------------------------------------
------------------------
SQL> select /*+ parallel(t1,3) */ count(*) from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | T
Q |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| | |
| 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 | | 10000 | 2 (0)| 00:00:01 | Q1,
00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 10000 | 2 (0)| 00:00:01 | Q1,
00 | PCWP | |
--------------------------------------------------------------------------------
------------------------
分析下并行FTS步骤： 首先 （TABLE ACCESS FULL| T1） 分解成几个小扫描 （PX BLOCK ITERATOR） 每个扫描汇总它们的值 （SORT AGGREGATE）
把这些子结果传送给并行查询协调器（PX SEND QC (RANDOM)，PX COORDINATOR） 汇总这些结果(SORT AGGREGATE)
10g Cost at degree N = ceil(serial cost / (0.9 * N))~~~~~~~公式来自与cost-based oracle
SQL> select ceil(6/(0.9*2)) from dual;
CEIL(6/(0.9*2))
---------------
4~~~~~~~~~~~~~~~~~~~~与autotrace中值不一样
SQL> select ceil(6/(0.9*3)) from dual;
CEIL(6/(0.9*3))
---------------
3~~~~~~~~~~~~~~~~~~~~与autotrace中值不一样
SQL> select round(6/(0.9*3)) from dual;~~~~~~~~~~~~~~~~经过测试10GR2 换为round才行
ROUND(6/(0.9*3))
----------------
2
SQL> select round(6/(0.9*2)) from dual;
ROUND(6/(0.9*2))
----------------
3
若parallel_adaptive_multi_user boolean TRUE (10G default为TRUE)仅允许有限数目的用户按默认的并行程度运行
这个限度由_parallel_adaptive_max_users 2 maximum number of users running with default DOP 指定 现在显示为2个USER
~~~~~~~~~~~~~~~~~~~~~~~~~看 trace
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 10000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
Cost: 6.11 Resp: 6.11 Degree: 0
Cost_io: 6.00 Cost_cpu: 1642429
Resp_io: 6.00 Resp_cpu: 1642429
Best:: AccessPath: TableScan
Cost: 6.11 Degree: 1 Resp: 6.11 Card: 10000.00 Bytes: 0
Final - All Rows Plan: Best join order: 1
Cost: 6.1077 Degree: 1 Card: 10000.0000 Bytes: 0
Resc: 6.1077 Resc_io: 6.0000 Resc_cpu: 1642429
Resp: 6.1077 Resp_io: 6.0000 Resc_cpu: 1642429
上面这个是串行结果
几个参数解释:
resc Cost for serial execution of a step.
deg Degree of parallelism of a step.
resp Cost for full parallel execution of a step.
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 10000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
Cost: 6.10 Resp: 3.39 Degree: 0
Cost_io: 6.00 Cost_cpu: 1500000
Resp_io: 3.33 Resp_cpu: 833333
Best:: AccessPath: TableScan
Cost: 3.39 Degree: 2 Resp: 3.39 Card: 10000.00 Bytes: 0
Final - All Rows Plan: Best join order: 1
Cost: 3.3880 Degree: 2 Card: 10000.0000 Bytes: 0
Resc: 6.0984 Resc_io: 6.0000 Resc_cpu: 1500000
Resp: 3.3880 Resp_io: 3.3333 Resc_cpu: 833333
并行度为2时候结果 Resp: 3.3880 并行COST 用的ROUND 变成了3(Resc_io 是串行结果)
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 10000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
Cost: 6.10 Resp: 2.26 Degree: 0
Cost_io: 6.00 Cost_cpu: 1500000
Resp_io: 2.22 Resp_cpu: 555556
Best:: AccessPath: TableScan
Cost: 2.26 Degree: 3 Resp: 2.26 Card: 10000.00 Bytes: 0
Final - All Rows Plan: Best join order: 1
Cost: 2.2587 Degree: 3 Card: 10000.0000 Bytes: 0
Resc: 6.0984 Resc_io: 6.0000 Resc_cpu: 1500000
Resp: 2.2587 Resp_io: 2.2222 Resc_cpu: 555556
并行度为3时候结果 Resp: 2.2587 并行COST 用的ROUND 变成了2
~~~~~~~~~~~~~~早期传统成本计算 （只算IO 部分）
SQL> alter system set "_optimizer_cost_model"=IO;
系统已更改。
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';
会话已更改。
SQL> select /*+ parallel(t1,2) */ count(*) from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Cost | TQ |IN-OUT| PQ D
istrib |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 3 | | |
|
| 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 | | 10000 | 3 | Q1,00 | PCWC |
|
| 6 | TABLE ACCESS FULL| T1 | 10000 | 3 | Q1,00 | PCWP |
|
--------------------------------------------------------------------------------
--------
Note
-----
- cpu costing is off (consider enabling it)
SQL> select /*+ parallel(t1,3) */ count(*) from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Cost | TQ |IN-OUT| PQ D
istrib |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 2 | | |
|
| 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 | | 10000 | 2 | Q1,00 | PCWC |
|
| 6 | TABLE ACCESS FULL| T1 | 10000 | 2 | Q1,00 | PCWP |
|
--------------------------------------------------------------------------------
--------
Note
-----
- cpu costing is off (consider enabling it)
trace ******************************
并行度2
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 10000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
Cost: 4.00 Resp: 3.00 Degree: 0
Cost_io: 4.00 Cost_cpu: 0
Resp_io: 3.00 Resp_cpu: 0
Best:: AccessPath: TableScan
Cost: 3.00 Degree: 2 Resp: 3.00 Card: 10000.00 Bytes: 0
Final - All Rows Plan: Best join order: 1
Cost: 3.0000 Degree: 2 Card: 10000.0000 Bytes: 0
Resc: 4.0000 Resc_io: 4.0000 Resc_cpu: 0
Resp: 3.0000 Resp_io: 3.0000 Resc_cpu: 0
这样计算采用的是
COST=(HWM下block/adjusted dbf_mbrc)
Cost at degree N = ceil(serial cost / (0.9 * N))
SQL> SELECT CEIL(4/(2*0.9)) FROM DUAL;
CEIL(4/(2*0.9))
---------------
3
并行度3
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 10000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
Cost: 4.00 Resp: 2.00 Degree: 0
Cost_io: 4.00 Cost_cpu: 0
Resp_io: 2.00 Resp_cpu: 0
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 3 Resp: 2.00 Card: 10000.00 Bytes: 0
Final - All Rows Plan: Best join order: 1
Cost: 2.0000 Degree: 3 Card: 10000.0000 Bytes: 0
Resc: 4.0000 Resc_io: 4.0000 Resc_cpu: 0
Resp: 2.0000 Resp_io: 2.0000 Resc_cpu: 0
SQL> SELECT CEIL(4/(3*0.9)) FROM DUAL;
CEIL(4/(3*0.9))
---------------
2
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-613727/ ，如需转载，请注明出处，否则将追究法律责任。
