File open wait
- From: Luis Fernando Cerri <lcerri@xxxxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Jan 2006 14:45:55 -0300
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
Other related posts:
