RE: Important: Oracle processes taking lots of CPU

Hi New DBA,

>We are facing a unique problem. Lots of DB processes
>take close to 100% CPU and an overall CPU time of more
>than 500 mins. This happens with lots of different
>applications/forms. The problem doesn't happen all the
>time i.e. if the same form is run 10 times, only 1-2
>faces that kind of problem.

Please be aware that 9i (and above) performs bind-peeking, which is a double-edged sword. Bind peeking enables execution plans to consider the values in histograms when available although bind variables are used in the SQL. While this is advantageous since histograms can now be used, it cuts the other way when the *first* bind generates a plan that is inefficient for *subsequent* values. Combine this with the fact that Oracle Apps has a large number of histograms (see APPLSYS.FND_HISTOGRAM_COLS table!), and by its nature has very skewed data especially in Org_ID type of columns, and fully uses bind variables in Forms processing (a good thing!), I would guess that this is the case... [I recently had a slow running report that needed to have its bind variables replaced with hardcoded values so that the *right* plan could be used]. Traces may show that you are performing excessive amounts of 'db file sequential reads' - which will also result in CBC latching and large amount of LIOs.

If this is indeed the case, you will need to look at disabling bind-peeking using the hidden parm '_optim_peek_user_binds' - obviously you need to consult with Support for this.

Btw, 'CPU used by this session' is not upadated until the call completes. As well, it seems that you have a background in 7.x and lower, as STATSPACK is the default 8i and above....

Let us know how it goes.
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Fear connects you to the Negative, but Faith connects you to the Positive! I Jn 4:18

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- http://www.freelists.org/webpage/oracle-l