Performance Tuning Factors (Application & Database)

V$ACCESS   This view shows objects in the database that are currently locked and the sessions that are accessing them

V$ACTIVE_INSTANCES  This view maps instance names to instance numbers for all instances that have the database currently mounted.

V$ARCHIVE  This view contains information on archive logs for each thread in the database system. Each row provides information for one thread.

V$BGPROCESS  This view describes the background processes.

Concurrent managers tuning

-PMON, QUEUE SIZE, SLEEP TIME
- Purging Concurrent Request

-adjusting concurrent manager cache size

-Analyzing Oracle Apps Dictionary Tables for High Performance

PMON cycle->waits between the time it checks for failed managers

QUEUE Size->no. of PMOn cycles the ICM waits to check for new CM or the disabled CM's

SLEEP Time-> no. of sec the ICM waits for the checking the request to be run

Purging concurrent request

Purging these two tables

-> FND_CONCURRENT_PROCESS
-> FND_CONCURRENT_REQUESTS
( should not exceed 50k )

Gather table stat for

FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.
Purge obsolete workflow runtime data as sysadmin


##gather schema stat

as sysadmin ->run-> gather schema stat%
as sysadmin ->run-> gather table stat%

form issues -->  help -> diag->trace with binds
concurrent manager issues->
profile option -> allow debugging-> set profile yes


Database Tuning

Wait Events for 11g

v$session
v$waitclassmetric
v$waitclassmetric_history
v$waitstat
v$wait_chains

Oracle 11g Concurrency and SQL Tuning

v$lock
v$sql
v$sqlarea
v$sesstat
v$mystat
v$sess_io
v$sysstat
v$statname
v$osstat
v$active_session_history
v$active_sess_pool_mth
v$session_wait
v$session_wait_class
v$system_wait_class
v$transaction
v$locked_object
v$latch
v$latch_children
v$latch_parent
v$latchname
v$latchholder
v$latch_misses
v$enqueue_lock
v$transaction_enqueue
v$sys_optimizer_env
v$ses_optimizer_env
v$sql_optimizer_env
v$sql_plan
v$sql_plan_statistics
v$sql_plan_statistics_all