Re: reduce wait times

Is the etl procedure run from a script or specific program? if so a much better approach to tuning it would be as follows

1, immediately before it starts, in either the script or the program issue ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED; -- might not be valid on 816 so you might need just a large number
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; 2. run your procedure
3. after the procedure has finished issue ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; this will generate a (possibly huge) trace file containing timing information about *only* the process in which you are interested.

if you run that through tkprof with sort=(prsela,exeela,fchela) as your sort ptions it will produce a more easily readable file with your most time consuming (and therefore problematic) statements handily placed right at the top. This will likely save you valuable troubleshooting time because you are only looking at what you wish to tune.

statspack is good for short periods and for system overview. sql_trace as per the above is good for tuning problem processes.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
"gp" <gieppetto_at_tiscali.it> wrote in message
news:c0icbg$3rm$1_at_fata.cs.interbusiness.it...


> I consider a whole day for statspack because during the night start an etl

> procedure that read/write big quantity of data and i want tuning that,

too.


> thanks.

>

> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> ha scritto nel

> messaggio news:402cac67$0$10342$ed9e5944_at_reading.news.pipex.net...

> > "gp" <gieppetto_at_tiscali.it> wrote in message

> > news:c0i34i$plh$1_at_fata.cs.interbusiness.it...

> >

> > Hi

> >

> > Do you actually believe that you have a performance issue, or are you

> trying

> > to learn about statspack?

> >

> > Your snapshot spans a whole day, essentially it will be useless for

> > performance tuning since you have just aggregated everything that

happened


> > in the last 24 hours (including presumably if this is a prod system at

> least

> > 1 backup). You might use such a report as a baseline for your systems,

but


> > that is probably about the extent of its usefulness.

> >

> > I hear a number of good things about Donald Burleson's book on

statspack,


> > though I can't say that I have read it, so if you are trying to get to

> grips

> > just with statspack you might have a browse of that book in your nearest

> > bookshop and see if it is suitable. If you want to get a better

> > understanding of waiting and performance tuning though, you should get

> > Oracle Performance Tuning 101 http://tinyurl.com/3egqh which does cover

> the

> > statspack utility, but teaches the whys and wherefores of wait event

based


> > tuning. This book I *have* read and is excellent.

> >

> >

> > -- 

> > Niall Litchfield

> > Oracle DBA

> > Audit Commission UK

> >

> >

>

>