Re: FW: Misleading Wait: db file scattered read ?

From: John Kanagaraj <john.kanagaraj_at_gmail.com>
Date: Wed, 20 Jun 2007 14:40:29 -0700
Message-ID: <2ead3a60706201440u78e021aaw9127ee22b8820476@mail.gmail.com>

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 **
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 20 2007 - 16:40:29 CDT