weird behaviour for mreadtim < sreadtim in 10g

From: amit poddar <amit.poddar_at_yale.edu>
Date: Fri, 08 Sep 2006 14:38:39 -0400
Message-ID: <4501B8AF.4060202@yale.edu>

Hi,

I am struggling with a query performance issue which brought me to this quandary about calculation of cost of full table scans when system stats are enabled

Database Version: 10.2.0.2

Here are the details

Num Blocks in the table: 91872

mbrc 6

Mreadtim Sreadtim Cost

1                      3                      91864
1.1                   3                      91864
1.3                   3                      91864
1.8                   3                      91864
2.8                   3                      91864
3                      3                      91864
3.05                 3                      15569  (approximately = 

91872/mbrc)

My question is what calculation 10g is using for calculating FTS cost when mreadtim < sreadtim.

Because in 9i when mreadtim < sreadtim oracle seems to ignore the system stats and calculates FTS cost as approximately (num_blocks/5.6) (since db_file_multiblock_read_count is 8)

But 10.2 is behaving differently.

Any help would be appreciated

thanks
amit

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

Received on Fri Sep 08 2006 - 13:38:39 CDT