Prompt Enter the operating system oracle process id for this concurrent request
accept v_spid
Prompt Getting the sid
SELECT SID,SERIAL#,LOGON_TIME FROM V$SESSION WHERE PADDR IN
(SELECT ADDR FROM V$PROCESS WHERE SPID='&v_spid');

prompt Enter the session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';

prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;

prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;

prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';

prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;

prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';