Saturday, January 26, 2013

Session Activity Queries.docx

--> Checking session
select sesion.sid, sesion.username, optimizer_mode,
hash_value, address, cpu_time, elapsed_time, sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null

--> Get the rows fetched, it there is difference it means processing is happening
select b.name, a.value vlu
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and sid =&sid
and a.value != 0
and b.name like '%row%'

--> Get the sql_hash_value
select sql_hash_value from v$session where sid='&sid';
Get the sql_Text
select sql_text v$sql from v$sql where hash_value =&Enter_Hash_Value;
Get the explain_plan
set lines 190
col XMS_PLAN_STEP format a40
set pages 100
select
case when access_predicates is not null then 'A' else ' ' end ||
case when filter_predicates is not null then 'F' else ' ' end xms_pred,
id xms_id,
lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,
object_name xms_object_name,
cost xms_opt_cost,
cardinality xms_opt_card,
bytes xms_opt_bytes,
optimizer xms_optimizer
from v$sql_plan
where hash_value in (&SQL_HASH_VALUE)
and to_char(child_number) like '%';
Based the cost u can decide what to be done.
One of the solutions is to analyse the statistics
exec fnd_stats.gather_schema_statistics('ALL');

--> Time Remaining to complete the current task
set lines 150
col username format a20
col opname format a30
col target format a40
select sid,opname,target,
to_char(start_time,'DD-MON-YY HH24:MI') START_TIME,
time_remaining/60 "Time Remaining in Mins",username
from v$session_longops where time_remaining>1 order by time_remaining
/

--> Active session
select sid, to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
username, type, status, process, sql_address, sql_hash_value
from v$session
where username is not null
Work remaining
select V1.sid, V1.serial#, V2.USERNAME, V2.OSUSER, substr(V1.opname,1,10), to_char(V1.start_time, 'HH24:MI:SS') AS Started, (V1.SOFAR/V1.TOTALWORK)*100 AS Pct_completed
FROM V$SESSION_LONGOPS V1, V$SESSION V2
WHERE V1.SID= V2.SID AND V1.SERIAL#=V2.SERIAL#
AND (SOFAR/TOTALWORK)*100 < 100
AND TOTALWORK > 0
/

--> Memory usage
SELECT username, value/(1024*1024) "Current session memory MB", sess.sid,sess.status
FROM v$session sess, v$sesstat stat, v$statname name
WHERE sess.sid = stat.sid
AND stat.statistic# = name.statistic#
AND name.name like '%memory%'
AND username = ‘APPS’
Order by 2,4 asc

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect