PLSQL CPU consumption

  • From: Adrian <ade.turner@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 25 Sep 2006 20:22:55 +0100



Platform: V440, Solaris 8, 9206 Enterprise Edition.


Related parameters:






I'm on a site whereby the plsql guys have won the war and the java guys call
PLSQL packages which do nothing but return open ref cursors for fetching;
however the bind variable message did not get across so cursor_sharing is
set to SIMILAR.


The system is very CPU bound under load and I've been investigating why. 


Initially statspack/tracing showed a candidate select statement which was
incurring several thousand times more CPU than any other due to a several
hash joins (which I fixed with a materialized view). 


Unfortunately despite tuning this to run in 0.003secs they've still got the
CPU performance issue - in effect tuning the query made no difference.


When looking at v$sqlarea cpu_time I discovered that the CPU incurred by the
call to the plsql package was disproportional to the CPU incurred by the SQL
statement (ratio of 9:1) and the calls show up in v$sqlarea as


      Call package_name.proc(:1, :2, :3);


There are about six package calls to ref cursor procedures which are
introducing 80% of the CPU load. One commonality is that they are large sql
statements within an even larger package.


I suspect the cursor* parameters are to blame, and that possibly setting
cursor_sharing=exact in the session prior to the call would resolve it, but
as it only occurred to me while I was driving home, does this scenario sound
familiar to anyone?


TIA for any feedback











Other related posts: