PLSQL CPU consumption

All,  

Platform: V440, Solaris 8, 9206 Enterprise Edition.  

Related parameters:

      Cursor_sharing=similar

      Cursor_space_for_time=true

      Query_rewrite_enabled=true

      Shared_pool_size=800M  

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  

Cheers,

Adrian              

--
http://www.freelists.org/webpage/oracle-l