shared pool memory

Prabhakar K · Oct 6, 2020 3:03PM

Hi

I am getting this error every day ..

Errors in file /opt/oracle/admin/QTFEEDB/bdump/diag/rdbms/qtfeedb/QTFEEDB/trace/QTFEEDB_dm00_20139.trc (incident=21331):

ORA-04031: unable to allocate 81160 bytes of shared memory ("shared pool","KUPM$MCP","PLMCD^86cf633e","BAMIMA: Bam Buffer")

ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPM$MCP"

Every day I am commented ORA-Errors as .. my name initials in the log file . then flushing the shared pool memory ..

SQL >alter system flush shared_pool;

for that day .. it won't repeat this error . again next day .. alerting ..

Shared pool memory , we have 2G. Senior DBAs saying that .. flushing shared pool memory is not a solution .. and don't increase the shared pool memory.

so I am bit confused here .. Please give me the solution .. it is very nice of you .. Adv. Thanks.

BR

Frd

Billy Verreynne · Oct 6, 2020 5:59PM

Please provide technical details. Operating system version. Database version. Size of the SGA, and how it is configured.

Prabhakar K · Oct 6, 2020 6:07PM

Hi Billy,

OS : SunOS 5.11

DB version: 12.1.0.1

shared_pool_size big integer 2G

sga_max_size big integer 2624M

Hope these details are enough .. if not please .. feel free

BR

Frd

Prabhakar K · Oct 7, 2020 2:09AM

Any update on this ?

BR

Frd

Billy Verreynne · Oct 7, 2020 5:05AM

This is a public forum, and not staffed with Oracle support employees. Forum members are from all kinds of time zones that are different from yours. So you need to be patient here - or consider instead logging a SR (Service Request) with Oracle Support.

Seems like a DataPump proccess triggered the error. Is it always this process that triggers the error?

On the SGA side, we need to know how it is configured - automated memory management, manual memory management, automated memory management with fixed min pool sizes, etc. See [Managing Memory].

What are the values of settings MEMORY_TARGET, MAX_MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET?

What does the following SQL show?: select * from v$sga_resize_ops

Are bind variables used by Oracle client applications for executing SQLs? This is critical for performance and effective use of the Shared Pool.

Prabhakar K · Oct 7, 2020 5:26AM

Hi Billy,

select * from v$sga_resize_ops;

COMPONENT OPER_TYPE

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

OPER_MODE

---------

PARAMETER

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

INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIM END_TIME CON_ID

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

shared pool GROW

MANUAL

shared_pool_size

2147483648 4294967296 2147483648 ERROR 22-SEP-20 06-OCT-20 0

800 rows selected.

I got 800 records . Last report I am posting here.

SQL> show parameter MEMORY_TARGET;

NAME TYPE VALUE

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

memory_target big integer 0

SQL> show parameter SGA_TARGET;

NAME TYPE VALUE

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

sga_target big integer 0

SQL> show parameter PGA_AGGREGATE_TARGET;

NAME TYPE VALUE

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

pga_aggregate_target big integer 528M

Hope these details are Ok ..

BR

Frd

Billy Verreynne · Oct 7, 2020 6:48AM

Can you run this instead:

col "Final Size"       format 999,999,999,999 
col "Target Size"      format 999,999,999,999 
col "Initial Size"     format 999,999,999,999 
col "Resize Amount"    format 999,999,999,999 
col "Component"        format a30 
col "Started"          format a20 
col "Completed"        format a20 
col "Label"            format a30 
col "Parameter"        format a50 
 
break on Completed 
 
select 
       to_char(end_time ,'mm/dd Dy hh24:mi:ss')       as "Completed", 
       component                                      as "Component", 
       lower(oper_type)                               as "Operation", 
       lower(status)                                  as "Status", 
       round(initial_size/1024/1024,2)                as "Initial Size", 
       round(target_size/1024/1024,2)                 as "Target Size", 
       round((target_size-initial_size)/1024/1024,2)  as "Resize Amount", 
       round(final_size/1024/1024,2)                  as "Final Size" 
from   v$sga_resize_ops 
order by 
       end_time desc, 
       start_time desc 
fetch first 10 rows only 
/ 
 
select 
       name                           as "Label", 
       round(value/1024/1024,2)       as MB 
from v$sga; 
 
select 
       'Dynamic Components:'                                  as "Label", 
       to_char( sum(current_size)/1024/1024, '999,990.0')     as "Current", 
       to_char( sum(min_size)/1024/1024, '999,990.0')         as "Min Size", 
       to_char( sum(max_size)/1024/1024, '999,990.0')         as "Max Size" 
