Gather Financial APPS info

-- For patch information
set pages 1000
set lines 120
column application_name format a50
select a.application_name,
decode(b.status,'I','Installed','S','Shared','N/A') STATUS, PATCH_LEVEL from
APPS.fnd_application_vl a, APPS.fnd_product_installations b where
a.application_id = b.application_id order by 2,1;

--- for URL
select to_char(a.profile_option_id) id,b.profile_option_name,a.profile_option_value
from fnd_profile_option_values a, fnd_profile_options b
where a.profile_option_id=b.profile_option_id and profile_option_value like '%http%';

---for number of nodes
select concurrent_queue_name,target_node,node_name from fnd_concurrent_queues;
select distinct target_node from fnd_concurrent_queues;
select node_name, support_cp,support_forms,support_admin,support_web status from fnd_nodes;

-- node information
select concurrent_queue_name,target_node,node_name from fnd_concurrent_queues;
select distinct target_node from fnd_concurrent_queues;
select node_name, support_cp,support_forms,support_admin,support_web status from fnd_nodes;

Useful File and Filesystem Commands

directory name
dirname /x/y/z/

removing extension from file
$ basename /this/is/a/file.txt .txt

List subdirectores sorted by size
du -k | sort -n

List file sorted by size
ls -l | sort +4n

only in linux :
file size in K,M : ls -lh
files sorted by size : ls -lS



symbols along with file type
ls -F

List just the directory
ls -ld dirname

vmstat

vmstat 1 16 :
Legend:

- kthr = kernel thread - state change (the columns selected are of interest here)
- r = run-queue - number of tasks executing and consuming CPU resources - average per interval
- b = blocked-queue - number of tasks waiting (blocked ) for CPU resources - average per interval
- pi = pages-in - memory contents read to memory from disk (where it was paged out) - avg per second
- po = pages-out - memory contents written from memory to disk (non-zero is NORMAL) - avg per second
- wa = waiting-CPU - percentage of CPU time waiting for external operations (I/O) to finish - avg per interval

A few simple rules of thumb tell us that, when busy, the system is:

MEMORY-BOUND: - the paging size (in MB) > SGA, or non-zero values in the pi column, or page percent too high
CPU-BOUND: - the average values in columns r and b > # of CPU's, or load max > load coef (e.g., 10)
I/O-BOUND: - there are values in the wa column > 20% (up to max 40 percent by some authors)
RUNS-AT-FULL-CAPACITY: - the sum of us and sy approach 100% and id and wa are very small

AIX Handy Commands

CPU
lsdev -C|grep Process|wc -l

Memory
lsattr -El mem0

Swap
lsps -s

sar
sar -q -f /var/adm/sa/sa19|pg #sa19 means date 19th of the month

Process Management
truss -a -e -f -rall -wall -p mypid # mypid means pid number

More info on truss

SQL Performance related Dynamic Views

What is my sesison doing

steps
1. find sid of the session from v$session
2. check v$Session_wait for last wait activity
3. check v$session_event for commuvative waits
4. check v$sesstat for resource usage stats


from where and what



my sid v$mystat
rownum=1

others sid v$session ,v$process

what's up v$sesstat v$statname v$sess_io v$session_wait
CPU used by this session

which segment v$sesion_wait
buffer bust waits db file sequential read db file scattered read free buffer waits

which latch v$session_wait v$latchname
latch free


which sql v$sqltext v$sqlarea v$session
sid






Current State Views

V$SESSION - Sessions currently connected to the instance

v$session_wait - last/current wait
This is a key view for finding bottlenecks. It tells what every session in the
database is currently waiting for (or the last event waited for by the session
if it is not waiting for anything). This view can be used as a starting point
to find which direction to proceed in when a system is experiencing performance
problems.
Since 10g, Oracle displays the v$session_wait information also in the v$session view.

Summary Since Session Startup - cummulative

v$mystat - Resource usage summary for your own session
This view records statistical data about the session that accesses it.

v$session_event - Session-level summary of all the waits for current sessions
This view summarizes wait events for every session. While V$SESSION_WAIT shows
the current waits for a session, V$SESSION_EVENT provides summary of all the
events the session has waited for since it started.

v$sesstat - session-level summary of resource usage since session startup
V$SESSTAT stores session-specific resource usage statistics, beginning at login
and ending at logout.
Includes session logical reads, CPU used by this session, db block changes,
redo size, physical writes, parse count (hard), parse count (total),
sorts (memory), and sorts (disk).
V$SESSTAT can be used to find sessions with the following:

* The highest resource usage
* The highest average resource usage rate (ratio of resource usage to logon time)
* The current resource usage rate (delta between two snapshots)


v$sysstat - Summary of resource usage
V$SYSSTAT stores instance-wide statistics on resource usage, cumulative since
the instance was started.
Similar to V$SESSTAT, this view stores the following types of statistics:

* A count of the number of times an action occurred (user commits)
* A running total of volumes of data generated, accessed, or manipulated (redo size)
* If TIMED_STATISTICS is true, then the cumulative time spent performing some
actions (CPU used by this session)
The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed from V$SYSSTAT data.


v$system_event - cummulative Instance wide summary of resources waited for
This view displays the count (total_waits) of all wait events since startup of the instance.
This view is a summary of waits for an event by an instance. While V$SESSION_WAIT
shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all
the event waits on the instance since it started. It is useful to get a historical
picture of waits on the system. By taking two snapshots and doing the delta on
the waits, you can determine the waits on the system in a given time interval.

v$waitstat - Break down of buffer waits by block class
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
This view keeps a summary all buffer waits since instance startup. It is useful
for breaking down the waits by class if you see a large number of buffer busy
waits on the system.

LINK
oracle 9i performance document
oracle 10g performance document