Smart scan do not occur for every query run on Exadata. There are three
basic requirements that must be met for smart scans to occur.
·
There must be a full scan for an object
·
The scan must be a Direct path read operation.
·
The object must be on Exadata storage.
However, even when those conditions are met, there are cases that prevent
smart scans. Here are the few situations where smart scans
·
Cannot be used on Clustered tables
·
Cannot be used on Index organized tables (IOT’s)
·
Cannot be used on tables created with
ROWDEPENDENCIES enabled.
Here is an Index Organized table, having a huge data set.
demo@PDB1>
select table_name,iot_type,num_rows,avg_row_len
2 from
user_tables
3
where table_name ='T';
TABLE_NAME
IOT_TYPE NUM_ROWS AVG_ROW_LEN
----------
------------ ---------- -----------
T IOT 100000000 135
demo@PDB1>
when we run a simple query against a non-indexed column, it took more
than a min.
demo@PDB1>
show parameter cell_offload_processing
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
cell_offload_processing boolean TRUE
demo@PDB1>
set serveroutput off timing on
demo@PDB1>
select count(*) from t where owner ='SYS';
COUNT(*)
----------
69329593
Elapsed:
00:01:00.94
demo@PDB1>
select * from table( dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID 9cjaxv5d7rg8m, child number 0
-------------------------------------
select count(*)
from t where owner ='SYS'
Plan hash
value: 454320086
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| |
| 529K(100)| |
| 1 |
SORT AGGREGATE | |
1 | 5 | | |
|* 2 |
INDEX FAST FULL SCAN| T_PK |
3225K| 15M| 529K
(1)| 00:00:21 |
------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
The absence of “Storage” filter in the above plan, confirms that smart
scan was not used in this execution.
Also a quick check on V$SQL shows this.
demo@PDB1>
col io_cell_offload_eligible_bytes format 999 heading "OFFLOAD_ELIG"
demo@PDB1>
col io_interconnect_bytes format 999999999999 heading
"BYTES_RETURNED"
demo@PDB1>
select sql_id, sql_text,child_number,
2
io_cell_offload_eligible_bytes ,
3
io_interconnect_bytes
4 from
v$sql
5
where sql_id ='9cjaxv5d7rg8m';
SQL_ID SQL_TEXT CHILD_NUMBER OFFLOAD_ELIG BYTES_RETURNED
-------------
---------------------- ------------ ------------ --------------
9cjaxv5d7rg8m
select count(*) from t 0 0 16039387136
where owner ='SYS'
The V$SQL view contains a column called IO_CELL_OFFLOAD_ELIGIBLE_BYTES,
which shows the number of bytes that are eligible for offloading. This column
can be used as an indicator of weather a statement used a smart scan. It
appears that this column is set to a value greater than zero only when smart
scan is used. Since it zero now, smart scan was not used in this case.
The same goes for the tables created with ROWDEPENDENCIES enabled.
demo@PDB1>
select table_name,dependencies,num_rows,avg_row_len
2 from
user_tables
3
where table_name ='T';
TABLE_NAME
DEPENDEN NUM_ROWS AVG_ROW_LEN
----------
-------- ---------- -----------
T ENABLED 100000000 135
demo@PDB1>
show parameter cell_offload_processing
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
cell_offload_processing boolean TRUE
demo@PDB1>
set serveroutput off timing on
demo@PDB1>
select count(*) from t where owner ='SYS';
COUNT(*)
----------
69329593
Elapsed:
00:00:22.73
demo@PDB1> select
* from table( dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID 9cjaxv5d7rg8m, child number 0
-------------------------------------
select count(*)
from t where owner ='SYS'
Plan hash
value: 2966233522
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 551K(100)| |
| 1 |
SORT AGGREGATE | |
1 | 5 | | |
|* 2 |
TABLE ACCESS STORAGE FULL| T
| 3225K| 15M|
551K (1)| 00:00:22 |
-----------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - storage("OWNER"='SYS')
filter("OWNER"='SYS')
20 rows
selected.
demo@PDB1>
col sql_text format a22
demo@PDB1>
col io_cell_offload_eligible_bytes format 999 heading "OFFLOAD_ELIG"
demo@PDB1>
col io_interconnect_bytes format 999999999999 heading
"BYTES_RETURNED"
demo@PDB1>
select sql_id, sql_text,child_number,
2
io_cell_offload_eligible_bytes ,
3
io_interconnect_bytes
4 from
v$sql
5
where sql_id ='9cjaxv5d7rg8m';
SQL_ID SQL_TEXT CHILD_NUMBER OFFLOAD_ELIG BYTES_RETURNED
-------------
---------------------- ------------ ------------
--------------
9cjaxv5d7rg8m
select count(*) from t 0 0 16603504640
where owner ='SYS'
With Clustered tables, it goes like this:
demo@PDB1>
select table_name, cluster_name,num_rows,blocks
2 from
user_tables
3
where cluster_name is not null;
TABLE_NAME
CLUSTER_NAME NUM_ROWS BLOCKS
----------
-------------------- ---------- ----------
DEPT EMP_DEPTNO_CLUST 10000
1063434
EMP EMP_DEPTNO_CLUST 100000000 1063434
demo@PDB1>
show parameter cell_offload_processing
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
cell_offload_processing boolean TRUE
demo@PDB1>
set serveroutput off timing on
demo@PDB1>
select count(*) from emp where job='TABLE';
COUNT(*)
----------
3377882
Elapsed:
00:00:14.85
demo@PDB1>
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID ay9rgugytpy1k, child number 0
-------------------------------------
select count(*)
from emp where job='TABLE'
Plan hash
value: 2083865914
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 289K(100)| |
| 1 |
SORT AGGREGATE | |
1 | 10 | | |
|* 2 |
TABLE ACCESS STORAGE FULL| EMP
| 2941K| 28M|
289K (1)| 00:00:12 |
-----------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='TABLE')
19 rows
selected.
demo@PDB1>
col sql_text format a22
demo@PDB1>
col io_cell_offload_eligible_bytes format 999 heading "OFFLOAD_ELIG"
demo@PDB1>
col io_interconnect_bytes format 999999999999 heading
"BYTES_RETURNED"
demo@PDB1>
select sql_id, sql_text,child_number,
2
io_cell_offload_eligible_bytes ,
3
io_interconnect_bytes
4 from
v$sql
5
where sql_id ='ay9rgugytpy1k';
SQL_ID SQL_TEXT CHILD_NUMBER OFFLOAD_ELIG BYTES_RETURNED
-------------
---------------------- ------------ ------------
--------------
ay9rgugytpy1k
select count(*) from e 0 0 8699068416
mp where job='TABLE'
demo@PDB1>
In all the above three cases, we see IO_CELL_OFFLOAD_ELIGIBLE_BYTES is
zero, which confirms that smart scan was not possible for those workloads
targeted on Index organized and clustered tables and for those tables created
with ROWDEPDENCIES enabled.
No comments:
Post a Comment