Re: V$SYSTEM_WAIT_CLASS

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 14 Aug 2007 08:30:35 -0700
Message-ID: <1187105434.422372@bubbleator.drizzle.com>

dean wrote:

> On Aug 13, 2:14 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> dean wrote:
>>> On Aug 13, 12:54 pm, DA Morgan <damor..._at_psoug.org> wrote:
>>>> dean wrote:
>>>>> On Aug 13, 11:54 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
>>>>> wrote:

>>>>>> On Aug 13, 12:10 am, dean <deanbrow..._at_yahoo.com> wrote:

>>>>>>> 10g
>>>>>>> select * from V$SYSTEM_WAIT_CLASS order by TIME_WAITED desc;
>>>>>>> WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS     TOTAL_WAITS TIME_WAITED
>>>>>>> ------------- ----------- -------------- ----------- -----------
>>>>>>>    2723168908           6 Idle              15742538   990583927
>>>>>>>    1740759767           8 User I/O           1501104      541245
>>>>>>>    2000153315           7 Network           35587647      227757
>>>>>>>    4108307767           9 System I/O          690084      194154
>>>>>>>    3386400367           5 Commit               70951       23803
>>>>>>>    3875070507           4 Concurrency          11212       13790
>>>>>>>    4217450380           1 Application          12112        5730
>>>>>>>    1893977003           0 Other                 7106        5411
>>>>>>>    3290255840           2 Configuration          171        1979
>>>>>>> This is the first time I have looked at this view, and I'm trying to
>>>>>>> understand it - does this indicate performance issues with the disk i/
>>>>>>> o?
>>>>>>> Thanks!
>>>>>>> Dean

>>>>>> Not necessarily; the TIME_WAITED values are in centiseconds (1/100 of
>>>>>> a second) and are cumulative for all sessions for as long as the
>>>>>> database is running uninterrupted. The User I/O number you posted
>>>>>> represents roughly 90 minutes of wait time for ALL sessions since the
>>>>>> database started. Presuming you have more than one connected session
>>>>>> and that the database has been up and running for more than two hours
>>>>>> I'd say no; follow Daniel's advice and file this away for future
>>>>>> reference. When and if a user (or group of users) decides to complain
>>>>>> about performance you can resurrect these values and compare them to
>>>>>> the current numbers (again, presuming you haven't shut down the
>>>>>> database between now and then) and possibly find the area or areas
>>>>>> which have changed.
>>>>>> There is no need to create problems where none currently exist.
>>>>>> David Fitzjarrell- Hide quoted text -
>>>>>> - Show quoted text -

>>>>> Ok thanks to both. There is no problem here on the development server,
>>>>> I just wanted to familiarize myself. I wasn't sure if this was
>>>>> indicating we could use a faster set of drives (forgot it was in
>>>>> centiseconds).
>>>> These days drives are less relevant as most disk arrays come with a
>>>> cache managed by its own operating system.
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damor..._at_x.washington.edu (replace x with u to respond)
>>>> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>>>> - Show quoted text -
>>> Even with disk cache, doesn't Oracle wait for confirmation of a
>>> physical write to disk?
>> No. It has no way of knowing. A write to the cache is a write to
>> the disk.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> 
> I've read to the contrary several times. Consider what would happen if
> Oracle thought it saved a change to disk, and then there was a power
> failure before the cache was written to disk? It would then be in a
> corrupt state.

That is the storage vendor's problem ... not Oracle's.

And the last time anyone lost a NetApp, EMC, or Pillar storage array due to a complete melt-down?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Aug 14 2007 - 10:30:35 CDT