If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

statspack wait events

i Guy,
My database is 10g and and my database is running in auto tuneing,i.e I kept in sga_target,
and my application is hanging ,can any body help me in finding why cpu is high and
what is parse cpu to parsed in statspack.

I don't know if I'm being stupid here, but have you looked at the STATSPACK report that the poster attached? All the SQL seems to use bind variables and % Non-Parse CPU = 99.88. I don't think bind variables are the issue here, although I could be wrong?

. . . also your two reports are very different: the "in-line" one shows 21 seconds CPU in 20 minutes, the .txt file shows 8'798 second in 120 minutes and only one CPU ! ! ! (probably an effect of a long transaction finishing in the time slot).

"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

From the posted file 2 hour window. It looks like your app is transmitting most/ if not all of the PL/SQL to be executed into the server. Hence the high sql net activity.

What type of system and How many CPU's?

Can you modify the application to utilize named procedures/packages instead of anonymous procedures? Then use compiled PL/SQL and ping the packages.

BAD SQL:
SELECT ABS(:b1),ABS(:b2) FROM DUAL ----> Rewrite this, you don't need the DB to do this math work.
There seems to be a lot of that type thing. Each one by itself is not a lot, but it adds up. If you don't have to select the data, don't!
Instead:

var2 = abs(var1) --- If this is embedded in the app. Then no round trip to the DB is required... 100 x faster.

or if in PL/SQL
var2 := abs(:var1) ; (this is a function call and requires less overhead)