RE: Delayed block clean out on Exadata DW

As usual, agreeing with JL, but adding that an interesting genesis of repeatedly seeing the same undo over and over and over again is when after blocks are filled all the further use of those blocks takes place via direct read. Cleaning up only takes place from the shared buffer pool (or you can make it disappear with a move, but don’t get on that treadmill lightly). When you get a block into your non-shared buffer space it still gets rolled to the correct read consistent time, but the cleaned block cannot be written back (because being private, it is only protected for the current session).  

A trickle feed to a DW might well exhibit this inefficiency (whereas changes via direct path should not.)  

Forcefully scanning blocks into the shared pool, such as reading one row per used block via rowid and including at least one column from each row piece for “born multi-block”, subsequently chained, and migrated rows for an object you see chronically involved is a way to make them go away.  

If there is an official utility to do a segment scan into shared buffer and clean out blocks I missed the memo.  

In theory Oracle could create a maintenance list of blocks from direct read sessions and take care of that in slack time (if any slack time windows exist) but I can’t claim I’ve figured out whether that would be net conservative of resources in the general case even if the code magically appeared. Keeping statistics on the segments involved might be more justifiable rather than attempting automation.  

A periodic “slow” scan of an object (especially if you can limit it to blocks touched since last time) is usually much cheaper than periodic “moves” unless we’re talking about time based partitioning where after a certain period of time further change is forbidden, so you can schedule a SINGLE move of that partition.  

This is also a useful exercise before something is made read only for an intended long term (including permanent) but will be read enough to justify the move. Avoid optimizations that don’t pay back for the work done to complete the optimization before the sun burns out. Unless, of course, you’re doing it for fun.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Saturday, January 30, 2021 6:30 AM To: moustafa_dba_at_hotmail.com
Cc: oracle-l
Subject: Re: Delayed block clean out on Exadata DW    

Could you start by supplying the URLs of two or three of the ones you think are most relevant, please.

It's not just delayed block cleanout that could cause this to happen.  

Also, which version of Oracle are you using.  

Regards

Jonathan Lewis    

On Fri, 29 Jan 2021 at 20:29, Moustafa Ahmed <moustafa_dba_at_hotmail.com> wrote:

Hello listers

There are many articles talking about seeing massive cell single block physical read on Exadata combined with delayed block clean outs... What are the best practices to avoid and mitigate such catastrophic thing ?-- http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l