Oracle Apps

Don't miss Tanel Poder's upcoming seminar Advanced Oracle Troubleshooting Seminar, to be held in Denver June 15-17. If you're on the fence about whether to take this intensive class, take a look at some class feedback.

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 a 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 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 the 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 it's evident that all latches are in use; they all have a 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 this 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 that 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 to 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

 

There was a recent thread in Oracle-L about deadlocks and a recommendation to dump various instance information when the deadlock happens. A deadlock trace dumps some useful things automatically, but sometimes you want more, especially in a RAC environment.

So is it possible to make Oracle dump additional things when the deadlock event happens? Yes, it is, and it's doable using Oracle's diagnostic event handling infrastructure.

First I'll take a step back and explain what the following command means:

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Of course you know what it does; it enables extended SQL trace. But why such cumbersome syntax?

This syntax actually reveals some of the power of KSD diagnostic event syntax (KSD=kernel service debug):

10046

The first word in event string (the 10046) specifies when some action should be taken. And everything after that (trace name context forever, level 12) is that action.

trace

The first word in action (trace) specifies what type of action Oracle needs to take if that event (10046) occurs. By"'event occurs", I mean that if an Oracle parsing function calls the ksdpec() function with 10046 as a parameter, which in turn may recursively call some action set for that event, and then returns that event level (12) back to the caller, It's up to the caller to act on that returned value. For the event 10046, some ksd* tracing function is then called.

In event syntax, "trace" is the most generic action, which is used for tracing, behavior changing and Oracle dumps. In this post I will concentrate on the "trace" action, as it is the most common one to use.

name

The "name" specifies that the name of what to dump/trace will follow. The "name" is always present when "trace" option is used (as far as I know).

context

Now the next keyword (context) is the one where you can define whether you want Oracle to dump something when the event is hit or just do context specific trace. If you replace the "context" with "errorstack" for example, you wouldn't get SQL trace output, but rather an Oracle errorstack dump whenever event 10046 is hit.

You can use ""oradebug dumplist" to find all the possible dump commands what you can set as actions for an event.

forever

The next keyword (forever) is actually an option to the action, not an action keyword itself. Forever means that keep invoking the action when the 10046 event is hit, forever (or until explicitly disabled). If we don't specify forever, then the action would be invoked only once and the event will disable itself after that.

level 12

The "level 12" is also just another option to the action, specifying that the value for that event handler in given session should be 12. This means that whenever some Oracle function is checking whether that event is set, they will be returned value 12, the calling function interprets the value and acts appropriately (traces both binds and waits in our case).

As both the "forever" and "level 12" are just options for the same action, separated by commas, we can replace their order like that:

SQL> alter session set events '10046 trace name context level 12, forever';

Session altered.

The outcome is exactly the same, tracing is enabled at level 12, for forever.
Also we can leave both options out to make Oracle use default values ( invoke the action only once and tracing level is set to 1 ):

SQL> alter session set events '10046 trace name context';

Session altered.

There are other options available in addition to just "forever". If we want the action to be invoked let say 10000 times only and then automatically disable that event, we can use the LIFETIME option:

SQL> alter session set events '10046 trace name context level 12, lifetime 10000';

Session altered.

And if we want to ignore that event for some occurrences and start acting only after that, we can also specify AFTER x OCCURRENCES option to tell Oracle not to act on first x occurrences of that event and only activate itself after that:

SQL> alter session set events '10046 trace name context level 12, lifetime 10000, after 5000 occurrences';

Session altered.

You see I have combined 3 options to the action, level, lifetime and after 5000 occurrences. They can be specified in any order and need to be separated by commas (only if there are multiple options to an action, the first option must not be preceded by comma).
So far I spoke about 10046 event which is used for tracing, but there are other uses for diagnostic events.

Every ORA- error code in Oracle checks for an event with the same number. In fact Oracle diagnostic events and ORA- errors share the same range of numbers from 0 to 65535 for their codes (you can see most of them in $ORACLE_HOME/rdbms/mesg/oraus.msg file)

So, for example ORA-60 which is "deadlock detected" error will also check for event number 60 and if it is set, it will invoke its action(s) before sending the deadlock exception up the application stack.

For example, I could invoke hanganalyze at level 4 when ORA-60 is hit in my session:

SQL> alter session set events '60 trace name hanganalyze level 4';

Session altered.

As mentioned previously, you can run "oradebug dumplist" to see all actions available in your Oracle version (of course be careful when setting any undocumented events as some can even corrupt your database).

Sometimes we want more than just one type of dump when an error occurs. So it is possible to invoke multiple trace actions for a specific event. You need to separate these actions with semicolons.

SQL> alter session set events '60 trace name hanganalyze_global ; name systemstate level 266';

