Re: PLSQL CPU consumption

  • From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
  • To: Bernard.Polarski@xxxxxxxxxxxxxx
  • Date: Tue, 26 Sep 2006 10:19:55 -0500

Hi Adrian

Can you print lines from statspack for top events, as well as, lines from latch details area ?

Is gets and misses uniform across all the library cache children latches ? or Is it one library cache latch child has higher misses ? This will tell you, whether one library cache object is causing all these misses or not. I highly doubt that scenario, but just want to be sure of it.

If the misses are uniform, then can you query to v$sql to find SQL that doesn't use, let's say a rarely used feature known as, bind variables?

while session_cached_cursors must help, still that is not going to resolve issues with non-sharable SQLs.

Here is one script I use. I am not sure whether I wrote this or somebody sent to me, apologies, if I am not the author [use of subquery factoring and poor comments tells me that I may be the one ;-) ]. SQLs just differ in literal variable bound to have same access plan and plan_hash_value will remain the same, generally. Idea is to find SQLs using same plan_hash_value. Then we query v$sql again to print full text, as these developers seems to be fond of SQL full text.

PS: Hopefully, this script will work for you. I have tested this in 10g, not in 9i.

---------------
set serveroutput on size 1000000
spool /tmp/sqls_without_bind.lst
declare
v_sql_text varchar2(250);
begin
for v_my_row in (
with sqls_with_high_cnt as (
select plan_hash_value, count(*) from v$sql
where plan_hash_value > 1
group by plan_hash_value having count(*) > 50 --or whatever number you like
order by count(*)
)
select
s.plan_hash_value, substr(sql_text,1,40 ) sqltext, sum(executions) tot_exec, sum(parse_calls) tot_parse
from v$sql s, sqls_with_high_cnt scnt
where s.plan_hash_value=scnt.plan_hash_value
group by s.plan_hash_value, substr(sql_text,1,40 )
order by 3
) loop
select substr(sql_text,1,250) into v_sql_text
from v$sql
where plan_hash_value = v_my_row.plan_hash_value
and substr(sql_text,1,40) = v_my_row.sqltext
and rownum <2;
dbms_output.put_line ('-------------------------------------------------------' );
dbms_output.put_line (v_sql_text );
dbms_output.put_line ('EXEC :'||v_my_row.tot_exec|| ' : '|| v_my_row.tot_parse);
end loop;
end;
/
spool off

------------

Of course, since you use ref cursor, they MUST be soft parsed. Read Tom Kyte's website.

http://asktom.oracle.com/pls/ask/f?p=4950:8:15838649304879256784::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:904605517791

Thanks
Riyaj

Polarski, Bernard wrote:
A guess : cursor_space_for_time trigger premature flush out of shared pool of high number of objects hence cpu activity, specialy in DB with sql without bind.
The effect would be similiar to a small SGA.
B. Polarski

------------------------------------------------------------------------
*From:* Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
*Sent:* Tuesday, 26 September, 2006 3:06 PM
*To:* panandrao@xxxxxxxxx; ade.turner@xxxxxxxxx
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* RE: PLSQL CPU consumption

Um, how is cursor_space_for_time "known to be CPU bound"? It will certainly cause higher memory usage, but how does it affect CPU consumption?


*--*
*Mark J. Bobak*
*Senior Oracle Architect*
*ProQuest Information & Learning*

"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly. Specialization is for insects." --Robert A. Heinlein




------------------------------------------------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Anand Rao
*Sent:* Tuesday, September 26, 2006 12:49 AM
*To:* ade.turner@xxxxxxxxx
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: PLSQL CPU consumption

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



On 26/09/06, *Adrian* <ade.turner@xxxxxxxxx <mailto:ade.turner@xxxxxxxxx>> wrote:

    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
    <mailto:davidsharples@xxxxxxxxx>]
    *Sent:* 25 September 2006 20:33
    *To:* ade.turner@xxxxxxxxx <mailto:ade.turner@xxxxxxxxx>
    *Cc:* oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
    *Subject:* Re: PLSQL CPU consumption



    what are you biggest wait  / timed events




The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2006