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!
