Lazy Write-Back??

From: Chip Dawes <ACD008_at_email.mot.com>
Date: Wed, 26 Oct 1994 21:50:35 GMT
Message-ID: <ACD008-261094164527_at_dawes.corp.mot.com>

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 Wed Oct 26 1994 - 22:50:35 CET