Hi,
i would try disabling cursor_space_for_time. it is known to be CPU bound. not very sure how much of that is affecting you. your wait event suggests libary cache issues. i am no good with ref cursors, so i can't really comment on that.
could be that there are large no. of copies of the same statement or that your packages / sql are getting invalidated from inside another proc. needs more diagnosis for sure.
just try,
cursor_space_for_time=false
and bounce your instance.
your next step is to drill down into V$SQL, V$SQLAREA and all those packages. do you use a lot of SQL from inside those packages?
cheers anand
Hi David,
Sorry, I'm not onsite (UK based) so cant give you exact info, but pretty much
Latch Free(librarycache) 50%
CPU Time, 45%
sequential read(much lower) 5%
The latch frees only appear under CPU starvation. Under normal load its 90% to CPU Time.
Tkprof output does not seem to show the considerable cpu time attributed by v$sqlarea to the package call.
Cheers
Adrian
------------------------------
*From:* David Sharples [mailto:davidsharples@xxxxxxxxx] *Sent:* 25 September 2006 20:33 *To:* ade.turner@xxxxxxxxx *Cc:* oracle-l@xxxxxxxxxxxxx *Subject:* Re: PLSQL CPU consumption
what are you biggest wait / timed events
-- All I need to make a comedy is a park, a policeman and a pretty girl - Charlie Chaplin