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
for V$SESSION_WAIT
SELECT /*+ no_merge ordered use_nl(s,a) */
<snip>
decode(a.wait_time, 0, 'WAITING', 'ON CPU'), <=***** translates to
V$ACTIVE_SESSION_HISTORY.SESSION_STATE
<snip>
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 ;
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
http://www.amazon.com/exec/obidos/tg/detail/-/0672327910/
** 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
To: ORACLE-L
Subject: Re: File open wait
Hi,
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
CPU?
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).
Tanel.
----- 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 8.1.7.4 EE
I'm facing right now a "file open" wait for a specific session as
follows:
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 /
SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT STATE
----- ------------ ---------- --------------- -------------------
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#
,s.ROW_WAIT_BLOCK#,s.ROW_WAIT_ROW#
2 from v$session s
3 where s.sid=40;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
-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,
Luis
--
//www.freelists.org/webpage/oracle-l
Other related posts:
