Monitoring on an Apps 11i Web Tier Server Proactively

When asked how many were proactively monitoring their access_log. Not many people will put their hands up.

There is a wealth of information in the access_log that can be mined to give information about user behaviour, possible user or system problems, as well as having the potential to give valuable performance information.

Here are just three examples where you can quickly answer important questions about your system with very little effort:

1. How long does it take 9iAS to serve requests?

The main performance information is the time taken to serve a request. This is not included in the access_log by default (I have raised enhancement request 5349693 to include this data), but can easily be added by adding the following line to the httpd.conf file via an AutoConfig customization (see Metalink Note 270519.1):

LogFormat "%h %l %u %t \"%r\" %>s %b %T"

This line can be added anywhere in the file, but would sensibly be placed after the existing LogFormat entries. This directive adds an extra column into the access_log to show the
time in seconds it took from receiving a request to sending back a
response. Valid log formats are described in the Apache documentation.

2. Am I getting any server errors?

The access_log includes the HTTP status code of the request. They are listed in full in RFC 2616 but the codes we would be immediately concerned about are any in the 400 or 500 range.

For example:

• Status 500 (internal server error) may typically be seen for a JServ request and often means the JVM has some kind of problem or has died.
  

  For example: This entry may indicate that the JServ JVM is not responding to any requests:
  
  192.168.1.10 - - [21/Jun/2006:13:25:30 +0100] "POST /oa_servlet/actions/processApplicantSearch HTTP/1.1" 500 0

  
  
• Status 403 (forbidden) could typically be seen for oprocmgr
  
  For example: This entry in access_log may indicate a problem with system configuration (oprocmgr.conf): requests and often means there is a misconfiguration that needs to be resolved.

myserver - - [21/Jun/2006:13:25:30
+0100] "GET
/oprocmgr-service?cmd=Register&index=0&modName=JServ
&grpName=OACoreGroup&port=16000
HTTP/1.1" 403 226

3. Are users having problems accessing pages?

The status code of 200 means the request was successful, however a dash ( - ) for the "Bytes sent" column, normally means that the request hit the Apache timeout. You would also see a time taken to serve request above 300 seconds as the default Apache timeout is 5 minutes.

If you see this situation occurring regularly then your users are either navigating away from the browser page before it has rendered, or are likely to be getting a "white screen of death" in their browser window where it will appear to hang.

In this situation you need to identify why the requests are not being processed in good time, which is a large subject in itself.

Identifying these issues

Hopefully these examples will inspire you to want to analyse your access_log, but I hear you ask, "Where will I get the time?"

Luckily the access_log is a simple text file, so if you do not have commercial monitoring software you can use some "quick and dirty" scripts to report just the exceptions you are interesting in seeing. For example I would often use the following to scan access_logs for problems:

## Start of script
##
##  Check for HTTP statuses in 400 or 500 range for JServ
##  or PLSQL requests only
##
awk ' $9>=400 && $9<=599 { print $0 }' access_log* | grep -e    "servlet" -e "\/pls\/" | grep -v .gif ## ##  Check for requests taking more than 30 seconds to be returned ## awk ' $11>30 {print $0} ' access_log*
##
##  This one is not an exception report, you need to manually check
##  Look for when the JVMs are restarting
##
grep "GET /oprocmgr-service?cmd=Register" access_log*
##
## End of script

