Workaround Queries(11g) While Monitoring

------------------------ check session information -----------------------

select * form v$fixed_table;
select * from v$session where osuser='501200I127';
select * from v$sysstat
select * from v$session_longops where sid in (130,138)
select * from v$session where osuser='50120C1008';
select * from v$session where type!= 'BACKGROUND';
select 'alter system kill session ' '''' sid ',' serial#'''' ' immediate;' as script from v$session where osuser='501200I127';
select * from v$session where sid in (147);
alter system kill session '138,8217' immediate;

-------------find which session is using a particular object --

select * from v$access where object like 'RPT_TREASURY_REPORT_FILTERS%';

---------- enable trace for a particular session ------

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(53 ,5083,true);

---------- find the trace files for a session -------

select b.username, c.value '\' lower(d.value) '_ora_'
to_char(a.spid, 'fm00000') '.trc' "TRACE_FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr and c.name = 'user_dump_dest'
and d.name = 'db_name' and b.username is not null and b.sid=53;
alter system kill session '191,19465' immediate;

----------------- find the undo space usage in UNDO tablespace for a database ---------------------------

select sum(decode(status,'EXPIRED',bytes,0))/sum(bytes) EXPIRED_PERCENT,
sum(decode(status,'UNEXPIRED',bytes,0))/sum(bytes) UNEXPIRED_PERCENT,
sum(decode(status,'ACTIVE',bytes,0))/sum(bytes) ACTIVE_PERCENT
from dba_undo_extents;

--------------------- find the Database components features usage ----------------------------------

select * from DBA_FEATURE_USAGE_STATISTICS;

------------------------ find current running query using OS process id -----------------

SELECT s.sid, s.serial#, q.SQL_TEXT ,s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p,v$sqltext q
WHERE s.paddr = p.addr and q.address = s.sql_address
AND q.hash_value = s.sql_hash_value AND p.spid = 22737;
---------- 1 row

SELECT s.sid,s.osuser,q.SQL_FULLTEXT
FROM v$session s, v$process p,v$sqlarea q
WHERE s.paddr = p.addr and s.sql_address= q.address AND p.spid = 24510;

exec dbms_stats.GATHER_TABLE_STATS('STARREP','DISCOUNT_FACTOR_DATA');

--------------------- find the OS process id -----------------------

SELECT s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (488);

SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (39, 24);

--------- cpu usage information ---------

select a.sid, a.value cpu_usage, a.value/b.value cpu_bycall from v$sesstat a, v$sesstat b
where a.sid=b.sid and a.statistic#=12 and b.statistic#=6
and a.value>0 and b.value>0 order by a.value desc;

-------------------------- find the top session ------------------

Select sid,username,round(100 * total_user_io/total_io,2) tot_io_pct
from (select
b.sid sid,nvl(b.username,p.name) username,sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by b.sid, nvl(b.username,p.name)),
(select sum(value) total_io from
sys.v_$statname c,
sys.v_$sesstat a
where a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by 3 desc;

-------------------- find the blocking sessions --------------------------

select s1.username '@' s1.machine
' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
SELECT sid, serial#, blocking_session_status, blocking_session
FROM v$session WHERE blocking_session IS NOT NULL;

------------- identify locked object ----------------------------

select object_name from dba_objects where object_id = 88519;
select * from v$lock;

--------------- SQL run time monitor -----------------------------

select * from v$sql_monitor where sid = 344;

---------------- find top SQL queries ------------------------------

SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('09/01/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('09/09/2008','MM/DD/YYYY'))
GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC;

------------------------ resources used by a session --------------

select s.osuser osuser,s.serial# serial,se.sid,n.name,max(se.value) maxmem
from v$sesstat se,v$statname n,v$session s
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
and s.sid=se.sid
group by s.osuser, s.serial#, se.sid, n.name
order by 2;

--- check local session

select a.name,b.value from v$statname a ,v$mystat b where a.statistic#=b.statistic# and b.sid in (474,475,477,478,481,494,499) ;

-------------------check number of cursors opened -----------------------------------------------------

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,s.username, s.machine
from v$sesstat a,v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'
group by s.username, s.machine order by 1 desc;