oracle實驗記錄（並行操作與FTS COST）

實驗下並行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

--------------------------------------------------------------------------------

--------

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

--------------------------------------------------------------------------------

--------

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部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613727/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄（並行操作與FTS COST）

相關文章