[Editor: Due to formatting restrictions on this blog, if you're cutting-and-pasting this script, you must manually join the line above ending with grep -e with the following line starting with "servlet" into a single unbroken line.]

Conclusion

Proactive monitoring of the access_log will help you to :-

• Baseline your system performance
• Identify user usage patterns
• Highlight possible system or user problems
• Identify areas with possible performance issues
• Verify user reported problems

HAPPY LEARNING!

Concurrent Managers and Control Scripts

The concurrent managers in the Oracle e-Business suite serve several important administrative functions. Foremost, the concurrent managers ensure that the applications are not overwhelmed with requests, and the second areas of functions are the management of batch processing and report generation.

This article will explore tools that are used by experienced administrators to gain insight and improved control over the concurrent management functions. We will explore how the concurrent managers can be configured via the GUI, and also explore scripts and dictionary queries that are used to improve the functionality of concurrent management.

The Master Concurrent Managers

There is a lot of talk about "the" concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are "super" Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:

• Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.

• Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.

• Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.

Now that we understand the functions of the master Concurrent Managers, let's take a quick look at techniques that are used by Oracle Apps DBAs to monitor the tune the behavior of the Concurrent Managers.

Tuning the Concurrent Manager

All successful Oracle Apps DBAs must understand how to monitor and tune each of the Concurrent Managers. This article will explore some of the important techniques for monitoring and tuning the Oracle Apps Concurrent Manager processes. The topics will include:

• Tuning the Concurrent Manager
  • Tuning the Internal Concurrent Manager
    
  • Purging Concurrent Requests
    
  • Troubleshooting Oracle Apps performance problems
    
  • Adjusting the Concurrent Manager Cache Size
    
  • Analyzing the Oracle Apps Dictionary Tables
• Monitoring Pending Requests in the Concurrent Manager
  
• Changing the dispatching priority within the Concurrent Manager

Let's start by looking at tuning the ICM, and drill-down into more detail.

Tuning the Internal Concurrent Manager (ICM)

The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.

• PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.

• Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.

• Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.

All of the concurrent managers, with the exception of the ICM and CRM, can be configured to run as many processes as needed, as well as the time and days a manager can process requests. However, the number of processes needed is dependent on each organization's environment. An Applications DBA must monitor the concurrent processing in order to decide how to configure each manager. For a fresh install of the applications, initially configure the standard manager to run with five processes, and all the other managers with two processes. After the applications have been in operation for a while, the concurrent managers should be monitored to determine is more operating system process should be allocated.

Purging Concurrent Requests

One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.

Adjusting the Concurrent Manager Cache Size

Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.

Analyzing Oracle Apps Dictionary Tables for High Performance

It is also very important to run the request Gather Table Statistics on these tables:

• FND_CONCURRENT_PROCESSES
  
• FND_CONCURRENT_PROGRAMS
  
• FND_CONCURRENT_REQUESTS
  
• FND_CONCURRENT_QUEUES.

Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.

To troubleshoot performance, a DBA can use three types of trace. A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications. Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace. At the bottom right of the screen you can check the box Enable Trace. (Figure 1)

Figure 1: Troubleshooting Concurrent Manager Performance.

Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.

Monitoring Pending Requests in the Concurrent Managers

Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:

1. The concurrent managers were brought down will a request was running.

2. The database was shutdown before shutting down the concurrent managers.

3. There is a shortage of RAM memory or CPU resources.

When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.

To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.

Figure 2: Allocating more processes to the Concurrent Manager.

However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:

update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';

Changing Dispatching Priority within the Concurrent Manager

If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.

Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.

Using data Dictionary Scripts with the Concurrent Manager

Few Oracle Applications DBAs understand that sophisticated data dictionary queries can be run to reveal details about the workings within each Concurrent Manager. Oracle provides several internal tables that can be queried from SQL*Plus to see the status of the concurrent requests, and the most important are FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS.

Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:

afcmstat.sql	Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql	Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql	Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql	Displays the requests that are pending, held, and scheduled.
afrqstat.sql	Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql	Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql	Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.

In addition to these canned scripts you can skill write custom Concurrent Manager scripts. For example, the following query can be executed to identify requests based on the number of minutes the request ran:

conc_stat.sql

set echo off
set feedback off
set linesize 97
set verify off

col request_id format 9999999999    heading "Request ID"
    col exec_time format 999999999 heading "Exec Time|(Minutes)"
   col start_date format a10       heading "Start Date"
    col conc_prog format a20       heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"

spool long_running_cr.lst
SELECT
  fcr.request_id request_id,
  TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
  fcr.actual_start_date start_date,
  fcp.concurrent_program_name conc_prog,
  fcpt.user_concurrent_program_name user_conc_prog
FROM
 fnd_concurrent_programs fcp,
 fnd_concurrent_programs_tl fcpt,
 fnd_concurrent_requests fcr
WHERE
  TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
  fcr.concurrent_program_id = fcp.concurrent_program_id
and
  fcr.program_application_id = fcp.application_id
and
  fcr.concurrent_program_id = fcpt.concurrent_program_id
and
  fcr.program_application_id = fcpt.application_id
and
  fcpt.language = USERENV('Lang')
ORDER BY
  TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
         
spool off

Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.

Enter          value for min: 60

            Exec Time
       
Request ID (Minutes)  Start Date Conc Program Name    User Program Name
----------- ---------- ---------- -------------------- --------------------------------------
   1445627        218 01-SEP-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)
    444965        211 03-JUL-01  CSTRBICR5G           Cost Rollup - No Report GUI
   1418262        208 22-AUG-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)
    439443        205 28-JUN-01  CSTRBICR5G           Cost Rollup - No Report GUI
    516074        178 10-AUG-01  CSTRBICR6G           Cost Rollup - Print Report GUI
   1417551        164 22-AUG-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)
   1449973        160 03-SEP-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)
    520648        159 13-AUG-01  CSTRBICR5G           Cost Rollup - No Report GUI
    446007        122 03-JUL-01  CSTRBICR5G           Cost Rollup - No Report GUI
    392996        120 01-JUN-01  BMCOIN               Bill and Routing Interface

Conclusion

The Oracle Concurrent Managers remain on of Oracle most important components for the Oracle Applications eBusiness suite and they perform an important TP monitor function. It is only by properly understand the functions and tuning of the Concurrent Managers that the Oracle Apps DBA can be successful in keep their sophisticated Applications optimizer for high-performance.

HAPPY LEARNING!

Semaphores and Shared Memory

Shared memory and semaphores are two important resources for an Oracle instance on Unix. An instance cannot start if it is unable to allocate what it needs.

DEFINITIONS

Shared memory is exactly that - a memory region that can shared between different processes. Oracle uses shared memory for implementing the SGA, which needs to be visible to all database sessions.

Semaphores can be thought of as flags (hence their name, semaphores). They are either on or off. A process can turn on the flag or turn it off. If the flag is already on, processes who try to turn on the flag will sleep until the flag is off. Upon awakening, the process will reattempt to turn the flag on, possibly suceeding or possibly sleeping again. Such behaviour allows semaphores to be used in implementing a post-wait driver - a system where processes can wait for events (i.e. wait on turning on a semphore) and post events (i.e. turning of a semaphore). This mechanism is used by Oracle to maintain concurrency control over the SGA, since it is writeable by all processes attached.

ALLOCATION IN SIMPLE TERMS

Shared memory required by the Oracle Instance : On instance startup, the first things that the instance does is: -Read the "init.ora" -Start the background processes -Allocate the shared memory and semphores required The size of the SGA will be calculated from various "init.ora" parameters. This will be the amount of shared memory required. The SGA is broken into 4 sections - the fixed portion, which is constant in size, the variable portion, which varies in size depending on "init.ora" parameters, the redo block buffer, which has its size controlled by log_buffers, and the db block buffer, which has its size controlled by db_block_buffers. The size of the SGA is the sum of the sizes of the 4 portions. There is unfortunately no simple ormula for determining the size of the variable portion.

Generally, the shared pool dominates all other parts of the variable portion, so as a rule of thumb, one can estimate the size as the value of shared_pool_size.

The number of semphores required is much simpler to determine.
Oracle will need exactly as many semaphores as the value of the processes "init.ora" parameter.

SHARED MEMORY ALLOCATION

1. One-segment

2. Contigous multi-segment

3. Non-contigous multi-segment

