Exadata Smart Scan predicate offloading and sequence.NEXTVAL

There was a question in the twitter-sphere about whether using sequences (sequence.NEXTVAL) in your select query’s projection list would somehow disable smart scans happening?

The answer is no, sequence use with smart scans works just fine. The smart scan offloading applies to data retrieval row sources (and filtering) only and nothing else. So, what you have in the query’s projection list (the sequence use for example), does not directly affect the smart scan decision. Just like any other operations like sorting, grouping etc, do not have anything to do with smart scans and don’t disable their use. Smart scans are only related to data retrieval and any other operations do not affect them.

In other words, sequence.NEXTVAL can be used in a query and you can still get smart scans. Here’s simple proof:

 


SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ MONITOR */ t.object_name, s.NEXTVAL n FROM t WHERE owner LIKE 'TANEL%'

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  TANEL (204:2707)
 SQL ID              :  5kzs2ucxwf4wk
 SQL Execution ID    :  16777216
 Execution Started   :  04/13/2012 09:06:46
 First Refresh Time  :  04/13/2012 09:06:46
 Last Refresh Time   :  04/13/2012 09:06:48
 Duration            :  2s
 Module/Action       :  SQL*Plus/-
 Service             :  dbm
 Program             :  sqlplus@mac02.local (TNS V1-V3)
 Fetch Calls         :  4

Global Stats
========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Fetch | Buffer | Read | Read  |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Calls |  Gets  | Reqs | Bytes | Offload |
========================================================================================
|    1.50 |    0.36 |     1.06 |        0.08 |     4 |   323K | 2503 |   2GB |  99.97% |
========================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1589210007)
======================================================================================================================================================================
| Id |          Operation           | Name |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  |  Cell   | Activity |      Activity Detail      |
|    |                              |      | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload |   (%)    |        (# samples)        |
======================================================================================================================================================================
|  0 | SELECT STATEMENT             |      |         |       |         3 |     +0 |     1 |     1408 |      |       |         |          |                           |
|  1 |   SEQUENCE                   | S    |         |       |         3 |     +0 |     1 |     1408 |      |       |         |          |                           |
|  2 |    TABLE ACCESS STORAGE FULL | T    |    439K | 20738 |         4 |     +0 |     1 |     1408 | 2496 |   2GB |  99.97% |   100.00 | cell smart table scan (1) |
======================================================================================================================================================================

I have highlighted the query text (which includes sequence.NEXTVAL) and the TABLE ACCESS STORAGE FULL row source, if you scroll right, you’ll see the “cell smart table scan” wait event and also the Offload % column populated, which both indicate that smart scan did happen for that monitored query execution.

Note that often I hear a saying, something like “xyz disables smart scan”, or “using a non-offloadable function disables the smart scan”. This can be misleading and give the impression that if you use a non-Exadata supported function/feature in one part of your query, then smart scanning is disabled for the whole query. This is incorrect.

First of all, the smart scanning is not a query level thing, it’s an access path level thing. So, you can easily run a two table join on one table which is on NFS storage (no smart scanning) and another table, which is on Exadata storage (which allows smart scanning). So, even though smart scanning is “disabled” for the first table, the other table still can be accessed using it! By the way, it’s even possible to have some partitions of your table on NFS and other partitions of the same table on Exadata storage cells and the same table scan can use smart scanning for the partitions stored on the cells and regular buffered or direct path read access for the partitions on NFS. So, smart scanning is not some “all or nothing” query level thing, this decision is made separately for every single segment full scanned in the query. More details in the book.

Secondly, using a non-offloadable function in a filter predicate when retrieving rows does not prevent Oracle to push other, offloadable, predicate functions to be pushed to the storage cells. So again, it’s not an all-or-nothing decision. If a predicate function is not offloadable, it will just not be offloaded (and will show up as just a filter() predicate as opposed to both storage() and filter() predicate), but all the other supported predicates can still be offloaded without a problem. Here’s an example:


SELECT * FROM t 
WHERE 
     line = 9999
AND  mod(line,10) = 0 
AND  f1(line) =9999

Plan hash value: 1601196873

----------------------------------------------------------------
| Id  | Operation                 | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |        |  2887 (100)|
|*  1 |  TABLE ACCESS STORAGE FULL| T    |      1 |  2887   (1)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage(("LINE"=9999 AND MOD("LINE",10)=0))
       filter(("LINE"=9999 AND MOD("LINE",10)=0 AND "F1"("LINE")=9999))

 

Note that the F1() function is a PL/SQL function and filtering based on PL/SQL functions can not be offloaded (as Exadata cells don’t run PL/SQL). This is visible in the predicate section where two predicates are shown in the storage() predicate list but the third one (F1) only shows up in the filter() predicate list.

So, as I explained in the beginning, the smart scan is not an all-or-nothing thing, this decision is made for every segment (partition) separately during query execution phase, again for every execution, of course assuming that we are doing a full table scan (of fast full index scan) on them – that part is fixed in the execution plan.

And as shown above, predicate offload is not an all-or-nothing thing either. If one predicate can’t be offloaded, then other predicates still can.

However, there’s a catch. If you combine such predicates with ANDs, Oracle can push the offloadable (storage()) predicates to the storage cells, where they will do the early filtering – and the last, non-offloadable (filter()) predicate is applied then on the retrieved dataset in the database layer.

But when you combine such predicates with an OR clause, then there’s a logical problem here. OR has to return rows which match ANY of the predicates (not ALL the predicates), therefore you can’t only return a subset of rows from the storage cells which match the predicate A and hope that this set contains all the rows which would match the (non-offloadable) predicate B as well. To evaluate B, you would still need to go through all the rows, but as B is non-offloadable, this has to be done in the database layer. All rows have to be fetched into the database layer for this.

This explains why the storage() predicate disappears when I chain my query predicates together with an OR instead of the AND:


SELECT * FROM t 
WHERE 
     line = 9999
AND  mod(line,10) = 0 
OR   f1(line) =9999

Plan hash value: 1601196873

----------------------------------------------------------------
| Id  | Operation                 | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |        |  2963 (100)|
|*  1 |  TABLE ACCESS STORAGE FULL| T    |   6353 |  2963   (3)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("F1"("LINE")=9999 OR ("LINE"=9999 AND MOD("LINE",10)=0)))

