By Robin Schumacher
You can use the large (16k or 32K)
blocksize data caches to contain data from indexes or tables that are
the object of repeated large scans. Does such a thing really help
performance? A small but revealing test can answer that question.
For the test, the following
query will be used against a 9i database that has a database block size
of 8K, but also has the 16K cache enabled along with a 16K tablespace:
select
count(*)
from
eradmin.admission
where
patient_id between 1 and 40000;
The
ERADMIN.ADMISSION table has 150,000 rows in it and has an index build on
the PATIENT_ID column. An EXPLAIN of the query reveals that it uses an
index fast-full scan (a multi-block read) to produce the desired end result:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 (Cost=41 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'ADMISSION_PATIENT_ID'
(NON-UNIQUE) (Cost=41 Card=120002 Bytes=480008)
Executing the query (twice to eliminate parse activity and to cache any
data) with the index residing in a standard 8K tablespace produces these
runtime statistics:
Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
421 consistent gets
0 physical reads
0 redo size
371 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
To test the
effectiveness of the new 16K cache and 16K tablespace, the index used by
the query will be rebuilt into the 16K tablespace that has the exact
same characteristics as the original 8K tablespace, except for the
larger blocksize:
alter index
eradmin.admission_patient_id
rebuild nologging noreverse tablespace indx_16k;
Once the index is nestled firmly into the 16K tablespace, the
query is re-executed (again twice)with the following runtime statistics
being produced:
Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
211 consistent gets
0 physical reads
0 redo size
371 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, the amount of
logical reads has been reduced in half simply by using the new 16K
tablespace and accompanying 16K data cache. Clearly, the benefits of
properly using the new data caches and multi-block tablespace feature of
Oracle9i and above are worth your investigation and trials in your own
database.
As Robin demonstrated in his example, the
consistent reads are cut in half (340 for 8k block index, 172 for 16k
block index). Lets evaluate the performance numbers. The trace file
shows that the 16k block index scan ran in 0.04 seconds where the 8k
block index scan ran in 0.03. It might appear that the 8k block index is
actually faster, but if we look at the raw trace, its basically a
rounding issue: TKPROF only reports seconds to two decimal places.
Calculating the times from the raw trace we get 0.043023 seconds vs.
0.039051, close enough in my book to call equal. So, while in this
example the consistent reads are cut in half, it did not yield any
significant performance difference. I suspect the same for Robin's
example, but it doesn't seem he provided any timings.
