Re: Update Statements and Exadata
Date: Sun, 16 Dec 2012 16:34:08 +0200
Message-ID: <CAMHX9JLxhzxgnYwYyHiJxCfWXqqjYBP5-TYbqKvOwO4yrLfjwA_at_mail.gmail.com>
Hi,
There should be no cleanouts done by storage cells. The in-buffer-cache commit cleanout on Exadata works the same way like on non-Exadata - some latest modified blocks are cleaned out in the buffer cache (if they still are in cache) and written back by DBWR later.
When performing a smart scan on unclean blocks then in the worst case the
smart scan has to fall back to regular block IO *for only those blocks* that
require reading of undo (done at the DB layer, not cells). But there are
optimizations around this, like when a storage cell goes back to the DB
layer because it needs info about whether a transaction with XID "123" has
committed or not, it can cache this info in a storage cells commit cache
for any next blocks which may have ITLs with this transaction still active.
The next time it knows, thanks to the commit cache, that this transaction
is already committed. So it does not need to worry about the unclean status
of this block anymore. There's a metric *cell blocks helped by commit
cache* for
that in Exadata - and I've briefly explained this mechanism and some other
related metrics (like the minimum-active-SCN optimization) in our Exadata
book's performance metrics chapter (starting from page 357 in the eBook).
Regarding why an update behaves differently than a plain select regarding
the direct path read decision - yep it looks there's some hard-coded logic
somewhere, which switches to buffered reads for the select part of DML
statements. I guess the logic in the developer's head was that as the
blocks need to be brought into buffer cache anyway for any changing, then
why not read these into buffer cache right away. But this will be
inefficient when you scan through millions of records just to update a few
of these, especially on Exadata. Note that this "feature" is not
Exadata-specific, it's the usual direct path read decision thing
(which has changed
greatly<http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/>since
its introduction).
It affects updates, deletes and also regrettably insert selects (even when using the append hint). There was even a bug logged against this (something like insert-select disables the use of smart scans on Exadata) but I don't know whether it's "fixed" or not as I've just worked around this issue using either the *_serial_direct_read = ALWAYS* setting (11.2.0.2+) or by using parallel DML (without parallel_degree_policy = AUTO) and haven't had to worry much about this "feature".
-- Tanel Poder Blog - http://blog.tanelpoder.com App - http://voic.ee On Thu, Dec 13, 2012 at 11:14 AM, Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>wrote:Received on Sun Dec 16 2012 - 15:34:08 CET
> Correction to my earlier reply on Exadat block cleanout's:
> Storage cell cleanout's are similar to commit time cleanout's except that
> it is being done while full scanning the object. Just like there is no redo
> generated for commit time cleanout, no redo generated for storage cell
> cleanout also. Another way to ensure that that full block cleanout's always
> generate redo is without which physical standby can run into ORA-600 [3020]
> error's.
>
> Thanks,
> Sai
> http://sai-oracle.blogspot.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l