Re: Single block reads instead of multiblock reads
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: kennethnaim@xxxxxxxxx
- Date: Wed, 18 Aug 2010 18:43:37 +0800
Do you see any multiblock reads at all?
This is likely due row chaining, due too large row size or due having over
255 columns in the table.
You should measure whether the "table fetch continued rows" statistic
increases while you scan the table:
http://blog.tanelpoder.com/2009/11/04/detect-chained-and-migrated-rows-in-oracle/
--
Tanel Poder
http://tech.e2sn.com
http://blog.tanelpoder.com
On Wed, Aug 18, 2010 at 1:16 PM, Kenneth Naim <kennethnaim@xxxxxxxxx> wrote:
> 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:
