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

From: <mccmx_at_hotmail.com>
Date: 24 Oct 2006 01:53:41 -0700
Message-ID: <1161680021.508895.179550@f16g2000cwb.googlegroups.com>

> 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 Received on Tue Oct 24 2006 - 03:53:41 CDT