This discussion is archived
12 Replies Latest reply: Dec 20, 2012 9:53 AM by jgarry RSS

shared pool usage

972227 Newbie
Currently Being Moderated
Hello guys

Is there any way to find out what objects or sqls are using up most of the shared_pool memory in 11.2.0.3.0. One of my databases just crashed due to the error "ORA-04031: unable to allocate 32 bytes of shared memory ". So I had to bounce the database. But I want to find out why the the shared memory could not be allocated and I also want to find out what used up most of the shared pool memory. my shared pool size is 256mb and the size of my database is 47gb.

Thank You

Edited by: 969224 on Dec 19, 2012 12:40 PM
  • 1. Re: shared pool usage
    jgarry Guru
    Currently Being Moderated
    It's a little difficult after you've bounced the db. See MOS SCRIPT: HOW TO IDENTIFY what packages are in the shared_pool and how many times have they been executed [ID 262296.1]

    Also see What is eating up the shared pool?

    If you are licensed for AWR, google shared pool awr. otherwise google shared pool oracle statspack jonathan lewis

    Edit: I also meant to say search on MOS for shared pool issues. I found a memory leak bug in 11.2.0.3 patched in 11.2.0.4, then MOS screwed up and I don't have time to search again. So ask support.

    Edited by: jgarry on Dec 19, 2012 12:53 PM
  • 2. Re: shared pool usage
    sb92075 Guru
    Currently Being Moderated
    969224 wrote:
    Hello guys

    Is there any way to find out what objects or sqls are using up most of the shared_pool memory in 11.2.0.3.0. One of my databases just crashed due to the error "ORA-04031: unable to allocate 32 bytes of shared memory ".
    Above should have only impacted a single process; not the whole DB
    So I had to bounce the database.
    Really?
    what forced you to bounce the DB?

    post excerpt from alert_SID.log file just prior to the DB restart.
  • 3. Re: shared pool usage
    VenkatB Guru
    Currently Being Moderated
    >
    Is there any way to find out what objects or sqls are using up most of the shared_pool memory in 11.2.0.3.0. One of my databases just crashed due to the error "ORA-04031: unable to allocate 32 bytes of shared memory ". So I had to bounce the database. But I want to find out why the the shared memory could not be allocated and I also want to find out what used up most of the shared pool memory. my shared pool size is 256mb and the size of my database is 47gb.
    >

    What's your MEMORY_TARGET (if you are on AMM)
    What's your shared_pool_size (default size, if any)

    Look into V$SQL for literal SQLs. It's possible that your shared pool is bombarded with tons of distinct SQLs causing ORA-4031. If your memory is sized fine, it will be mostly bind variable issue.

    Flushing shared pool or restarting database will only temporarily solve the issue. You will have to find the real cause.

    V$SQL will be your starting point. Also check V$SGA_DYNAMIC_COMPONENTS to see if your shared pool is resizing very frequently--could be due to bind variables issue again (parsing too many distinct SQLs or memory shortage)

    Regards
    Venkat
  • 4. Re: shared pool usage
    972227 Newbie
    Currently Being Moderated
    this is the excerpt from alert log just 3 mins before bouncing the database, could you identify anything wrong?

    Wed Dec 19 15:02:59 2012


    ***********************************************************************

    Fatal NI connect error 12170.

    VERSION INFORMATION:
    TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
    TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
    Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
    Time: 19-DEC-2012 15:02:59
    Tracing not turned on.
    Tns error struct:
    ns main err code: 12535

    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
    Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.25.75)(PORT=4252))
    Wed Dec 19 15:03:01 2012


    ***********************************************************************

    Fatal NI connect error 12170.

    VERSION INFORMATION:
    TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
    TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
    Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
    Time: 19-DEC-2012 15:03:01
    Tracing not turned on.
    Tns error struct:
    ns main err code: 12535

    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
    Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.29.32)(PORT=1105))
    Wed Dec 19 15:03:11 2012
    Errors in file /oradump/tcs1970/diag/rdbms/tcs1970/tcs1970/trace/tcs1970_smon_3932826.trc:
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^42b5153","kglHeapInitialize:temp")
    Wed Dec 19 15:03:26 2012
    Errors in file /oradump/tcs1970/diag/rdbms/tcs1970/tcs1970/trace/tcs1970_smon_3932826.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")
    Wed Dec 19 15:03:41 2012
    Errors in file /oradump/tcs1970/diag/rdbms/tcs1970/tcs1970/trace/tcs1970_smon_3932826.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")
    Wed Dec 19 15:03:52 2012
    License high water mark = 79
    USER (ospid: 31131774): terminating the instance
    Instance terminated by USER, pid = 31131774
    Wed Dec 19 15:03:58 2012
    Starting ORACLE instance (normal)
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    Picked latch-free SCN scheme 3
    Autotune of undo retention is turned on.
    IMODE=BR
    ILAT =332
    LICENSE_MAX_USERS = 0
    SYS auditing is disabled
    Starting up:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options.
    ORACLE_HOME = /ora112/11203
    System name: AIX
    Node name: chh-lpar7
    Release: 1
    Version: 6
    Machine: 00C07E254C00
    Using parameter settings in server-side pfile /ora112/11203/dbs/inittcs1970.ora
    System parameters with non-default values:
    processes = 2000
    sessions = 3024
  • 5. Re: shared pool usage
    jgarry Guru
    Currently Being Moderated
    Plug the parameters into the 4031 search tool on MOS.
  • 6. Re: shared pool usage
    sb92075 Guru
    Currently Being Moderated
    969224 wrote:
    Wed Dec 19 15:03:52 2012
    License high water mark = 79
    USER (ospid: 31131774): terminating the instance
    Instance terminated by USER, pid = 31131774
    Wed Dec 19 15:03:58 2012
    it appears somebody hit the Panic Button & shutdown the DB for no good reason.
    TNS-12535: TNS:operation timed out
    above is symptomatic of FireWall issue.
    For how long have these been occurring?
  • 7. Re: shared pool usage
    VenkatB Guru
    Currently Being Moderated
    You may also want to check this BUG with 11G in case you are hitting it

    *Bug 13250244 - Shared pool leak of "KGLHD" memory when using multiple subpools [ID 13250244.8]*

    Regards
    Venkat
  • 8. Re: shared pool usage
    972227 Newbie
    Currently Being Moderated
    hello sb92075

    do you mean to say that somebody shutdown the database, and that is why the errors occured? because 15:03:52 and 15:03:58 is the time in between when the database was bounced.

    Edited by: 969224 on Dec 19, 2012 1:38 PM

    Edited by: 969224 on Dec 19, 2012 1:39 PM

    Edited by: 969224 on Dec 19, 2012 1:39 PM
  • 9. Re: shared pool usage
    972227 Newbie
    Currently Being Moderated
    Hello Venkat

    If you dont mind, would you be able to explain a little bit more on what KGLHD is, please?

    Thank you
  • 10. Re: shared pool usage
    sb92075 Guru
    Currently Being Moderated
    969224 wrote:
    hello sb92075

    do you mean to say that somebody shutdown the database, and that is why the errors occured?
    somebody did SHUTDOWN the DB.

    Whether or not they saw the errors prior to the shutdown, I don't know.

    the errors did occur, but are independent of the shutdown.
    The DB was manually terminated.
  • 11. Re: shared pool usage
    972227 Newbie
    Currently Being Moderated
    The time i specified in my last reply to you comment is the time when the database was bounced. at first i tried flushing the shared pool, but i couldn't connect to the database through toad. then i tried logging into sqlplus from the unix command prompt, i was connected but was unable to execute any sql or flush the shared pool, that is why had to bounce the database.

    Please tell me the steps you would have followed to resolve this issue (I am new to oracle and dont have much experience, I am still learning)

    thank you

    Edited by: 969224 on Dec 19, 2012 1:43 PM
  • 12. Re: shared pool usage
    jgarry Guru
    Currently Being Moderated
    MOS has available to you most of what support analysts can see. So it is very useful to you to search, before you even open a support call. Google can be more hit-and-miss, easily leading you on a wild goose chase, but there is also good info to be had. Figuring out what is good is a skill in itself, some people advise using a "trust, but verify" approach. I'd say "don't trust, and verify" myself, leaving the former for the docs and what support says.

    TOAD is a good tool as far as it goes, but most of us eventually decide it is a good thing to use sqlplus (I prefer on the server, but that might just be me).

    When you have some problem, the alert log is the first place to look. Also, the newest versions have the diagnostic repository, which has its own learning curve.

    When you have a production problem, there is bound to be some tension between making an immediate fix and finding the root cause and fixing that. Of course you want to do the latter if possible, sometimes you only see how in retrospect.

    In this case, we see you have multiple problems - the possible firewall issue that sb pointed out, the shared pool issue, and not being able to connect. These could have some relation.

    The 4031 error is well known and well documented on the MOS site. Your issue might very well be fixed in a patch. That is why support will usually recommend that first, which makes sense in a situation where there is a clear problem fixed by a particular answer. Often things are not so clear. Tanel Poder has some excellent deep dives into these things, but it could be a bit much for a novice. Even so, googling through his online stuff for particular things mentioned among the MOS bugs can at least help you understand what they are.

    Connection errors can be a little more puzzling, especially to a novice under pressure. How one deals with this pressure is really a personal issue, and one of those DBA qualities that can't be defined for an interview process and is often site-dependent, a bad thing when people are used to rebooting Windows to fix everything. On the technical side, there is a lot of good advice available among the bad, bloggers like Ed Stevens and Charles Hooper have some very detailed examples of how to investigate these things. As usual, sb has likely hit the nail on the head for you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points