ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql

Since Oracle 9.2 the shared pool can be “partitioned” into multiple parts. This was probably done for relieving shared pool latch contention for crappy applications (which use shared pool latches too much due bad cursor or connection management).

The “partitions” are called shared pool subpools and there can be up to 7 subpools. Each subpool is protected by a separate shared pool latch and each subpool has its own freelists and LRU list. If you are interested in more details, a good starting point is this whitepaper by Oracle.

There are few different ways for detecting how many subpools you have in use. The more convenient ones are here:

You could query X$KGHLU which has a line for each shared pool subpool and (from 10g) also java pool if it’s defined:

SQL> select count(distinct kghluidx) num_subpools
  2  from x$kghlu
  3  where kghlushrpool = 1;

NUM_SUBPOOLS
------------
           7

The “kghlushrpool” column, which is 1 for shared pool subheaps and 0 for java pool, isn’t there in 9i (and in 9i the java pool apparently is not reported in x$kghlu anyway).
The reason why I don’t just count all matching lines from x$kghlu but use count distinct instead is that in Oracle 10.2.0.1 there are 4x more lines reported in this x$table. There’s an additional concept called sub-sub-pool starting from 10.2 where each shared pool sub-pool is split futher into 4 areas (allocations with different expected lifetime/durations go into different sub-sub-pools, but the same sub-pool latch protects all activity in sub-sub pools too). But in 10.2.0.1 the x$kghlu reports all sub-sub-pools too for some reason. The whitepaper from Oracle mentioned above explains this in more detail.

So from above output I see that in my instance all 7 shared pool subpools are in use. Oracle determines the number of needed subpools (during instance startup) based on your shared pool size and cpu_count. IIRC in 9.2 if you had 4 CPUs or more AND the shared_pool_size was bigger than 256 MB then 2 subpools were used, in 10g shared_pool_size had to be bigger for that, 512 MB I think and in 11g its 1GB. I don’t recall the exact threshold values and that’s not really important as you can see yourself how many subpools are in use with the above query.

For sake of this experiment I set the _kghdsidx_count variable to 7, this parameter can be used to force the number of subpools you want. In 9.2 days it was actually quite common to set this back to 1 IF you had ORA-4031 errors AND the reason was diagnosed to be free space imbalance between subpools. However since 10g this has been almost unnecessary as Oracle has improved their heap management algorithms.

SQL> @pd kghdsidx

NAME                                          VALUE                          DESCRIPTION
--------------------------------------------- ------------------------------ ------------------
_kghdsidx_count                               7                              max kghdsidx count

The script above queries few X$ tables to show the value of this hidden parameter.

So far the two above approaches have required access to X$ tables which usually means you need to be logged on as SYSDBA. What if you don’t have such access?

In such case you can work this out pretty reliably by looking into how many of the shared pool latches are actually in use. All 7 latches are always there, even if you have less subpools in use, that number is hardcoded into Oracle. But you can see how many latches have a significant number of gets against them.

In my case its evident that all latches are in use, they all have significant number of gets against them:

SQL> select child#, gets
  2  from v$latch_children
  3  where name = 'shared pool'
  4  order by child#;

    CHILD#       GETS
---------- ----------
         1     765883
         2    3560835
         3     101684
         4      98391
         5      86481
         6    6130039
         7      82593

7 rows selected.

It’s ok to see some latch gets against the latches of unused subheaps, but this number should be much much smaller than others. The reason appears to be that all subheap latches are taken when shared pool is allocated and when shared pool resize operations are done.

For example, this is what I see after setting the number of shared pool subpools to 2 in my test database (and running some hard parsing workload):

SQL> select child#, gets
  2  from v$latch_children
  3  where name = 'shared pool'
  4  order by child#;

    CHILD#       GETS
---------- ----------
         1   27538623
         2   17924565
         3        131
         4        131
         5        131
         6        131
         7        131

7 rows selected.

And now to the troubleshooting part!

