Home » RDBMS Server » Server Administration » Find current shared pool usage of my program (Oracle 11g)
Find current shared pool usage of my program [message #573633] Tue, 01 January 2013 08:09 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi,

I have a package when it runs, it terminates with
"unable to allocate 4120 bytes of shared memory"

error at a particular INSERT statement. So everytime we had this error, we had to flush the shared pool area, after which it would run without issues(for 3-4 weeks).

It had a dynamic subpartition clause framed, which made it to repharse the query every time.
So to reduce this, we removed the subpartition clause(made it to static query), which did benefit of not getting shared pool error.

I would like to find the current shared pool memory usage of my program.
Is there a way I could find the live consumption of shared pool area of my program(pkg).

Regards
Srivaths
Re: Find current shared pool usage of my program [message #573634 is a reply to message #573633] Tue, 01 January 2013 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way I could find the live consumption of shared pool area of my program(pkg).
no, but in some sense does not really matter.
The SHARED GLOBAL AREA is appropriately named.
The SQL within the SGA is literally "shared" & not owned by any single schema.

>we had to flush the shared pool area,
BAD approach to eliminate the symptom.
Re: Find current shared pool usage of my program [message #573636 is a reply to message #573634] Tue, 01 January 2013 09:20 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

So in my case, how would I prove statistically that "Yes, my program has reduced the consumption of shared pool memory area"?
Re: Find current shared pool usage of my program [message #573638 is a reply to message #573636] Tue, 01 January 2013 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your case, and anyone(s, it is important to post the COMPLETE error meesage to know the origin of the problem.
Hiding information does not help to help you.

Regards
Michel
Re: Find current shared pool usage of my program [message #573639 is a reply to message #573638] Tue, 01 January 2013 09:57 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Sure, the error message we get is

Error in XXX_PROGRAM ORA-04031: unable to allocate 4120 bytes of shared memory 
("shared pool","TABLE_NAME","PRTMV^31918d6f","Allocate kctph[]/ckyph[]/ckyprt[] array")

[Updated on: Tue, 01 January 2013 10:06] by Moderator

Report message to a moderator

Re: Find current shared pool usage of my program [message #573641 is a reply to message #573639] Tue, 01 January 2013 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most likely bug 9508254 (or 11879563 if you have have some ORA-07445 in alert.log) (even if you didn't post your oracle version with 4 decimals as requested).

Regards
Michel
Re: Find current shared pool usage of my program [message #573649 is a reply to message #573641] Tue, 01 January 2013 22:36 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Thanks Michel, will check the alert.log.

Oracle Version : 11.2.0.2 (64-bit)
Re: Find current shared pool usage of my program [message #573657 is a reply to message #573649] Wed, 02 January 2013 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The (best) solution is to upgrade to 11.2.0.3.

Regards
Michel
Re: Find current shared pool usage of my program [message #574912 is a reply to message #573633] Wed, 16 January 2013 13:40 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
We have been reducing the shared_pool_size by finding sqls that are missing bind variables with the following
ENWEBP1P > @V$SQLAREA_MEMORY.sql

PARSE_CALLS   COUNT(*)    MEG SUBSTR(SQL_TEXT,1,40)
----------- ---------- ------ ----------------------------------------

        168        167      2 select FLAGS from SYS_FBA_TRACKEDTABLES
        153        153      3 select /*+ FIRST_ROWS(1) PARALLEL("WRH$_
       4614        217      4 select count(FA#) from SYS_FBA_TRACKEDTA
        120        114      5 SELECT COUNT(*) FROM (SELECT * FROM DBA_
        132        132      6 create global temporary table sys.ora_te
        399        399      8 SELECT count(*) FROM sys.wri$_adv_execut
        248        113      9 WITH a as (SELECT a$.dbid, a$.snap_id, a
        480        201      9 select min(minbkt),maxbkt,substrb(dump(m
        238        126      9  SELECT  count(*) as cnt ,         a.SQL
        131        124     11 INSERT INTO wri$_adv_inst_fdg (TASK_ID,
       1773        118     12 select * from ( select distinct content0
       2759        214     21 select distinct person0_.esb_id as esb1_
       3608       1504     45 select substrb(dump(val,16,0,32),1,120)
       1131        356     50 /* SQL Analyze(1) */ select /*+  full(t)
     376781        343     53 select playerweek0_.esb_id as esb1_109_,
    2977750        565     53 select ecmrelatio0_.game_id as col_0_0_
     568453        200     56 select * from ( select playerweek0_.esb_
     376457        104     85 select gameschedu2_.game_id as col_0_0_
      78008        373     93 select workoutres0_.combine_person as co
    4012851        315    101 select personposi0_.esb_id as esb1_1_, p
     114364        383    146 select persons0_.video_person as video1_
     128135        213    178 select * from ( select row_.*, rownum ro
    1864120        430    194 select video0_.id as id134_, video0_.adm
     108432       3536    227   declare       cost sys.ODCICost := sys
      28272        122    348 select teamgamest0_.game_id as game1_110
    4805951        557    425 select personteam0_.esb_id as esb1_1_, p
     135913       1078    526 select * from ( select this_.workout_res
    3106518        112    657 select * from ( select this_.esb_id as e
    2118777        302    687 select * from ( select video0_.id as id1
      21654        178    769 select persongame0_.esb_id as esb1_106_,
     218531        147    938 select teamstat0_.role as role112_, team
     456030        290   1303 /*  select new map(max(pts.passingAttemp
   49356580       2125   3241 /* dynamic native SQL query */ select nv
     537272        415   3603 select personteam0_.esb_id as esb1_108_,
                       ------
sum                     13879

34 rows selected.

ENWEBP1P > list
  1  select sum(parse_calls) parse_calls,count(*),
  2  sum(SHARABLE_MEM)/1024/1024 meg,substr(sql_text,1,40)
  3  from gv$sqlarea
  4  group by substr(sql_text,1,40)
  5  having sum(SHARABLE_MEM)/1024/1024>1 and count(*)>100
  6* order by meg
Re: Find current shared pool usage of my program [message #574919 is a reply to message #574912] Wed, 16 January 2013 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A query with no explanation has a value of NULL.

Regards
Michel
Re: Find current shared pool usage of my program [message #574923 is a reply to message #574919] Wed, 16 January 2013 14:09 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
My query identifies the sqls that hog the most shared_pool memory and that cause the shared_pool memory issues. Only by adding bind variables to sqls that are hogging the most shared_pool memory can the problem be fixed. The sql that I wrote identified the biggest memory hogs because it is sorted by MEG.

[Updated on: Wed, 16 January 2013 14:13]

Report message to a moderator

Previous Topic: SQL for generating roles
Next Topic: System tablespace resize
Goto Forum:
  


Current Time: Thu Mar 28 07:04:04 CDT 2024