Wait times differences

[dbalearner]

Hi,

I created a table T1 with index as follows:

CREATE TABLE T1
(
n1 NUMBER(5) NOT NULL,
ind_pad VARCHAR2(40) NOT NULL,
n2 NUMBER(5) NOT NULL,
small_vc VARCHAR2(10) NOT NULL,
padding VARCHAR2(200) NOT NULL
);
create index t1_i1 on t1(n1, ind_pad, n2)
nologging
pctfree 91
;

I then populated this table with 12,000,000 rows and updated stats on the table. I used two schemas, one where tablespace was created on Solid State Disks (SSD) and the other on conventional mag disks (HDD). So two T1 tables one on SSD and one on HDD with identical records.

I then ran the following query against both tables (T1 on SSD and T1 on HDD) and used TKPROF to see the performance matrix.

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE( waits=>true );
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'query_T1_index';
SELECT count(1)
FROM T1
where n1 <= 8
and ind_pad <= rpad('x',39)||'x'
and n2 < 15;
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE;
Exit

This query selected 3,242,364 out of 12 million. That is around 26% of the total rows.

The query running on SSD disks returned the following:

SELECT count(1)
FROM T1
where n1 <= 8
and ind_pad <= rpad('x',39)||'x'
and n2 < 15

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.12 3.13 154851 119122 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.12 3.13 154851 119122 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (SSDUSER)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=119122 pr=154851 pw=0 time=0 us)
3242364 INDEX SKIP SCAN T1_I1 (cr=119122 pr=154851 pw=0 time=5498743 us cost=118126 size=152382272 card=3242176)(object id 75131)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
3242364 INDEX MODE: ANALYZED (SKIP SCAN) OF 'T1_I1' (INDEX)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 24305 0.00 0.21
db file scattered read 22165 0.00 0.61
SQL*Net message from client 2 0.00 0.00
********************************************************************************

I did then the same test on HDD and got the following:

SELECT count(1)
FROM T1
where n1 <= 8
and ind_pad <= rpad('x',39)||'x'
and n2 < 15

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.82 21.17 151107 117921 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.82 21.17 151107 117921 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 (TESTER)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=117921 pr=151107 pw=0 time=0 us)
3242364 INDEX SKIP SCAN T1_I1 (cr=117921 pr=151107 pw=0 time=14982249 us cost=116774 size=152382272 card=3242176)(object id 75130)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
3242364 INDEX MODE: ANALYZED (SKIP SCAN) OF 'T1_I1' (INDEX)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 2 0.00 0.00
db file sequential read 23673 0.07 8.99
db file scattered read 21544 0.10 9.85
SQL*Net message from client 2 0.00 0.00
********************************************************************************

We have db file file wait times of (8.99+9.85) for HDD vs (0.21+0.61). The index scan took 15 sec for HDD against 5 sec for SSD. Although the physical Reads were pretty similar in both cases (pr=151107) against (pr=154851).

Am I correct that the differences in execution times can be attributed to the seek time difference between Solid State Disks and conventional disks?

Thanks