Re: Enterprise slow, Standard fast?

"Arska" <NaruArska_at_hotmail.com> wrote in message news:cmiodv$g8t$1_at_plaza.suomi.net...
> I think we still look too much this one setting.
> I can set this to original value and it does not have any impact to this
> 'huge' job.

So this is one particular job?

<snip>

> How can I find what exactly oracle EE is doing when it does not wait
> anything and is not using all cpu available?

Now you have asked an excellent question. EE (or for that matter SE) is either using the CPU *or* it is waiting for something - These are the only two states a process can be in. How can you tell which, well Oracle has a facility generally referred to as extended SQL trace that will show you, pretty much exactly, the answer to your question. It is exactly designed to answer your question namely what is my slow application session doing?

So what would I advise you to do? Well first I'd advise you to go to http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html to get an overview of what I am about to suggest. I'd amend your 'huge' job so that at the start it issues

alter session set max_dump_file_size=unlimited; alter session set events '10046 trace name context forever, level 8'; replace 8 with 12 if you use bind variables. then do your 'job'
finally end with
alter session set events '10046 trace name context off';

This will create a (probably large) trace file in the directory pointed to by the init.ora parameter USER_DUMP_DEST Next find this file and use the supplied Oracle command line utility tkprof as follows.
tkprof <your trace> slow.log sort=(prsela,fchela,exeela)

This will produce a list of your sql statements sorted by elapsed time. The problem ones are at the top. Tkprof will also tell you how much time was spent executing and how much time was spent waiting. You then need to focus on these statements to solve your issue.

By the way SE and EE will generally perform in the same way unless you are making use of Enterprise only features in your enterprise example (in which case your suggestion that the two setups were the same is just wrong). That is SE is not slower or faster than EE, EE just enables you to do more things, setup identically on identical hardware and with an identical load SE and EE will perform exactly the same code in exactly the same way.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com