Re: Inconsistent file IO times on AWR report
- From: Shaun Batterton <shaun.batterton@xxxxxxxxx>
- To: nkodner@xxxxxxxxx
- Date: Thu, 27 May 2010 10:35:31 -0400
For the select statement, the number of rows returned for this range scan
quadrupled and its throwing most of the rows away.
111544 INDEX RANGE SCAN OP_TRANSACTIONS_PRTY_ID_I (cr=51674
pr=1190 pw=0 time=5585210 us)(object id 238107)
486443 INDEX RANGE SCAN OP_TRANSACTIONS_PRTY_ID_I (cr=128359 pr=7420 pw=0
time=95075128 us)(object id 238107)
On Mon, May 24, 2010 at 12:17 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> That's exactly what's bothering me! That these numbers have increased so
> much. I think I should have enough table statistics history to look back
> on, I'll check that. I can't think of anything else that would drive those
> numbers up so much higher. From one week to the next, I dont think we have
> experienced a dramatic growth in table size.
>
>
> On Mon, May 24, 2010 at 11:22 AM, Marcin Przepiorowski
> <pioro1@xxxxxxxxx>wrote:
>
>> On Mon, May 24, 2010 at 12:38 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
>> > We enabled directIO and set filesystemIO_options to setall last week.
>> Our
>> > first real test was this morning and our main batch jobs ran slower. A
>> few
>> > things noticed was that there was more physical IO, and by looking at
>> the
>> > tkprof (and soon 10046), the cardinality numbers on the explain plans
>> for
>> > nearly all statements were much higher. The same plans were used but
>> the
>> > cardinality numbers on the tkprof were much, much higher.
>> > I feel that this issue relates to optimizer stats and not a direct
>> result of
>> > the parameter change but am not 100% sure. We haven't changed the way
>> we
>> > analyze our tables in weeks.
>>
>> Hi Neil
>>
>> I doubt that changing filesystemIO_options has direct impact on
>> cardinality of joins or
>> selections.
>>
>> Did you have any significant operations on OP_TRANSACTIONS table ?
>>
>> In "before" example number of consistent reads is
>> 72143 for table
>> 51674 for index
>>
>> Physical reads
>> 12115 for table
>> 1190 for index
>>
>>
>> In "after" example numbers are:
>> consistent reads:
>> 191111 for table
>> 128359 for index
>>
>> Physical reads:
>> 41166 for table
>> 7420 for table
>>
>>
>> It look like size of table has been changed or additional CR are
>> related to consistent read (UNDO)
>> mechanism - is there a lot of transactions ?
>>
>> regards,
>>
>> --
>> Marcin Przepiorowski
>> http://oracleprof.blogspot.com
>>
>
>
Other related posts:
