Re: Can anyone explain this huge increase in logical I./O....?

mccmx_at_hotmail.com wrote:
> > CONSISTENT CHANGES: Statistic indicates the number of times a
> > database block has rollback entries applied to perform a consistent
> > read of the block. Work loads that produce a great deal of consistent
> > changes can consume a great deal of resources. The value of this
> > statistic should be small in relation to the 'consistent gets'
> > statistic.
>
> >From an AWR report for the same period I can see:
>
> consistent changes 106,796,588 29,755.6
> 3,611.5
> consistent gets 124,635,243 34,725.8
> 4,214.8
>
> So most of the logical I/O was for blocks that had rollback entries
> applied to them to satisfy a consistent read. This backs up the theory
> that something else was changing the data at the same time.
>
> One thing that is a mystery to me is that the CR count for the index
> lookup is exactly the same in the 2 databases, but the CR count for the
> table fetch is 20 times higher:
>
> 317827 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_GPQ (cr=1613065...
> 317827 INDEX UNIQUE SCAN PS_TM_PEFF_GPQ (cr=1281108
>
> 317827 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_GPQ (cr=40083925...
> 317827 INDEX UNIQUE SCAN PS_TM_PEFF_GPQ (cr=1281108...
>
> Wouldn't the index also be susceptible to rollback changes too..?
>
> Matt

That is great that you found a possible cause.

For a table with no indexes, adding a row to the table typically requires one IO. Creating one index on the table causes each inserted row to generate at least three additional IOs to update the index. Thus, if a table contains five indexes, one IO would be required to add the table row, and at least 15 IOs would be required to update the indexes. So, what happens when the row is updated, but the indexed columns are not changed (specifying the same value that they already are set to is considered a change)? If the indexed columns are not changed, the index does not need to be updated. If the PS_TM_PEFF_GPQ index is on the table's primary key columns, it is understandable why rollback/undo does not need to be applied - the values of a row's primary key columns should not be changed. Changing the values of the primary key columns can cause full table locks for tables with unindexed foreign keys that point to the table whose primary key columns have been changed.

What does the above say? If the database application was designed correctly, little undo should need to be applied to indexes during the execution of a query.

How long does it take for the query to execute on each database? Don't forget, the production database was waiting on the client computer for nearly twice as long, so you can deduct that time difference from the execution time on the production database - assuming that the trace file only contains the time frame when the SQL statement was executed (and not extra time before or after).

Also, keep in mind that Oracle does not immediately flush changed (dirty) blocks from the buffer cache. As Mark indicated, your SQL statement may force Oracle to flush the changed blocks to disk that were updated by another session, in order to make room for the table and index blocks needed by your SQL statement. This will cause additional delays in the execution of your SQL statement. A properly sized database buffer cache is one way to correct for this - not too small and not too large.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Oct 24 2006 - 06:34:02 CDT