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