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
--------------------------------------------------------------------------------
--------
| 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部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613727/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄 （FTS的cost與基數計算）Oracle
- oracle實驗記錄 （計算hash join cost）Oracle
- oracle實驗記錄 （predicate對cpu cost的影響）Oracle
- oracle實驗記錄 （全表掃描COST計算方法）Oracle
- oracle實驗記錄 (oracle b*tree index訪問Cost計算(1))OracleIndex
- oracle實驗記錄 （SQL*PLUS 命令操作）OracleSQL
- oracle實驗記錄 （logfile基礎操作）Oracle
- oracle實驗記錄（logfile基礎操作2）Oracle
- Oracle並行操作——並行DML操作Oracle並行
- oracle實驗記錄 （子游標與解析）Oracle
- oracle實驗記錄 （恢復-rman操作delete(all) input ）Oracledelete
- oracle實驗記錄 （基礎,truncate與delete區別實驗）Oracledelete
- oracle實驗記錄（分割槽全表掃描（全區掃描） FTS 時候的成本計算）Oracle
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- oracle實驗記錄 (oracle reset parameter)Oracle
- oracle實驗記錄 （PGA manual or auto 與hash join）Oracle
- oracle實驗記錄 （恢復-rman操作(設定&備份)）Oracle
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- oracle實驗記錄 （listener.ora與 tnsnames.ora）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- oracle實驗記錄 (oracle 10G dataguard(10)flashback 與dg)Oracle
- Oracle檔案改名實驗記錄Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （dump logfile）Oracle
- oracle實驗記錄 （事務控制）Oracle
- oracle實驗記錄 （函式index）Oracle函式Index
- oracle實驗記錄 （bigfile tablespace）Oracle
- oracle實驗記錄 （恢復-redo）Oracle
- oracle實驗記錄 （expdp/impdp使用）Oracle
- oracle實驗記錄 （transport tablespace(Rman)）Oracle
- oracle實驗記錄 （使用exp/imp）Oracle
- Oracle並行操作——從序列到並行Oracle並行