并行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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8806316/viewspace-913294/