Re: PLSQL CPU consumption

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Tue, 26 Sep 2006 10:19:55 -0500
Message-ID: <4519451B.8060400@jcpenney.com>

('binary' encoding is not supported, stored as-is)

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_at_il.proquest.com]

> *Sent:* Tuesday, 26 September, 2006 3:06 PM

> *To:* panandrao_at_gmail.com; ade.turner_at_gmail.com

> *Cc:* oracle-l_at_freelists.org
> *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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Anand Rao
> *Sent:* Tuesday, September 26, 2006 12:49 AM
> *To:* ade.turner_at_gmail.com
> *Cc:* oracle-l_at_freelists.org
> *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_at_gmail.com
> <mailto:ade.turner_at_gmail.com>> 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_at_gmail.com

>     <mailto:davidsharples_at_gmail.com>]

>     *Sent:* 25 September 2006 20:33

>     *To:* ade.turner_at_gmail.com <mailto:ade.turner_at_gmail.com>

>     *Cc:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>

>     *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.

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

Received on Tue Sep 26 2006 - 10:19:55 CDT