6.3.7.4 Monitoring Smart I/O Using SQL Explain Plan
The SQL EXPLAIN PLAN
command displays information about smart I/O
optimizations in the SQL execution plan.
You can use the EXPLAIN PLAN
command to identify parts of a SQL query
that can be offloaded to the storage server. The database parameter
CELL_OFFLOAD_PLAN_DISPLAY
must be set to AUTO
or
ALWAYS
for the EXPLAIN PLAN
command to display the
smart I/O optimizations in the SQL execution plan.
Example 6-5 Monitoring Smart I/O Using SQL Explain Plan
This example shows how to use the EXPLAIN PLAN
command
to display the smart I/O optimizations in the SQL execution plan.
In the query plan, the TABLE ACCESS STORAGE FULL
operation indicates that the corresponding full table scan is offloaded to the
storage server. The predicate information further describes the query predicates
that are offloaded to the storage server.
SQL> ALTER SESSION SET CELL_OFFLOAD_PLAN_DISPLAY = ALWAYS;
Session altered.
SQL> EXPLAIN PLAN FOR
SELECT t.prod_id, v.exp1, t2_prod_id, t2_amount_sold
FROM sales t, v1 v
WHERE t.prod_id = v.prod_id AND t.cust_id = v.cust_id
AND t.prod_id != 45
AND v.amount_sold * v.quantity_sold > 10000;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2267424675
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | TABLE ACCESS STORAGE FULL| SALES |
|* 4 | TABLE ACCESS STORAGE FULL| SALES |
|* 5 | TABLE ACCESS STORAGE FULL | SALES |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."CUST_ID"="T2"."CUST_ID" AND
"T1"."PROD_ID"="T2"."PROD_ID" AND "T1"."CUST_ID"="T2"."CUST_ID")
2 - access("T"."PROD_ID"="T1"."PROD_ID")
3 - storage("T1"."PROD_ID"<200 AND
"T1"."AMOUNT_SOLD"*"T1"."QUANTITY_SOLD">10000 AND "T1"."PROD_ID"<>45)
filter("T1"."PROD_ID"<200 AND
"T1"."AMOUNT_SOLD"*"T1"."QUANTITY_SOLD">10000 AND "T1"."PROD_ID"<>45)
4 - storage("T"."PROD_ID"<200 AND "T"."PROD_ID"<>45)
filter("T"."PROD_ID"<200 AND "T"."PROD_ID"<>45)
5 - storage("T2"."PROD_ID"<200 AND "T2"."PROD_ID"<>45)
filter("T2"."PROD_ID"<200 AND "T2"."PROD_ID"<>45)
Parent topic: Monitoring Smart I/O