When attempting to allocate and attach shared memory for the SGA, it will attempt each one, in the above order, until one succeeds or raises an ORA error. On other, non-fatal, errors, Oracle simply cleans up and tries again using the next memory model. The entire SGA must fit into shared memory, so the total amount of shared memory allocated under any model will be equal of the size of the SGA(SGASIZE).

1. One-segment:- The one-segment model is the simplest and first model tried. In this model, the SGA resides in only one shared memory segment. Oracle attempts to allocate and attach one shared memory segement of size equal to total size of the SGA. However, if the SGASIZE is larger than the configured SHMMAX, this will obviously fail. In this case, the SGA will need to be placed in multiple shared memory segments, and Oracle proceeds to the next memory model for the SGA.

With multiple segments there are two possibilities. The segments can be attached contiguously, so that it appears to be one large shared memory segment, or non-contiguously, with gaps between the segments.

2. Contigous multi-segment - In the contiguous segment model, Oracle simply divides the SGA into SGASIZE/SHMMAX (rounded down) segments of size SHMMAX plus another segment of size SGASIZE modulo SHMMAX

3. Non- contigous multi-segment : Once the number of segments and their sizes is determined, Oracle then allocates and attaches the segments one at a time; first the fixed and variable portion segment(s), then the redo block buffer segment(s), then the db block buffer segment(s). They will be attached non-contiguously,
At this point, we have either attached the entire SGA or returned an ORA error. The total size of segments attached is exactly SGASIZE; no space is wasted. Once Oracle has the shared memory attached, Oracle proceeds to allocating the semaphores it requires.

Recommended values of kernel parameters for Shared memory in
Oracle 8i

SHMMAX= max value of shared memory segment = .5 * size of
physical memory

SHMMIN= min size of shared memory segment=1

SHMMNI= max number of shared memory identifiers on system = 100

SHMSEG= max number of shared memory segments per process = 10

max Sga that can be created by the one segment model is SHMMAX*SHMSEG

You can display the current kernel parameters by doing a "sysdef -i"

SEMAPHORE ALLOCATION

Oracle just needs to allocate a number of semaphores equal to the processes parameter in "init.ora".

SEMMSL= # of semaphores in a semaphore set

SEMMNI= the maximum # of semaphores sets in the system

SEMMNS= the number of semaphores in the system.

SEMOPM= max number of operations per semop call = 100

SEMVMX = semaphore max value = 32767

When an Oracle instance is started, all required semaphores will be allocated. Semaphores are allocated in sets.

Since each oracle process* requires a semaphore, the number that is allocated is equal to the value of the init.ora parameter PROCESSES. The total # of semaphores required is the sum of all your instance's PROCESSES.

You can allocate all of your semaphores in one or more semaphore sets. If SEMMSL=PROCESSES, then only one semaphore set is required.

The maximum # of semaphores that can be allocated will be the lesser of (SEMMSL*SEMMNI) or SEMMNS.

If SEMMSL is not equal to PROCESSES, be sure that the total # of semaphores required (sum of PROCESSES) does not exceed the maximum (SEMMSL*SEMMNI, SEMMNS).

For example, if SEMMSL=25 and SEMMNI=10, total # of semaphores required (sum of PROCESSES) must not exceed 250 (10 semaphore sets * 25 semaphores/set).

Note: some Operating Systems have a maximum # of semaphore sets in the system.

If you have more than one instance and the values of PROCESSES are different, you may want to make SEMMSL equal to the lowest PROCESSES so that you don't allocate semaphores that will not be used. Otherwise, this could prevent you from being able to allocate all of your requirements.

For example:
Instance PROD has PROCESSES=100
Instance DEV has PROCESSES=50

If SEMMSL = 50, 3 semaphore sets will be allocated, 2 for PROD and 1 for DEV.

If SEMMSL = 100, 2 semaphore sets will be allocated, 1 for PROD
and 1 for DEV.In this case, 100 semaphores will be allocated for DEV when it will only use 50. These unused 50 semaphores cannot be allocated for any other databases.

To see what semaphores have been allocated, use the Unix command 'ipcs -b'.

For example:

Semaphores:
T      ID     KEY       MODE        OWNER    GROUP NSEMS
s       0        0   --ra-r-----  osupport      dba    25
s       1        0   --ra-r-----  osupport      dba    25
s      18        0   --ra-r-----  osupport      dba    25
s      19        0   --ra-r-----  osupport      dba    25
s       4        0   --ra-r-----  osupport      dba    25
s       5        0   --ra-r-----  osupport      dba    25

NSEMS=the number of semaphores in each semaphores set.

Perform these steps for each instance that is up and running:

$ svrmgrl
SVRMGR>connect internal
SVRMGR>oradebug ipc

This will show the shared memory segment and semaphore that each instance has attached/in use.

Example output from oradebug ipc command:

  -------------- Shared memory --------------
Seg Id     Address   Size
10250      c1eaf000  4591616
Total: # of segments = 1, size = 4591616
-------------- Semaphores ----------------
Total number of semaphores = 50
Number of semaphores per set = 50
Number of semaphore sets = 1
Semaphore identifiers:
188434

The Seg Id shows 10250 for the shared memory which is attacehed to the RUNNING instance. DO NOT REMOVE THAT ONE.

The Semaphore identifiers shows 188434 for the semaphore which is attacehed to the RUNNING instance. DO NOT REMOVE THAT ONE.

Once you have noted ALL of the identifiers for ALL of the instances which are up and running, compare these id numbers to those in the "ipcs -b" listing.

The entry that does not have a running instance to match is the orphaned entry. THAT ONE SHOULD BE REMOVED.

The command used to remove these entries is: ipcrm

NOTE: The option differs for shared memory and semaphores.

ipcrm -m       <== Use for the Shared Memory entry  ipcrm -s       <== Use for the Semaphore entry

HAPPY LEARNING!

