Re: Lazy Write-Back??

Chip,

You are right, Oracle uses a lazy right back. If you ran the third query a second time you would see less io then you did the first time you ran it. This has to do with the way Oracle uses rollback segments.

When you alter a row within Oracle you change the block right away and leave a pointer to the rollback segment you used. (this pointer allows reading processes to construct before image versions of the row you altered)
When you commit, Oracle simply flips a bit in the rollback segment header instead of going back to change all the rows you changed (this is why a commit takes about the same about of time regardless of the amount of data you altered)
The next process to read the block you altered will still see the pointer you left pointing to the rollback segment you used. This second process will follow the pointer to the rollback segment, see that you did in fact commit your change and go back and alter the block to be committed. If a third process read in the same block they would see the record as committed and not have consult any rollback segments.

Hope this sheds some light,

Steve Hoffman, Consulting at Apple Computer

In article <ACD008-261094164527_at_dawes.corp.mot.com>, ACD008_at_email.mot.com (Chip Dawes) wrote:
>
> A colleague and I tried a test to document the impact of the high water
> mark
> on a table that has had a spike in usage but has shrunken back down.
>
> We ran 3 queries. The first was on a new 1100 row table, the second was on
> the same table after increasing the size to 23,000 rows, and the third was
> on the same table after deleting rows back down to 1100. As expected the
> CPU
> and hence the elapsed time for the third query was higher than the first
> but
> lower than the second (it takes fewer CPU cycles to search empty
> datablocks),
> but we were suprised to find MORE logical and physical reads on the third
> query than on the second. It seemed that Oracle did more reading of
> datablocks
> after we deleted some rows than it did before we deleted them.
>
> I think we may have been caught in the "lazy write-back" algorithm that
> Oracle
> uses. I have heard of this but do not fully understand it. Can someone
> explain how it works and if it is why we got more logical/physical reads
> for
> our third query?
>
>
> tkprof'ed resulted of our 3 queries follow:
>
>
> Query 1: 1100 rows in table with low High Water Mark
> ============================================================================
> select * from wip_event1 where event_datetime between
> to_date('05-Sep-94','
> DD-MON-YY') and to_date('06-Sep-94','DD-MON-YY')
>
>
> count cpu elap phys cr cur rows
> Parse: 1 13 20 4 24 0
> Execute: 1 0 9 1 0 2 0
> Fetch: 17 54 61 20 52 0 252
>
> Execution plan:
> TABLE ACCESS (FULL) OF 'WIP_EVENT1'
> ============================================================================
>
>
> Query 2: 23000 rows in table with high High Water Mark
> ============================================================================
> select * from wip_event1 where event_datetime between
> to_date('05-Sep-94','
> DD-MON-YY') and to_date('06-Sep-94','DD-MON-YY')
>
>
> count cpu elap phys cr cur rows
> Parse: 1 2 2 0 0 0
> Execute: 1 0 0 0 0 2 0
> Fetch: 17 776 872 372 441 0 252
>
> Execution plan:
> TABLE ACCESS (FULL) OF 'WIP_EVENT1'
> ============================================================================
>
>
> Query 3: 1100 rows in table with high High Water Mark
> ============================================================================
> select * from wip_event1 where event_datetime between
> to_date('05-Sep-94','
> DD-MON-YY') and to_date('06-Sep-94','DD-MON-YY')
>
>
> count cpu elap phys cr cur rows
> Parse: 1 8 11 3 12 0
> Execute: 1 0 0 0 0 2 0
> Fetch: 17 123 305 407 662 0 252
>
> Execution plan:
> TABLE ACCESS (FULL) OF 'WIP_EVENT1'
> ============================================================================
>
> Thanks,
>
> Chip Dawes
>
>
> --
> I only speak for myself NOT for my employer
Received on Fri Nov 04 1994 - 17:51:06 CET