Re: Inconsistent file IO times on AWR report

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: