Re: Lazy Write-Back??

In article <ACD008-261094164527_at_dawes.corp.mot.com> ACD008_at_email.mot.com (Chip Dawes) writes:
>From: ACD008_at_email.mot.com (Chip Dawes)
>Subject: Lazy Write-Back??
>Date: Wed, 26 Oct 1994 21:50:35 GMT
 

>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?

What else was going on? Is it possible that it didn't have to do physical reads for the first part of your second query because the DB block buffer already had them from the first query. Then on the third query it couldn't find them in the buffer so had to read them again. This would happen even without any other activity if you buffer is smaller that the total blocks since the least recently used blocks would have been the first part of the table and would have been reused for the end of the table in your second query.

If you want a repeatable measure of physical reads you need to flush any possible existing blocks. I do it by selecting from a really large table unrelated to my test. That fills the buffer up with that table and I know that none of my test tables are in it.

>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 Sat Oct 29 1994 - 01:11:48 CET