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

From: kyle Hailey <kylelf_at_gmail.com>
Date: Wed, 20 Jun 2007 17:28:45 -0700
Message-ID: <6f373fd20706201728h1d5be48bj3777c49127c6f1e2@mail.gmail.com>

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_at_gmail.com> 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 **
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 20 2007 - 19:28:45 CDT