Forum Stats

  • 3,728,688 Users
  • 2,245,675 Discussions
  • 7,853,705 Comments

Discussions

shared pool memory

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

Comments

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,187 Red Diamond

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

  • Prabhakar K
    Prabhakar K Member Posts: 202 Blue Ribbon

    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
    Prabhakar K Member Posts: 202 Blue Ribbon

    Any update on this ?

    BR

    Frd

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,187 Red Diamond
    edited October 2020

    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
    Prabhakar K Member Posts: 202 Blue Ribbon

    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
    Billy Verreynne Member Posts: 28,187 Red Diamond

    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
  • Prabhakar K
    Prabhakar K Member Posts: 202 Blue Ribbon

    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

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

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

                        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

                        DEFAULT buffer cache          shrink       error                 272             256             -16             256

                        DEFAULT buffer cache          shrink       error                 272             256             -16             256

                        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
    Billy Verreynne Member Posts: 28,187 Red Diamond

    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
  • Prabhakar K
    Prabhakar K Member Posts: 202 Blue Ribbon

    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
    Billy Verreynne Member Posts: 28,187 Red Diamond

    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.

Sign In or Register to comment.