Re: CBO performance between 7.1.4 and 7.3.2.1

From: F Lange <flange_at_pt.lu>
Date: 1997/06/19
Message-ID: <01bc7ce8$7298ba80$b2d79ac2@LangeFrancois>#1/1

Hi,

        There is a funny things with the optimiser since version 7.2.

        For Full table scan in 7.1, it was reading db_file_multiblock_read_count oracle block ( <= 64K in UNIX ) till it reach the high water mark.

        For Full table scan in 7.2 & 7.3, it read only all the block which are not in the SGA till it reach the high water mark.

Try the follow exemple.

	Create table a (
			a number ,
			b varchar2( 2000 ) ,
			)
		storage ( initial 
db_block_buffer * db_block_size
		next 4K
		pctincrease 0 
		minextents 1
		maxtextents 1 ) ;
	create table b ( r rowid )
			storage ( initial 100k next 100k pctincrease 0 );


	
	declare
		v_a number := 0 ;
	begin
		loop
			insert into a ( a , b  ) values ( v_a , 
'800 Characteres.'  );
			v_a := v_a + 1 ;
			commit ;
		end loop ;
	exception when others then null ;
	end ;
	/

	REM This script has fill the table a.


	insert into b ( r ) select rowid from a where substr ( rowid , 8 , 1 ) ¦¦

substr ( rowid , 13 , 1 ) in ( '10' , '30' , '50' , '70' , '90', 'B0' , 'D0' , 'F0' ) ;

        commit ;

        REM This script insert the first rowid of each odd block into the table b.

        shutdow immediate

        startup

        rem flush all the buffer

        echo 'select count ( a ) from a , b where a.rowid =b.r ;' ¦ sqlplus u/p_at_d

        rem load in sga all the odd block

        echo 'select count ( a ) from a ;' ¦ truss sqlplus u/p_at_d 2>truss_file

        rem look at the truss_file in 7.1 and in 7.2 & 7.3

        rem 7.1 number read ceil ( initial_extents / db_file_multiblock_read_count )

        rem the initial extents is ceil to the next 5 * oracle_block_size

        rem 7.2 & 7.3 number read initial_extents / 2 skeep initial_extents / 2

I cannot used parallele queries my full tablescan returned nearly 80% of the table. The paralelle queriy will crete and return a table as big as 80% of the initial ! Time from 7.1 to 7.3 increase a lot !

Regards Francois                                                   

fuocor_at_novachem.com wrote in article <866656463.19454_at_dejanews.com>...
> I just installed oracle ver 7.3.2.1 on a dec unix box. 7.1.4 already
> resides on the same box. I created an identical database for each
> version from one of our production systems. Both databases has stats for
> all tables with choose as the optimizer mode. I took three sql
> statements from the sql cache on our prod system and ran them through the
> explain and tkprof on the two versions of oracle. The 7.1.4 database
> performed approx 50 % better than the 7.3.2.1. The explain showed that
> the 7.1.4 databased used different indexes than the 7.3.2.1.
>
> I was always told that CBO does not work properly in any release prior to
> 7.3. Has anyone else noticed a performance loss on there applications
> after migrating to 7.3 from 7.1? In my situation 7.1.4 is obviously
> making better decisions on an execution path than 7.3.2
>
> note: both databases have the same init parameters and is using the same
> hardware and both have new stats generated
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
>
  Received on Thu Jun 19 1997 - 00:00:00 CDT