Note that this article doesn’t aim to explain all the basics of ORA-4031 troubleshooting, I’ll talk about the subpool utilization imbalance problem only. If you haven’t read metalink note 396940.1 – “Troubleshooting and Diagnosing ORA-4031 Error” yet, I recommend to do this first and then read my comments here.

As you know, ORA-4031 errors look like this:

ORA-04031: "unable to allocate n bytes of shared memory ("shared pool", "object_name", "alloc type(2,0)" ...)

“n” shows how many bytes we tried to allocate when ended up with the failure. Italic strings can show various different values but essentially they’re just some metadata describing for what did we try to allocate that memory.

Note the two bold pieces. The “shared pool” means that we tried to make the allocation from shared pool (if you have problems with other pools you can see there “large pool”, “streams pool”, “java pool” as well).
The “2″ in “(2,0)” means that the failure happened in shared pool sub pool number 2 and the “0″ shows sub-sub-pool number 0.

Sometimes the error happens just due heavily undersized shared pool (combined bad cursor management or some incorrect parameter values). In such cases you would see the shared pool free memory drop to near-zero in V$SGASTAT.

However, sometimes you can have ORA-4031′s even when you see plenty of free space available in V$SGASTAT. What’s the issue with that?

This case happens mainly for two reasons:

1) Shared pool free memory fragmentation
There is no big enough free chunk available even after flushing out unpinned chunks from LRU list. In other words, you have a lot of small free chunks scattered around in different places in shared pool but there is no single big enough chunk available for acommodating our allocation. I will talk about troubleshooting this problem in a separate post.

2) Unbalanced memory usage / free memory in different shared pool subpools
This is what I’m explaining in current post.

So, how to monitor which subpool has how much free memory available?

V$SGASTAT unfortunately just shows a sum of all subpools:

SQL> select * from v$sgastat
  2  where pool = 'shared pool'
  3  and name = 'free memory';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                 188017360

However when we look into the source code of GV$SGASTAT we see this (output edited for readability):

SQL> @v gv$sgastat

VIEW_NAME  TEXT
---------- --------------------------------------------------------------------------------
GV$SGASTAT select inst_id,'',ksmssnam,ksmsslen from x$ksmfs where ksmsslen>1  union all

           select inst_id,'shared pool',ksmssnam, sum(ksmsslen) 
           from x$ksmss
           where ksmsslen>1 
           group by inst_id, 'shared pool', ksmssnam

           union all  select
           inst_id,'large pool',ksmssnam, sum(ksmsslen) from x$ksmls    where ksmsslen>1
           group by inst_id, 'large pool', ksmssnam  union all  select inst_id,'java
           pool',ksmssnam, sum(ksmsslen) from x$ksmjs    where ksmsslen>1 group by inst_id,
           'java pool', ksmssnam  union all  select inst_id,'streams pool',ksmssnam,
           sum(ksmsslen) from x$ksmstrs    where ksmsslen>1 group by inst_id, 'streams
           pool', ksmssnam

V$SGASTAT gets its shared pool stats from x$ksmss. Lets describe it:

SQL> desc x$ksmss
           Name                            Null?    Type
           ------------------------------- -------- ------------
    1      ADDR                                     RAW(8)
    2      INDX                                     NUMBER
    3      INST_ID                                  NUMBER
    4      KSMSSLEN                                 NUMBER
    5      KSMSSNAM                                 VARCHAR2(26)
    6      KSMDSIDX                                 NUMBER

There’s an interesting column, KSMDSIDX column – and it’s also what I was looking for! This column contains the subpool number in it!

Before I query by that column, remember, I had set the number of subpools back to 2 in my test instance:

SQL> @pd kghdsidx

NAME                                          VALUE                          DESCRIPTION
--------------------------------------------- ------------------------------ -------------------
_kghdsidx_count                               2                              max kghdsidx count

Ok, lets see what values we have in that column:

SQL> select distinct ksmdsidx from x$ksmss;

  KSMDSIDX
----------
         1
         2
         0

Hmm… 1 and 2 look ok as I have subpool 1 and 2 defined in the instance, but why is there a subpool 0 also reported? (from 10g anyway).

