As much as the v$"wait" views are touted they do have problems. First, I believe they
are only updated every 3 seconds and can miss events. Second, examine enough
samplings from the tables and you'll discover bizarre data. Events which managed to
wait say 50 seconds in a single sampling period when the sampling rate was 5 Hz. On
the other hand, one may see events which are continuous over many samplings, but their
wait times are not incremented.
If you really want to know what's going on there's no substitute for a 10046 trace.
N.B., I am not stating the v$"wait" statistics tables are useless just that they have
their shortcomings.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-----Original Message-----
Sent: Tuesday, November 26, 2002 3:26 PM
To: Multiple recipients of list ORACLE-L
Dennis,
I did some quick & dirty testing by creating a very small(10M) datafile with a
large(2000m) autoextend clause. On the insert, the session was waiting on 'file open'
for most of the time. When I did a rollback and reinserted the data, there were no
waits (that I saw) on file open.
Interestingly, this wait event does not appear to be accurately tracked in
v$session_event. In v$session_wait the seconds in wait (last
trapped) was 132. In v$session_event, it shows 0. Okay, gurus, why? Am I missing
something in this?
select * from v$session_wait where sid = 14
SID SEQ# EVENT
---------- ----------
----------------------------------------------------------------
P1TEXT P1
P1RAW
---------------------------------------------------------------- ----------
----------------
P2TEXT P2
P2RAW
---------------------------------------------------------------- ----------
----------------
P3TEXT P3
P3RAW WAIT_TIME SECONDS_IN_WAIT
---------------------------------------------------------------- ----------
---------------- ---------- ---------------
STATE
-------------------
14 322 file open
fib 4327126592
0000000101EAB640
iov 4327069760
0000000101E9D840
0 0
00 -1 132
WAITED SHORT TIME
select * from v$session_event where sid = 14
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS
TIME_WAITED AVERAGE_WAIT MAX_WAIT
---------- ------------------------------ ----------- --------------
----------- ------------ ----------
14 rdbms ipc reply 4 1
210 52.5 205
14 control file sequential read 18 0
16 .888888889 15
14 local write wait 1 0
0 0 0
14 log buffer space 72 0
1242 17.25 82
14 log file switch completion 6 0
250 41.6666667 72
14 log file sync 4 0
61 15.25 28
14 db file sequential read 7 0
1 .142857143 1
14 db file scattered read 164 0
152 .926829268 5
14 db file single write 2 0
1 .5 1
14 file identify 4 0
0 0 0
14 file open 6 0
0 0 0
14 SQL*Net message to client 41 0
0 0 0
14 SQL*Net message from client 40 0
67829 1695.725 19952
Dan Fink
-----Original Message-----
Sent: Tuesday, November 26, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L
Oracle says that when a file autoextends, there is a slight delay. Does anyone know
which Oracle WAIT statistic that would appear under?
We have been using autoextend on OLTP production tables for awhile now, and the
results have been satisfactory. This is an ERP system, so the critical performance
time is at month-end. Some of the developers are concerned that table autoextending
may slow batch programs, and suggesting that I should determine which tables are
likely to autoextend during month-end and add storage beforehand. I would like to
ensure that I am fixing a real problem (short on time, like most of you), so I am
wondering if autoextend was causing a delay, what wait statistic would it show up
under. Any ideas?
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY,
include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
removed from). You may also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY,
include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
removed from). You may also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
