Guidelines for Application & DB Tuning

Guidelines for Application & DB Tuning

1.Ensure  the initialization parameters for eBusiness suite are set correctly.
2.Make sure Gather Shema Stats is running on periodical basis

- Preferred method is GATHER_AUTO
The option 'GATHER_AUTO' can be used, to gather statistics only on objects that have changes above the specified 'Modification Threshold' (percentage of DML compared to the number of rows of the table).

SQL> exec fnd_stats.gather_schema_statistics('BEN'); -> Paritcular schema
SQL> exec fnd_stats.gather_schema_statistics('ALL'); -> For All schemas
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES'); -> Paritcular schema's table

3. Enable ASMM(Automatic shared memory management)

SGA_TARGET to value other than 0 ( Oracle controls the memory pools and para's can be altered to max size of SGA_MAX_SIZE )

If SGA_TARGET not set. Then pools are set by para's

DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE

- If still U r facing issue then AWR/ STATSPACK report may help U

In AWR report look for TOP 5 Timed Events

Event                              Waits Time(s)     Avg Wait(ms) % Total Call Time  Wait Class
db file sequential read          
db file scattered read          
enq: TX - row lock contention    
CPU time                            

db file sequential read -  possible cause of db file sequential read is poorly tuned SQL

> Need to investigate SQL ordered by Reads
> Check is tht SQL belong to Standard or Custom Modules. Based on tht need to check with Oracle / Clients Development teams.

-- Its a Good Approach to have Statspack report when performance is good such that..it forms a benchmark to compare things

CPU time

> Check for SQL ordered by CPU Time

Commonly Observed Wait Events

- db file sequential read ( I/O SQL tuning - Review AWR/Statspack Top SQL ordered by reads )

> The session has issued an I/O request to read one block from a data
file into buffer cache and is waiting for the operation to complete.
This typically happens during an index lookup or fetch from a table by ROWID when
the required data block is not already in memory.

- db file scattered read ( I/O SQL tuning - Review AWR/Statspack Top SQL ordered by gets and by reads.Segments by Physical Reads.)

> The session has issued an I/O request to read a series of contiguous blocks from a data file Into the  buffer cache and is waiting for the operation to complete. This typically happens during full table scan or fast full index scan.

- Buffer busy waits ( Buffer cache/DBWR - Review segment statistics by buffer busy waits )
- Library cache ( shared pool/latches - Review AWR/Statspack SQL ordered by Parse Calls )
- Enque waits (enq:) ( Locks - Review the following sections: enqueue,  row lock & ITL waits )

Best practices for tuning performance

DB :

- Apply all the recommended perf patches ( note  244040.1  )
- Convert to OATM

Application :

- Deploy with socket mode for internal users ( For R12 ref Note 384241.1.)
- Set FORMS_TIMEOUT=10 and Dont set FORMS_CATCHTERM
- Use one JVM per 2 CPUs
• No more than one JVM/CPU
• No more than 100 concurrent users per JVM


Ref  : Tuning performance on eBusiness suite [ID 744143.1]

UR's Key Run ;-) 

OC4J Tuning

Sizing of the Java memory regions :


Heap Sizing :

The maximum heap size of a Java application is limited by three factors:

1) Process data model (32-bit or 64-bit) and the associated operating system limitations
2) Amount of virtual memory available on the system and
3) Amount of physical memory available on the system.

The size of the Java heap for a particular application can never exceed or even reach the maximum virtual address space of the process data model.
In most cases, using heap sizes greater than 2gig is not recommended for web based applications unless you use a lot of large objects. Using a larger heap size will affect performance due to the garbage collection process taking too long for larger heap sizes. It is very important to test your application if you choose to use a heap size over 2gig when it is available. The test should include a large load so that full garbage collection is performed and data can be collected for the affect this will have on the performance.

Garbage Collection Policies:

The -XX:+UseParallelGC parallel (throughput) garbage collector, or
The -XX:+UseConcMarkSweepGC concurrent (low pause time) garbage collector (also known as CMS)
The -XX:+UseSerialGC serial garbage collector (for smaller applications and systems)


UR's Key Run ;-) 

JVM Taking High CPU / toooooooo many JDBC connections ( 11i )

JVM Taking High CPU / toooooooo many JDBC connections ( 11i )

- Don't change the JDBC default para's unless specified:

FND_MAX_JDBC_CONNECTIONS
FND_JDBC_BUFFER_MIN
FND_JDBC_BUFFER_MAX
FND_JDBC_BUFFER_DECAY_INTERVAL
FND_JDBC_BUFFER_DECAY_SIZE
FND_JDBC_USABLE_CHECK
FND_JDBC_CONTEXT_CHECK
FND_JDBC_PLSQL_RESET

FND_MAX_JDBC_CONNECTIONS - The maximum pool size. This is the sum of the number of available connections and the number of locked connections.

This needs to be changed only after monitoring locked connections in the "AOL/J Database Connection Pool Status"

Leaked connections ( Stack in Red ) : Threads that died without releasing the connection. ( If this is few in number then not a concern )
Stack in black : Connections that were checked out of the pool and are not available to be used by new JVM sessions

- Check JDBC Connections from the DB

select count(*), module from v$session
where program like '%JDBC%' group by module order by 1 asc;

or

From OAM

Set FND: Diagnostics should be to "Yes".
Site Map > Select the Monitoring tab > Jserv usage

or

From the Application: Click on the Diagnostics link > Select Show Pool Monitor

- Have the latest JDBC drivers installed

- Check JVM is sized properly ( Heap Size nd GC - Garbage Collection )

Ref ID : 370583.1


UR's Key Run ;-) 

Garbage Collection What ? Y ? How ?

When a Java object becomes unreachable to the program, then it is subjected to garbage collection. The main use of garbage collection is to identify and discard objects that are no longer needed by a program so that their resources can be reclaimed and reused.



Montior GC in OACoreGroup.X.stdout log files to tune the JVM heaps (-Xmx and –Xms) accordingly.

Log entries when running with –XX:+PrintGCTimeStamps option are:

52.250: [Full GC 100053K->34603K(519488K), 2.9323533 secs]
380.674: [GC 188843K->39516K(519488K), 0.4440873 secs]
615.363: [Full GC 37508K->37422K(519488K), 1.7572831 secs]
701.094: [GC 191662K->40146K(519488K), 0.1742931 secs]

T1 : [ Full GC A->B(C), T2 secs ]
T3 : [ GC P->Q(R), T4 secs ]

T1 - Timestamp in seconds, since the JVM started
T3-T1 - Gives number of seconds between garbage collections
A  - Heap size before the GC
B  - Size after the GC was executed
(C) - Current heap size

How to Analyse :

Minor GCs should be spread out far enough so most objects have died and can be collected.
Frequent GCs indicate that you may need to increase your heap size.


UR's Key Run ;-)