| Find current shared pool usage of my program [message #573633] |
Tue, 01 January 2013 08:09  |
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 #574912 is a reply to message #573633] |
Wed, 16 January 2013 13:40  |
 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
