EXADATA Smart Scan - cell_offload_plan_display = NEVER / AUTO /ALWAYS Parameter

CELL_OFFLOAD_PLAN_DISPLAY parameter desides whether the SQL execution plan to show or not to show the offload predicates. This parameter can be dynamically modified by using ALTER SESSION or ALTER SYSTEM command. This parameter will not make any difference to the SQL execution speed.

The parameter values can be
NEVER: This value is not to produce the changes due to the EXADATA system to the SQL execution plan. If you set NEVER as the parameter value you can compare the EXADATA execution plan with normal database execution plan as it won’t show the EXADATA components in the SQL execution plan.   

AUTO: If you set the parameter value as AUTO, the SQL execution plan will display the predicates that can be evaluated as STORAGE only if a cell is present and if the table is on the cell.

ALWAYS: This value gives the SQL execution plan output as whether EXADATA is present or table is on cell.

Example:

Parameter value AUTO
SQL>show parameter cell_offload_plan_display

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
cell_offload_plan_display            string      AUTO
SQL>  set lines 120
SQL> set pages 100
SQL> set autotrace traceonly explain
SQL> select count(*) from object_list
where object_id between 500 and 600;

Execution Plan
----------------------------------------------------------
Plan hash value: 599033881

----------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |     5 | 95299   (1)| 00:19:04 |
|   1 |  SORT AGGREGATE            |             |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| OBJECT_LIST | 18788 | 93940 | 95299   (1)| 00:19:04 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("OBJECT_ID"<=600 AND "OBJECT_ID">=500)
       filter("OBJECT_ID"<=600 AND "OBJECT_ID">=500)

Parameter value NEVER: Storage clause is missing.
SQL> alter session set cell_offload_plan_display=never;

Session altered.

SQL> set autotrace off
SQL> show parameter cell_offload_plan_display

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
cell_offload_plan_display            string      NEVER

SQL> set autotrace traceonly explain
SQL> select count(*) from object_list
where object_id between 500 and 600;

Execution Plan
----------------------------------------------------------
Plan hash value: 599033881

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     5 | 95299   (1)| 00:19:04 |
|   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| OBJECT_LIST | 18788 | 93940 | 95299   (1)| 00:19:04 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"<=600 AND "OBJECT_ID">=500)
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google