Re: Db file scattered reads vs. direct path reads
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Feb 2008 20:54:02 -0000
scattered reads: the CPU cost of clearing buffers, linking buffer
headers to different buffer cache chains, pinning buffer headers,
and then loading the buffers may be included in the "wait" time
for scattered reads. Direct path reads don't have these overheads.
(Nevertheless, 11 minutes down to 3 is a bit dramatic).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Josh Collier" <Josh.Collier@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, February 20, 2008 8:20 PM
Subject: Db file scattered reads vs. direct path reads
Hi,
I've got a conundrum. We've got a new disk system. When I full scan a table with
the nocache hint the trace files record lots of reads like this
WAIT #1: nam='db file scattered read' ela= 38471 file#=777 block#=238789
blocks=64 obj#=2805234 tim=359927798919
And the query takes 11 minutes for a 19gb table, much slower than our old system
When I enforce direct path reads with the following parameter
:alter session set "_serial_direct_read" = true
The trace files record lots of reads like this
WAIT #1: nam='direct path read' ela= 5 file number=777 first dba=238789 block
cnt=64 obj#=2805234 tim=343398354853
And the query returns in 3 minutes
What would cause the scattered reads to be so much slower than the direct path
reads?
I've enabled direct io on the db and verified it via truss.
Version is 10.2.0.1
Solaris 10
--
//www.freelists.org/webpage/oracle-l
Other related posts:
