Hello,
I have a question about smart scan. I would like to know if it is really using offloading.
I use the following query to get queries that its using or not smart scan:
select sql_id, child_number child, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions)/
decode(px_servers_executions,0,1,px_servers_executions/decode(nvl(executions,0),0,1,executions)) avg_etime,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)
/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) "IO_SAVED_%",
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like 'BEGIN :sql_text := %'
and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/
A simple example is the following query:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID a4x7zt79c6s9b, child number 0
-------------------------------------
SELECT tab1.column1, tab1.column2, tab1.column3
FROM tab1, tab2
WHERE tab1.column5 = tab2.column5
AND tab1.column1 = 1000 AND tab1.column2 = 1 AND tab1.column3 = 'Z'
Plan hash value: 944094920
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
.
.
.
|* 10 | TABLE ACCESS STORAGE FULL| tab1 | X | X | X (48) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(:X1>=:X2).....
10 - storage((tab1.column1 = 1000 AND tab1.column2 = 1 AND tab1.column3 = 'Z'))
I would like to understand why on the predicate there is the storage((tab1.column1 = 1000 AND tab1.column2 = 1 AND tab1.column3 = 'Z'))
but when I query v$sql column IO_CELL_OFFLOAD_ELIGIBLE_BYTES IS "0" indicating that its not using smart scan/offloading.
I didn't understand this specific case.
It's like it's not using offloading /smart scan.
What can I do to use smart scan? cost_adj = 5 and caching = 100. Do I need virtual columns?
Somebody can help me, please?
Thank you very much!