Richard Foote wrote:
> <hasta_l3_at_hotmail.com> wrote in message
> news:1163615993.616586.162730_at_h54g2000cwb.googlegroups.com...
>> Richard Foote wrote :
>>
>>> <hasta_l3_at_hotmail.com> wrote in message
>>> news:1163528916.396146.307250_at_m7g2000cwm.googlegroups.com...
>>>> hpuxrac wrote :
>>>>
>>>>> hasta_l3_at_hotmail.com wrote:
>>>>>>> What conclusions did you reach based on your approach?
>>>>>> On my system - and I stress : on my system
>>>>>>
>>>>> What selects saw a 30% improvement? Was that in response time?
>>>>> Any OLTP application that is retrieving 4000 rows repeatedly is going
>>>>> to
>>>>> vary in response time anyway based on transient conditions, at least
>>>>> in
>>>>> my experience. Again the phrase "An occasional" makes me wonder if
>>>>> this is based on test cases or what exactly.
>>>> Yes, the test case is well specified. It happens that we open lists,
>>>> and the underlying select goes through a huge index but answers
>>>> only 4000 rows or so...
>>>>
>>>> The time to open a list - to execute the test plan - is "occasionaly"
>>>> important, probably when the cache has to be filled. We are here
>>>> talking of times on the order of 20 secs. When the test is retried,
>>>> response time is of course dramatically better.
>>>>
>>>> Now, the 30% was measured between an "occasional" slowdown
>>>> before rebuild, and an "occasional" slowdown after the rebuild.
>>>>
>>>> I realize that the conditions varies wildly. I'm reporting the notes I
>>>>
>>>> took.
>>>
>>> However, my money is on it having nothing to do with just an index
>>> rebuild
>>> ...
>>>
>> Could very well be, Richard. Perhaps you are reading too much in
>> my words ? (which were admittedly very terse in the first report :-)
>>
>> I was actually very careful not to voice any conclusions till now.
>> For the record, the conclusions are :
>>
>> 1) Did the rebuild impact the display of screens ? NO.
>> 2) Did the rebuild impact the night job ? YES, definitely yes.
>> 3) Did the rebuild impact opening lists ? Definitely a suspect area.
>>
>
> Hi Raoul
>
> Now my suspicious mind is cast towards this night job that "YES, definitely
> yes" improved after index rebuilds.
>
> You mentioned earlier that this night job dropped from 5 hours down to 2
> hours. And all from some index rebuilds. Again this is an extraordinary
> result, one that I would have thought warranted further investigation if
> only as previously mentioned by hpuxrac to determine exactly what these
> magical indexes might be.
Is it possible what the OP is seeing is a delayed block cleanout?
--
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 Sun Nov 19 2006 - 19:48:38 CST
