Here are some preliminary test results for a single disk query.
The disk is a Seagate Cheetah ST318404LC, of 18.37Gb and 10,000 rpm. Manufacturer’s read seek times are 5.2ms (average), 0.6ms (single track), 10ms (max full seek).
A single tablespace was created on the disk, with a single table create and populated as in this script.
create tablespace sandbox_one_disk datafile '/opt/d1/sandbox__one_disk.dbf' size 4g extent management local uniform size 256M segment space management manual / create table t2 (col1 varchar2(1000)) tablespace sandbox_one_disk pctfree 99 pctused 0 noparallel nologging nocompress nomonitoring storage (minextents 8) /
insert /*+ append */ into t2
select lpad(rownum,100)
from dual
connect by level <= 250000
/
begin
dbms_stats.gather_table_stats
(ownname => user,
tabname => 'T2',
estimate_percent => 1,
block_sample => true,
method_opt => 'for all columns size 1');
end;
/
This gave a table size of just less than 2Gb to be queried as follows:
set timing on echo on heading off select /*+ noparallel(t2) */ count(*) from t2; select /*+ parallel(t2 2) */ count(*) from t2; select /*+ parallel(t2 3) */ count(*) from t2; select /*+ parallel(t2 4) */ count(*) from t2; select /*+ parallel(t2 5) */ count(*) from t2; select /*+ parallel(t2 6) */ count(*) from t2; select /*+ parallel(t2 7) */ count(*) from t2; select /*+ parallel(t2 8) */ count(*) from t2; select /*+ parallel(t2 20) */ count(*) from t2; select /*+ parallel(t2 40) */ count(*) from t2; select /*+ parallel(t2 80) */ count(*) from t2;
The wall clock time for each query was noted and graphed.
Each test run was executed with the server rebooted and reconfigured to use a different i/o scheduler each time. The scheduler in use was verified using:
dmesg | grep scheduler
All four available schedulers were tested with the following results:
In the above results a parallelism of “1″ represents nonparallel query without direct serial reads being used.
I shall pause here for comments … :)
