RE: Single block reads instead of multiblock reads
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <kennethnaim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Aug 2010 02:48:15 -0400
600 columns is probably your answer
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kenneth Naim
Sent: Wednesday, August 18, 2010 1:17 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Single block reads instead of multiblock reads
I have a query that needs to read 25% of a 100gb (116 million row, 600
columns) table which has been recently analyzed. The database is 11.1.0.7,
sga is 35gb, pga 15gb, solaris, 16 cpus, 16k blocksize.
The query only accesses one table. When run single threaded or in parallel
the query runs for hours with the top wait event being db file sequential
read not sequential reads with the p3 showing 1 block. I have checked the
explain plan, rowsource in a tkrpof-ed trace file, the v$sql_plan all show
full table scan. I've specified a full hint with the correct alias and I'm
at a loss on why I'm not seeing a multiblock read.
I've even simplified the case to be ctas * from big_table where
rownum<1000000 and it still does single block reads.
Anyone has ideas to why this could be happening?
Ken
Other related posts:
