Application Modules info ( short name and id ) and identifying Patch Level apps

Use the following query to retrieve this information on your specific instance.

SELECT FND.APPLICATION_ID, APPLICATION_SHORT_NAME, PRODUCT_CODE, APPLICATION_NAME
FROM FND_APPLICATION FND , FND_APPLICATION_TL FNDTL WHERE FND.APPLICATION_ID=FNDTL.APPLICATION_ID


How to find an AD patchset level in Oracle Applications


SQL> select PATCH_LEVEL from fnd_product_installations where APPLICATION_ID=50;

select APP_SHORT_NAME, PATCH_LEVEL from apps.AD_PATCH_DRIVER_MINIPKS where APP_SHORT_NAME like '%PF%';

Views for DBA

V$views

v$archive_dest
v$archive_dest_status
v$archive_gap
v$archive_processes
v$archived_log
v$bh
v$buffer_pool
v$buffer_pool_statistics
v$controlfile
v$controlfile_record_section
v$database
v$datafile
v$datafile_header
v$dataguard_status
v$db_object_cache
v$enqueue_stat
v$event_name
v$eventmetric
v$filemetric
v$filestat
v$fixed_table
v$fixed_view_definition
v$instance
v$latch
v$librarycache
v$lock
v$locked_object
v$log
v$log_history
v$logfile
v$logmnr_contents
v$logstdby
v$managed_standby
v$mystat
v$nls_parameters
v$nls_valid_values
v$object_usage
v$open_cursor
v$option
v$parameter
v$pgastat
v$process
v$pwfile_users
v$recover_file
v$reserved_words
v$resource_limit
v$rollname
v$rollstat
v$session
v$session_event
v$session_longops
v$session_wait
v$session_wait_history
v$sessmetric
v$sesstat
v$sga
v$sga_dynamic_components
v$sga_resize_ops
v$sgastat
v$sort_segment
v$sort_usage
v$spparameter
v$sql
v$sql_bind_capture
v$sql_bind_data
v$sql_cursor
v$sql_plan
v$sql_text_with_newlines
v$sql_workarea
v$sqlarea
v$sqltext
v$sqltext_with_newlines
v$standby_log
v$statname
v$sysaux_occupants
v$sysmetric
v$sysmetric_history
v$sysstat
v$system_event
v$tempfile
v$tempseg_usage
v$tempseg_usage
v$tempstat
v$thread
v$timer
v$timezone_names
v$transaction
v$transportable_platform
v$undostat
v$version
v$waitstat

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