See How to monitor alert log See How many opened cursors See db links describes how to correctly read v$session with dblink connections See Locks
Session Browser for Oracle database Download jdev-rt.jar(layout manager) ojdbc14.jar(Oracle driver) SessionBrowser.jar(application) ...\java -cp "ojdbc14.jar;jdev-rt.jar" -jar SessionBrowser.jar You may use the quick connection button to connect to target, repository is only available... for me. pause |
Quick info by Unix spid select s.sid, s.program, s.terminal, machine, osuser, type, module, to_char(logon_time, 'dd/mm/yyyy hh24:mi') logon_time, pga_used_mem/1024/1024 pga_used_mem_MB, pga_alloc_mem/1024/1024 pga_alloc_mem_MB, pga_max_mem/1024/1024 pga_max_mem_MB from v$process p, v$session s where s.paddr = p.addr and spid = 8122 |
Monitoring scripts
| Oracle 9, 10g, 11g - Each active session Last updated 04 September 2008 with Streams monitoring select STATUS, wait_event, QC_SLAVE, PARALLEL_SESS, QC_SID,
SID, USERNAME, OSUSER, MACHINE, TERMINAL,
PROGRAM, LOGON_TIME, SERVER, SPID, PROCESS, SERIAL#,
TYPE, MODULE, ACTION, SCHEMANAME,
CURRENT_USER, TEMP_MB_USED, locks_blocking, locks_not_blocking, waiting_count, holding_count,
IO_block_gets, IO_consistent_gets, IO_physical_reads, IO_block_changes,
IO_consistent_changes,
to_char(PGA_USED_MEM_MB, 'FM99990.09') PGA_USED_MEM_MB,
to_char(PGA_ALLOC_MEM_MB, 'FM99990.09') PGA_ALLOC_MEM_MB,
to_char(PGA_MAX_MEM_MB, 'FM99990.09') PGA_MAX_MEM_MB,
INACTIVITY_DAYS, OPEN_CURSORS, PARAM_OPEN_CURSORS, Param_sess_cached_cursors,
to_char(PERC_OPEN_CURSORS, 'FM99990.09') PERC_OPEN_CURSORS,
to_char(open_cursors_and_cache_closed, 'FM99990.09') open_cursors_and_cache_closed,
PLAN_VARIATIONS,
SQL_HASH_VALUE, trim(to_char(SQL_ADDRESS||' ')) SQL_ADDRESS,
trim(to_char(saddr||' ')) saddr, KILL_SESSION,
START_TRACE, STOP_TRACE,
KILL_UNIX_PROCESS, undo_records, undo_start_time,
trim(STREAM_INFO || ' ' || STREAM_CAPTURE_INFO || ' ' || STREAM_APPLY_READER_INFO || ' ' || STREAM_APPLY_COORDINATOR) stream,
STREAM_CAPTURE_PROBLEM stream_status from (
select a.*
, (select event from v$session_wait where sid = a.sid and not event in ( --select event from perfstat.stats$idle_event
'AQ Proxy Cleanup Wait',
'ASM background timer',
'DBRM Logical Idle Wait',
'DIAG idle wait',
'EMON idle wait',
'EMON slave idle wait',
'IORM Scheduler Slave Idle Wait',
'KSV master wait',
'LNS ASYNC archive log',
'LNS ASYNC dest activation',
'LNS ASYNC end of log',
'LogMiner: client waiting for transaction',
'LogMiner: generic process sleep',
'LogMiner: reader waiting for more redo',
'LogMiner: slave waiting for activate message',
'LogMiner: waiting for processes to soft detach',
'LogMiner: wakeup event for builder',
'LogMiner: wakeup event for preparer',
'LogMiner: wakeup event for reader',
'MRP redo arrival',
'Null event',
'PING',
'PX Deq Credit: need buffer',
'PX Deq Credit: send blkd',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Par Recov Execute',
'PX Deq: Signal ACK',
'PX Deq: Table Q Normal',
'PX Deq: Table Q Sample',
'PX Deque wait',
'PX Idle Wait',
'Queue Monitor Shutdown Wait',
'Queue Monitor Slave Wait',
'Queue Monitor Wait',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data from client',
'STREAMS apply coord waiting for slave message',
'STREAMS apply slave idle wait',
'STREAMS apply slave waiting for coord message',
'STREAMS capture process filter callback wait for ruleset',
'STREAMS fetch slave waiting for txns',
'STREAMS waiting for subscribers to catch up',
'Space Manager: slave idle wait',
'Streams AQ: RAC qmn coordinator idle wait',
'Streams AQ: deallocate messages from Streams Pool',
'Streams AQ: delete acknowledged messages',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams capture: waiting for archive log',
'Streams fetch slave: waiting for txns',
'class slave wait',
'client message',
'cmon timer',
'dispatcher timer',
'fbar timer',
'gcs for action',
'gcs remote message',
'ges remote message',
'i/o slave wait',
'jobq slave wait',
'knlqdeq',
'lock manager wait for remote message',
'master wait',
'null event',
'parallel query dequeue',
'parallel recovery coordinator waits for slave cleanup',
'parallel recovery slave idle wait',
'parallel recovery slave next change',
'parallel recovery slave wait for change',
'pipe get',
'pmon timer',
'pool server timer',
'queue messages',
'rdbms ipc message',
'slave wait',
'smon timer',
'virtual circuit status',
'wait for activate message',
'wait for unread message on broadcast channel',
'wakeup event for builder',
'wakeup event for preparer',
'wakeup event for reader',
'wakeup time manager',
'watchdog main loop'
)) wait_event
, (select decode(a.sid, m.sid, '************', '') from v$mystat m where rownum=1) current_user
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', TRUE); end;') start_trace
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', FALSE); end;') stop_trace
, ('ALTER SYSTEM DISCONNECT SESSION ''' || sid ||','||serial#||''' IMMEDIATE/*spid='||spid||'*/') kill_session
, ('kill -9 '||spid) kill_unix_process
, (select count(*) from v$lock where sid = a.sid and block != 0) locks_blocking
, (select count(*) from v$lock where sid = a.sid and block = 0) locks_not_blocking
, (open_cursors * 100 / param_open_cursors) perc_open_cursors
--Is the session using a query with a variated plan?
, (select count(*) from (
select hash_value, count(*) from (
select hash_value, plan_hash_value from v$sql group by hash_value, plan_hash_value
) group by hash_value having count(*) > 1
) where hash_value = sql_hash_value) plan_variations
-- R O L L B A C K
, (select decode(sort_space_mb, null, 'No temporary used',
sort_space_mb || 'MB, ' || to_char(sort_space_mb * 100 / (sum(bytes_used)/1024/1024), 'FM90.99')||'% of used, '||
to_char(sort_space_mb * 100 / (sum(bytes_used + bytes_free)/1024/1024), 'FM90.99')||'% of ' || (sum(bytes_used + bytes_free)/1024/1024) ||'MB')
from v$temp_space_header) temp_MB_used
from (
select
s.status, s.saddr
, nvl((select decode(px.qcinst_id,NULL, 'Master(QC)', 'Slave') from v$px_session px where px.sid = s.sid), 'Master') QC_Slave
, (select decode(count(*), 0, 0, count(*)-1) from v$px_session px where px.qcsid=(select qcsid from v$px_session where sid =s.sid)) parallel_sess
, (select decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) from v$px_session px where px.sid = s.sid) QC_SID
, (select spid from v$process where addr = paddr) spid
, (select value from v$parameter where name = 'open_cursors') param_open_cursors
, (select value from v$parameter where name = 'session_cached_cursors') Param_sess_cached_cursors
, s.sid, decode(s.username, null, 'Server process', s.username) username
, osuser, machine, terminal, program
, logon_time, to_char((last_call_et / 86400), 'FM99990.09') inactivity_days, server, process, s.serial#, type, s.module, s.action,
resource_consumer_group,
sql_hash_value, sql_address, schemaname
--ORACLE 8 MUST COMMENT ALL begin
, (select pga_used_mem/1024/1024 from v$process where addr = paddr) pga_used_mem_MB
, (select pga_alloc_mem/1024/1024 from v$process where addr = paddr) pga_alloc_mem_MB
, (select pga_max_mem/1024/1024 from v$process where addr = paddr) pga_max_mem_MB
--ORACLE 8 MUST COMMENT All end
, (select sum(V$SORT_USAGE.blocks *
--(select value from v$parameter where upper(name) = 'DB_BLOCK_SIZE')
--ORACLE 8 MUST COMMENT THIS LINE BELOW AND ENABLE THE ABOVE
dba_tablespaces.block_size
)/1024/1024 from V$SORT_USAGE, dba_tablespaces
where dba_tablespaces.tablespace_name = V$SORT_USAGE.tablespace and V$SORT_USAGE.session_addr = s.saddr) sort_space_MB
, (select a.value from v$sesstat a, v$statname b where a.statistic# = b.statistic#
and b.name = 'opened cursors current' and a.sid = s.sid) open_cursors
, (select count(*) from v$open_cursor where sid=s.sid) open_cursors_and_cache_closed
, V$SESS_IO.block_gets IO_block_gets, V$SESS_IO.consistent_gets IO_consistent_gets
, V$SESS_IO.physical_reads IO_physical_reads, V$SESS_IO.block_changes IO_block_changes
, V$SESS_IO.consistent_changes IO_consistent_changes
, (select count(*) from DBA_WAITERS where waiting_session = s.sid) waiting_count
, (select count(*) from DBA_WAITERS where holding_session = s.sid) holding_count
, (select sum(used_urec) from V$TRANSACTION where s.saddr = ses_addr) undo_records
, (select min(start_time) from V$TRANSACTION where s.saddr = ses_addr) undo_start_time
/* stream */
, (select decode(module, 'STREAMS', module||', ' || action, null) from v$session where sid=s.sid) STREAM_INFO
/* stream capture */
, (select state || ', [' || CAPTURE_NAME || ' capt. name], [' || ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) || '/' ||
((SYSDATE - CAPTURE_TIME)*86400 || ' latency sec]') from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_INFO
, (select decode(state, 'CAPTURING CHANGES', 0, 1) from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_PROBLEM
, (select '[Apply status: ' || DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-enqueued messages','UNKNOWN') || ']' ||
'[Program: ' || SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) || ']'
from V$STREAMS_APPLY_READER, DBA_APPLY ap
where SID = s.SID AND SERIAL# = s.SERIAL# and V$STREAMS_APPLY_READER.APPLY_NAME = ap.APPLY_NAME)
STREAM_APPLY_READER_INFO
, (SELECT 'Apply Coordinator: ' || apply_name || ', ' || c.state || ', process: ' || substr(s.program,instr(s.program,'(')+1,4)
FROM v$streams_apply_coordinator c where c.sid = s.sid and c.serial# = s.serial#)
STREAM_APPLY_COORDINATOR
from
v$session s, V$SESS_IO
--where not username is null --avoid Oracle processes
where V$SESS_IO.sid (+)= s.sid
) a
) b
|
Get current(my) transaction Is txid unique and may repeat? Is unique for instance and should not repeat for the lifetime of the database http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30725030554956 --Useful in extreme triggers... --grant all on v_$mystat to aldo; --grant all on v_$session to aldo; --grant all on v_$transaction to aldo; select DBMS_TRANSACTION.LOCAL_TRANSACTION_ID from dual or select xid, UTL_RAW.CAST_TO_number(xid) from v$transaction, v$session, (select sid from v$mystat m where rownum=1) my where v$session.saddr = v$transaction.ses_addr and my.sid = v$session.sid |
Master sessions and parallel spawned sessions col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for A10
set pages 100
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from
v$px_session px,
v$session s
where
px.sid=s.sid (+)
and
px.serial#=s.serial#
order by 5 , 1 desc;
|
| Alert log in tail tail -f p | grep ORA- |
All queries with a plan variationselect hash_value, count(*) from ( select hash_value, plan_hash_value from v$sql group by hash_value, plan_hash_value ) group by hash_value having count(*) > 1 --and hash_value = 2008808730 order by 2 desc |
Get all long running queries
SELECT /*+ rule */
SYSDATE tracciamento, a.SID SID, b.serial# serial, b.last_call_et,
a.event wait_event, b.username username, b.machine machine,
b.program programma, sq.sql_text
FROM v$session_wait a, v$session b, v$sql sq
WHERE event NOT IN
('smon timer',
'pmon timer',
'rdbms ipc message',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'SQL*Net message from dblink',
'jobq slave wait'
)
AND a.SID = b.SID
AND b.sql_address = sq.address
AND b.last_call_et > 60
AND b.username NOT IN ('SYS', 'SYSTEM', 'EXPDB')
AND b.SID NOT IN (SELECT SID
FROM dba_jobs_running)
ORDER BY b.last_call_et DESC |