Re: INSERTS - Reducing Buffer Gets/Sequential Read Waits

c5sieb schrieb:
> Performance Gurus,
>
>
> Has anyone a proven technique for reducing "sequential file waits" during
> INSERT statements? Some very large tables, with only one or two indexes are
> incurring this wait event (from a SQL trace) and have high buffer gets (from
> Statspack). Is it safe to assume that this appears to be primarily due to
> the index probes? We really can't drop or disable the indexes in this OLTP
> environment. We are planning to partition several of these large tables
> with over 1000 range partitions and use local indexes. Also, would changing
> the block size of either the table and/or index tablespace from 8k to 16k
> (or 32k) help? We can't change the code (like adding hints), as we are
> using a 3rd party application to perform the data loads.
>
>
> Steve
>
>

The high amount of "db file sequential read" itself doesn't mean you have bad performance. As long as your IO subsystem can serve sequential reads timely - you have not necessarily performance issues. Higher amount of buffer gets ( compared to the insert into nonindexed table ) is due to the the need to read (additionally to table blocks) root block, branch blocks and leaf blocks of index. One of wideadopted methods to get around the performance issues during data load is to use direct load (aka insert --+ append) , make indexes unusable before the load and rebuild them after load with high degree of parallelism. In an OLTP system overall performance can get much worser by introducing   partitioning ( especially in conjunction with local indexes especially with relativ high number of partitions ~ 1000) because your select statements would require much more buffer gets and as consequence much more sequential reads. This thema is good discussed in the Tom Kyte's "Effective Oracle by Design" - i have observed such behaviour many times also.

Best regards

Maxim Received on Fri Jun 17 2005 - 06:17:06 CDT