Tune Buffer Cache

Introduction

Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied without going to disk.

If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.

Checking The Cache Hit Ratio

Oracle maintains statistics of buffer cache hits and misses. The following query will show you the overall buffer cache hit ratio for the entire instance since it was started:

     SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
    FROM   v$sysstat P1, v$sysstat P2, v$sysstat P3
    WHERE  P1.name = 'db block gets'
    AND    P2.name = 'consistent gets'
    AND    P3.name = 'physical reads'

You can also see the buffer cache hit ratio for one specific session since that session started:

     SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
    FROM   v$sesstat P1, v$statname N1, v$sesstat P2, v$statname N2,
           v$sesstat P3, v$statname N3
    WHERE  N1.name = 'db block gets'
    AND    P1.statistic# = N1.statistic#
    AND    P1.sid = 
    AND    N2.name = 'consistent gets'
    AND    P2.statistic# = N2.statistic#
    AND    P2.sid = P1.sid
    AND    N3.name = 'physical reads'
    AND    P3.statistic# = N3.statistic#
    AND    P3.sid = P1.sid

You can also measure the buffer cache hit ratio between time X and time Y by collecting statistics at times X and Y and computing the deltas.

Adjusting The Size Of The Buffer Cache

The db_block_buffers parameter in the parameter file determines the size of the buffer cache for the instance. The size of the buffer cache (in bytes) is equal to the value of the db_block_buffers parameter multiplied by the data block size.

You can change the size of the buffer cache by editing the db_block_buffers parameter in the parameter file and restarting the instance.

Determining If The Buffer Cache Should Be Enlarged

If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger.

For example, if you set db_block_lru_extended_statistics to 1000 and restart the instance, you can see how the buffer cache hit ratio would have improved if the buffer cache were one buffer bigger, two buffers bigger, and so on up to 1000 buffers bigger than its current size. Following is a query you can use, along with a sample result:

     SELECT   250 * TRUNC (rownum / 250) + 1 || ' to ' ||
             250 * (TRUNC (rownum / 250) + 1) "Interval",
             SUM (count) "Buffer Cache Hits"
    FROM     v$recent_bucket
    GROUP BY TRUNC (rownum / 250)

    Interval           Buffer Cache Hits
    --------------- --------------------
    1 to 250                       16083
    251 to 500                     11422
    501 to 750                       683
    751 to 1000                      177

This result set shows that enlarging the buffer cache by 250 buffers would have resulted in 16,083 more hits. If there were about 30,000 hits in the buffer cache at the time this query was performed, then it would appear that adding 500 buffers to the buffer cache might be worthwhile. Adding more than 500 buffers might lead to under-utilized buffers and therefore wasted memory.

There is overhead involved in collecting extended LRU statistics. Therefore you should set the db_block_lru_extended_ statistics parameter back to zero as soon as your analysis is complete.

In Oracle7, the v$recent_bucket view was named X$KCBRBH. Only the SYS user can query X$KCBRBH. Also note that in X$KCBRBH the columns are called indx and count, instead of rownum and count.

Determining If The Buffer Cache Is Bigger Than Necessary

If you set the db_block_lru_statistics parameter to true in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$current_bucket. This view will contain one row for each buffer in the buffer cache, and each row will indicate how many of the overall cache hits have been attributable to that particular buffer.

By querying v$current_bucket with a GROUP BY clause, you can get an idea of how well the buffer cache would perform if it were smaller. Following is a query you can use, along with a sample result:

     SELECT   1000 * TRUNC (rownum / 1000) + 1 || ' to ' ||
             1000 * (TRUNC (rownum / 1000) + 1) "Interval",
             SUM (count) "Buffer Cache Hits"
    FROM     v$current_bucket
    WHERE    rownum > 0
    GROUP BY TRUNC (rownum / 1000)

    Interval     Buffer Cache Hits
    ------------ -----------------
    1 to 1000               668415  
    1001 to 2000            281760  
    2001 to 3000            166940  
    3001 to 4000             14770   
    4001 to 5000              7030    
    5001 to 6000               959

This result set shows that the first 3000 buffers are responsible for over 98% of the hits in the buffer cache. This suggests that the buffer cache would be almost as effective if it were half the size; memory is being wasted on an oversized buffer cache.

There is overhead involved in collecting LRU statistics. Therefore you should set the db_block_lru_statistics parameter back to false as soon as your analysis is complete.

In Oracle7, the v$current_bucket view was named X$KCBCBH. Only the SYS user can query X$KCBCBH. Also note that in X$KCBCBH the columns are called indx and count, instead of rownum and count.

Full Table Scans

When Oracle performs a full table scan of a large table, the blocks are read into the buffer cache but placed at the least recently used end of the LRU list. This causes the blocks to be aged out quickly, and prevents one large full table scan from wiping out the entire buffer cache.

Full table scans of large tables usually result in physical disk reads and a lower buffer cache hit ratio. You can get an idea of full table scan activity at the data file level by querying v$filestat and joining to SYS.dba_data_files. Following is a query you can use and sample results:

     SELECT   A.file_name, B.phyrds, B.phyblkrd
    FROM     SYS.dba_data_files A, v$filestat B
    WHERE    B.file# = A.file_id
    ORDER BY A.file_id

    FILE_NAME                            PHYRDS   PHYBLKRD
    -------------------------------- ---------- ----------
    /u01/oradata/PROD/system01.dbf        92832     130721
    /u02/oradata/PROD/temp01.dbf           1136       7825
    /u01/oradata/PROD/tools01.dbf          7994       8002
    /u01/oradata/PROD/users01.dbf           214        214
    /u03/oradata/PROD/rbs01.dbf           20518      20518
    /u04/oradata/PROD/data01.dbf         593336    9441037
    /u05/oradata/PROD/data02.dbf        4638037    4703454
    /u06/oradata/PROD/index01.dbf       1007638    1007638
    /u07/oradata/PROD/index02.dbf       1408270    1408270

