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 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
Oracleand above are worth your investigation and trials in your own
database.
and include the URL for the page.
