Improving SQL execution plans
Intelligent buffer segregation improves overall execution speed
by reducing buffer gets, but there are also some other important
reasons to use multiple blocksizes.
In general, the Oracle CBO is unaware of buffer
details, except when the
optimizer_index_caching
parameter is set where
using multiple data buffers will not impact SQL execution
plans. When data using the new
cpu_cost
parameter in Oracle10g, the Oracle SQL optimizer builds
the SQL plan decision tree based on the execution plan that will have the lowest estimated CPU cost.
For example, if a 32k data buffer is implemented for the index tablespaces, the DBA can ensure that the indexes are cached for
optimal performance and minimal logical I/O in range scans.
For example, if a database has 50 gigabytes of index space, a 60
gigabyte
db_32k_cache_size can be defined and then the
optimizer_index_caching parameter can be set to
100, telling the SQL optimizer that all of the Oracle indexes
reside in RAM. When Oracle makes the index versus table scan
decision, knowing that the index nodes are in RAM will greatly
influence the optimizer because the CBO knows that a
logical I/O is often 100 times faster than a physical disk read.
In sum, moving Oracle indexes into a fully cached 32k buffer
will ensure that Oracle favors index access, reducing
unnecessary full table scans and greatly reducing logical I/O
because adjacent index nodes will reside within the larger, 32k
block.
SEE CODE DEPOT FOR FULL SCRIPTS