PHYRDS shows the number of reads from the data file since the instance was started. PHYBLKRD shows the actual number of data blocks read. Usually blocks are requested one at a time. However, Oracle requests blocks in batches when performing full table scans. (The db_file_multiblock_read_count parameter controls this batch size.)

In the sample result set above, there appears to be quite a bit of full table scan activity in the data01.dbf data file, since 593,336 read requests have resulted in 9,441,037 actual blocks read.

Spotting I/O Intensive SQL Statements

The v$sqlarea dynamic performance view contains one row for each SQL statement currently in the shared SQL area of the SGA for the instance. v$sqlarea shows the first 1000 bytes of each SQL statement, along with various statistics. Following is a query you can use:

     SELECT   executions, buffer_gets, disk_reads,
             first_load_time, sql_text
    FROM     v$sqlarea
    ORDER BY disk_reads

EXECUTIONS indicates the number of times the SQL statement has been executed since it entered the shared SQL area. BUFFER_GETS indicates the collective number of logical reads issued by all executions of the statement. DISK_READS shows the collective number of physical reads issued by all executions of the statement. (A logical read is a read that resulted in a cache hit or a physical disk read. A physical read is a read that resulted in a physical disk read.)

You can review the results of this query to find SQL statements that perform lots of reads, both logical and physical. Consider how many times a SQL statement has been executed when evaluating the number of reads.

Conclusion

This brief document gives you the basic information you need in order to optimize the buffer cache size for your Oracle database. Also, you can zero in on SQL statements that cause a lot of I/O, and data files that experience a lot of full table scans.

HAPPY LEARNING!

How Oracle Database Uses RAM ?

The goal of server optimization for any Oracle databases is to manage the RAM and CPU resources of the machine, and make sure that expensive RAM is not under-allocated. When we talk about optimizing Oracle database performance in an MS-Windows environment, the techniques that we use are very similar to those used on larger UNIX platforms.

In my experience as an Oracle consultant, I see millions of dollars' worth of RAM being wasted by Oracle shops. Because the Oracle DBA does not know how to accurately compute the RAM demands of the database, they under-allocate the RAM. On larger servers, RAM is still very expensive and depreciates regardless of use. The savvy Oracle professional knows how to accurately predict the high-water mark of RAM demands for their database, and fully allocates the RAM, reserving only enough to accommodate spikes in user connections.

Let's take a look at each of these activities, using Oracle on an MS-Windows environment as an example.

RAM and Virtual Memory

On all platforms, we need to ensure that the RAM processing demands of the Oracle database do not exceed the real RAM memory of the server. As we may know, all large servers use a Virtual Memory (VM) scheme to allow sharing of RAM resources. Oracle servers (Windows, UNIX, OS390) have a special swap disks to manage excessive RAM demands. Virtual memory is an internal "trick" that relies on the fact that not every executing task is always referencing its RAM memory region. Since all RAM regions are not constantly in use, vendors have developed a paging algorithm that move RAM memory pages to the swap disk when it appears that they will not be needed in the immediate future.

In order to provide for the sharing of RAM, a special area of disk called a swap disk is required, and the primary purpose of the swap disk is to hold page frames from in active programs on disk. The purpose of the swap disk is to offload the least-frequently-used (LRU) RAM page frames so that many applications can concurrently share the same memory. Once RAM pages from inactive programs are written to disk (a page-out), the operating system can make the freed RAM memory available for another active task. Later, when the inactive program resumes execution, the RAM pages are re-loaded from the swap disk into RAM (a page-in). This reloading of RAM pages is called swapping, and swapping is very time-consuming and degrades the performance of the target program.

While having the swap disk ensures concurrent RAM usage above the real amount of RAM, optimal performance requires that the swap disk is never used for active programs. This is because reading RAM pages off of the swap disk is about 14,000 times slower than reading the memory pages from directly from RAM. As we know, disk access is measured in milliseconds, or millionths of the second, while RAM access is access to in nanoseconds, or billionths of a second.

In a VM architecture, the OS will write Ram to the swap disk, even thought the real RAM has not been exceeded. This is done in anticipation of a RAM shortage, and if a real RAM shortage occurs, the LRU RAM frames are already on the swap disk.

For an Oracle server, the goal is to keep all of the RAM memory demands of the database and database connections beneath the amount of physical RAM memory. In an Oracle environment, we can accurately control the amount of RAM memory that is used by the database instance System Global Area (SGA). This is because the Oracle database administrator can issue an alter system command to change the RAM memory areas, and can grow and shrink the RAM memory areas on as-needed basis.

We can see the allocated size of the SGA in the Oracle alert log, and it is also displayed on the console when Oracle is started as shown in listing 1.

SQL> startup ORACLE instance started. Total System Global Area 143421172 bytes Fixed Size 282356 bytes Variable Size 117440512 bytes Database Buffers 25165824 bytes Redo Buffers 532480 bytes Database mounted. Database opened.

Listing 1: Starting an Oracle database.

We can also see the SGA RAM region by issuing the show sga command. In the example below we see that our total SGA size is 143 megabytes (refer to listing 2).

SQL> connect system/manager as sysdba Connected. SQL> show sga Total System Global Area 143421172 bytes Fixed Size 282356 bytes Variable Size 117440512 bytes Database Buffers 25165824 bytes Redo Buffers 532480 bytes

Listing 2: Using the show sga command.

Next, let's see how we can quickly find the amount of RAM on our server.

Determining the RAM on Your Server

On most Oracle servers you can issue a few commands to see the amount of RAM. Let's look at a few examples.

RAM on UNIX Servers

On each UNIX dialect, there are specific commands that are required to display the RAM usage.

