PLSQL CPU consumption
- From: Adrian <ade.turner@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 25 Sep 2006 20:22:55 +0100
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
Other related posts:
