Exadata smart scan is not only limited to predicate filtering, but also possible with column projection. The term Column
projection refers to the Exadata ability to limit the volume of data
transferred between storage tier and database tier by returning only the
columns of interest (that is those in the select list or necessary for join
operations on the database tier).
That is if your query returns only five column from a hundred-column
table. Exadata can eliminate most of the data that would be returned to the
database server by non-exadata storage. This feature is much bigger than you
might expect and it can have a very significant impact on response times.
c##rajesh@QCPR1>
set serveroutput off
c##rajesh@QCPR1>
show parameter cell_offload_processing
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
cell_offload_processing
boolean TRUE
c##rajesh@QCPR1>
alter session set cell_offload_processing=false;
Session
altered.
c##rajesh@QCPR1>
select count(distinct owner) from big_table ;
COUNT(DISTINCTOWNER)
--------------------
31
c##rajesh@QCPR1>
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
3xw054b8k55xu, child number 0
-------------------------------------
select
count(distinct owner) from big_table
Plan hash
value: 1273675752
----------------------------------------------------------------------------------------------------------------------------------------
| Id
| Operation
| Name | Starts | E-Rows | A-Rows |
A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0
| SELECT STATEMENT |
| 1 | |
1 |00:00:31.42 | 2024K| 2024K| |
| |
| 1
| SORT AGGREGATE |
| 1 | 1 |
1 |00:00:31.42 | 2024K| 2024K|
| | |
| 2
| VIEW
| VW_DAG_0 | 1 | 31 |
31 |00:00:31.42 | 2024K| 2024K|
| | |
| 3
| HASH GROUP BY |
| 1 | 31 |
31 |00:00:31.42 | 2024K| 2024K| 2783K|
2783K| 4850K (0)|
| 4
| TABLE ACCESS STORAGE FULL| BIG_TABLE | 1 |
100M| 100M|00:00:23.46 | 2024K|
2024K| 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------------
16 rows
selected.
Without smart scan, the query took around 30 seconds to complete.
c##rajesh@QCPR1>
alter system flush buffer_cache;
System
altered.
c##rajesh@QCPR1>
alter session set cell_offload_processing=true;
Session
altered.
c##rajesh@QCPR1>
select count(distinct owner) from big_table ;
COUNT(DISTINCTOWNER)
--------------------
31
c##rajesh@QCPR1>
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
3xw054b8k55xu, child number 1
-------------------------------------
select
count(distinct owner) from big_table
Plan hash
value: 1273675752
----------------------------------------------------------------------------------------------------------------------------------------
| Id
| Operation
| Name | Starts | E-Rows | A-Rows |
A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0
| SELECT STATEMENT |
| 1 | |
1 |00:00:01.83 | 2024K| 2024K| |
| |
| 1
| SORT AGGREGATE |
| 1 | 1 |
1 |00:00:01.83 | 2024K| 2024K|
| | |
| 2
| VIEW
| VW_DAG_0 | 1 | 31 |
31 |00:00:01.83 | 2024K| 2024K|
| | |
| 3
| HASH GROUP BY |
| 1 | 31 |
31 |00:00:01.83 | 2024K| 2024K| 2783K|
2783K| 4840K (0)|
| 4
| TABLE ACCESS STORAGE FULL| BIG_TABLE | 1 |
100M| 100M|00:00:00.96 | 2024K|
2024K| 1025K| 1025K| 6170K (0)|
----------------------------------------------------------------------------------------------------------------------------------------
16 rows
selected.
With smart scan in place, the query completed in less than a couple of
seconds.
But how could we confirm that smart scan was benefited in the second execution–
with no details in the “predicate information” section to indicate Storage option. Luckily the V$SQL view
contains columns that define the volume of data that might be saved by smart
scan (IO_CELL_OFFLOAD_ELIGIBLE_BYTES) and the volume of data that actually
returned by the storage servers (IO_INTERCONNECT_BYTES).
Looking at those columns from V$SQL for those two above execution,
shows this.
c##rajesh@QCPR1>
select child_number,executions,
2
io_cell_offload_eligible_bytes eligible,
3
io_interconnect_bytes actual
4
from v$sql
5
where sql_id ='3xw054b8k55xu';
CHILD_NUMBER
EXECUTIONS ELIGIBLE ACTUAL
------------
---------- ------------- -------------
0 1
0 16585129984
1 1
16585129984 98257712
c##rajesh@QCPR1>
For the first execution (with child_number=0), with no smart scan
(since “eligible” = 0) we transferred the entire volume of the table (16GB)
from storage to database server (since “actual” = size of the table). However
for the second execution (with child_number=1), with smart scan in place, has
improved the execution by transferring a very small volume (98MB) from storage
to database server.
From this we conclude that smart scan on exadata is possible either by
·
looking at the predicate section in the plan for
storage filtering
·
looking at the V$SQL for IO_CELL_OFFLOAD_ELIGIBLE_BYTES
and IO_INTERCONNECT_BYTES columns.
No comments:
Post a Comment