exadata中的offloading特性

syhnd

exadata中的offloading包括很多特性，比如：Column Projection、Predicate Filtering、Storage Indexes、Bloom Filters、Function Offloading等等。

如果我将cell_offload_processing参数设置为false，则是否offloading中的这些特性将全部关闭？

ora-hansey

有exadata玩的人，真羡慕哈

Maclean Liu(刘相兵

A example :

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
   
6. PL/SQL Release 11.2.0.1.0 - Production
   
7. CORE    11.2.0.1.0      Production
   
8. TNS for Linux: Version 11.2.0.1.0 - Production
   
9. NLSRTL Version 11.2.0.1.0 - Production
   
10. 
    
11. 5 rows selected.
    
12. 
    
13. 
    
14. SQL> show parameter cell_offload
    
15. 
    
16. NAME                                 TYPE                             VALUE
    
17. ------------------------------------ -------------------------------- ------------------------------
    
18. cell_offload_compaction              string                           ADAPTIVE
    
19. cell_offload_decryption              boolean                          TRUE
    
20. cell_offload_parameters              string
    
21. cell_offload_plan_display            string                           AUTO
    
22. cell_offload_processing              boolean                          TRUE
    
23. 
    
24. 
    
25. SQL> set timing on;
    
26. 
    
27. SQL> set autotrace on;
    
28. 
    
29. SQL> select count(*) from MAC.MACLEAN ;
    
30. 
    
31.   COUNT(*)
    
32. ----------
    
33. 543144693
    
34. 
    
35. 1 row selected.
    
36. 
    
37. Elapsed: 00:01:24.46
    
38. 
    
39. Execution Plan
    
40. ----------------------------------------------------------
    
41. Plan hash value: 2967464386
    
42. 
    
43. ------------------------------------------------------------------------------------------
    
44. | Id  | Operation                  | Name                | Rows  | Cost (%CPU)| Time     |
    
45. ------------------------------------------------------------------------------------------
    
46. |   0 | SELECT STATEMENT           |                     |     1 |  7169K  (1)| 23:53:59 |
    
47. |   1 |  SORT AGGREGATE            |                     |     1 |            |          |
    
48. |   2 |   TABLE ACCESS STORAGE FULL| MACLEAN             |   547M|  7169K  (1)| 23:53:59 |
    
49. ------------------------------------------------------------------------------------------
    
50. 
    
51. 
    
52. Statistics
    
53. ----------------------------------------------------------
    
54.           1  recursive calls
    
55.           0  db block gets
    
56.    32731859  consistent gets
    
57.    32731502  physical reads
    
58.           0  redo size
    
59.         530  bytes sent via SQL*Net to client
    
60.         519  bytes received via SQL*Net from client
    
61.           2  SQL*Net roundtrips to/from client
    
62.           0  sorts (memory)
    
63.           0  sorts (disk)
    
64.           1  rows processed
    
65.                   
    
66. SQL> alter session set cell_offload_processing=false;
    
67. 
    
68. Session altered.                  
    
69.                   
    
70. SQL> select count(*) from MAC.MACLEAN ;
    
71. select count(*) from MAC.MACLEAN
    
72. 
    
73. take about 45 minutes here!

复制代码

Maclean Liu(刘相兵

cell_offload_processing enable SQL processing offload to cells

The Smart Scan feature of the Exadata Storage Server Software will offload
processing of certain SQL operations to the Exadata Storage Servers, thereby
allowing the data to be processed closer to where it resides. SQL offload
processing is enabled by default at the database instance level by the
initialization parameter CELL_OFFLOAD_PROCESSING.
When CELL_OFFLOAD_PROCESSING is TRUE, the database will
attempt to offload the SQL to be processed by the Exadata Storage Servers.
The decision to offload the SQL is a runtime decision and depends on
several factors, which are detailed in the Exadata Storage Server Software
Users Guide. When CELL_OFFLOAD_PROCESSING is set to FALSE, the
database processes the SQL in the database servers and the Exadata Storage
Servers will merely serve regular blocks to the database nodes.

CELL_OFFLOAD_PROCESSING can be set at the system and the session
level by using the ALTER SYSTEM or the ALTER SESSION commands. This
command enables SQL offload processing for the session executing the
command:
SQL> ALTER SESSION SET CELL_OFFLOAD_PROCESSING = TRUE;
You can also enable (or disable) the CELL_OFFLOAD_PROCESSING
for a SQL by using the OPT_PARAM hint. The SQL-level setting overrides
the session-level settings, which in turn overrides the system-level settings.
This example will enable offload processing only for the SQL using the
OPT_PARAM hint:
SQL> SELECT /*+ OPT_PARAM('cell_offload_processing', 'true') */ COUNT(*)
FROM SALES;
Whether the SQL query is offloaded to the Exadata Storage Server for
processing or not, you can always look at the execution plan of the SQL
with the EXPLAIN PLAN command and determine the predicate or portion
of the SQL that will be a candidate for offload processing. The EXPLAIN
PLAN command has been enhanced to display the offloaded predicates in
the storage section of its output. This feature of EXPLAIN PLAN exists even
when you do not have the Exadata Storage Servers as the storage grid
serving the database.