advertisements
_____________________________________________________________________________________________________________________
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.
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)
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment