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 ;-)