Session altered.

In the example above I tell Oracle to invoke a RAC global hang analyze and a systemstate dump with short stacks of processes ( level 256 + 10 = 266 ) whenever an ORA-60 deadlock error is raised.

It is possible to specify different options such level, event lifetime etc at each action level, for example we might not want to dump systemstate multiple times if the deadlock keeps reoccurring:

SQL> alter session set events '60 trace name hanganalyze_global level 4, forever; -
> name systemstate level 266, lifetime 1; -
> name processstate level 2, forever'
2 /

Session altered.

So far I've defined the events upon which to take action using their numbers. Event 60 (which corresponds to ORA-00060 error
message) is the "deadlock detected while waiting for resource" error message.

10046 on the other hand is not an actual error, but just a condition which is checked in few locations in Oracle kernel code (such OPI parse, execute, fetch and wait interface functions). Whenever these functions run, they check whether an event number 10046 is enabled for current process or session and if yes, then take appropriate action (which normally means tracing).

Note that it is possible to see most ORA- error messages and tracing/debug event numbers in $ORACLE_HOME/rdbms/admin/oraus.msg file on Unix platforms. Never set an event without well understanding what this event is doing and what do you want to achieve with it (and ideally you should have blessing from Oracle support/Metalink for using it).

A few numeric events have also text aliases. For example, instead of using ORA-60 as deadlock event definition, you could run the following statement:

SQL> alter session set events 'deadlock trace name hanganalyze_global';

Session altered.

Few more useful text aliases are:
PARSE_SQL_STATEMENT - corresponds to event 10035 which allows to write parse failures to alert.log file
SORT_END - corresponds to event 10033, "sort statistics (SOR*)"
SORT_RUN - corresponds to event 10032, "sort run information (SRD*/SRS*)"

So, armed with the knowledge for setting events, we can define fairly complex conditions:

For example we can use the following statement to follow some memory allocation of a sort operation. We take level 29 heapdumps (PGA+UGA+Call heaps) every sort pass (sort_run event) and also in the end of a sort operation (sort_end event). And also we want to take a workareatab_dump when sort ends but only during the first sort pass (thus the lifetime 1 condition).

SQL> alter session set events ' -
> sort_end trace name workareatab_dump level 3, forever; -
> name heapdump level 29, forever: -
> sort_run trace name workareatab_dump level 3, lifetime 1; -
> name heapdump level 29, forever'
2 /

Session altered.

Note that the lifetime and after x occurrences conditions are accounted at event level in session or process state, not statement execution level.

So if event's lifetime expires during some statement execution, then that event will be disabled until manually initialized with another alter session command.

There is also a special named event - IMMEDIATE. This means that don't wait for some event to happen in the future, but execute the required action immediately, right now.

SQL> alter session set events 'immediate trace name processstate level 10';

Session altered.

This would invoke a process state dump immediately. It's the same as running ORADEBUG DUMP PROCESSSTATE 10 command.

A few more interesting bits:

Event syntax can understand numbers in hex as well if you prefix them with "0x":

SQL> alter session set events '0x273E trace name context forever, level 0xC';

Session altered.

Also, in 11g you can run following command too:

SQL> alter session set events '10046 off';

Session altered.

The above clears any set actions for that event.

Before 11g you can either explicitly set all event handlers to off for an event or you can use the "all" qualifier:

SQL> alter session set events '60 trace name all off';

Session altered.

This would clear all tracing actions for that event in session. However crash and debug actions would still remain (more about these later).

That should be enough for part 1, the syntax of KSD debug events. In next part I will demo couple examples where the advanced event syntax is useful. Also, there's a reason why I keep mentioning KSD diagnostic events; there's another diagnostic event handling mechanism in Oracle as well, and Oracle has introduced pretty big improvements in 11g, but more about this in the future

Robert's latest technical book about Oracle is available. It's called OCP Oracle Database 11g Certified Professional Study Guide. It's a prep guide for the Oracle Database 11g OCP Exam (Exam 1Z0-053). Robert will be teaching a corresponding class, so watch our training schedule for an announcement.

Check out this and other great books on our books link.

Steven Chan, Oracle's Senior Director of Oracle Applications Technology, says that Oracle has not yet certified IE8 for the E-Business Suite. Steven says there are issues with launching forms, and further states that "it is imperative that your Apps users stay on IE7 until we complete our certification of IE 8". Fortunately, Microsoft has been kind enough to come out with an IE8 Blocker Toolkit, so check that out.

And while you're at it, Steven says, don't expect JInitiator to run on Vista desktops. Steven recommends moving off of JInitiator for this reason, and because JInitiator will be desupported on July 31, 2009. So if you haven't made the leap to Native Sun JRE, now's the time to get going.