The storage server in the cell nodes of Exadata database machine very well understands various predicates of SQL and take decisions to return data accordingly.
In its bid to return data intelligently to make the process faster, the storage server software utilize smart scan, storage indexes, rman block scanning and other cool features. Generally speaking cell offload means that database server at the compute node offloads its task of finding the exact data to be returned to user to the storage server at cell node.
If you would like to see that how much data related to your query has been offloaded to the cell node then you can utilize the SQL monitor:
SQL> set lines 100
SQL> set pages 100
SQL> set long 10000000
set longchunksize 10000000
set linesize 200
select dbms_sqltune.report_sql_monitor from dual;
SQL> SQL> SQL>
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : test (944:5510)
SQL ID : 55555555555
SQL Execution ID : 444
Execution Started : 04/04/2011 07:45:37
First Refresh Time : 04/04/2011 07:45:38
Last Refresh Time : 04/04/2011 07:46:11
Duration : 35s
Module/Action : test
Service : test
Program : test
DOP Downgrade : 88%
SQL Plan Monitoring Details (Plan Hash Value=560085029)
========================================================================================================================================================================================================
=
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail
|
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | | (%) | (# samples)
|
========================================================================================================================================================================================================
=
| 0 | SELECT STATEMENT | | | | | | 17 | | | | | | |
|
| 1 | PX COORDINATOR | | | | | | 17 | | | | | | |
|
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 27335 | | | 16 | | | | | | |
|
| -> 3 | HASH JOIN | | 1 | 27335 | 33 | +3 | 16 | 0 | | | | 22M | |
|
| 4 | BUFFER SORT | | | | 1 | +3 | 16 | 26112 | | | | | |
|
| 5 | PX RECEIVE | | 5311 | 102 | 1 | +3 | 16 | 26112 | | | | | |
|
| 6 | PX SEND BROADCAST | :TQ10000 | 5311 | 102 | 1 | +2 | 1 | 26112 | | | | | |
|
| 7 | TABLE ACCESS STORAGE FULL | test1 | 5311 | 102 | 1 | +2 | 1 | 1632 | | | | | |
|
| -> 8 | PX BLOCK ITERATOR | | 511M | 27216 | 33 | +3 | 16 | 3M | | | | | 0.18 | Cpu (1)
|
| -> 9 | TABLE ACCESS STORAGE FULL | test2 | 511M | 27216 | 35 | +1 | 7774 | 3M | 79030 | 73GB | 84.78% | | 99.82 | Cpu (125)
|
| | | | | | | | | | | | | | | cell smart table scan (434)
|
=======================================================================================================================================================================
No comments:
Post a Comment