RE: File open wait

  • From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
  • To: <tanel.poder.003@xxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 20 Jan 2006 11:46:59 -0800

Luis et al,
To add to this discussion, looking at V$SESSION_WAIT alone does not always 
provide a complete picture. The EVENT column shows the last instrumented wait 
encountered and as Tanel mentioned, this has to be qualified by the value in 
the WAITING column. This is probably best illustrated in a small nugget of code 
buried in the description of GV$ACTIVE_SESSION_HISTORY which is new in 10g and 
the main ASH view. Although your are at 8.1.7, this concept still holds true 

SELECT /*+ no_merge ordered use_nl(s,a) */
 decode(a.wait_time, 0, 'WAITING', 'ON CPU'),  <=***** translates to 
  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 ;

You should compare values of V$SESSTAT taken at different times - you will see 
values for '% gets' and 'session logical reads' increase. This implies that you 
are burning CPU accessing blocks that are already cached. (And for sure, your 
BCHR will be close to 100%!! ;-)

Cheers - it is Friday!!!
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Co-Author: Oracle Database 10g Insider Solutions
** The opinions and facts contained in this message are entirely mine and do 
not reflect those of my employer or customers **


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Tanel Põder
Sent: Thursday, January 19, 2006 11:31 AM
Subject: Re: File open wait

If v$session_wait = WAITED UNKNOWN TIME , then it means you are not waiting 
anymore, you WAITED. This means, as long as instrumentation is correct, you're 
using CPU. If you don't have any trace entries and even processtate dump didnt 
work (you did attach to the right process, right?), then maybe you're in some 
loop or hang situation. Normal oradebug dumps work using sending a SIGUSR2 
signal to the process IIRC, but if maybe even the signal handler isn't invoked 
correctly or can't do its job.
If you do top or glance in OS, do you see your process continuously consuming 
Run pstack on this process few times and see whether it changes. If you don't 
have pstack then you can use a debugger, gdb or tusc, attach to the process and 
get call stack using "backtrace" from the process. If the process uses CPU and 
stack trace doesnt change, youre in some kind of endless or very long loop 
(caused by a bug).

        ----- Original Message ----- 
        From: Luis Fernando Cerri <mailto:lcerri@xxxxxxxxxxxxxxxxxxxxxxx>  
        To: oracle-l@xxxxxxxxxxxxx 
        Sent: Thursday, January 19, 2006 11:45 AM
        Subject: File open wait

        Hello, list! 

        OS: HPUX 11.11 
        Oracle EE 

        I'm facing right now a "file open" wait for a specific session as 

        This select is returning the same result for like one hour: 

           select substr(event,1,35) event,TOTAL_WAITS 
             from v$session_event 
            where sid= 40; 

        EVENT                               TOTAL_WAITS 
        ----------------------------------- ----------- 
        log file sync                                 1 
        db file sequential read                      12 
        file open                                     2 
        SQL*Net message to client                    38 
        SQL*Net message from client                  38 

        Current active wait event: 

        SQL> select seq#,event,wait_time,seconds_in_wait,state 
          2  from v$session_wait 
          3  where sid=40 
          4  / 

        ----- ------------ ---------- --------------- ------------------- 
           91 file open            -2            4751 WAITED UNKNOWN TIME 

        No join information between v$session and dba_data_files: 

        SQL> select s.ROW_WAIT_FILE#,f.file_name 
          2  from v$session s, dba_data_files f 
          3  where s.ROW_WAIT_FILE# = f.file_id 
          4  and s.sid=40 
          5  / 

        no rows selected 

        SQL>  select s.ROW_WAIT_OBJ#,s.ROW_WAIT_FILE# 
          2  from v$session s 
          3 where s.sid=40; 

        ------------- -------------- --------------- ------------- 
                   -1              0               0             0 

        Enabling 10046 trace through dbms_support didn't generated a trace file 
on UDUMP destination. The ORADEBUG DUMP PROCESSSTATE didn't return usable 
information (or I couldn't find it).

        Any guesses on determining which file is the session waiting for? Do 
P1, P2 and/or P3 mean anything usable? Oracle states that this information can 
be used only by development team.

        Thanks in advance for your collaboration. 

        Best regards, 


Other related posts: