Re: FW: Misleading Wait: db file scattered read ?
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxx
- Date: Wed, 20 Jun 2007 14:40:29 -0700
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 **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
