RE: Single block reads instead of multiblock reads

LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K BLOCKSIZE 16K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;   Db block size is 16k  

And db_file_nultiblock_read_count is unset in the spfile and is 64 in v$parameter.  

Thanks,

Ken  

From: Matt Schouviller [mailto:matt.schouviller_at_banfield.net] Sent: Wednesday, August 18, 2010 2:47 AM To: kennethnaim_at_gmail.com; oracle-l_at_freelists.org Subject: RE: Single block reads instead of multiblock reads  

Can you provide the configuration for the tablespace(s) that this table is in? Also please provide the block size and the value of the db_file_multiblock_read_count init parameter.  

Thanks

Matt  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kenneth Naim
Sent: Tuesday, August 17, 2010 10:17 PM
To: oracle-l_at_freelists.org
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      

--
http://www.freelists.org/webpage/oracle-l