This is due a little feature in Oracle. When you start the instance in 10g, then not all memory reserved for shared pool is immediately given to subpool heaps. Some memory is reserved for individual subpool growth. This allows some subpools to grab more memory than others if they have more allocations after instance startup. This may be useful in cases where due some specific issue some subpool always needs much more memory than others. On the other hand, I have not seen a subpool heap give memory back to some other subpool so if one subpool allocates all of the reserved memory after instance start due some application startup activity, then the other pools may remain too small for the whole lifetime of the instance.

So, if you have ORA-4031 out of shared pool memory errors or suspect that shared pool memory pressure is the cause of some performance problem (like shared pool latch contention and excessive library cache evictions/reloads) then you’d want to monitor shared pool memory breakdown at the subheap level.

And (finally) I can introduce a little script sgastatx.sql which queries X$KSMSS and formats the output for better readability.

The script takes one parameter, what memory allocation reasons to report (% would report all):

I will start with “total” which just reports me the shared pool totals and doesn’t break down by allocation reason.

SQL> @sgastatx total

-- All allocations:

SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):       192937984        184
shared pool (1):                 62919000         60
shared pool (2):                 67109232         64
shared pool (Total):            322966216        308

-- Allocations matching "total":

no rows selected

The bold part above is the total memory reserved for shared pool. The “0 – unused” is the not-yet-used-for-any-subheap part of the memory. And 1 & 2 are the allocations to subheap 1 and 2.

By the way I can confirm these numbers by querying v$sgainfo:

SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2075656 No
Redo Buffers                        6311936 No
Buffer Cache Size                 184549376 Yes
Shared Pool Size                  322961408 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  734003200 No
Startup overhead in Shared Pool    88080384 No
Free SGA Memory Available         209715200

11 rows selected.

….or V$SGA_DYNAMIC_COMPONENTS:

SQL> select current_size from v$sga_dynamic_components where component = 'shared pool';

CURRENT_SIZE
------------
   322961408

Continuing with the examples, usually I would be interested in seeing how much free memory each subpool has in it:

SQL> @sgastatx "free memory"

-- All allocations:

SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):       180355072        172
shared pool (1):                 67113304         64
shared pool (2):                 75497840         72
shared pool (Total):            322966216        308

-- Allocations matching "free memory":

SUBPOOL                        NAME                       SUM(BYTES)         MB
------------------------------ -------------------------- ---------- ----------
shared pool (0 - Unused):      free memory                 180355072        172

shared pool (1):               free memory                  12689424       12.1  <-- 12 MB of 64 allocated to this subheap is free

shared pool (2):               free memory                  14822176      14.14

And when there’s not enough free memory in some subpool then you can run sgastatx with % parameter to report all memory users in that subpool. I removed some lines from output for brevity:

SQL> @sgastatx %

-- All allocations:

SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):       171966464        164
shared pool (1):                 71307608         68
shared pool (2):                 79692144         76
shared pool (Total):            322966216        308

-- Allocations matching "%":

SUBPOOL                        NAME                       SUM(BYTES)         MB
------------------------------ -------------------------- ---------- ----------
shared pool (0 - Unused):      free memory                 171966464        164

shared pool (1):               free memory                  12045488      11.49
                               ASH buffers                   6291456          6
                               sql area                      6133120       5.85
                               private strands               4359168       4.16
                               library cache                 4122344       3.93
                               KSFD SGA I/O b                3977088       3.79
                               event statistics per sess     2764800       2.64
                               KTI-UNDO                      2323992       2.22
                               KGLS heap                     2154696       2.05
                               kglsim hash table bkts        2097152          2
                               PL/SQL MPCODE                 1884456        1.8
                               sessions                      1588808       1.52

[...snip...]

