Shows current statements for active sessions
select p.username pu , s.username su, s.status stat, s.sid ssid, s.serial# sser , substr(p.spid,1,8) spid, substr(sa.sql_text,1,2000) txt from v$process p , v$session s, v$sqlarea sa where p.addr = s.paddr and s.username is not null and s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+) and s.status = 'ACTIVE' order by 1,2,7 ;
Show SESSION login history
select c.username,a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.module, a.machine, b.SQL_TEXT from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b, dba_users c where a.SQL_ID = b.SQL_ID(+) and a.user_id=c.user_id and c.username='&username' order by a.SQL_EXEC_START asc;
Find bind variable values used by SQL's
select s.sid, s.username, sq.sql_text, s.sql_hash_value, s.sql_id, s.sql_child_number, spc.name, spc.value_string, last_captured from v$sql_bind_capture spc, v$session s,v$sql sq where s.sql_hash_value = spc.hash_value and s.sql_address = spc.address and sq.sql_id=s.sql_id and spc.was_captured='YES' and s.type<>'BACKGROUND' and s.status='ACTIVE';
Identifies Segments That Are Getting Close to Running Out Of Contiguous Free Space
select owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest from dba_segments s, dba_free_space f where s.tablespace_name = f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes, next_extent having next_extent*2 >max(f.bytes);
How to check if oracle instance is leaking memory
Use session statistics (to measure)The Oracle server maintains statistics that record the PGA and UGA memory consumption on a per session basis. If you suspect a memory leak then use SQL similar to that presented below to verify the amount of memory in use and re-issue it several times and monitor to confirm that memory is indeed growing.Select sid, substr(name,1,30), value , value/(1024*1024) "MB"from v$statname a, v$sesstat bwhere a.statistic# = b.statistic#and name like '%a memory'order by sid, name;select b.sid, substr(name,1,30), value , value/(1024*1024) "MB", c.serial#,c.status,c.username,c.schemaname,c.osuser,c.machine,c.terminal,c.program,c.module,state,logon_time from v$statname a, v$sesstat b,v$session c where a.statistic# = b.statistic# and name like '%a memory' and b.sid=c.sid and osuser!='oracle' order by status,MB desc,sid, name; If you want to the SQL also join v$sqlarea table- This will give you how much memory is utilized by package/procedure and a simple/complex SQL text select b.sid, substr(name,1,30), value , value/(1024*1024) "MB", s.serial#,s.sid ssid, s.status,s.username,s.schemaname,s.osuser,s.machine,s.terminal,s.program,s.module,state,logon_time,substr(p.spid,1,8) spid, substr(sa.sql_text,1,2000) txt from v$statname a, v$sesstat b,v$session s, v$process p ,v$sqlarea sa where a.statistic# = b.statistic# and name like '%a memory' and b.sid=s.sid and osuser!='oracle' and p.addr = s.paddr and s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+) order by status,MB desc,sid, name; FIND LOCKED TABLES AND KILL SESSION LOCKING TABLESselect oracle_username || ' (' || s.osuser || ')' username , s.sid || ',' || s.serial# sess_id , owner || '.' || object_name object , object_type ,decode( l.block ,0, 'Not Blocking',1, 'Blocking', 2, 'Global') status , decode(v.locked_mode,0, 'None' , 1, 'Null' ,2, 'Row-S (SS)' , 3, 'Row-X (SX)' , 4, 'Share',5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held from v$locked_object v , dba_objects d , v$lock l , v$session s where v.object_id = d.object_id and v.object_id = l.id1 and v.session_id = s.sid and OBJECT_name in ( 'TQUEUEDSANTERAINCCDR','TQUEUEDSANTERACDR')order by oracle_username , session_id ;select p.username pu , s.username su, s.status stat, s.sid ssid, s.serial# sser , substr(p.spid,1,8) spid, substr(sa.sql_text,1,2000) txtfrom v$process p , v$session s, v$sqlarea sa where p.addr = s.paddr and s.username is not null and s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+)and s.sid in (select s.sid ifromfrom v$locked_object v , dba_objects d , v$lock l , v$session s where v.object_id = d.object_id and v.object_id = l.id1 and v.session_id = s.sid and OBJECT_name in ( 'TQUEUEDSANTERAINCCDR','TQUEUEDSANTERACDR')) order by 1,2,7 ;
Shows waiting sessions and the events they await
select sw.sid sid, p.spid spid, s.username username , s.osuser osuser, sw.event event, s.machine machine , s.program program, decode(sw.event,'db file sequential read', sw.p3, 'db file scattered read', sw.p3, null) blocks from v$session_wait sw,v$session s, v$process p where s.paddr = p.addr and event not in ('pipe get','client message') and sw.sid = s.sid
Identify which object is being waited for:
SELECT kglnaown "Owner", kglnaobj "Object" FROM x$kglob WHERE kglhdadr='value of p1raw'
Who is pinning the object? SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status, kglpnmod "Mode", kglpnreq "Req" FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='value of p1raw';
Use the following script to generate SQL to kill off multiple sessions all waiting for the same library cache pin.
SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='value of p1raw';
Tablespace and File size
SELECT /*+ ordered */ d.tablespace_name tablespace , d.file_name filename , d.bytes filesize , d.autoextensible autoextensible , d.increment_by * e.value increment_by , d.maxbytes maxbytes FROM sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e WHERE (d.file_name = v.name) UNION SELECT d.tablespace_name tablespace , d.file_name filename , d.bytes filesize , d.autoextensible autoextensible , d.increment_by * e.value increment_by , d.maxbytes maxbytes FROM sys.dba_temp_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e UNION SELECT '[ ONLINE REDO LOG ]' , a.member , b.bytes , null , TO_NUMBER(null) , TO_NUMBER(null) FROM v$logfile a , v$log b WHERE a.group# = b.group# UNION SELECT '[ CONTROL FILE ]' , a.name , TO_NUMBER(null) , null , TO_NUMBER(null) , TO_NUMBER(null) FROM v$controlfile a ORDER BY 1,2;
set lines 100 pages 999 col username format a20 col sess_id format a10 col object format a25 col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username , s.sid || ',' || s.serial# sess_id , owner || '.' || object_name object , object_type , decode( l.block , 0, 'Not Blocking' , 1, 'Blocking' , 2, 'Global') status , decode(v.locked_mode , 0, 'None' , 1, 'Null' , 2, 'Row-S (SS)' , 3, 'Row-X (SX)' , 4, 'Share' , 5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held from v$locked_object v , dba_objects d , v$lock l , v$session s where v.object_id = d.object_id and v.object_id = l.id1 and v.session_id = s.sid order by oracle_username , session_id
Locked Object QUERYset linesize 230set pages 100col username format a20col sess_id format a10col object format a45col mode_held format a10select oracle_username || ' (' || s.osuser || ')' username, s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object, object_type, substr(p.spid,1,8) spid, decode( l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') status, decode(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode)) mode_heldfrom v$locked_object v, dba_objects d, v$lock l, v$session s, v$process pwhere v.object_id = d.object_idand v.object_id = l.id1and v.session_id = s.sidand p.addr = s.paddrorder by oracle_username, session_id/select * from (select opname, target, sofar, totalwork,units, elapsed_seconds, message from v$session_longopswhere sid = <sid> and serial# = <serialno> order by start_time desc)where rownum <=1;select s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid in (select SESSION_ID from v$locked_object); select * from (select opname, target, sofar, totalwork,units, elapsed_seconds, message from v$session_longops order by start_time desc)where rownum <=1;
Identify Segments That Are Getting Close To Their Max-Extent Values
select owner,tablespace_name,segment_name,bytes,extents,
max_extents from dba_segments where extents*2 > max_extents;
Library cache wating
select sid, event, p1raw, seconds_in_wait,wait_time from v$session_wait where event = 'library cache pin' and state = 'WAITING'; SID,EVENT,P1RAW,SECONDS_IN_WAIT,WAIT_TIME 4962,library cache pin,070000031733BDF8,51,0
Identify which object is being waited for: SELECT kglnaown "Owner", kglnaobj "Object" FROM x$kglob WHERE kglhdadr='070000031733BDF8';
Who is pinning the object?
SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status, kglpnmod "Mode", kglpnreq "Req" FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='070000031733BDF8';
Use the following script to generate SQL to kill off multiple sessions all waiting for the same library cache pin.
SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='070000031733BDF8'
what sessions (and what SQL statements) are using sorting resources
select s.sid || ',' || s.serial# sid, s.username, u.tablespace, substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text, u.blocks/128 mb, ((u.blocks/128)/(sum(f.blocks)/128))*100 pct from v$sort_usage u, v$session s, v$sqlarea a, dba_data_files f where s.saddr = u.session_addr and a.address (+) = s.sql_address and a.hash_value (+) = s.sql_hash_value and f.tablespace_name = u.tablespace group by s.sid || ',' || s.serial#, s.username, substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)), u.tablespace, u.blocks/128
|