oracle 並行cpu查詢分割槽表測試
並行cpu查詢分割槽表測試 12月01日(星期四)
這篇文章是回應玉面飛龍所提出的均勻分佈資料的情況而實驗
測試均勻資料分佈的並行查詢與非並行查詢，結果是均勻資料分佈的並行查詢要優於非並行查詢
測試環境: OS: HP9000 DB:9201(資料檔案分佈在裸裝置上)
[@more@]並行cpu查詢分割槽表測試 12月01日(星期四)
這篇文章是回應玉面飛龍所提出的均勻分佈資料的情況而實驗
測試均勻資料分佈的並行查詢與非並行查詢，結果是均勻資料分佈的並行查詢要優於非並行查詢
測試環境: OS: HP9000 DB:9201(資料檔案分佈在裸裝置上)
1.建立一個200M的data1邏輯卷，在系統提示符下鍵入命令：
lvcreate -L 500 -n data1 /dev/vg00
lvcreate -L 500 -n data2 /dev/vg00
lvcreate -L 500 -n index /dev/vg00
2.賦許可權
chown oracle:oinstall /dev/vg00/rdata1
chown oracle:oinstall /dev/vg00/rdata2
chown oracle:oinstall /dev/vg00/rindex
3.建立表空間
SQL> CREATE TABLESPACE rdata1
2 DATAFILE '/dev/vg00/rdata1' SIZE 400M
3 AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
4 LOGGING
5 DEFAULT NOCOMPRESS
6 ONLINE
7 PERMANENT
8 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
9 SEGMENT SPACE MANAGEMENT AUTO
10 /
Tablespace created
SQL> CREATE TABLESPACE rdata2
2 DATAFILE '/dev/vg00/rdata2' SIZE 400M
3 AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
4 LOGGING
5 DEFAULT NOCOMPRESS
6 ONLINE
7 PERMANENT
8 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
9 SEGMENT SPACE MANAGEMENT AUTO
10 /
Tablespace created
SQL> CREATE TABLESPACE rindex
2 DATAFILE '/dev/vg00/rindex' SIZE 400M
3 AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
4 LOGGING
5 DEFAULT NOCOMPRESS
6 ONLINE
7 PERMANENT
8 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
9 SEGMENT SPACE MANAGEMENT AUTO
10 /
Tablespace created
4.建立表及填充資料
SQL> CREATE TABLE test
2 (
3 ID INT,
4 description VARCHAR(10)
5 )
6 PCTFREE 0
7 PARTITION BY RANGE (ID)
8 (
9 PARTITION data01 VALUES LESS THAN (1000000) tablespace rdata1,
10 PARTITION data02 VALUES LESS THAN (3000000) tablespace rdata2
11 ) nologging
12 /
Table created
SQL> alter table test add constraint pk_test primary key(id) using index local tablespace rindex;
Table altered
SQL> select * from user_tab_partitions;
TABLE_NAME COMPOSITE PARTITION_NAME
------------------------------ --------- ------------------------
TEST NO DATA01
TEST NO DATA02
SQL> select * from user_ind_partitions;
INDEX_NAME COMPOSITE PARTITION_NAME
------------------------------ --------- ------------------------
PK_TEST NO DATA01
PK_TEST NO DATA02
放2000000條資料
SQL> begin
2 for nindex in 1..2000000 loop
3 insert /*+ append */
4 into test
5 values (nindex, to_char(nindex)||'test');
6 if mod(nindex,1000) =0 then
7 commit;
8 end if;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed
資料布布情況:
SQL> select count(*) from test partition(DATA01);
COUNT(*)
----------
999999
SQL> select count(*) from test partition(DATA02);
COUNT(*)
----------
1000001
比較均勻
SQL> alter system flush shared_pool;
System altered
SQL> select count(*) from test;
COUNT(*)
----------
2000000
已用時間: 00: 00: 01.49
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 INDEX (FAST FULL SCAN) OF 'PK_TEST' (UNIQUE) (Cost=4 C
ard=451528)
Statistics
----------------------------------------------------------
1836 recursive calls
0 db block gets
4384 consistent gets
0 physical reads
0 redo size
488 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
45 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ parallel(test,2) */ count(*) from test;
COUNT(*)
----------
2000000
已用時間: 00: 00: 01.45
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 INDEX (FAST FULL SCAN) OF 'PK_TEST' (UNIQUE) (Cost=4 C
ard=451528)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3982 consistent gets
0 physical reads
0 redo size
488 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
經過我反覆實驗，並行查詢資料分佈均勻的表時，邏輯讀明顯小於非並行查詢，且並行查詢不需要排序,而非並行排序43次,不
知道這一塊原理是什麼？
測試均勻資料分佈的並行查詢與非並行查詢，結果是均勻資料分佈的並行查詢要優於非並行查詢
文章分類: 技術
前篇(05-11-30): 並行cpu測試
後篇(05-12-02): 促進睡眠的飲食
最新回覆(2件) 主題/內容 作者/日時
Re: 並行cpu查詢分割槽表測試
奇怪；你的資料庫版本比較高，setautotrace竟然還顯示不出並行的執行計劃
JServer Release 8.1.7.3.0 - Production
SQL> set autotrace on
SQL> select /*+ parallel(trade,4) full(trade) */ count(*) from trade;
COUNT(*)
----------
202618
Execution Plan
-------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=651 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q311470 00
3 2 TABLE ACCESS* (FULL) OF 'TRADE' (Cost=651 Card=207400) :Q311470 00
2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) FROM (SELECT /*+ NO_EXPAND ROWID(A2)
3 PARALLEL_COMBINED_WITH_PARENT
----------------------------------
365 recursive calls
860 db block gets
10982 consistent gets
10874 physical reads
692 redo size
369 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
然後察看該session的v$session_event，只有很少的db file scatter read/db file sequence read. 說明setautotrace其統
計的physical reads/consistent gets並不準確，肯能包括parallel slave的統計。不過並行應該是使用db file direct read
的。
另外你能否測試一下full table scan?你的並行測試都是掃描的表空間rindex上的索引。資料物理上並沒有“share nothing”
的分佈。
玉面飛龍
05-12-02 10:54
Re: 並行cpu查詢分割槽表測試
SQL> alter system flush shared_pool;
系統已更改。
已用時間: 00: 00: 00.05
SQL> select /*+full(test) */ count(*) from test;
COUNT(*)
----------
2000000
已用時間: 00: 00: 02.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=533 Card=1)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (FULL) OF 'TEST' (Cost=533 Card=451528)
Statistics
----------------------------------------------------------
2039 recursive calls
0 db block gets
5979 consistent gets
2737 physical reads
0 redo size
206 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
49 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+full(test) parallel(test,2) */ count(*)from test;
COUNT(*)
----------
2000000
已用時間: 00: 00: 03.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=267 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q26000
3 2 PARTITION RANGE* (ALL) :Q26000
4 3 TABLE ACCESS* (FULL) OF 'TEST' (Cost=267 Card=451528 :Q26000
)
2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ NO_EXPAND ROWID(A2)
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
549 recursive calls
3 db block gets
5692 consistent gets
5529 physical reads
920 redo size
205 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
奇怪當按照全表掃描時，並行沒有顯示出優勢,物理讀比非並行多。
且上次的並行提示沒有顯示並行的資訊，是因為走的是索引掃描。
SQL> select distinct sid from v$mystat;
SID
----------
12
已用時間: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: 檢視基本物件的許可權不足
SP2-0612: 生成AUTOTRACE EXPLAIN報告時出錯
Statistics
----------------------------------------------------------
434 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
201 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select sid,event from v$session_event where sid=12;
SID EVENT
---------- ----------------------------------------------------------------
12 db file sequential read
12 SQL*Net message to client
12 SQL*Net more data to client
12 SQL*Net message from client
已用時間: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: 檢視基本物件的許可權不足
SP2-0612: 生成AUTOTRACE EXPLAIN報告時出錯
Statistics
----------------------------------------------------------
286 recursive calls
0 db block gets
58 consistent gets
0 physical reads
0 redo size
359 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
4 rows processed
開心就好
05-12-02 12:56
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8806316/viewspace-913294/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle查詢分割槽表的最後一個分割槽值Oracle
- 分割槽表分割槽索引查詢效率探究索引
- 如何查詢分割槽表的分割槽及子分割槽
- oracle實用sql(14)--查詢分割槽表的分割槽列和子分割槽列OracleSQL
- Oracle查詢Interval partition分割槽表內資料Oracle
- oracle分割槽表的分類及測試Oracle
- 分割槽表並行建立索引並行索引
- oracle 並行查詢時並行資源分配追蹤測試Oracle並行
- 表分割槽機制測試
- oracle表查詢的並行度Oracle並行
- oracle partition分割槽_分割槽列為空測試(一)Oracle
- oracle分割槽表和分割槽表exchangeOracle
- SCI分割槽查詢
- 測試oracle子分割槽維護Oracle
- Oracle分割槽表及分割槽索引Oracle索引
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle 表分割槽Oracle
- oracle分割槽表Oracle
- oracle表分割槽Oracle
- Oracle 分割槽表Oracle
- Hash分割槽表分割槽數與資料分佈的測試
- oracle 分割槽表進行shrink操作Oracle
- oracle分割槽表執行計劃Oracle
- 大分割槽表的手工並行優化並行優化
- Oracle查詢資料庫中所有表和分割槽表的記錄數Oracle資料庫
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- Oracle資料庫表範圍分割槽策略測試過程Oracle資料庫
- mysql~關於mysql分割槽表的測試MySql
- 表和索引並行查詢索引並行
- Oracle 建立分割槽表Oracle
- ORACLE分割槽表管理Oracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Oracle並行操作——並行查詢（Parallel Query）Oracle並行Parallel
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- postgresql 9.6 分割槽表測試方案與記錄SQL
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引