 |
|
Oracle Tips by Burleson |
Oracle physical I/O Myths,
exposed
There are several myths of Oracle physical
I/O that must be exposed at this point:
-
All Oracle databases are I/O-bound:
Untrue. Databases with a reasonable data buffer cache size and
a small working set will usually be constrained by CPU or
network latency.
-
The Data Buffer Hit Ratio (DBHR) will
yield caching efficiency: Untrue, except in cases of a super
small cache. The DBHR only measures the propensity that a data
block will be in the buffer on the second I/O request.
-
Only faster disk can remove I/O
bottlenecks: Untrue. This is a common myth. There are other
non-RAM approaches to reducing disk I/O for Oracle databases:
-
Adjusting optimizer_mode: Oracle will
generate widely differing SQL execution plans depending on the
optimizer mode.
-
Re-analyze SQL Optimizer statistics:
Using better quality CBO statistics with dbms_stats and adding
column histograms can make a huge difference in disk activity.
-
Adjusting Oracle parameters: Resetting
the optimizer_index_cost_adj and optimizer_index_caching
parameters can affect physical reads
-
Improve clustering_factor for index
range scans: Manually resequence table rows to improve
clustering_factor, sometimes using single-table clusters, can
reduce disk I/O.
-
Use Materialized Views: Systems with batch only updates may
greatly benefit from Materialized Views to pre-join tables. Of
course, the overhead of refresh commit is too great for high
update systems.
The above book excerpt is from:
Oracle RAC
& Grid Tuning with Solid State Disk
Expert Secrets for High Performance Clustered Grid Computing
ISBN:
0-9761573-5-7
Mike Ault, Donald K. Burleson
http://www.rampant-books.com/book_2005_2_rac_ssd_tuning.htm |