The storage() predicate section above is gone – as Oracle has to retrieve all the rows to the database layer anyway (due to the non-offloadable F1 function used in an OR predicate), it doesn’t make sense to offload the other filters to the storage (and then return the row anyway as it’s needed in the db layer). Makes sense? ;-)

Note that even though the storage() predicates disappeared from the plan, this doesn’t mean that the entire smart scanning has been disabled, the smart scanning can still happen, it’s only the predicate offloading sub-component of the smart scan what is not used. The storage cells can still do the asynchronous prefetching, decompression offloading and projection offloading in these cases.

By the way, it’s all explained in the book!

This entry was posted in Exadata, Oracle. Bookmark the permalink.

5 Responses to Exadata Smart Scan predicate offloading and sequence.NEXTVAL

  1. A very intelligent, skilled individual with access to an Exadata system. That could be dangerous for the StoryTellers(tm) :-)

  2. Vivek says:

    Excellent explanation Tanel. I could also reproduce the smart scan using Oracle Sequences and am therefore wondering, where my analysis went wrong. The original ctas had to be killed in 2 hours v/s 3 minutes. The original trace shows that the waits were on cell physical multiblock read. It means, there is something else that is impacting and requires investigation.

    Will post an apology blog for my readers, as the analysis was incorrect.

    Regards
    Vivek

    • Tanel Poder says:

      No problems man, I’ve gotten to wrong conclusions plenty of times myself. And I think even blogged “junk” a few times too … just can’t remember the exact articles anymore :)

      When we started researching Exadata more seriously then the direct path read decision was the most troublesome to figure out. It is based on the segment size/HWM, _small_table_threshold (which is derived from the current buffer cache size) AND the number of buffers of the scanned segments currently in cache (X$KCBOQH.NUM_BUF). So, if your buffer cache dynamically changes, this decision may change, if your table/partition size grows, this decision may change. If some other workload loads enough buffers of that segment into cache, then again the direct path read decision may change and the smart scan is dependent on that.

      So, it took a while to figure this out well enough. Nowadays, if I want to *force* a serial smart scan for testing, I’ll just use _serial_direct_read = ALWAYS (and compile a new child cursor and ensure that the exec plan still has the full table scan in it). If you force the smart scan and it works when forced, then you’ll at least know it works in principle and it’s matter of finding out what dynamic decision causes it to not be used in specific circumstances.

  3. yousuf says:

    Hi Tanel,

    Just one request.. Need some more information about sequence has to be “NOORDER” specially in RAC env

    Sequence with order clause will create contention & hence need to use “NOORDER” clause for sequences in RAC. (Does SEQ$ table will create any problem or contention?)

    Please request to shed some light on this..

    Thanks,
    Yousuf

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>