Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Redo Problem ... Find from Statspack

Re: Redo Problem ... Find from Statspack

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 18 Oct 2007 08:46:45 -0700
Message-ID: <1192722393.927139@bubbleator.drizzle.com>


Steve Robin wrote:

> On 17 Oct, 20:52, DA Morgan <damor..._at_psoug.org> wrote:
>> Steve Robin wrote:

>>> On 17 Oct, 16:55, DA Morgan <damor..._at_psoug.org> wrote:
>>>> Steve Robin wrote:
>>>>> Hi,
>>>>> Two days before performance was down.... I find that log file sync and
>>>>> redo allocation latch was high.... now is there any way to find which
>>>>> queries or session were generating so many redo.
>>>>> Thanks
>>>>> Oracle : 9.2
>>>>> Server : Solaris
>>>> Define "log file sync and redo allocation latch was high."
>>>> Compared to a baseline or just a big number and you are assuming that
>>>> it indicates an issue?
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damor..._at_x.washington.edu (replace x with u to respond)
>>>> Puget Sound Oracle Users Groupwww.psoug.org

>>> Daniel,
>>> I had given the values with log sync, about redo allocation.
>>> Latch Name Requests Misses Sleeps
>>> Sleeps 1->4
>>> ------------------Problem Performance
>>> redo allocation 19,830,541 32,005 25
>>> 31980/25/0/0
>>> /
>>> 0
>>> -------------------Right Performation
>>> redo allocation 19,813,484 72,544 4
>>> 72540/4/0/0/
>>> 0
>>> as well as I found following latch in problematic report which was not
>>> in right report.
>>> mostly latch-free SCN 23,251 190 1
>>> 189/1/0/0/0
>>> Please suggest.
>>> Thanks
>> My suggestion would be that these numbers, in and of themselves,
>> indicate nothing. Do you have anyone you can work with that knows
>> how to use StatsPack for tuning? This usenet group is not the place
>> to teach an ad hoc tuning class and I've yet to see any indication
>> that you actually have an issue. Not that you don't but "performance
>> was down" is not very precise.
>> --
>> 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 -
> Daniel,
> 
> Thanks for your attention.
> 
> I hv the compare 2 statspack..... this was the only big difference....
> that's why I had given only these parameter.
> And also whole db was not very slow... only a very imported batch took
> a lot hell of time as compare to other days as per application team.
> That's why I need suggestion, is there any way to find which queries
> or session (username) creates so many redo in between that time,
> because I hv snapshots for that time.
> 
> Thanks

Is this the only time that has been the case or is this something that is repeatable? There are many reasons, such as delayed block clean-out that something might be slow once and not be an issue again.

If you can get your hands on Cary Millsap's book you will find the instructions on how, in 9i, to figure out what is going on. Needless to say a short paragraph in a usenet group is no substitute for a 350 page book.

-- 
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 Thu Oct 18 2007 - 10:46:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US