shared pool (2):               free memory                  14726904      14.04
                               row cache                     7495336       7.15
                               ASH buffers                   6029312       5.75
                               sql area                      4405040        4.2
                               private strands               4359168       4.16
                               KCB Table Scan Buffer         3981120        3.8
                               FileOpenBlock                 3977984       3.79
                               event statistics per sess     2764800       2.64
                               KTI-UNDO                      2323992       2.22
                               kglsim hash table bkts        2097152          2
                               library cache                 2059376       1.96
                               KQR M PO                      1603584       1.53
                               sessions                      1593600       1.52
                               VIRTUAL CIRCUITS              1401600       1.34
                               KGLS heap                     1377192       1.31
                               PL/SQL MPCODE                 1163344       1.11
                               CCursor                       1134520       1.08
                               dbwriter coalesce buffer      1052672          1
                               KSXR receive buffers          1036000        .99

[...snip...]

731 rows selected.

Of course sometimes you’d want to know how the memory usage breakdown changes over time, for that you’d need to write a little collector script which dumps the data into some table and visualize it later on, like I have done for regular V$SGASTAT data with my PerfSheet tool ;-)

Happy monitoring :)

This entry was posted in Oracle and tagged , . Bookmark the permalink.

41 Responses to ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql

  1. Timur Akhmadeev says:

    Hi, Tanel.

    Thank you for excellent script and explanation of sub-pools. I have one question:
    >On the other hand, I have not seen a subpool heap give memory back to some other subpool so if one subpool allocates all of the reserved memory after instance start due some application startup activity, then the other pools may remain too small for the whole lifetime of the instance.
    What happens in case of ASMM/AMM?

    TIA.

  2. amalendu says:

    Hi Tanel,

    Excellent note.

    Thanks a lot , again for sharing this wonderful research to the Oracle community of the world.

    You are doing a great service to all of us.

    Personally, also as a part of Oracle users of the world, I am feeling indebted to you .

    Best regards,

    amalendu.

  3. Md. Zafar Ahsan says:

    Yeah..
    I can just say great article.. I have become a very fan of yr analysis and research and feel blessed to search out this site.Keep it up..

    With Regards.
    Zafar

  4. Santosh says:

    Hi Tanel,

    If you remember I was trying to resolve the shared pool fragmentation problem by reducing the number of subpools from 4 to 2.But then I found a few weired thing in shared pool i.e. db block buffers headers consuming lot of space so I replaced it with db_cache_size and this component got removed from the shared pool and I also reduced the shared pool min size alloc to solve this issue.

    thanks,
    Santosh

  5. Paresh says:

    Santosh,
    If you don’t mind, could you please share your research to investigate the issue and how you concluded that this issue is related to db block buffers headers’ space consumption in shared pool?

    Tanel,
    Sorry for putting a question for someone else on your Blog.

  6. Tanel Poder says:

    Paresh, no problems!

    Both v$sgastat (or my sgastatx script which uses x$ksmss) and also shared pool heapdumps/x$ksmsp should show “db_block_buffers” – a component *held in shared pool* if old fashioned memory management is used (9i only I think).

    But I’d be interested in hearing your approach, Santosh.

  7. Paresh says:

    Thanks Tanel.

    I just came across another question while doing some research related to flushing shared pool. I did flush the shared pool and after a while I executed below query,

    select inst_id,’shared pool’,ksmssnam, sum(ksmsslen) from x$ksmss
    where ksmsslen>1
    group by inst_id, ‘shared pool’, ksmssnam;

    I thought flushing out the shared pool will reset all the statistics but it didn’t so is it normal behavior or should it flush out?

    Thanks!

  8. Tanel Poder says:

    When you flush shared pool, only the currently unpinned recreatable chunks of memory are flushed out directly.

    Permanent allocations and chunks which happen to be in use (pinned) at the flush time, are not flushed out.

    So its ok to see some components still take as much memory as they were taking…

  9. santosh says:

    Hi Paresh,

    I was busy with something else, so couldn’t reply …

    The issues was avg -10 4031 per day and all the errors were only in subpool 1 only.We configured 4 subpools in shared pool.

    while checking the memory consumption in shared pool , came across this fixed component “db block buffers headers” consuming significant amount of memory almost 20%, in shared pool.This component got initiated because of db_block_buffers setting, so after few tests , observed that if we replace db_block_buffers with db_cache_Size , we can get rid of this component , allowing that memory for user programs.

  10. Mimins says:

    Hi Tanel,
    In RAC environment, 2 node, but application only connect to node1 and node2 is idle.
    if I check the free memory of shared pool (use sgastatx “free memory”), it show that node1 have much more free memory than node2, even the node 2 is idle.
    Please advise?

    Regards,
    Mimins

  11. Tanel Poder says:

    @Mimins
    Are you using SGA_TARGET? Perhaps the shared pool on the node1 is much bigger thanks to the automatic shared memory manager feature? Query V$SGA_DYNAMIC_COMPONENTS to see various pool sizes…

  12. Mimins says:

    Hi Tanel,
    Not using SGA_TARGET, I set shared pool size 4G for each node and I set _kghdsidx_count=1.
    Below is the output:

    Node1 (active):
    – All allocations:

    SUBPOOL BYTES MB
    —————————— ———- ———-
    shared pool (0 – Unused): 1560281088 1488
    shared pool (1): 2734719560 2608.03
    shared pool (Total): 4295000648 4096.03

    – Allocations matching “free memory”:
    old 15: AND LOWER(ksmssnam) LIKE LOWER(‘%&1%’)
    new 15: AND LOWER(ksmssnam) LIKE LOWER(‘%free memory%’)

    SUBPOOL NAME SUM(BYTES) MB
    —————————— ————————– ———- ———-
    shared pool (0 – Unused): free memory 1560281088 1488

    shared pool (1): free memory 332008336 316.63

    Node2 (passive):
    – All allocations:

    SUBPOOL BYTES MB
    —————————— ———- ———-
    shared pool (1): 4294983360 4096.02
    shared pool (Total): 4294983360 4096.02

    – Allocations matching “free memory”:
    old 15: AND LOWER(ksmssnam) LIKE LOWER(‘%&1%’)
    new 15: AND LOWER(ksmssnam) LIKE LOWER(‘%free memory%’)

    SUBPOOL NAME SUM(BYTES) MB
    —————————— ————————– ———- ———-
    shared pool (1): free memory 711515408 678.55

  13. Tanel Poder says:

    @Mimins
    I would run sgastatx with % parameter and see which components are the biggest memory users on the idle instance then…

  14. Mimins says:

    @Tanel Poder

    Hi Tanel,
    I can see the sqlarea are the biggest one on both node. I still confused how my second node have also high utilization of shared pool. there are very limited connection there (idle).

    Regards,
    Mimins

  15. Tanel Poder says:

    @Mimins
    well perhaps there was some activity there in past which filled up the pool. if the node is idle now, all stuff in shared pool will remain there until someone flushes it out…

    if it’s sql area, you can further break the memory usage down by querying V$SQL.SHARABLE_MEM and grouping by parsing_user_id or something to see which cursors use the most of memory.

    But i wouldn’t worry about the memory usage at all, sql areas can be flushed out when there’s memory shortage…

  16. Mimins says:

    @Tanel Poder
    Thanks Tanel, It really help me.

    Regards,
    Mimins

  17. maclean says:

    ooh, I think 4031 will be the only problem which is stick from 8i to 11g, and a little difficult to handle.

  18. Niyas says:

    I am unix admin. User is getting the below error on aix server, Please advise how to resolve the issue. ” :/ORACLE SQLPLUS SQL*PLUS: RELEASE 10.2.0.4.0 – PRODUCTION ON TUE JUN 15 SQL STARTUP ORA-04031: UNABLE TO ALLOCATE 1572896 BYTES OF SHARED MEMORY ( SHARED POOL , UNKNOWN OBJECT , SGA HEAP(1,0) , KKS STBKT ) “

  19. Hi Tanel
    From one of my RAC instance I see

    SQL> select inst_id, component, current_size/1024/1024 “Current Size (MB)”
    from GV$SGA_DYNAMIC_COMPONENTS
    where current_size > 0
    order by 3 desc 2 3 4
    5 /
    INST_ID COMPONENT Current Size (MB)
    ———- —————————————- —————–
    2 shared pool 12272
    1 shared pool 12272
    1 DEFAULT buffer cache 8160
    2 DEFAULT buffer cache 8144
    2 java pool 32
    2 large pool 16
    1 large pool 16
    1 java pool 16

    SQL> select * from GV$SGAINFO
    where name = ‘Shared Pool Size’ 2
    3 /

    INST_ID NAME BYTES RES
    ———- —————————— ———- —
    2 Shared Pool Size 1.2868E+10 Yes
    1 Shared Pool Size 1.2868E+10 Yes

    SQL> @sgastatx “free memory”

    – All allocations:

    SUBPOOL BYTES MB
    —————————— ———- ———-
    shared pool (1): 7485501952 7138.73
    shared pool (2): 7753160960 7393.99
    shared pool (Total): 1.5239E+10 14532.72

    GV$SGA views shows shared pool total size as 12272 MB and script – 14532.72 MB. What result is correct?

    Is this possible that Oracle RAC instances SGA component sizes are forced to remain (almost) equal when ASMM is using?

  20. Pardon, that’s my mistake – I had to query v$sgastat. If poosible – look at second question, pls

  21. harsha kb says:

    Hi,

    I’m getting the ora 04031 error while starting the database itself. I’m unable to query the database for analysis.

    Also i tried with with increasing the SGA memory. Still i’m getting the error.

    I’m using Oracle 10g and windows 2003 enterprise 32 bit OS.

    Please help.

    Harsha

  22. Viral Patel says:

    Thanks for this great post. I have been fighting with shared pool for last few days but shared pool doesn’t want to release any free memory it has. This is a 11g single instance database with ASM. I started up this database with Automatic SGA memory management with 1.5GB of total SGA. Out of 1.5g, shared pool took about 850M of memory leaving only 400M some memory for buffer cache. I am trying to adjust this memory without restarting database but for some reason Oracle thinks it really needs 800+ memory. When I run query on v$sgastat, I can see that there is about 600+ MB of memory free in shared pool. I have flushed the shared pool, disabled ASMM(Automatic SGA), re-enabled ASMM but shared pool still will not release this free memory to buffer cache. Does anyone has any idea how to force shared pool to release this memory without restarting database? Any help would be appreciated. Thanks.

  23. Raj says:

    Hi Tanel
    Nice Post.
    Thanks for it.

    The link to the Oracle White Paper might have changed.
    Do you have the changed link?
    TIA for providing it.

    regards
    —Raj

  24. Tanel Poder says:

    @Raj
    Yep the doc got lost when Oracle upgraded their website appearance…

    I googled around a bit and found it here:
    http://www.personal.psu.edu/users/t/x/txo8/oracle10g%20Admin%20Slides/PS_S003_274003_106-1_FIN_v2.pdf

  25. PD Malik says:

    Hello Tanel,

    Even the new link for that white paper seems to be broken now :-(

    Thanks.

  26. Tanel Poder says:

    @PD Malik
    Blame Oracle – they keep changing their website URL structure

    I think I’ve put the latest link here: http://tech.e2sn.com/oracle-scripts-and-tools

  27. PhoenixBai says:

    Hi Tanel,
    Really loved your post. you are awesome.

    I can`t download your script sgastatx.sql, seems the link is broken. So, is there anywhere else that I can get it?

    Thank you!

  28. Juravle says:

    Hi,
    I have the same issue, need sgastatx.sql and link is broken.

    Best Regards
    GJ

  29. Jitendra kumar Lakra says:

    Hi All,

    I have found the link for understanding internal of shared pool provided by oracle.

    http://www.oracle.com/technetwork/database/focus-areas/manageability/ps-s003-274003-106-1-fin-v2-128827.pdf

  30. Ed says:

    Hi Tanel,

    As always, very, very good article.

    Is there any way to know what is the current used and free memory out of the shared_pool_size when I get the ORA-04031 error?

    That is, for example, if I get ORA-04031: unable to allocate 4328 bytes of shared memory (“shared, what is the current free memory in the shared pool? Obviously it ls less than 4328 bytes then but over time, some memory got freed up ‘coz I was able to re-run the same job that failed with this error.

    Is this information available in one of the DBA_HIST views?

    FYI, I have SGA_TARGET = 0 so I am not using automatic memory management.

    Any advice/feedback much appreciated. Thanks in advance.

  31. Jared says:

    We have seen the memory imbalance on 11.2.0.3 on Solaris X86-64.
    This was on a fairly large and very busy 3 node RAC.

    Oracle’s advice was to reduce the pools to 3.

    When the ORA-4031′s persisted, the advice was then to set the pools = 1.

    At this point we just decided to no use automatic memory management,
    set all the parameters manually and restarted all instances.

    No more ORA-4031 after that.

  32. Rajan says:

    Hi Tanel,

    Do we have any control over on which subpool the parse information is stored. Recently i was working on a 4031 on subpool 1 for a session and flushed shared pool and only to find out the subpool 1 is quickly filling up(even though other subpools were having plenty of space) and user session is getting the same error(even after logout/login, user connection is on subpool 1 only ). I heard that hash algorithm is used to redirect to particular subpool based on session information . Is that true? Can DBA control anything abt it?

    Regards,
    Rajan

    • Tanel Poder says:

      No unfortunately there’s no control – other than reducing the number of subpools with (_kghdsidx_count) to avoid bugs/issues. Yep there ought to be some hash value used (and some KGL object directory) which determines where the allocations are made from – but it very likely depends on the type of allocation itself (there are still some allocations which can’t go to any subpool and can’t be “striped” across subpools). You could try to open another session (while keeping the old one logged in too) to get a new SID and Oracle PID values and see if this results in a different subpool usage. Note that the whole subpool thing has had plenty of changes since it was introduced in Oracle 9i.

      If you want to avoid such errors – patch to latest DB levels or just reduce the number of subpools. Of course after reducing the shared pool usage if possible (less hard parses etc etc etc). I’ve troubleshooted issues where going from 4 to 2 subpools avoided the issues (and going back to 1 would be the “best” unless you need multiple subpools due to heavy shared pool latch usage or NUMA reasons). If you don’t see almost any shared pool latch contention, you likely do not need multiple subpools.

  33. Ashwin says:

    hi,

    Do u have a x$ view or query to find out the unpinned/free-able memory in various subpools/shared pool, which is safe to run on a busy DB?. We dont have _library_cache_advice set, so x$kglmem does not have any data. Heap dump is also ruled out as ours is a VVLarge&Busy db instance and we do not want to take a heapdump.

    regds
    ashwin

    • Ashwin says:

      Also, above it is noted that “distinct kghluidx” is used because there would be >1 row per subpool,referring to each duration (which means x$kglhu,with 7 subpools will have 28 lines or so and not seven)… but J lewis’s ‘oracle core’ book (which you reviewed) ,on Page 190 says even when durations are enabled, x$kglhu will have only one line per subpool as there is a single LRU list per subpool, irresepective of # of durations).

      can you please clarify?. Also how does one disable/enable durations?

      • Tanel Poder says:

        In Oracle 10.2.0.1 for some reason up to 28 rows were reported in X$KGHLU. Afterwards (in 10.2.0.2+ I think) only 7 were reported. So it was a reporting issue / bug with X$KGHLU. The heapdump would tell you the truth. The durations are controlled by _enable_shared_pool_durations parameter.

    • Tanel Poder says:

      There’s no low-impact way to know the details. X$KSMSP has been improved lately (11.2.0.2+ I think) to use a kghdmp_new() routine that supposedly holds the shared pool latches for less time at a time, but I wouldn’t still dare to use it in production in most cases. I’d rather gather and plot X$KGHLU and X$KSMLRU views (and the “sql area evicted” / “CCursor + sql area evicted”) metrics and try to use these as an early warning system.

      FYI, Jonathan has published an errata that mentions the x$kghlu issue as well: http://jonathanlewis.wordpress.com/oracle-core/oc-7-parsing-and-optimising/

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>