set pages 1000 lines 120
col name for a60
col value for a30
spool diagnosis1.lst
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
SELECT substr(sql_text,1,90) "SQL",count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5
GROUP BY substr(sql_text,1,90) HAVING count(*) > 30 ORDER BY 2;
select * from v$sgastat where pool like 'shared%' order by bytes;
select sa.sql_text,sa.version_count ,ss.* from v$sqlarea sa,v$sql_shared_cursor ss where sa.address=ss.ADDRESS and
sa.version_count > 50 order by sa.version_count ;
select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and
nam.ksppinm like '%shared%' order by 1;
col free_space for 999,999,999,999 head "TOTAL FREE"
col avg_free_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE"
col free_count for 999,999,999,999 head "COUNT"
col reqeust_misses for 999,999,999,999 head "REQUEST|MISSES"
col reqeust_failures for 999,999,999,999 head "REQUEST|FAILURES"
col max_free_size for 999,999,999,999 head "LARGEST CHUNK"
select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures,LAST_FAILURE_SIZE from v$shared_pool_reserved
col Parameter format a25
col "Session Value" format a15
col "Instance Value" format a15
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and a.ksppinm in
('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size');
set pagesize 100
select * from v$sga_dynamic_components;
col requests for 999,999,999
col last_failure_size for 999,999,999 head "LAST FAILURE| SIZE "
col last_miss_size for 999,999,999 head "LAST MISS|SIZE "
col pct for 999 head "HIT|% "
col request_failures for 999,999,999,999 head "FAILURES"
select requests,
decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_size
from v$shared_pool_reserved;
select p.inst_id, p.free_space, p.avg_free_size, p.free_count,
p.max_free_size, p.used_space, p.avg_used_size, p.used_count, p.max_used_size,
s.requests, s.request_misses, s.last_miss_size, s.max_miss_size,
s.request_failures, s.last_failure_size, s.aborted_request_threshold,
s.aborted_requests, s.last_aborted_size
from (select avg(x$ksmspr.inst_id) inst_id,
sum(decode(ksmchcls,'R-free',ksmchsiz,0)) free_space,
avg(decode(ksmchcls,'R-free',ksmchsiz,0)) avg_free_size,
sum(decode(ksmchcls,'R-free',1,0)) free_count,
max(decode(ksmchcls,'R-free',ksmchsiz,0)) max_free_size,
sum(decode(ksmchcls,'R-free',0,ksmchsiz)) used_space,
avg(decode(ksmchcls,'R-free',0,ksmchsiz)) avg_used_size,
sum(decode(ksmchcls,'R-free',0,1)) used_count,
max(decode(ksmchcls,'R-free',0,ksmchsiz)) max_used_size from x$ksmspr
where ksmchcom not like '%reserved sto%') p,
(select sum(kghlurcn) requests, sum(kghlurmi) request_misses,
max(kghlurmz) last_miss_size, max(kghlurmx) max_miss_size,
sum(kghlunfu) request_failures, max(kghlunfs) last_failure_size,
max(kghlumxa) aborted_request_threshold, sum(kghlumer) aborted_requests,
max(kghlumes) last_aborted_size from x$kghlu) s;
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS;
set pagesize 80
set verify off
set heading off
set feedback off
set termout off
col sp_size format 999,999,999 justify right
col x_sp_used format 999,999,999 justify right
col sp_avail format 999,999,999 justify right
col sp_sz_pins format 999,999,999 justify right
col sp_no_pins format 999,999 justify right
col sp_no_obj format 999,999 justify right
col sp_sz_obj format 999,999 justify right
col sp_no_stmts format 999,999 justify right
col sp_sz_kept_chks format 999,999,999 justify right
col sp_no_kept_chks format 999,999 justify right
col val2 new_val x_sp_size noprint
select value val2
from v$parameter
where name='shared_pool_size'
/
col val2 new_val x_sp_used noprint
col val3 new_val x_sp_no_stmts noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val2, count(*) val3
from v$sqlarea
/
col val2 new_val x_sp_no_obj noprint
col val3 new_val x_sp_sz_obj noprint
select decode(count(*),'',0,count(*)) val2,
decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3
from v$db_object_cache
where type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')
/
col val2 new_val x_sp_avail noprint
select sum(ksmchsiz) val2 from x$ksmsp where ksmchcls = 'free'
/
col val2 new_val x_sp_no_kept_chks noprint
col val3 new_val x_sp_sz_kept_chks noprint
select decode(count(*),'',0,count(*)) val2,
decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3
from v$db_object_cache
where kept='YES'
/
col val2 new_val x_sp_no_pins noprint
select count(*) val2
from v$session a, v$sqltext b
where a.sql_address||a.sql_hash_value = b.address||b.hash_value
/
col val2 new_val x_sp_sz_pins noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val2
from v$session a,
v$sqltext b,
v$sqlarea c
where a.sql_address||a.sql_hash_value = b.address||b.hash_value and
b.address||b.hash_value = c.address||c.hash_value
/
set termout on
set heading off
ttitle -
center 'Shared Pool''s Library Cache Information' skip 2
select 'Size : '
||&x_sp_size sp_size,
'Number of shared cursors : '
||&x_sp_no_stmts sp_no_stmts,
'Used by shared cursors : '
||&x_sp_used,
'Number of programmatic constructs : '
||&x_sp_no_obj sp_no_obj,
'Used by programmatic constructs : '
||&x_sp_sz_obj sp_sz_obj,
'Available : '
||&x_sp_avail sp_avail,
'Kept object chunks : '
||&x_sp_no_kept_chks sp_no_kept_chks,
'Kept object chunks size : '
||&x_sp_sz_kept_chks sp_sz_kept_chks,
'Pinned statements : '
||&x_sp_no_pins sp_no_pins,
'Pinned statements size : '
||&x_sp_sz_pins sp_sz_pins
from dual
/
ttitle off
set heading on
set feedback on
select '0 (<140)' BUCKET,
KSMCHCLS,
KSMCHIDX,
10 * trunc(KSMCHSIZ / 10) "From",
count(*) "Count",
max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize",
trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ < 140
and KSMCHCLS = 'free'
group by KSMCHCLS, KSMCHIDX, 10 * trunc(KSMCHSIZ / 10)
UNION ALL
select '1 (140-267)' BUCKET,
KSMCHCLS,
KSMCHIDX,
20 * trunc(KSMCHSIZ / 20),
count(*),
max(KSMCHSIZ),
trunc(avg(KSMCHSIZ)) "AvgSize",
trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS = 'free'
group by KSMCHCLS, KSMCHIDX, 20 * trunc(KSMCHSIZ / 20)
UNION ALL
select '2 (268-523)' BUCKET,
KSMCHCLS,
KSMCHIDX,
50 * trunc(KSMCHSIZ / 50),
count(*),
max(KSMCHSIZ),
trunc(avg(KSMCHSIZ)) "AvgSize",
trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 268 and 523
and KSMCHCLS = 'free'
group by KSMCHCLS, KSMCHIDX, 50 * trunc(KSMCHSIZ / 50)
UNION ALL
select '3-5 (524-4107)' BUCKET,
KSMCHCLS,
KSMCHIDX,
500 * trunc(KSMCHSIZ / 500),
count(*),
max(KSMCHSIZ),
trunc(avg(KSMCHSIZ)) "AvgSize",
trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107
and KSMCHCLS = 'free'
group by KSMCHCLS, KSMCHIDX, 500 * trunc(KSMCHSIZ / 500)
UNION ALL
select '6+ (4108+)' BUCKET,
KSMCHCLS,
KSMCHIDX,
1000 * trunc(KSMCHSIZ / 1000),
count(*),
max(KSMCHSIZ),
trunc(avg(KSMCHSIZ)) "AvgSize",
trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ >= 4108
and KSMCHCLS = 'free'
group by KSMCHCLS, KSMCHIDX, 1000 * trunc(KSMCHSIZ / 1000)
/
SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded
FROM (SELECT ksmlrcom alloc_type,
ksmlrsiz alloc_size,
ksmlrnum num_objs_flushed,
ksmlrhon object_loaded,
RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking
FROM x$ksmlru
WHERE inst_id = USERENV('INSTANCE')
AND ksmlrsiz > 0)
WHERE order_ranking <= 100
ORDER BY order_ranking
/
SELECT sql_text, rawtohex(address), hash_value
FROM (SELECT sql_text,
address,
hash_value,
RANK() OVER(ORDER BY rownum) AS order_ranking
FROM v$sqlarea
WHERE command_type = 47
AND LENGTH(sql_text) > 400)
WHERE order_ranking <= 100
ORDER BY order_ranking
/
SELECT NAME, TYPE, sharable_mem, loads, executions, kept
FROM (SELECT name,
o.type,
sharable_mem,
loads,
executions,
kept,
RANK() OVER(order by sharable_mem desc) AS order_ranking
FROM v$db_object_cache o
WHERE loads > 0
AND o.type LIKE 'JAVA%')
WHERE order_ranking <= 100
ORDER BY order_ranking
/
SELECT owner, NAME, TYPE, sharable_mem, loads, executions, kept
FROM (SELECT owner,
name,
o.type,
sharable_mem,
loads,
executions,
kept,
RANK() OVER(ORDER BY sharable_mem desc) AS order_ranking
FROM v$db_object_cache o
WHERE loads > 0
AND o.type in
('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE'))
WHERE order_ranking <= 100
order by order_ranking
/
SELECT NAME, TYPE, sharable_mem, loads, executions, kept
FROM ( SELECT o.name, o.type, o.sharable_mem,
o.loads, o.executions, o.kept,
RANK() OVER (ORDER BY sharable_mem desc) AS order_ranking
FROM v$db_object_cache o
WHERE o.executions > 0
AND o.type = 'CURSOR'
)
WHERE order_ranking <= 100
ORDER BY order_ranking
/
© 2007 - 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
1) by checking the share pool usage
Shared Pool Usage
===========================================================
R-free - Reserved List
R-freea - Reserved List
free - Free Memory
freeabl - Memory for user / system processing
perm - Memory allocated to the system
recr - Memory for user / system processing
what exactly perm is referring to ? is there any indication for showing the healthiness of the share pool by referring to this statistics ?
we found that , every time there was a ora-4031 occur, the value of perm with a high value.
2) doc 146599.1, is it useful to to monitor X$KSMSP View , is it Similar to Heapdump Information ?
3) doc 146599.1, is it Useful to track allocations in the shared pool that cause other objects in the shared pool to be aged?
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
4)Is REQUEST_MISSES valid when init para SHARED_POOL_RESERVED_MIN_ALLOC not set?
1.) PERM allocations refer to PERMANENT allocations. Permanent allocations are never freed. They live till lifetime of instance. That is why those allocations keep on growing. For 10.2.0.4 are you using Automatic Shared Memory Management ?
2.) It is similar to heapdump information but If you are using Automatic shared_memory management it may not give exact information.
3.) It is useful in case you are seeing lots of invalidations/reloads. Query v$librarycache to get that information.
4.) By default, _shared_pool_reserved_min_alloc is set to 4400 bytes. So, request misses are valid in that case as well.
Please note that frequently querying x$ views is not recommended. Queries on these views are lock intensive and may impact performance.
a.) Permanent allocations cannot be flushed by using ‘alter system flush shared_pool’ command. Permanent allocations get released only after instance restart. There is no specified ratio/percentage of permanent chunks in the shared_pool space.
b.) The first point to start is
1. Monitor v$sgastat. Check for any component that is growing abnormally. For example, if ‘KGH: NO ACCESS’ component has grown abnormally, you can check in metalink for known bugs. In case the ‘SQL area’ is growing abnormally, it may be a cause of concern as application may not be bind variables. Then taking heapdump may reveal if that is cause of fragmentation.
2. Querying v$librarycache is of help to understand the HIT ratio, reloads and invalidations that have happened since instance startup.
3. In severe cases or for diagnostics, heapdumps may be taken along with errorstack.
‘sql area’ is a component that can be seen as a part of output from v$sgastat.
Suppose we have shared_pool sized to 500M and as the time passes. As the time progresses, in case you see this component growing to 200-250M, it is likely that application is not using bind variables. To check if application is using bind variables or not, you can gather AWR reports and check sections ‘sql ordered by version count’ This section would give SQLs that didn’t get shared due to some reasons. After that SR can be raised for more understanding.
In case ORA-04031 error gets reported, you can refer the following document and upload the information to the SR for analysis.
Article-ID: Note 430473.1
Title: ORA-4031 Common Analysis/Diagnostic Scripts
1.) Library cache overview gives the following:
*** High level breakdown of memory ***
sharable : 39.93M
persistent : 32.11M
runtime : 30.27M
SQL Memory Usage (total) : 102.31M (80%)
Shareable memory refers to memory that can be shared. Example: if an sql is using bind variables, it is likely that the parse tree for that statement would be shared with next session executing the same sql.
Persistent memory is something similar to Permanent memory that would remain till lifetime.
Execution/runtime memory refers to the memory that is required during execution of a cursor/sql
There should be less number of SQLs that are executed just once. Because, if sql’s are not shared, each SQL would be having its own execution plan and would get executed independently… in other words, multiple unshareable statements just one execution.
On the other hand, if SQLs are shared, there would be one statement with multiple executions.
2.) There should be less misses in the library cache. The more the hit percentage, more is the probability that a shareable sql is found in library cache. In case, we are not able to find the misses in library cache would increase and we would require reload of parse tree in the library cache.
3.) Resize operations are done internally by oracle when using ASMM using feedback mechanisms from advisories. In case there are lots of resize operations and there are lots of shrinks and growth of components happenning in the SGA, you may need to set the values of those components to some value and as such those components would not shrink below the specified value.
4.) Versions HWM: Gives The highest number of children that are present in the cache under a parent for an SQL statement, from the time when instance was last started.
I believe that you are trying to set these warnings and alert using EM.
Anyways, there is no rule of considering the permanent memory growth as Threshold and critical.
You can set 60% as threshold and 75% as critical.
Please note that 200M is very low for a production database to work. With 200M of shared_pool size, It is highly likely to receive ORA-04031 in this case.
Minimum size of shared_pool for 10g database is recommended as 500M based on empirical analysis. In case the permanent chunks are increasing abnormally, we would consider taking a heapdump incase the problem is occurring again and again.
When Automatic Shared Memory is in use, we have a concept of shared pool durations. In memory there is different lifetime for every memory data structure. Some data structures (memory chunks) remain till lifetime of instance (instance duration or permanent chunks) and some live just till the time session is there (session duration) and so forth. With ASMM enabled, permanent chunks don’t scan LRU list to get memory. They directly contact granule manager for more memory. In case if granule manager is not able to release that memory, ORA-04031 gets reported.
In such cases, we would consider disabling ASMM (setting sga_target to zero and manually setting values of db_cache_size, shared_pool_size).
In case you have disabled ASMM and still seeing abnormal growth of permanent chunks, we may have to take heapdump for analysis and check in case it is a memory leak/bug. However, to alleviate the problem, DB restart is the only option because, permanent chunks would not get released using ‘alter system flush shared_pool’ command.
Regarding RAC and shared_pool_size:
RAC specific memory is allocated at the time of SGA creation, mostly in the shared pool. However, the memory does not count in the SHARED_POOL_SIZE as defined in the init.ora parameter file. Therefore, when migrating Oracle from single instance to RAC, you do not need to adjust the SHARED_POOL_SIZE parameter to accommodate the additional memory that RAC uses; Oracle does that automatically. Note that the memory for the KCL global cache lock elements will be allocated in the buffer cache, and not in the shared pool.
The note: 455179.1 clearly talks about how to calculate number of subpools allocated at startup. What is your question ?
Shared_pool reserved is for large requests greater than or equal to 4400 bytes. So, any request that wants shared pool space of 4400 bytes or more would search normal shared_pool space. In case it is not able to find the same sized chunk, only then it would go and scan the reserved pool for free chunks. So, only in case of large requests, shared_pool_reserved would come into picture.
In the view v$shared_pool_reserved, we generally look at “REQUEST_FAILURES” to check ‘Number of times that no memory was found to satisfy a request.’ In case this keeps on increasing, it may be a signal of impending ORA-04031 error.
It is the same question that you asked earlier as well.
Yes, the number of misses and number of objects executed once has to be monitored.
How much is the threshold value for that is something that we cannot easily suggest. Ideally, misses should be zero. That means, whatever we are trying to find is already present in the library cache. As suggested earlier in the output of ‘LibCacheOverview.sql’ , Ideal percentages for 1 time executions is 20% or lower.
The REQUEST_MISSES you are referring to the view from v$shared_pool_reserved. That is within the reserved area. You may be seeing lots of misses in the normal shared_pool area. Query v$librarycache to get the number of misses in library cache. And apart from Misses there are lots of other reasons to receive ORA-04031 error.
There are lots of queries that may have different version counts. Suppose:
SQL> select * from emp, may have 4 different children.
Another SQL may be having version count of 12. So, if 12 is the highest version count for the instance, Version HWM would report the highest version count for a sql in instance.
Largest memory object refers to the max. size of object that was loaded in shared pool. In your case it is 408,084 bytes.
REQUEST_MISSES is related to RESERVED POOL. Reserved pool is meant for large allocations like PL/SQL compilations or trigger compilation. Only requests that are larger than 4400 bytes can go and check for free space in reserved pool.
Please note that allocations below 4400 bytes would not go to reserved area. You may get ORA-04031 error while requesting for 64 bytes.
So, in this case REQUEST_MISSES parameter would not mean anything. It may be zero or any positive number. This is because a 64 bytes request would never go and check RESERVED POOL.
got BUG 9578670 – ORA-4031 WHILE DROP PARTITIONS so we need a patch for the bug
Bug 9578670: ORA-4031 WHILE DROP PARTITIONS, SOLUTION FROM DOC ID: 419551.1 DIDN’T HELP
Product Version 11.1.0.7
Fixed in Product Version 12.1
Fix By 11.2.0.3
No backport available for 11.2.0.2
REDISCOVERY INFORMATION:
If you are running frequent partition maintenance operations, have multiple
subpools in the shared pool, and encounter ORA-4031 out of memory errors due
to many “mvobj part des” allocations, you could be hitting this issue.
WORKAROUND:
Set “_kghdsidx_count”=1.
Alert file
———-
Wed Feb 16 04:26:22 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_dw00_13679.trc (incident=36418):
ORA-04031: unable to allocate 4128 bytes of shared memory (“shared pool”,”UPDATE_CREATE”,”PRTMV^3a844b88″,”kkpomSort hashed kpn’s”)
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36418/jasper_dw00_13679_i36418.trc
Wed Feb 16 04:26:44 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_ora_31713.trc (incident=36578):
ORA-04031: unable to allocate 32 bytes of shared memory (“shared pool”,”unknown object”,”KGLH0^edb30de5″,”kglHeapInitialize:temp”)
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36578/jasper_ora_31713_i36578.trc
Wed Feb 16 04:26:47 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_dw0e_6924.trc (incident=36466):
ORA-04031: unable to allocate 32 bytes of shared memory (“shared pool”,”unknown object”,”KGLH0^56d22d85″,”kglHeapInitialize:temp”)
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36466/jasper_dw0e_6924_i36466.trc
Wed Feb 16 04:26:47 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_cjq0_9684.trc (incident=36362):
ORA-04031: unable to allocate 32 bytes of shared memory (“shared pool”,”unknown object”,”KGLH0^bd2af6d6″,”kglHeapInitialize:temp”)
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36362/jasper_cjq0_9684_i36362.trc
Wed Feb 16 04:26:47 2011
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_dw01_3033.trc (incident=36666):
ORA-04031: unable to allocate 3952 bytes of shared memory (“shared pool”,”SELECT “,”pacdHds_kkpaco”,”kggec.c.kggfa”)
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir_36666/jasper_dw01_3033_i36666.trc
Wed Feb 16 04:26:47 2011
Dumping diagnostic data in directory=[cdmp_20110216042647], requested by (instance=1, osid=31713), summary=[incident=36578].
…
Trace files
———–
Not provided
Incident trace files
——————–
jasper_dw01_3033_i36666.trc
*** ACTION NAME:(SYS_IMPORT_TABLE_03) 2011-02-16 04:26:47.535
Dump continued from file: /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper_dw01_3033.trc
ORA-04031: unable to allocate 3952 bytes of shared memory (“shared pool”,”SELECT “,”pacdHds_kkpaco”,”kggec.c.kggfa”)
========= Dump for incident 36666 (ORA 4031) ========
*** 2011-02-16 04:26:47.535
dbkedDefDump(): Starting incident default dumps (flags=0×2, level=3, mask=0×0)
—– Current SQL Statement for this session (sql_id=4ujz53z0x60rn) —–
INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL(“GDR”.”AVG_ARH” NOT XMLTYPE) PARTITION (“D110120_22″) (“TIMESTAMP”,”TEID”,”DST_IP”,”DST_PORT”,”SRC_IP”,”SRC_PORT”,”BYTES”) VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL)
—– PL/SQL Stack —–
—– PL/SQL Call Stack —–
0x3614e5dd0 58 package body SYS.KUPD$DATA_INT
0x361a817d0 3443 package body SYS.KUPD$DATA
0x3647ec9f0 14258 package body SYS.KUPW$WORKER
0x3647ec9f0 4535 package body SYS.KUPW$WORKER
0x3647ec9f0 9096 package body SYS.KUPW$WORKER
0x3647ec9f0 1688 package body SYS.KUPW$WORKER
—> HEAP DUMP heap name=”SQLA^c1d302f4″ desc=0x75dfc6b0
Type Count Sum Average
~~~~ ~~~~~ ~~~ ~~~~~~~
freeable 30 47528 1584.27
recreate 1 4056 4056.00
perm 1 80 80.00
free 1 1040 1040.00
BreakDown
~~~~~~~~~
Type Count Sum Average Percent
~~~~ ~~~~~ ~~~ ~~~~~~~ ~~~~~~~
TCHK^c1d302f4 12 48672 4056.00 92.35
perm 1 80 80.00 0.15
Free(heap.awk) 1 1040 1040.00 1.97
…
Total = 52704 bytes 51.47k 0.05MB
Descriptor 36de851e8 was the largest with size 48672
This is 92.35 % of the total heap
ds=0x36de851e8 has size 44616 (% of heap=84.65)
Memory used by descriptors with more than 10240 bytes:
Total = 44616 (% of heap=84.65)
Total PERMANENT memory actually used is 80
Largest CONTIGUOUS free memory (NORMAL) was 1040 ( 1.02k)
Largest descriptor count was 12
—> HEAP DUMP heap name=”KGLH0^c1d302f4″ desc=0x381acb728
Type Count Sum Average
~~~~ ~~~~~ ~~~ ~~~~~~~
perm 2 2648 1324.00
free 1 776 776.00
freeable 4 608 152.00
BreakDown
~~~~~~~~~
Type Count Sum Average Percent
~~~~ ~~~~~ ~~~ ~~~~~~~ ~~~~~~~
perm 2 2648 1324.00 65.67
Free(heap.awk) 1 776 776.00 19.25
kgltbtab 4 608 152.00 15.08
Total = 4032 bytes 3.94k 0.00MB
Total PERMANENT memory actually used is 2464
Largest CONTIGUOUS free memory (NORMAL) was 776 ( 0.76k)
Free List Bucket Summary :
Bucket 0 [size=0 ] Count= 2 Av.Size= 388 Max= 776
Opatch
——
There are no Interim patches installed in this Oracle Home.
RDA
—-
db_cache_size 0
shared_pool_size 0
streams_pool_size 234881024
memory_target 13555990528
Minimum and Maximum Component Final Size
Component Lowest Low (MiB) Highest High (MiB)
DEFAULT buffer cache 1375731712 1312 2717908992 2592
java pool 67108864 64 67108864 64
large pool 33554432 32 67108864 64
shared pool 2382364672 2272 2919235584 2784
streams pool 234881024 224 301989888 288
Trace files
———–
Trace file jasper_dw01_3033.trc not provided.
jasper_dw01_3033_i36666.trc
—> HEAP DUMP heap name=”SQLA^c1d302f4″ desc=0x75dfc6b0
BreakDown
~~~~~~~~~
Type Count Sum Average Percent
~~~~ ~~~~~ ~~~ ~~~~~~~ ~~~~~~~
TCHK^c1d302f4 12 48672 4056.00 92.35
Free(heap.awk) 1 1040 1040.00 1.97
Total = 52704 bytes 51.47k 0.05MB
Descriptor 36de851e8 was the largest with size 48672
This is 92.35 % of the total heap
ds=0x36de851e8 has size 44616 (% of heap=84.65)
Memory used by descriptors with more than 10240 bytes:
Total = 44616 (% of heap=84.65)
Total PERMANENT memory actually used is 80
Largest CONTIGUOUS free memory (NORMAL) was 1040 ( 1.02k)
Largest descriptor count was 12
This does not include a complete heapdump.
alter system set events ‘4031 trace name heapdump level 536870914’;
reproduce the problem and provide the next generated trace files so that we can check the complete memory allocation when the problem is raised?