Dialect of UNIX	RAM memory display command
DEC-UNIX	uerf -r 300 | grep -i mem
Solaris	prtconf|grep -i mem
AIX	lsdev -C|grep mem
Linux	free
HP/UX	swapinfo -tm

RAM on IBM-AIX UNIX

In the IBM AIX dialect of UNIX, we have a two-step command to display the amount of available RAM memory. We start with the lsdevlsdev command produces a large listing of all devices, but we can pipe the output from lsdev to the grep command to refine the display to show only the name of the device that has the RAM memory. command to show all devices that are attached to the UNIX server. The

root> lsdev -C|grep mem

mem0       Available 00-00           Memory

Here we see that mem0 is the name of the memory device on this AIX server. Now we can issue the lsattr -El command (passing mem0 as an argument) to see the amount of memory on the server. Below we see that this server has 2 gigabytes of RAM memory attached to the mem0 device.

root> lsattr -El mem0
size     2048 Total amount of physical memory in Mbytes  False
goodsize 2048 Amount of usable physical memory in Mbytes False

RAM in Linux

In Linux, see RAM is easy. The free command can be used to quickly display the amount of RAM memory on the server.

root> lsattr -El mem0

size     2048 Total amount of physical memory in Mbytes  False
goodsize 2048 Amount of usable physical memory in Mbytes False

RAM on MS-Windows

To see how much RAM you have on your MS-Windows server, you can go to start settings control panel system, and click on the "general" tab (refer to figure 1). Here we see that this server has 1,250 megabytes of RAM.

Figure 1: The MS-windows system display screen.

Now that we know how to tell the size of our MS-Windows RAM and the size of the SGA, we have to consider the RAM usage for Oracle connections.

Reserving RAM for Database Connections

The Oracle DBA can use math to determine the optimal RAM allocation for a MS-Windows server. For the purposes of this example, let's assume that we are on a dedicated MS-Windows Oracle server, and Oracle will be the only program running on the server.

The total RAM demands for Oracle on MS-Windows are as follows:

• OS RAM — 20 percent of total RAM for MS-Windows, 10% of RAM for UNIX
• Oracle SGA RAM — determined with the show sga command
• Oracle database connections RAM — Each Oracle connection (when not using the Oracle multi-threaded server) will use two megabytes of RAM plus sort_area_size plus hash_area_size.

Once we know the total available RAM memory, we have to subtract 20 percent from this value for MS-Windows overhead. Even in an idle state, Windows services use RAM resources, and we must subtract 20% to get the real free RAM on an idle server.

Finding the High-water Mark of User Connections

Once we know the amount of available RAM for Oracle, we must know the high-water mark (HWM) for the number of Oracle connections. For systems that are not using Oracle's multithreaded server architecture, each connected session to the Windows server is going require an area of memory for the program global area, or PGA.

There is no easy way to determine the high-water mark of connected Oracle sessions. If you use Oracle STATSPACK you can get this information from the stats$sysstat table, but most Oracle DBAs make a generous guess for this value.

Determining the PGA Size

In our example, we have 1,250 megabytes of RAM memory on our MS-Windows server, and less 20 percent, we wind up with the total available allocation for Oracle of 750 MB.

The size for each PGA RAM region is computed as follows:

• OS Overhead - We reserve 2 meg for Windows and 1 meg for UNIX
• Sort_area_size parameter value - This RAM is used for data row sorting inside the PGA
• Hash_area_size parameter value - This RAM defaults to 1.5 time sort_area_size, and is used for performing hash joins of Oracle tables.

We can use the Oracle show parameters command to quickly see the values for sort_area_size and hash_area_size (refer to listing 3).

SQL> show parameters area_size NAME TYPE VALUE ------------------------------------ ----------- --------- bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 hash_area_size integer 1048576 sort_area_size integer 524288 workarea_size_policy string MANUAL

Listing 3: Display PGA area sizes.

Here we can see the values for sort_area_size and hash_area_size for our Oracle database. To compute the value for the size of each PGA RAM region, we can write a quick data dictionary query against the v$parameter view (refer to listing 4).

set pages 999; column pga_size format 999,999,999 select 1048576+a.value+b.value pga_size from v$parameter a, v$parameter b where a.name = 'sort_area_size' and b.name = 'hash_area_size' ;

Listing 4: A dictionary query to compute PGA size.

The output from this data dictionary query shows that every connected Oracle session will use 3.6 megabytes of RAM memory for the Oracle PGA.

PGA_SIZE
------------
 3,621,440

Now, if we were to multiply the number of connected users by the total PGA demands for each connected user, we will know exactly how much RAM memory in order to reserve for connected sessions.

Getting back to our example, let's assume that we have a high-water mark of 100 connects sessions to our Oracle database server. We multiply 100 by the total area for each PGA memory region, and we can now determine the maximum size of our SGA:

Total RAM on Windows Server 1250 MB
Less:
Total PGA regions for 10 users: 362 MB
RAM reserved for Windows (20 percent) 500 MB
----------
862 MB

Hence, we would want to adjust the RAM to the data buffers in order to make the SGA size less than 388 MB. Any SGA size greater than 388 MB, and the server will start RAM paging, adversely affecting the performance of the entire server. The final task is to size the Oracle SGA such that the total memory involved does not exceed 388 MB.

Remember, RAM is an expensive server resource, and it is the job of the DBA to fully allocate RAM resources on the database server. Un-allocated RAM wastes expensive hardware resources, and RAM depreciates regardless of usage.

As a review, the size of an Oracle SGA is based upon the following parameter settings:

• shared_pool_size — This sizes the administrative RAM for Oracle and the library cache.
• db_cache_size — This parameter determines the size of the RAM for the data buffers
• large_pool_size — The size of the Java pool
• log_buffer — The size of the RAM buffer for redo logs

In general, the most variable of these parameters is db_cache_size. Because Oracle has an almost insatiable appetite for RAM data buffers, most DBAs add additional RAM to the db_cache_size.

