Hey John, Thanks for the welcome. Yes, selecting from v$session_wait has some confusing aspects, so here is another query that formats the info onto one line, decodes for CPU, joins in the sql_hash value and translates locks : create view waiters as select substr(nvl(s.username,substr(s.program,-6)),1,10) username, substr(to_char(s.sid),1,5) sid, substr(to_char(s.serial#),1,8) serial, s.sql_hash_value hash_value, substr(decode(w.wait_time, 0, w.event, 'ON CPU'),1,20) status, substr(decode(w.event, 'enqueue', chr(to_number(substr(ltrim(w.p1raw,0),1,2),'XX'))|| chr(to_number(substr(ltrim(w.p1raw,0),3,2),'XX'))|| ' '|| substr(w.p1raw,-1,1), w.p1),1,8) p1, substr(w.p2,1,8) p2, substr(w.p3,1,4) p3 from v$session s, v$session_wait w where w.sid=s.sid and s.status='ACTIVE' and s.type='USER' and w.event not in ('jobq slave wait') order by w.event; select * from waiters; select * from waiters; USERNAME SID SERIAL HASH_VALUE STATUS P1 P2 P3 ---------- ----- -------- ---------- -------------------- -------- -------- ---- SYS 64 8717 4116021597 PL/SQL lock timer 300 0 0 SYS 58 19467 961168820 ON CPU 16508152 1 0 STARGUS 71 6251 1311875676 direct path write 201 2155902 127 SYS 78 277 3624585095 enqueue TX 6 524330 6770 (CJQ0) 9 1 0 rdbms ipc message 500 0 0 Best Kyle http://perfvision.com On 6/20/07, John Kanagaraj <john.kanagaraj@xxxxxxxxx> wrote:
Ted, You will have to remember that the EVENT column (in V$SESSION/V$SESSION_WAIT and elsewhere) records the *last instrumented wait event* encountered by the session. The interpretation of whether it is actually waiting for that event to complete or it is executing something else in the CPU or waiting in the CPU queue can only be made based on the STATE and WAIT_TIME columns. For a currently active session that is now executing in the CPU, the V$SESSION_WAIT.WAIT_TIME column qualifies the validity of the V$SESSION_WAIT.EVENT column. This is best illustrated by the following code (Kyle will recognize this as the code behind V$ACTIVE_SESSION_HISTORY view) SELECT /*+ no_merge ordered use_nl(s,a) */ a.inst_id, s.sample_id, s.sample_time, a.session_id, a.session_serial#, a.user_id, a.sql_id, a.sql_child_number, a.sql_plan_hash_value, a.sql_opcode, a.service_hash, decode(a.session_type, 1, 'FOREGROUND', 2, 'BACKGROUND', 'UNKNOWN'), decode(a.wait_time, 0, 'WAITING', 'ON CPU'), a.qc_session_id, a.qc_instance_id, a.event, a.event_id, a.event#, a.seq#, a.p1, a.p2, a.p3, a.wait_time, a.time_waited, a.current_obj#, a.current_file#, a.current_block#, a.program, a.module, a.action, a.client_id FROM x$kewash s, x$ash a WHERE s.sample_addr = a.sample_addr and s.sample_id = a.sample_id and s.sample_time = a.sample_time; I would bet that the INSERT is fed by a SELECT (as in INSERT INTO ... SELECT ...) - in that case, you should look at inefficient access (probably via a ton of NESTED LOOPs) in the SELECT statement. Kyle - welcome to this list. We worked together as Tech Editors for Kirti's OWI book :) -- John Kanagaraj <>< DB Soft Inc ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **