Well, well, well. Apparently delayed block cleanout doesn’t happen
during a direct path read. Which makes a lot of sense, if several folks are doing direct path reads at the same
time on the same object, then how do you control the updates to the
blocks? In case you don't know, a driect path read is done into the PGA (Program Global Area) which is basically private space to your process. No one else knows you have those blocks. So if two (or more) folks are reading the same block at the same time into their own PGA, then each one could make a change to the block at the same time and really mess things up. Kind of blows up the entire read consistency and locking model.
What is quite interesting to me
is that on windows it’s not doing direct path and on UNIX it is. So the
blocks do get cleaned out on windows BUT not on UNIX. Not my test is at least unlikely to happen frequently
in “real life”.
My test was simple:
Create a big table. Select from it. Delete it all. Commit. Select from it again. Select from it another time.
In 11 and earlier, you’d see the clean outs happen on the first
select after the delete, and then not on the next. But starting in 12 (on UNIX), you’d see the
clean out every time after the delete. Because it's always doing direct path reads, hence it has to clean out the blocks each time it reads them in. It's cleaning out the locks and such left over from the delete, since most of the blocks are not cleaned out at the time of the commit. The commit does clean out some of them. But this table is big enough that there is no way it can get to all of them.
On windows the behavior was like it was in 11. It's not doing direct path it's doing "normal" reads in to the buffer cache.
So change in the
game for how reads are done in 12.
Hi Ric,
ReplyDelete@ In 11 and earlier, you’d see the clean outs happen on the first select after the delete, and then not on the next.
I slightly modified Christian Antognini code from this link:
http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/
Changed:
[code language="sql"]
UPDATE t SET id=id*10;
[/code]
to:
[code language="sql"]
delete t;
[/code]
I'm using Oracle 11.2.0.3 on Linux x86-64 and delayed block cleanout is performed each time on direct path read:
[code language="sql"]
SELECT *
FROM (
SELECT run, name, value-lag(value) OVER (ORDER BY name, run) AS value
FROM s
WHERE name IN ('cleanouts only - consistent read gets',
'session logical reads',
'physical reads',
'physical reads direct')
)
PIVOT (sum(value) FOR run IN (1 AS RUN_1, 2 AS RUN2_2))
ORDER BY name;
NAME RUN_1 RUN2_2
---------------------------------------- ---------- ----------
cleanouts only - consistent read gets 10000 9935
physical reads 10329 10081
physical reads direct 10081 10081
session logical reads 20286 20159
[/code]
Could you please provide test case to reproduce your issue?
Christian Antognini test case is below:
[code language="sql"]
drop table t;
drop table s;
CREATE TABLE t (id NUMBER, pad VARCHAR2(500));
INSERT INTO t
SELECT rownum, rpad('*',500,'*')
FROM dual
CONNECT BY level <= 140000;
commit;
--UPDATE t SET id=id*10;
delete t;
commit;
ALTER SYSTEM FLUSH BUFFER_CACHE;
CREATE TABLE s AS
SELECT 0 AS run, name, value
FROM v$statname NATURAL JOIN v$mystat;
SELECT count(*) FROM t;
INSERT INTO s
SELECT 1, name, value
FROM v$statname NATURAL JOIN v$mystat;
SELECT count(*) FROM t;
INSERT INTO s
SELECT 2, name, value
FROM v$statname NATURAL JOIN v$mystat;
SELECT *
FROM (
SELECT run, name, value-lag(value) OVER (ORDER BY name, run) AS value
FROM s
WHERE name IN ('cleanouts only - consistent read gets',
'session logical reads',
'physical reads',
'physical reads direct')
)
PIVOT (sum(value) FOR run IN (1 AS RUN_1, 2 AS RUN2_2))
ORDER BY name;
TRUNCATE TABLE s;
ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';
INSERT INTO s
SELECT 0 AS run, name, value
FROM v$statname NATURAL JOIN v$mystat;
SELECT count(*) FROM t;
INSERT INTO s
SELECT 1, name, value
FROM v$statname NATURAL JOIN v$mystat;
SELECT count(*) FROM t;
INSERT INTO s
SELECT 2, name, value
FROM v$statname NATURAL JOIN v$mystat;
SELECT *
FROM (
SELECT run, name, value-lag(value) OVER (ORDER BY name, run) AS value
FROM s
WHERE name IN ('cleanouts only - consistent read gets',
'session logical reads',
'physical reads',
'physical reads direct')
)
PIVOT (sum(value) FOR run IN (1 AS RUN_3, 2 AS RUN2_4))
ORDER BY name;
[/code]
Best regards,
Mikhail.
Yes that is exactly correct. But not necessarily true in 12c. The difference is that in 12c with more and more work being done as direct path reads (reads into the PGA not the SGA) the clean out happens but has to happen over and over because DBWR doesn't write the cleaned out blocks back to disk. This is a change to the behavior in 12c.
DeleteThere will be a note published about this soon (its not there yet, I just checked): Delayed Block Cleanout is Not Working as expected in 12c, Doc ID 1925688.1
This comment has been removed by the author.
ReplyDelete