A Script for Computing Total PGA RAM

In SQL*Plus, you can accept a parameter and then reference it inside your query by placing an ampersand in front of the variable name. In the simple example below, we declare a variable called myparm and direct SQL*Plus to accept this value when the script is executed:

set heading off
set echo on

accept myparm number prompt 'Choose a number between 1 and 10: '

select 'You chose the number '||&myparm from dual;

Our goal is to create a script called pga_size.sql. This script will prompt you for the high-water mark of connected users, and then compute the sum of all PGA RAM to be reserved for dedicated Oracle connections. In this example, we have a 2-meg overhead for MS-Windows PGA sessions.

Here is the finished script:

set pages 999;

column pga_size format 999,999,999

accept hwm number prompt 'Enter the high-water mark of connected users: '

select
  &hwm*(2048576+a.value+b.value) pga_size
from
 v$parameter a,
 v$parameter b
where
 a.name = 'sort_area_size'
and
 b.name = 'hash_area_size'
;

When we run the script, we see that we are prompted for the HWM, and Oracle takes care of the math needed to compute the total RAM to reserve for Oracle connections.

SQL> @pga_size


Enter the high-water mark of connected users: 100

old   2:     &hwm*(2048576+a.value+b.value) pga_size
new   2:            100*(2048576+a.value+b.value) pga_size


  PGA_SIZE
------------
362,144,000

Now that we understand sizing RAM regions for Oracle on Windows, let's look at how we can examine the RAM used by Windows during Oracle activities.

Monitoring Server Resources in MS-Windows

In MS-Windows we can use the performance manager screen to observe the resource consumption of the Oracle Windows server (refer to figure 2). The performance manager is hidden deep inside the Windows menus, but can be found by following start > settings > control panel > administrative tools > performance.

Figure 2: The MS-Windows server performance monitor.

The MS-Windows performance monitor plots three metrics:

• Green (CPU) - This is the percentage of CPU resources consumed
• Yellow (RAM) - This is the number of RAM pages per seconds used
• Blue (DISK) - This is the disk I/O queue length percentage

Let's take a closer look at the MS-Windows performance monitor. figure 2 is a time-based snapshot of an Oracle databases resource consumption at startup time. These lines form signatures (known usage patterns) that reveals some interesting patterns inside Oracle:

1. RAM Usage — The yellow line is RAM usage, and we see the first spike in the RAM when the SGA is allocated and a short spike in RAM as the database is mounted.
2. DISK Usage — The blue line is the disk I/O, and we see the disk I/O activity peg at the point where we mount the database. This is because Oracle must touch every data file header to read the system change number (SCN).
3. CPU Usage — The green line is CPU and it is interesting to note that the CPU never goes above 50 percent during Oracle database startup.

Conclusion

In sum, the allocation of RAM memory for an Oracle server can be done solely with mathematics, and no expensive performance monitors are required. The most difficult part of Oracle RAM optimization in any environment is accurately predicting the high-water mark of connected user sessions. If we have an unexpected spike of connected sessions, it is possible that we would exceed the amount of RAM on the server, causing active programs RAM regions to go out to the swap disk. The goal is to fully allocate RAM without ever experiencing RAM paging.

HAPPY LEARNING!

Understanding JDBC Connections From the eBusiness Middle Tier

In this article I will describe the basics of configuring and monitoring JDBC connections between the eBusiness Web Tier and the Database, then cover some common issues to help identify root causes for both Release 11i and Release 12 of eBusiness Suite.

Brief overview

In general, whenever a functional page requires data from the database, it makes a call to a lower code level (the Java layer in the Application Object Library, also known as AOL/J) which handles the database connectivity. The AOL/J code provides a JDBC connections to the database through the Database Connection Pool.

You should note that the Java Connection Pool mechanism for eBiz is completely provided by eBiz code and does not use the mechanisms provided to pool connections through Java language directly, nor through the Application Server configuration.

Configuring JDBC connection pooling

The JDBC connection pool is created using the settings in the DBC file. The name, location and contents of this DBC file is controlled through AutoConfig. To modify any of the JDBC connection pool parameters, you should therefore use the techniques described in the following document to ensure changes are maintained in a supportable and consistent way:

• Using AutoConfig to Manage System Configurations with Oracle Applications 11i (Metalink Note 165195.1)

The "s_dbc_file_name" variable in the CONTEXT.xml file provides the dbc filename and is located in the $FND_TOP/admin/<>_ directory.

JDBC connection pool parameters and their use are covered in the following documents:

• Diagnosing and tuning AOL/J JDBC Pool in Oracle e-Business Suite 11i (Metalink Note 278868.1)
• The "Application Module and Connection Pooling" chapter in:
  • Release 11i : Oracle Application Framework Developer's Guide (Metalink Note 269138.1)
  • Release 12 : Oracle Application Framework Developer's Guide (Metalink Note 394780.1)

When considering any changes, you should also take into account that every JVM has its own JDBC connection pool. For example, if you have one Web Node with three OACoreGroup JVMs plus one XmlSvcsGrp JVM configured, then you will have a total of four JDBC connection pools with connections to your eBiz database

Monitoring the JDBC connection pool

It is always a good idea to understand how your environment looks when things are going well, to give you a baseline to compare against if you need to investigate any issues.

You will most certainly need to review the JDBC connection data if you are experiencing issues.

Monitoring JDBC Connections through Oracle Applications Manager (OAM)

Login to OAM directly or via the "System Administration" responsibility.

1. Select the "JServ Usage" under the Monitoring section in OAM
2. Click the "Expand all" link to list the Servers and all the JServ processes for OACoregroup. This shows memory usage, connections (including "Potentially Leaked") and Application Module information. You can click the "Add to support cart" to capture this page if Oracle Support are assisting your investigation.
3. If there are any non zero values for "Potentially Leaked" then click on this number to drill down into the details
4. Select "Yes" for the "Filter by Potentially Leaked" option and click "Go" button
5. Click "Show all details" to display the Java Thread dump for all the potentially leaks Java connections

