並行查詢對於響應時間的影響實驗
測試表AC43有記錄204690413，二億多條。
並行處理花費時間17.47秒，正常查詢花費時間49.21，將近三倍的差距。
SQL> set timing on
SQL> set autotrace on
SQL> SELECT /*+ parallel(ac04 5) */count(DISTINCT aac001) FROM ac04;
COUNT(DISTINCTAAC001)
---------------------
1156171
已用時間: 00: 00: 17.47
執行計劃
----------------------------------------------------------
Plan hash value: 3388306996
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 13199 (1)| 00:02:39 | | | |
| 1 | SORT GROUP BY | | 1 | 5 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 5 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT GROUP BY | | 1 | 5 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 5 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 5 | | | Q1,00 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 5 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 26M| 124M| 13199 (1)| 00:02:39 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| AC04 | 26M| 124M| 13199 (1)| 00:02:39 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
87 recursive calls
4 db block gets
277758 consistent gets
277030 physical reads
720 redo size
223 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT count(DISTINCT aac001) FROM ac04;
COUNT(DISTINCTAAC001)
---------------------
1156171
已用時間: 00: 00: 49.21
執行計劃
----------------------------------------------------------
Plan hash value: 345483231
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 25391 (2)| 00:05:05 |
| 1 | SORT GROUP BY | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| PK_AC04 | 26M| 124M| 25391 (2)| 00:05:05 |
---------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
119794 consistent gets
2 physical reads
688 redo size
240 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可是我在一張相對較小的表AC01上測試的情況恰恰相反。
並行用了9秒多，正常查詢只用了2秒多。
檢視執行計劃，並行查詢用了全表掃描，正常的查詢是索引的方式。
看來用並行查詢的時候還是需要提前做好測試。
SQL> SELECT /*+ parallel(ac01 5) */count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
879392
已用時間: 00: 00: 09.41
執行計劃
----------------------------------------------------------
Plan hash value: 1972307605
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2881 (1)| 00:00:35 | | |
| 1 | SORT GROUP BY | | 1 | 7 | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 7 | | | Q1,01 | P->S | QC (RAND)
| 4 | SORT GROUP BY | | 1 | 7 | | | Q1,01 | PCWP |
| 5 | PX RECEIVE | | 1 | 7 | | | Q1,01 | PCWP |
| 6 | PX SEND HASH | :TQ10000 | 1 | 7 | | | Q1,00 | P->P | HASH
| 7 | SORT GROUP BY | | 1 | 7 | | | Q1,00 | PCWP |
| 8 | PX BLOCK ITERATOR | | 1928K| 12M| 2881 (1)| 00:00:35 | Q1,00 | PCWC |
| 9 | TABLE ACCESS FULL| AC01 | 1928K| 12M| 2881 (1)| 00:00:35 | Q1,00 | PCWP |
------------------------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
53 recursive calls
3 db block gets
60275 consistent gets
60094 physical reads
728 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
880064
已用時間: 00: 00: 01.76
執行計劃
----------------------------------------------------------
Plan hash value: 3761785375
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1064 (3)| 00:00:13 |
| 1 | SORT GROUP BY | | 1 | 7 | | |
| 2 | INDEX FAST FULL SCAN| IDX_AC01_AAC003 | 1928K| 12M| 1064 (3)| 00:00:13 |
-----------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
2 recursive calls
0 db block gets
5428 consistent gets
0 physical reads
0 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
還有怎麼才能在查詢中使用兩個HINT，我試瞭如下的方式，使用並行查詢，且想讓查詢使用索引IDX_AC01_AAC003，結果用的是另一個索引，並行查詢也沒起作用。我這種寫法對嗎？
SQL> SELECT /*+ parallel(ac01 5) index(ac01 IDX_AC01_AAC003) */count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
879393
已用時間: 00: 00: 14.47
執行計劃
----------------------------------------------------------
Plan hash value: 994991478
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 144K (1)| 00:28:53 |
| 1 | SORT GROUP BY | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | AC01 | 1928K| 12M| 144K (1)| 00:28:53 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 4 | BITMAP INDEX FULL SCAN | IDX_AC01_BAC136 | | | | |
-------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
3 recursive calls
0 db block gets
210121 consistent gets
53 physical reads
216 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
[ 本帖最後由 wei-xh 於 2010-5-4 09:59 編輯 ]
並行處理花費時間17.47秒，正常查詢花費時間49.21，將近三倍的差距。
SQL> set timing on
SQL> set autotrace on
SQL> SELECT /*+ parallel(ac04 5) */count(DISTINCT aac001) FROM ac04;
COUNT(DISTINCTAAC001)
---------------------
1156171
已用時間: 00: 00: 17.47
執行計劃
----------------------------------------------------------
Plan hash value: 3388306996
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 13199 (1)| 00:02:39 | | | |
| 1 | SORT GROUP BY | | 1 | 5 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 5 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT GROUP BY | | 1 | 5 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 5 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 5 | | | Q1,00 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 5 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 26M| 124M| 13199 (1)| 00:02:39 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| AC04 | 26M| 124M| 13199 (1)| 00:02:39 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
87 recursive calls
4 db block gets
277758 consistent gets
277030 physical reads
720 redo size
223 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT count(DISTINCT aac001) FROM ac04;
COUNT(DISTINCTAAC001)
---------------------
1156171
已用時間: 00: 00: 49.21
執行計劃
----------------------------------------------------------
Plan hash value: 345483231
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 25391 (2)| 00:05:05 |
| 1 | SORT GROUP BY | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| PK_AC04 | 26M| 124M| 25391 (2)| 00:05:05 |
---------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
119794 consistent gets
2 physical reads
688 redo size
240 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可是我在一張相對較小的表AC01上測試的情況恰恰相反。
並行用了9秒多，正常查詢只用了2秒多。
檢視執行計劃，並行查詢用了全表掃描，正常的查詢是索引的方式。
看來用並行查詢的時候還是需要提前做好測試。
SQL> SELECT /*+ parallel(ac01 5) */count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
879392
已用時間: 00: 00: 09.41
執行計劃
----------------------------------------------------------
Plan hash value: 1972307605
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2881 (1)| 00:00:35 | | |
| 1 | SORT GROUP BY | | 1 | 7 | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 7 | | | Q1,01 | P->S | QC (RAND)
| 4 | SORT GROUP BY | | 1 | 7 | | | Q1,01 | PCWP |
| 5 | PX RECEIVE | | 1 | 7 | | | Q1,01 | PCWP |
| 6 | PX SEND HASH | :TQ10000 | 1 | 7 | | | Q1,00 | P->P | HASH
| 7 | SORT GROUP BY | | 1 | 7 | | | Q1,00 | PCWP |
| 8 | PX BLOCK ITERATOR | | 1928K| 12M| 2881 (1)| 00:00:35 | Q1,00 | PCWC |
| 9 | TABLE ACCESS FULL| AC01 | 1928K| 12M| 2881 (1)| 00:00:35 | Q1,00 | PCWP |
------------------------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
53 recursive calls
3 db block gets
60275 consistent gets
60094 physical reads
728 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
880064
已用時間: 00: 00: 01.76
執行計劃
----------------------------------------------------------
Plan hash value: 3761785375
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1064 (3)| 00:00:13 |
| 1 | SORT GROUP BY | | 1 | 7 | | |
| 2 | INDEX FAST FULL SCAN| IDX_AC01_AAC003 | 1928K| 12M| 1064 (3)| 00:00:13 |
-----------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
2 recursive calls
0 db block gets
5428 consistent gets
0 physical reads
0 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
還有怎麼才能在查詢中使用兩個HINT，我試瞭如下的方式，使用並行查詢，且想讓查詢使用索引IDX_AC01_AAC003，結果用的是另一個索引，並行查詢也沒起作用。我這種寫法對嗎？
SQL> SELECT /*+ parallel(ac01 5) index(ac01 IDX_AC01_AAC003) */count(DISTINCT aac003) FROM ac01;
COUNT(DISTINCTAAC003)
---------------------
879393
已用時間: 00: 00: 14.47
執行計劃
----------------------------------------------------------
Plan hash value: 994991478
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 144K (1)| 00:28:53 |
| 1 | SORT GROUP BY | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | AC01 | 1928K| 12M| 144K (1)| 00:28:53 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 4 | BITMAP INDEX FULL SCAN | IDX_AC01_BAC136 | | | | |
-------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
3 recursive calls
0 db block gets
210121 consistent gets
53 physical reads
216 redo size
239 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
[ 本帖最後由 wei-xh 於 2010-5-4 09:59 編輯 ]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-662230/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【效能】Oracle表並行對邏輯遷移後查詢效能的影響Oracle並行
- 時區調整對job的執行時間的影響
- 淺談SQL Server中統計對於查詢的影響SQLServer
- 修改系統時間對oracle的影響Oracle
- MySQL：查詢欄位數量多少對查詢效率的影響MySql
- Arraysize的設定以及對查詢效能的影響
- iOS Dynamic Framework 對 App 啟動時間影響實測iOSFrameworkAPP
- dex最佳化對Arouter查詢路徑的影響
- Postgresql MVCC架構對從庫長查詢的影響SQLMVC架構
- SQL查詢結果集對注入的影響及利用SQL
- 變更OS時間對資料庫的影響資料庫
- 實驗-資料分佈對執行計劃的影響.txt
- ORACLE空間管理實驗5：塊管理之ASSM下高水位的影響--刪除和查詢OracleSSM
- 複合索引中前導列對sql查詢的影響索引SQL
- 修改系統時間對oracle資料庫的影響Oracle資料庫
- oracle實驗記錄 （predicate對cpu cost的影響）Oracle
- 查詢快取(query_cache)的影響快取
- oracle cardinality對於執行計劃的影響Oracle
- 遊戲暗示對於遊戲玩家的影響遊戲
- 關於OPcache對Swoole影響的理解opcache
- JAVA 異常對於效能的影響Java
- 關於drop操作對role的影響
- 測試修改作業系統時間&時區對oracle的影響作業系統Oracle
- 時鐘統一（時間同步）對全球發展程式的影響力
- 基於媒體查詢和 rem 的響應式佈局實踐REM
- Oracle 執行 DDL 長時間無響應Oracle
- 不等號影響執行計劃的相關實驗
- oracle 索引升降序及排序條件 對查詢計劃的影響Oracle索引排序
- 嚴格模式下對於this指向的影響模式
- reverse index 對於 MAX/MIN操作的影響Index
- 驗證資料壓縮對DML的影響
- 影響Oracle標量子查詢效能的三個因素Oracle
- (f)--閃回恢復區-- 並行載入對閃庫的影響並行
- cluster factor對執行計劃的影響
- 每一秒都很重要：響應時間與資料洩露影響之間的密切關係
- ORACLE空間管理實驗3：區管理之大區小區對I/O效能的影響Oracle
- 【實驗】【外來鍵】小議外來鍵約束對應用程式的影響
- 修改主機時區對Oracle的影響分析Oracle