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

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 **

Other related posts: