delete

Useful dictionary objects / queries

V$ACCESS – Displays objects in the database that are currently locked and the sessions that are accessing them. V$FILESTAT – Contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time. This is useful in isolating where the I/O activity is happening if the bottleneck is I/O related. show invalid objects: select * from all_objects o where o.status <> 'VALID show dependencies for some object: select DISTINCT D.OWNER, D.name, D.REFERENCED_NAME from aLL_dependencies d where d.name =...
delete

PGA / UGA memory per session

select * from (SELECT ssn.sid, nvl(ssn.username, nvl(bgp.name, 'background')) as username, SSN.STATUS, ssn.machine, ins.host_name, prc.spid as PID_THREAD, round((se1.value / 1024) / 1024, 4) as CURRENT_PGA_SIZE_MB, round((se2.value / 1024) / 1024, 4) as MAXIMUM_PGA_SIZE_MB, round((se3.value / 1024) / 1024, 4) as CURRENT_UGA_SIZE_MB, round((se4.value / 1024) / 1024, 4) as MAXIMUM_UGA_SIZE_MB FROM v$sesstat   se1, v$sesstat   se2, v$sesstat   se3, v$sesstat   se4, v$session   ssn, v$bgprocess bgp, v$process   prc, v$instance  ins, v$statname  stat1, v$statname  stat2,...
delete

Show usage of rollback segments

SELECT s.sid , s.username , t.used_ublk FROM v$transaction t , v$session s WHERE 1 = 1 AND t.ses_addr = s.saddr; SELECT s.sid , s.username , t.used_ublk, round((t.used_ublk*8)/1024) size_in_MB_8kb_Block_size, round((t.used_ublk*16)/1024 ) size_in_MB_16kb_Block_size FROM v$transaction t , v$session s WHERE 1 = 1 AND t.ses_addr = s.saddr; SELECT distinct rpad(s.sid,3) "SID",S.USERNAME, E.SEGMENT_NAME, T.START_TIME "Start", rpad(T.STATUS,9) "Status", round((t.used_ublk*8)/1024,4) "Size(MB)" --T.USED_UBLK||' Blocks and '||T.USED_UREC||'...
delete

Statistics of rollback segments

Script for SQL*Plus: column "Rollback Segment"       format a16 column "Size (Kb)"              format 9,999,999 column "Gets"                   format 999,999,990 column "Waits"                  format 9,999,990 column "% Waits"                format 90.00 column "# Shrinks"              format 999,990 column "# Extends"              format 999,990 Prompt Prompt ROLLBACK Segment Statistics... SELECT rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",...
delete

Check what are sessions doing

select s.sid sid, s.serial# sh, s.process pid, decode(s.status, 'INACTIVE', 'N', 'Y') ia, decode(sw.wait_time, 0, 'Y', 'N') iw, decode(sw.wait_time, 0, sw.seconds_in_wait, sw.seconds_in_wait - sw.wait_time / 100) secs, sw.event event, decode(sq.sql_text, null, ' - Not Available - ', sq.sql_text) sql, s.USERNAME, s.MACHINE from v$session_wait sw, v$session s, v$sql sq where s.sid = sw.sid and s.sql_hash_value = sq.hash_value(+) --   and s.program like '%...program....%' and upper(sq.sql_text) not like '%CONFIGURATION%' order by...
delete

Statistics pro session

select   ses.SID, nvl(ses.USERNAME,'ORACLE PROC') username, sn.NAME statistic, sest.VALUE from   v$session ses, v$statname sn, v$mystat sest where   ses.SID = sest.SID and   sn.STATISTIC# = sest.STATISTIC# and   sest.VALUE is not null and   sest.VALUE != 0 order   by ses.USERNAME, ses.SID, sn.NAME