Re: Update Statements and Exadata

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Mon, 17 Dec 2012 01:05:15 -0800 (PST)
Message-ID: <1355735115.26024.YahooMailNeo_at_web161305.mail.bf1.yahoo.com>

Thanks Tanel. I see it now. Couple of interesting behavior's in the below test case: alter session set "_serial_direct_read"=true;

drop table dbc1 purge;

create table dbc1(a number, b char(1000)) tablespace tools;

insert /*+ append */  into dbc1 select rownum, 'A' from dual connect by level <= 100000;

commit;

exec dbms_stats.gather_table_stats('OPS$ORACLE','DBC1');

update dbc1 set a=a+1;

• Flush buffer cache from another session.

exec dbms_lock.sleep(25);

commit;

select a.statistic#, b.name, a.value from v$mystat a, v$statname b where
a.statistic#=b.statistic# and
a.value <> 0 and
(b.name like '%commit%' or b.name like '%read%' or b.name like '%write%') /

set autotr on

select a from dbc1 where a™999;

set autotr off

select a.statistic#, b.name, a.value from v$mystat a, v$statname b where
a.statistic#=b.statistic# and
a.value <> 0 and
(b.name like '%commit%' or b.name like '%read%' or b.name like '%write%') /

select sql_id, sql_text, IO_CELL_OFFLOAD_ELIGIBLE_BYTES from V$sql where plan_hash_value34110983;

In the above test case, first session perform smart scan due to direct path reads for the serial scan and it does report block cleanout stats (even though it didn't really perform any real cleanout, probably meant for optimized cleanout's as you indicated).

In the second session, don't set "_serial_direct_read" parameter and try executing the query "select a from dbc1 where a™999", you will still direct path reads (this is what led to wrong conclusion). Once the query execution plan is influenced with "_serial_direct_read" parameter at session level, second session also use the same execution plan even though "_serial_direct_read" isn't set.

The moment I changed the second session query to something different, it did perform block cleanout's proving that the smart scan session didn't really do cleanout.

Thanks,
 Sai
http://sai-oracle.blogspot.com

---

 From: Tanel Poder <tanel_at_tanelpoder.com> To: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com> Cc: free <oracle-l_at_freelists.org>
Sent: Sunday, December 16, 2012 6:34 AM
Subject: Re: Update Statements and Exadata  

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

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

Received on Mon Dec 17 2012 - 10:05:15 CET