Exadata Smartscan For IAS
By Sezgi GECER on Mar 24, 2013
Hello all, In this post we will present an issue, we faced a few weeks ago, when one of our customers run annual job batches on Exadata. During those operations they realized that 'Insert as select (IAS)' was dramatically slower than 'Create table as select (CTAS)' for the same tables and application. There was a really huge difference on duration between them, whilst CTAS was taking 30 seconds, IAS took almost 5 minutes!
We've checked the execution plans of those queries and saw that CTAS was doing Smart Scan, it's wait event was "cell smart table scan" and Cell Offload Effiency %91 but IAS was not doing !
(1)prtscr of 'Create table as select (CTAS)'
Here is the output of IAS execution plan that shows the operation that resulted in the "cell multiblock physical read" wait event on the system.
(2)prtscr of 'Insert as select (IAS)'
Before 11gr2, full table scan access path read all the blocks of a table (or index fast full scan) under high water mark into the buffer cache unless either "_serial_direct_read" hidden parameter is set to true or the table/index have default parallelism set.
In Oracle Database 11gR2, Oracle will automatically decide whether to use direct path reads bypassing buffer cache for serial full table scans.When PARALLEL hint is used or Serial Direct Read is enabled, SMART SCAN is enabled for INSERT DML!
Depending on the table segment size and the db_cache_size, Oracle Database 11g Optimizer may decide to read blocks into buffer cache instead of offloading the data scanning process to the Storage Cells.
Setting "_serial_direct_read" = "true" allows smart scan to be used while using INSERT from SELECT
alter session set "_serial_direct_read"=true;
INSERT INTO table SELECT /*+full(w)*/
By using this setting, Smart Scan worked and query result decreased from 5min to 35sec !!!
Alternative, we could use APPEND hint. CTAS command always uses direct path but INSERT AS SELECT needs an APPEND hint to go direct, so data bypasses buffer caches
INSERT /*+ APPEND */ INTO table SELECT
and then, checked the parallel_degree_policy parameter, it was set on “manual”. Simply altering the table with parallel degree 8 and execute immediate 'ALTER SESSION ENABLE PARALLEL DML';
INSERT /*+ append
parallel */ INTO table SELECT /*+full(w)*/
and the result was a dramatic decrease in IAS duration from the initial 5 minutes to a stunning 8 seconds !!!
Related Note From Oracle Support: Exadata Smartscan Is Not Being Used On Insert As Select [ID 1348116.1]
This is part of the optimization process we usually perform next to a migration on Exadata, making sure that you receive the most out of Oracle's extreme performance platform. For any requests for assistance please feel free to contact us at partner.imc-AT-beehiveonline.oracle-DOT-com. Support.oracle.com should always be your first point of reference for problem resolution, offering a unique resource of all Oracle related topics, while the OPN Exastack Program can help you validate your ISV application as ready and optimimized for usage in our Engineered Systems Platform.