Apps DB Performance Info

To Find Which Session consuming More CPU:

select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='INACTIVE'
and
ss.username is not null
order by VALUE desc;

######################################################################
To Find the SID for the Process id(PID):

Note: 'PID' at the OS level is same as 'SPID' at the database level.

select b.spid,a.sid, a.serial#,a.username,a.event,a.status, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;


######################################################################



> select b.spid,a.sid, a.serial#,a.username,a.event,a.status, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;12:01:53 2 12:01:53 3 12:01:53 4 12:01:53 5
Enter value for spid: 20338
old 4: and b.spid='&spid'
new 4: and b.spid='20338'

SPID SID SNO USERNAME EVENT STATUS OSUSER
------------------------ ---------- ------ --------------- ------------------------------ -------- ----------
20338 1571 4482 SYS db file sequential read ACTIVE oracled






> select sql_hash_value from v$session where sid =1571;

SQL_HASH_VALUE
--------------
3471794499

1 row selected.



> select sql_Text from v$sql where hash_value =3471794499;

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE',
:5, 'FALSE'))


1 row selected.

>


###################################
Find the SID information for a running request

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process,d.STATUS,d.event
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';


###################################
Inactive Sessions and Thier Time Consumed.


SELECT SID,PROGRAM,STATUS,action,(LAST_CALL_ET/3600)/24 "Days" FROM V$SESSION WHERE STATUS='INACTIVE';

select username , floor(last_call_et / 60) "Minutes" , status from v$session where username is not null AND STATUS='INACTIVE';

###################################

Determining database state performance wise :

select
count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session_wait
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
/

#####################################################################################
To Find Which SQl is mostly executed:

select sql_hash_value, count(*) from v$session
where status = 'ACTIVE' group by sql_hash_value order by 2 desc;

#####################################################################################

Determining database state performance wise :

select event, state, count(*) from v$session_wait group by event, state order by 3 desc;

#####################################################################################
Archives Count:

DAILY COUNT:

SELECT TRUNC(FIRST_TIME),COUNT(*) FROM v$loghist WHERE TRUNC(FIRST_TIME)>TRUNC(SYSDATE)-60
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME);

MONTHLY COUNT:

SELECT TO_CHAR(FIRST_TIME, 'MONTH') "MONTH",TO_CHAR(FIRST_TIME, 'YYYY') "YEAR",COUNT(*) FROM V$LOGHIST WHERE TRUNC(FIRST_TIME)>TRUNC(SYSDATE)-90
GROUP BY TO_CHAR(FIRST_TIME, 'YYYY'),TO_CHAR(FIRST_TIME, 'MONTH')
ORDER BY TO_CHAR(FIRST_TIME, 'YYYY') DESC ;

#####################################################################################