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

18 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
  9. Call girls in Dwarka available 24*7 at your service, you just need to pick your phone and call or What Sapp to Dwarka escort service to book these hot call girls Dwarka. They all are very beautiful and well maintained figure. Just call best Dwarka escorts and get those in arms full night.
    Delhi call girls
    Chandigarh escorts
    Dera bassi escorts
    Kalka escorts service
    Baddi escort service

    ReplyDelete
  10. Date the prettiest Call girls in Karol Bagh and get blown with 100% satisfaction and
    grace. Our girls are completely trained and equipped to entertain you in the best way. Call the karol Bagh escort service today!
    Karol Bagh Escorts
    Geeta Colony escort service
    call girls pinjore
    call girls Jahangirpuri
    call girls shastri nagar

    ReplyDelete
  11. jack is from seattle where he works as an health specialist at Arrowmeds. He has 6 years of experience with ED with a focus on ED Solveing. In his spare time, jack enjoys Help of my patients in my hospital.
    Fildena 100
    || Cenforce 100 ||Vidalista 20

    ReplyDelete
  12. jack fron has worked in the health industry for 6 years, gaining experience in health specialist. As a seasoned Doctor, he is passionate about ED Expert. In addition to health, he is also involved
    cenforce pill ||cenforce 25mg ||cenforce 50mg

    ReplyDelete