Session Activity Queries in Oracle

--> 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