db file sequential read

8/10/2019 Db File Sequential Read

1/5

Home

E-mail Us

Oracle Articles

Oracle Training

Oracle Tips

Oracle Forum

Class Catalog

Remote DBAOracle Tuning

Emergency 911

RAC Support

Apps Support

Analysis

Design

Implementation

Oracle Support

SQL Tuning

Security

Oracle UNIX

Oracle Linux

Monitoring

Remote support

Remote plans

Remote services

Application Server

Applications

Oracle Forms

Oracle Portal

App Upgrades

SQL Server

Oracle Concepts

Software Support

Remote Support

Development

Implementation

Consulting Staff

Consulting Prices

Help Wanted!

Oracle Posters

Oracle Books

Oracle Scripts

Search BC Oracle Sites

Search

10gr2 Note: Starting in Oracle 10g release2, Oracle recommends not setting thedb_file_multiblock_read_countparameter, allowing Oracle to empirically determine theoptimal setting. For more details, see my notes on10gR2 automatically tuned multi-blockreads. Also, see my related important notes on tuning full-scan I/O.

Because all Oracle databases retrieve and store data, the relative cost of physical disk access isan important topic. In Oracle, we see two types of data block access:

db file sequential readA single-block read (i.e., index fetch by ROWID)

db file scattered readA multiblock read (a full-table scan, OPQ, sorting)

Physical disk speed is an important factor in weighing these costs. Faster disk access speedscan reduce the costs of a full-table scan vs. single block reads to a negligible level.

For example, the new solid state disksprovide up to 100,000 I/Os per second, six times fasterthan traditional disk devices. In a solid-state disk environment, disk I/O is much faster andmultiblock reads become far cheaper than with traditional disks.

The standard STATSPACK report can be generated when the database is processing a peakload, and you can get a super-detailed report of all elapsed-time metrics. The most importantof these metrics is the STATSPACK top-five timed events. This report is critical because it

shows the database events that constitute the bottleneck for the system. We can also see thesame phenomenon where a system is disk I/O bound. In the STATSPACK report in Listing A,we see that the system is clearly constrained by disk I/O.

Top 5 Timed Events% Total

Event Waits Time (s) Ela Time--------------------------- ------------ ----------- --------db file sequential read 2,598 7,146 48.54db file scattered read 25,519 3,246 22.04library cache load lock 673 1,363 9.26CPU time 1,154 7.83log file parallel write 19,157 837 5.68

Here we see that reads and a write constitute the majority of the total database time. In thiscase, we would want to increase the RAM size of thedb_cache_sizeto reduce disk I/O, tunethe SQL to reduce disk I/O, or invest in a faster disk I/O subsystem.

The ideal optimizer settings depend on your environment and are heavily influenced by yoursystem's costs for scattered disk reads vs. sequential disk reads. Below is a great script youcan use to measure these I/O costs on your database.

col c1 heading 'Average Waits|forFull| Scan Read I/O' format 9999.999col c2 heading 'Average Waits|for Index|Read I/O' format 9999.999col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999

selecta.average_wait c1,b.average_wait c2,a.total_waits /(a.total_waits + b.total_waits) c3,b.total_waits /(a.total_waits + b.total_waits) c4,(b.average_wait / a.average_wait)*100 c5

fromv$system_event a,v$system_event b

wherea.event = 'db file scattered read'

and

b.event = 'db file sequential read';

Oracle sequential read disk I/O tuning

Oracle Tips by Burleson ConsultingUpdated October 29, 2007

Oracle TipsGot Questions?

ORA 19815Warning

Restart a FailedDaemon Process

ORA 20000 ORU10027 bufferoverflow limit of2000 bytes

BEWARE of11gR2 UpgradeGotchas!

Page 1 of 5Oracle sequential read I/O tuning tips

12.06.2013http://www.dba-oracle.com/art_builder_io_speed.htm


2/5

Ion

Excel-DB

Scattered reads and full-table scans

Contrary to some opinions, full-table scans are not necessarily a detriment to performance, andthey are often the fastest way to access the table rows. The CBO (cost-based optimizer) choiceof performing a full-table scan depends on the settings for Oracle Parallel Query, thedb_block_size, the clustering_factor, the estimated percentage of rows returned by the query(according to the CBO statistics), and many other factors.

Once Oracle has chosen a full-table scan, the speed of performing a full-table scan (SOFTS)depends on internal and external factors:

The number of CPUs on the system The setting for Oracle Parallel Query (parallel hints, alter table)

Table partitioning The speed of the disk I/O subsystem (e.g., hardware-cached I/O, solid-state disk RAM

3)

With all of these factors, it may be impossible to determine the exact best setting for theweight in optimizer_index_cost_adj.In the real world, the decision to invoke a full-table scanis heavily influenced by run-time factors such as:

The availability of free blocks in the data buffers The amount of TEMP tablespace (if the FTS has an order byclause) The current demands on the CPUs

Hence, it follows that the optimizer_index_cost_adjshould change frequently, as the loadchanges on the server.

However, is it safe to assume that all of the SOFTS factors are reflected in the relative I/O

speed of FTS vs. index access? If we make this assumption, we've measured the relative speedin v$system_eventand have a foundation for creating a self-tuning parameter. To do this, wemust accept the following assumptions:

No systems are alike, and good DBAs must adjustoptimizer_index_cost_adjaccordingto their configuration and data access patterns.

The SOFTS is measurable and is reflected in the wait times in v$system_event.

The overall amount of time spent performing full-table scans is equal to the percentageof db file sequential readwaits as a percentage of total I/O waits from v$system_event:

(avg-wait-for-db-file-sequential-read /

avg-wait-for-db-file-scattered-read) * 100

Control disk I/O

Disk I/O-intensive systems are common to Oracle databases, and the time spent performingdisk I/O often consumes the majority of the wait time. The job of the Oracle professional is toexamine all SQL statements to ensure that they're performing the minimum amount of disk I/Oand to know their relative costs of index access vs. full-table scan access.




3/5

Here is sample output from a real system showing an empirical showing the numbers. Wealways expect scattered reads (full-table scans) to be far faster than sequential reads (indexprobes) because of Oracle sequential prefetch (db_file_multiblock_read_count):

- scattered read (full table scans) are fast at 13ms (c3)- sequential reads (index probes) take much longer 86ms (c4)

- starting setting for optimizer_index_cost_adj at 36:

C1 C2 C3 C4 C5

---------- ---------- ---------- ---------- ----------

13,824 5,072 13 86 36

Here is another variant, showing changes to optimizer_index_cost_adj (see important notebelow) wait components over time:

set pages 80

set lines 130

col c1 heading 'Average Waits for|Full Scan Read I/O' format 999999.999

col c2 heading 'Average Waits for|Index Read I/O' format 999999.999

col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format

999.99

col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format

999.99

col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 99999

select a.snap_id "Snap",

sum(a.time_waited_micro)/sum(a.total_waits)/10000 c1,

sum(b.time_waited_micro)/sum(b.total_waits)/10000 c2,

(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c3,

(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c4,

(sum(b.time_waited_micro)/sum(b.total_waits)) /

(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5

from

dba_hist_system_event a,

dba_hist_system_event b

where a.snap_id = b.snap_id

and a.event_name = 'db file scattered read'

and b.event_name = 'db file sequential read'

group by a.snap_id

order by 1

/

Snap Full Scan Read I/O Index Read I/O Full Scans Index Scans

---------- ------------------ ----------------- ------------- --------------

5079 .936 .074 10.14 89.86

5080 .936 .074 10.14 89.86

5081 .936 .074 10.14 89.86

5082 .936 .074 10.14 89.86

5083 .936 .074 10.13 89.87

5084 .936 .074 10.13 89.87

5085 .936 .074 10.13 89.87




4/5

Important Note: Prior to Oracle 10g, adjusting these optimizer parameters was the only wayto compensate for sample size issues with dbms_stats. As of 10g, the use ofdbms_stats.gather_system_statsand improved sampling withindbms_statshad madeadjustments to these parameters far less important. Ceteris Parabus, always adjust CBOstatistics before adjusting optimizer parms. For more details on optimizer parameters, see mylatest book "Oracle Tuning: The Definitive Reference".

Burleson is the American Team

Note:This Oracle documentation was created as a support and Oracle trainingreference for use by our DBA performance tuning consulting professionals. Feelfree to ask questions on our Oracle forum.

Verify experience!Anyone considering using the services of an Oracle support

expert should independently investigate their credentials and experience, andnot rely on advertisements and self-proclaimed expertise. All legitimate Oracleexperts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oraclesupport information. If you find an error or have a suggestion for improving ourcontent, we would appreciate your feedback. Just e-mail:

and include the URL for the page.




5/5

Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services

Copyright 1996 - 2012

All rights reserved.

Oracleis the registered trademark of Oracle Corporation.


12 06 2013h // db l / b ild i d h

db file sequential read

Documents