The "old" way of gathering this data was to use the URL http://host.domain:port/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp but this will only give data for the one JVM you happen to connect to, so may not be so useful in multi-JVM environments.

Run SQL scripts to monitor database connections

Using SQL scripts will not give so much information as OAM, but can be used to provide useful summary information on a periodic basis. For example you may wish to include the information from the SQL below as part of your baseline data:

REM
REM START OF SQL
REM
REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
/
REM
REM END OF SQL
REM

NOTE - when looking at V$SESSION, the STATUS of JDBC connections tend to show as INACTIVE, this is normal and does not indicate a specific problem

Where Could It Go Wrong?

Issues with the JDBC connection pool tend to be of a nature whereby the number of database connections increase over time and do not seem to be released again. If this continues unchecked, then you may find the database will run out of sessions/processes and/or the Middle Tier JVM will run out of connections or memory.

A "quick fix" would normally be to restart Apache, but the underlying cause would need to be investigated further.

Issues can potentially occur at five different levels:

1. Core Java Code
2. AOL/J JDBC Code
3. OA Framework
4. Functional Code
5. External Influences

I'll discuss each of these areas next.

Core Java code

Although eBiz does not use native Java connection pooling methods, we do rely on the underlying Java APIs generally. Any issues at this level will generally require the latest Java version to be installed

If you need to upgrade your Java version, see:

• Overview of Using Java with Oracle E-Business Suite Release 11i (Metalink Note 300482.1) if you need to upgrade your Java version

AOL/J JDBC code

As this is the code that handles JDBC connection, it is often the first area to be blamed, but the JDBC connection pool can only drop database connections where the calling application has released the JDBC connection it holds in the pool, so it often turns out to be an issue higher up the code stack.

Number of JDBC connections increase after ATG Rup 5 because jdbc parameters are lower case (Metalink Note 459072.1) describes a known issue with Apps 11i.

It is prudent to be on the latest JDBC driver patch, but should have at least applied one of the following patches:-

• Patch 4282857: REHOSTING ORACLE JDBC THIN DRIVERS V9.2.0.6+ FOR USE WITH APPS 11I
• Patch 4201222: REHOSTING ORACLE JDBC THIN DRIVERS V9.2.0.6 FOR USE WITH APPS 11I

The latest JDBC patch can be found in:

• Upgrading Oracle JDBC Drivers with Oracle E-Business Suite 11i (Metalink Note 164317.1)

You should note that the JDBC driver version has no relation to the Database version, as it is installed on the eBiz Middle Tier. For example, the latest version of JDBC drivers provided by patch 4899697 (9.2.0.8) is the same patch for all RDBMS versions.

OA Framework (OAF)

OA Framework calls AOL/J when it needs a database connection, and it is up to OAF to release any such connection when it has finished with it. There is an added complexity, in that OAF also has its own pooling mechanism for the OAF Pages, which is the "Application Module pool" (AM pool). This means that although a user may have finished with a page, the page and its associated database connection are retained for a period of time.

The AM pool is controlled by profile options, which are described in :-

• Oracle Application Framework Profile Options Release 11i (Metalink Note 275876.1)
• Oracle Application Framework Profile Options Release 12 (Metalink Note 395445.1)

Issues at this code level would tend to be either:

1. Issue with AM Pooling

You can test the effect of disabling AM pooling by setting the profile option "FND: Application Module Pool Enabled" to "No". Use this technique with caution if considering this for a production environment.

2. Specific bug where database connection is not released.

This would generally require patching.

Functional Code

Issues at this code level would tend to be a specific bug where a connection is not released.

External influences

Firewall timeouts are known to potentially cause an issue for JDBC connections. For details, see:

• Firewall BLACKOUT and JDBC connections with Oracle Applications 11i(Metalink Note 276557.1)

Although this note is for Apps 11i, the technical issue can also apply to Release 12 as well

Configuring eBiz to minimize JDBC connections

If investigating issues with JDBC connections increasing, it may be useful to minimise the database connections as much as possible by de-tuning the JDBC pool. This may reduce end user performance, so should be used with caution if considering this for a production environment.

To do so, you need to do both these steps:

1. Disable Application Module (AM) Pooling

This is necessary as the AM objects hold a JDBC connection whilst they are alive, even if they are not currently used.

Set the profile option "FND: Application Module Pool Enabled" (AMPOOL_ENABLED) at SITE level to a value of "No"

2. Set the JDBC connection pool parameters to release connections:

FND_JDBC_BUFFER_DECAY_INTERVAL=180
FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_JDBC_BUFFER_DECAY_SIZE=50

Conclusion

Identifying issues with JDBC connections can sometimes be a frustrating process, as the investigations may need to consider multiple failure points and complex architectures. I hope this article has given you a better understanding of JDBC Pooling and where to start looking for issues.

References

• Number of JDBC connections increase after ATG Rup 5 because jdbc parameters are lower case (Metalink Note 459072.1)
• Diagnosing and tuning AOL/J JDBC Pool in Oracle e-Business Suite 11i (Metalink Note 278868.1)
• Basic troubleshooting of JVM consuming cpu or too many JDBC connections in Apps 11i (Metalink Note 370583.1)
• Release 11i : Oracle Application Framework Developer�s Guide (Metalink Note 269138.1)
• Release 12 : Oracle Application Framework Developer�s Guide (Metalink Note 394780.1)
• Oracle Application Framework Profile Options Release 11i (Metalink Note 275876.1)
• Oracle Application Framework Profile Options Release 12 (Metalink Note 395445.1)

Related

• Preventing Apps 11i Performance Issues in Four Steps
• Debugging General Performance Issues with Oracle Apps

HAPPY LEARNING!