 |
|
Proof
that large index blocksizes reduce I/O
May 27, 2003Oracle Tips by
Robin Schumacher |
(Note: Robin's book excerpt work below
omits some important external factors that influence multiple
blocksize read performance.
For the latest consensus on using
multiple blocksizes in Oracle, see
The 2008 consensus on multiple
blocksizes.)
Empirical evidence suggests that you can use the
large (16-32K) blocksize and separate data caches to improve response
time under certain conditions. Does such a
thing really help performance? A small but revealing test can
answer that question. This is from
Robin Schumacher's book
“Oracle
Performance Troubleshooting”, demonstrating that a
larger blocksize can result in a reduction in logical I/O with
larger blocksizes. However, be aware that external influences (SAN,
NAS, RAID level, Stripe Size, &c) will influence your response time.
YMMV . . . .
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 range scan 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.
These and many other
revelations are now available in Robin’s phenomenal book “Oracle
Performance Troubleshooting”. IMHO, this is one of the
best Oracle performance books in many years. Robin’s book is
available at
this link:
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
