Re: session wait on "control file sequential read" event ,performance was very slowed

machao wrote:
> OS version :hpux 11i
> RDBMS Version :9.2.0.6
>
> I haved a application.
> it inserted data to a very large table,there was 1000+ partition in
> this table.
>
> the speed was very slowed.
>
> I use "select sid,event from v$session_wait",
> It was control file sequential read event.
>
> i run the application on another db,there was a same table ,but the
> table is smaller, it only has 100+ partition.
> the speed was very fast.
>
> what happend?
> what can i do?
> think you very much.

How big are the controlfiles on the two db's? Do you have alert log messages like "kccrsz: expanded controlfile section ..."? Are you using RMAN without a repository? How many data files do your db's have? Are you using multiple buffer pools? What other files are on all devices with controlfiles? How often are your logs switching? What sort of devices are they? What kind of partitioning are you using? What kind of spread over the partitions does your inserted data have? What are the values of
maxlogfiles
maxlogmembers
maxloghistory
maxinstances
control_file_record_keep_time

I expect what is happening is you have many datafiles, and as you update each has to talk to the controlfile. Also, it is likely you have some sort of conflict like having your archived logs on the same device as redo and a controlfile, or RAID-5 everywhere, and perhaps log files too small so they are switching too often. You probably have one particular controlfile that is messing up, so try select P1,P1_TEXT from V$SESSION_WAIT where EVENT like 'control file%' and STATE='WAITING'; to see which file.

jg

--
@home.com is bogus.
There's a boring job, listening to old order tapes:
http://www.signonsandiego.com/uniontrib/20060810/news_1b10sempra.html