Thursday, January 14, 2021

ORA-04031: unable to allocate nn bytes of shared memory

You will receive ORA-04031 error when allocated memory is small too small and does not have enough system global area (SGA).

Recently I got below error...

ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS_INTERNAL"
< ORA-06512: at line 1
< ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer")

Please find the cause and action

ORA-04031: unable to allocate nn bytes of shared memory
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE. If the error is issued from an Oracle Streams or XStream process, increase the initialization parameter STREAMS_POOL_SIZE or increase the capture or apply parameter MAX_SGA_SIZE.


I will explain some of the possible root causes and how to fix it.

1. Insufficient RAM – This is most common issue when the database usage increased over the period. Increase the RAM and also adjust related SGA_MAX_TARGET or MEMORY_MAX_SIZE in case of AMM.

2. Shared pool fragmentation – This can be addressed by increasing the shared_pool_size in spfile or init.ora parameter. If you cannot bounce the database for changes to effect then I flush shared pool using “alter system flush shared pool;” command for temporary fix

3. Pinned packages – You will see this error if you have pinned lots of packages with dbms_shared_pool.keep and DB does not have enough resources

If your application is not using bind variables then it will cause SGA to become fragmented. In this case, if you turn on cursor_sharing it will help. Also make sure you use bind varibales. 


Also, run below queries to get SGA/PGA memory advice.
select * from v$sga_target_advice; select * from v$pga_target_advice;

Refer:
MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Regards,
http://oracleracexpert.com

12 comments:

  1. Agra Call Girl and Female Model Escort Services in Agra for your spicy erotic desires. Agra Escorts is the best Escorts Girls service in Agra free and paid sex incall and outcall independent agency. If you are interested please contact me for more information visit our website.

    ReplyDelete
  2. Wow that is really a great post (review) to share. I really do like your review post.
    LEARN FROM SCRATCH ONLINE BEGINNER MUSIC LESSON Online Music Lessons

    ReplyDelete
  3. Most popular Independent Escorts Service in Mahipalpur, We are providing best Mahipalpur Escorts, Call Girls, Female Independent Escorts low and high price available like a sexy, hot, beautiful, charm, etc. If you are interested please call me and for more information visit our website.
    Escorts In Mahipalpur

    ReplyDelete
  4. 3D Rendering Services | Quickgunz |3D Rendering company
    Quickgunz Pvt Ltd are engaged in offering our clients 3D Rendering Services.Our 3d rendering company provide architecture, simulators, video games, films and television visual effects and design visualisation hire us for pocket friendly prices
    3D Rendering Company
    3D Rendering services

    ReplyDelete
  5. Packers and Movers Bangalore - Reliable and Verified Household Shifting Service Providers Give Reasonable ###Packers and Movers Charges. Cheap and Best Office Relocation Compare Quotation for Assurance for Local and Domestic House Shifting and Get estimates today to save upto 20%, ***Read Customer Reviews - @ Packers And Movers Bangalore

    ReplyDelete
  6. Nisha Arora Delhi escort service is always top class and highly professional. Delhi is one of the most visiting areas a place of full enjoyment for youth. Many people come to this place for enjoyment and weekend celebration with friends and many of them looking for escort service to refresh their mind and fulfill their physical need.
    Escorts in Delhi
    Call girls in Delhi
    Delhi escort
    Delhi Escorts Service

    ReplyDelete
  7. Hello I am Mansi Working with Manali escorts service. Tremendous quantities of free ladies are working in the escorts administrations in Manali

    for the entire day and night. On the off chance that you are feeling so alone or get disappointed from the furious life plans, at that point

    nothing is in a way that is better than having a friend who can give you a badge of affection and bliss.She is a free Escorts model will give you

    more joy and Full satisfaction.

    https://www.mansiescorts.com



    manali Call Girls
    manali escorts

    amritsar Call Girls
    amritsar escorts

    ambala Call Girls
    ambala escorts

    agra Call Girls
    agra escorts

    ahmedabad Call Girls
    ahmedabad escorts

    almora Call Girls
    almora escorts

    baddi Call Girls
    baddi escorts

    bharuch Call Girls
    bharuch escorts

    Chamba Call Girls
    Chamba escorts
    call girl in Chamba

    Chitkul Call Girls
    Chitkul escorts
    call girl in Chitkul

    ReplyDelete
  8. Our Ambala Escort Girls is fully matured to bring immense joy to your life and Get fully satisfied with Our Hot Girls in a luxury hotel -

    Ambala Call Girls | Escorts in Ambala | Ambala Escorts

    ReplyDelete