from v$sga_dynamic_components 
union all 
select 
       ' '||component, 
       to_char( current_size/1024/1024, '999,990.0'), 
       to_char( min_size/1024/1024, '999,990.0'), 
       to_char( max_size/1024/1024, '999,990.0') 
from v$sga_dynamic_components 
/ 
 
select 
       name||'='||value       as "Parameter" 
from   v$parameter 
where  name like '%sga%' 
or     name like '%pga%' 
or     name like '%memory%' 
order by 1 
/

And when pasting the results, select the paragraph symbol on the left side of the editor and format the copy-and-pasted block from sqlplus as code or quote. e.g. clicking on the symbol:

Prabhakar K · Oct 7, 2020 7:01AM

Completed Component Operation Status Initial Size Target Size Resize Amount Final Size

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

10/06 Tue 16:25:05 shared pool grow error 2,048 4,096 2,048 2,048

shared pool grow error 2,048 4,096 2,048 2,048

DEFAULT buffer cache shrink error 272 256 -16 256

10 rows selected

2nd Query

Label Current Min Size Max Size

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

Dynamic Components: 2,592.0 2,336.0 2,848.0

shared pool 2,048.0 2,032.0 2,048.0

large pool 0.0 0.0 0.0

java pool 48.0 48.0 48.0

streams pool 208.0 0.0 208.0

DEFAULT buffer cache 256.0 256.0 512.0

KEEP buffer cache 0.0 0.0 0.0

RECYCLE buffer cache 0.0 0.0 0.0

DEFAULT 2K buffer cache 0.0 0.0 0.0

DEFAULT 4K buffer cache 0.0 0.0 0.0

DEFAULT 8K buffer cache 0.0 0.0 0.0

DEFAULT 16K buffer cache 0.0 0.0 0.0

DEFAULT 32K buffer cache 0.0 0.0 0.0

Shared IO Pool 32.0 0.0 32.0

Data Transfer Cache 0.0 0.0 0.0

ASM Buffer Cache 0.0 0.0 0.0

16 rows selected.

3. query.

Parameter

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

hi_shared_memory_address=0

lock_sga=FALSE

memory_max_target=0

memory_target=0

pga_aggregate_limit=2147483648

pga_aggregate_target=553648128

pre_page_sga=FALSE

sga_max_size=2751463424

sga_target=0

shared_memory_address=0

unified_audit_sga_queue_size=1048576

11 rows selected.

BR

Frd

Billy Verreynne · Oct 7, 2020 7:49AM

Not much you can do ito resizing the SGA for more effective management of the various SGA pools. The Shared Pool is the maximum it can be, given the SGA size, and Oracle wanting to double the Shared Pool to 4G to meet demands.

The amount of SGA assigned, is also quite low. Is this a 32bit o/s? (never used Solaris on my servers)

You can refer to support note "Gathering Initial Troubleshooting Information for Analysis of ORA-4031 Errors on the Shared Pool (Doc ID 1674929.1)".

You can look at the Shared Pool and its contents for issues such as SQLs not using bind variables, very large SQL cursors, and so on. But these issues need to be addressed on the client app code side. From a DBA side, there is only The Large Hammer by flushing the Shared Pool often, in an attempt to make free Shared Pool memory available - and hopefully mostly as unfragmented free memory.

The real solution is to allocate more server memory to the SGA (and server if need be).

Prabhakar K · Oct 7, 2020 8:06AM

Thanks for your advises .. why can't you think about the below paramters list .. can't we do any thing .. here.

ORA-04031: unable to allocate 81160 bytes of shared memory ("shared pool","KUPM$MCP","PLMCD^86cf633e","BAMIMA: Bam Buffer")

ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPM$MCP"

KUPM$MCP -- this is one system package .. totally encrypted pkg. Hope this package is using so much of shared pool memory?

ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPM$MCP" -- here saying that .. could not find program unit. But it is there in my system ? I explained in above line.

Just for curiosity .. I am asking .

BR

Frd

Billy Verreynne · Oct 7, 2020 8:26PM

That is a C module reference in the db kernel. As far as I know it is used by Database Pump. Dbpump is a server based API that you can call from an external client such as expdp and impdp, and via PL/SQL.

This C module failed. Part of it's failure is a call reference. This was likely a call made via the SQL engine, that could not find memory to parse the cursor for that call. Yes, Oracle internally also uses its own SQL engine.

In other words, it seems like the memory issue was encountered in system code and not user code.

This however does not change the fact that the Shared Pool is too small for dealing with the demands made on it.

Forum Stats

Quick Links

Categories

shared pool memory

Comments

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

shared pool memory

Comments