opt_param ( 'cell_offload_processing' , ’TRUE/FALSE’ ) Hint to Enable EXADATA Smart Scan Feature: Speed Comparison with Smart Scan

opt_param( ‘cell_offload_processing’ ’TRUE’) hint is used to enable the exadata smart scan feature in sql execution process. Please see some speed comparison below with and without smart scan.

You can disable the smart scan feature in session level by using following commands
To Disable: alter session set CELL_OFFLOAD_PROCESSING = FALSE;
To Enable: alter session set CELL_OFFLOAD_PROCESSING = TRUE;


I have a table with 24718848 number of rows and 2.6GB size. In this example I am going to demonstrate the execution speed difference with Oracle Exadata Smart Scan feature.

SQL> select count(*) from OBJECT_LIST;

  COUNT(*)
----------
  24718848

SQL> select sum(bytes)/1024/1024/1024 SizeGb from dba_segments where segment_name='OBJECT_LIST';

    SIZEGB
----------
2.67871094
With Smart Scan
SQL> set lines 120
SQL> set pages 100
SQL> set autotrace on explain stat

SQL> l
   select /*+ opt_param('cell_offload_processing' 'true') */
   count(*) from OBJECT_LIST where object_id between 100 and 200
SQL> /

  COUNT(*)
----------
     22272

Elapsed: 00:00:00.36

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

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

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

   2 - storage("OBJECT_ID"<=200 AND "OBJECT_ID">=100)
       filter("OBJECT_ID"<=200 AND "OBJECT_ID">=100)

Here you can see STORAGE key word in execution plan which means it is utilizing the SMART SCAN feautre.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     345484  consistent gets
     345467  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Without Smart Scan

SQL> set lines 120
SQL> set pages 100
SQL> set autotrace on explain stat
SQL> l
   select /*+ opt_param('cell_offload_processing' 'false')*/
   count(*) from OBJECT_LIST where object_id between 100 and 200;


  COUNT(*)
----------
     22272

Elapsed: 00:00:18.40

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

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

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

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

0 comments:

Post a Comment

 

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