4.1 Administering SQL Processing Offload
To optimize the performance of queries that do table and index scans, the database can offload data search and retrieval processing to the Exadata storage servers. This feature is controlled by database initialization parameters:
The CELL_OFFLOAD_PROCESSING initialization parameter enables SQL processing offload to Oracle Exadata Storage Server.
When the value of the parameter is set to
TRUE, predicate evaluation can be offloaded to cells. The default value of the parameter is
TRUE. If the parameter is set to
FALSE at the session or system level, then the database performs all the predicate evaluation with cells serving blocks. You can set CELL_OFFLOAD_PROCESSING dynamically with the SQL
ALTER SYSTEM or
ALTER SESSION commands, for example:
SQL> ALTER SESSION SET CELL_OFFLOAD_PROCESSING = TRUE;
The CELL_OFFLOAD_PROCESSING parameter can also be set with the
OPT_PARAM optimizer hint to enable or disable predicate filtering for a specific SQL command.
To disable CELL_OFFLOAD_PROCESSING for a SQL command:
SELECT /*+ OPT_PARAM('cell_offload_processing' 'false') */ COUNT(*) FROM EMPLOYEES;
To enable CELL_OFFLOAD_PROCESSING for a SQL command:
SELECT /*+ OPT_PARAM('cell_offload_processing' 'true') */ COUNT(*) FROM EMPLOYEES;
The CELL_OFFLOAD_PROCESSING initialization parameter cannot be used to compare the performance of Oracle Exadata Storage Server with conventional storage. Even when CELL_OFFLOAD_PROCESSING is set to
FALSE, Oracle Exadata Storage Server has many advantages over conventional storage. Oracle Exadata Storage Server is highly optimized for fast processing of large queries. It has no bottlenecks at the controller or other levels inside the cell. Oracle Exadata System Software uses a modern scale-out architecture and a state-of-the-art InfiniBand network that has much higher throughput than conventional storage networks. Oracle Exadata System Software is tightly integrated with the Oracle Database, and has unique capabilities for setup, execution, monitoring, diagnostics, resource management, and corruption prevention.
The database parameter
CELL_OFFLOAD_PLAN_DISPLAY determines whether the SQL
EXPLAIN PLAN command displays the predicates that can be evaluated by Oracle Exadata System Software as
STORAGE predicates for a given SQL command.
The values for the
CELL_OFFLOAD_PLAN_DISPLAY parameter are
NEVER. The default value is
AUTOinstructs the SQL
EXPLAIN PLANcommand to display the predicates that can be evaluated as
STORAGEonly if a cell is present and if a table is on the cell.
ALWAYSproduces changes to the SQL
EXPLAIN PLANcommand based on Oracle Exadata System Software, whether or not Oracle Exadata System Software is present or the table is on the cell. You can use this setting to see what can be offloaded to Oracle Exadata Storage Server before migrating to Oracle Exadata Storage Server.
NEVERproduces no changes to the SQL
EXPLAIN PLANcommand for Oracle Exadata System Software.
You can set the
CELL_OFFLOAD_PLAN_DISPLAY parameter dynamically with the SQL
ALTER SYSTEM or
ALTER SESSION commands. For example:
SQL> ALTER SESSION SET cell_offload_plan_display = ALWAYS;
The CELL_OFFLOAD_DECRYPTION initialization parameter enables decryption offload to Oracle Exadata Storage Servers. This decryption applies to both encrypted tablespaces and encrypted columns. When the value of the parameter is set to
TRUE, decryption can be offloaded to cells. The default value of the parameter is
TRUE. If the parameter is set to
FALSE at the system level, then the database performs all decryption with cells serving blocks. You can set CELL_OFFLOAD_DECRYPTION dynamically with the SQL
ALTER SYSTEM command. For example:
SQL> ALTER SYSTEM SET CELL_OFFLOAD_DECRYPTION = FALSE;
4.2 Overriding the Default Caching Policy
Though typically not required, you can use the
segment storage option to override the automatic caching policy for Exadata Smart Flash Cache.
CELL_FLASH_CACHE option supports the following
NONE: This value ensures that Exadata Smart Flash Cache never caches the corresponding segment. By using this setting on peripheral database segments, more cache space is available for more-important and frequently-accessed database segments.
DEFAULT: This value specifies that database segments are cached using the default LRU (least recently used) algorithm of Exadata Smart Flash Cache. This value is the default setting for
KEEP: This value elevates the segment priority in Exadata Smart Flash Cache. By using this setting, you can increase the likelihood of keeping data from the corresponding segment in the cache.
Example 4-1 Using CREATE TABLE with CELL_FLASH_CACHE
The storage clause can be provided during the
ALTER commands for a table or other objects. This example shows the
CREATE TABLE command for
CELL_FLASH_CACHE use with the storage clause.
CREATE TABLE ptable (c1 number, c2 clob) TABLESPACE TBS_1 PARTITION BY RANGE(c1) ( PARTITION p1 VALUES LESS THAN (100) TABLESPACE TBS_2 STORAGE (CELL_FLASH_CACHE DEFAULT), PARTITION p2 VALUES LESS THAN (200) TABLESPACE TBS_3 STORAGE (CELL_FLASH_CACHE KEEP)); CREATE TABLE tkbcsrbc (c1 number, l1 clob) lob (l1) STORE AS securefile (cache nologging STORAGE (CELL_FLASH_CACHE NONE)) PCTFREE 0 TABLESPACE tbs_93 STORAGE (initial 128K next 128K pctincrease 0);
Example 4-2 Using ALTER TABLE with CELL_FLASH_CACHE
For objects where altering the storage clause is allowed, the
ALTER command can be used with
CELL_FLASH_CACHE, as shown in this example.
ALTER TABLE tkbcsrbc STORAGE( CELL_FLASH_CACHE DEFAULT); ALTER TABLE tkbcsrbc MODIFY LOB (l1) (STORAGE (CELL_FLASH_CACHE KEEP));
Example 4-3 Using Views to Query CELL_FLASH_CACHE Storage Clause
CELL_FLASH_CACHE storage clause attribute can be queried using database views based on the object involved.
SELECT TABLESPACE_NAME, TABLE_NAME, CELL_FLASH_CACHE FROM user_tables WHERE table_name='TKBCSRBC'; SELECT CELL_FLASH_CACHE FROM ALL_INDEXES WHERE index_name='TKBCIDX';
4.3 Administering In-Memory Columnar Caching
The columnar cache is a section of Exadata Smart Flash Cache that stores data in columnar format. When directed by Oracle Database, Exadata automatically uses a portion of the columnar cache to maintain data using the Oracle Database In-Memory format. You do not need to configure anything on Exadata to use this enhancement.
This feature is available if you have licensed the Oracle Database In-Memory option. To enable this feature, use either of the following database instance parameters:
INMEMORY_SIZEdatabase instance parameter to a value greater than zero.
Starting with Oracle Database version 184.108.40.206.200714, you can set
This option enables you to use In-Memory Columnar Caching in Exadata Smart Flash Cache without a dedicated In-Memory cache in the database instance.
You can use the
CELLMEMORY segment option to override
of the default behavior for In-Memory Columnar Caching in Exadata Smart Flash Cache:
SQL> ALTER TABLE table_name [ [ NO ] CELLMEMORY [ MEMCOMPRESS FOR [ QUERY | CAPACITY ] [ LOW | HIGH ] ]
|Options and Clauses||Usage Description|
||Indicates a table is ineligible for the rewrite from 220.127.116.11 columnar flash cache format into the 12.2 Database In-Memory format.|
||Allows a table to be cached in the default Oracle Database 12.2 In-Memory format. You only need to use this clause to undo a previously specified
||This option indicates that In-Memory column store data should be compressed less than if
||Not implemented at this time.|
Example 4-4 Using CELLMEMORY and INMEMORY Options on the Same Table
You can use both
CELLMEMORY on the same table. For example:
CREATE TABLE t (c1 NUMBER) INMEMORY CELLMEMORY MEMCOMPRESS FOR QUERY;
Specifying both options is useful for when you have a low priority table that is unlikely to get loaded in memory. By also specifying
CELLMEMORY you still get columnar performance.
4.4 Administering Exadata Hybrid Columnar Compression
Use these procedures to administer Oracle Database objects that use Exadata Hybrid Columnar Compression.
4.4.1 Determining If a Table Is Compressed
*_TAB_PARTITIONS data dictionary views to determine whether a table or partitioned is compressed.
- Query the
*_TABLESdata dictionary views to determine table compression.
*_TABLESdata dictionary views, compressed tables have
SQL> SELECT table_name, compression, compress_for FROM user_tables; TABLE_NAME COMPRESSION COMPRESS_FOR ---------------- ------------ ----------------- T1 DISABLED T2 ENABLED BASIC T3 ENABLED OLTP T4 ENABLED QUERY HIGH T5 ENABLED ARCHIVE LOWFor partitioned tables, the
*_TABLESdata dictionary views.
- For partitioned tables, query the
*_TAB_PARTITIONSdata dictionary views.
COMPRESSIONcolumn of the
*_TAB_PARTITIONSviews indicates the table partitions that are compressed. The
COMPRESS_FORcolumn indicates the compression method in use for the table or partition.
SQL> SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions; TABLE_NAME PARTITION_NAME COMPRESSION COMPRESS_FOR ----------- ---------------- ----------- ------------------------------ SALES Q4_2004 ENABLED ARCHIVE HIGH ... SALES Q3_2008 ENABLED QUERY HIGH SALES Q4_2008 ENABLED QUERY HIGH SALES Q1_2009 ENABLED OLTP SALES Q2_2009 ENABLED OLTP
4.4.2 Determining Which Rows are Compressed
When Exadata Hybrid Columnar Compression tables are updated, the rows change to a lower level of compression.
For example, the compression level might change from
By sampling the table rows, you can determine the percentage of rows that are no longer at the higher compression level.
- Use the following query to determine the compression level of a row:
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( ownname IN VARCHAR2, tabname IN VARCHAR2, row_id IN ROWID) RETURN NUMBER;
MOVE PARTITIONto set the rows to a higher compression level. For example, if 10 percent of the rows are no longer at the highest compression level, then you might alter or move the rows to a higher compression level.
4.4.3 Changing Compression Level
You can change the compression level for a partition, table, or tablespace.
The following example describes a scenario when you might want to change the compression level.
A company uses warehouse compression for its sales data, but sales data older than six months is rarely accessed. If the sales data is stored in a table that is partitioned based on the age of the data, then the compression level for the older data can be changed to archive compression to free up disk space.
To change the compression level of a partitioned table you can use the
This package performs online redefinition of a table by creating a temporary copy of the table which holds the table data while it is being redefined. The table being redefined remains available for queries and DML statements during the redefinition. The amount of free space for online table redefinition depends on the relative compression level for the existing table, and the new table. Ensure you have enough hard disk space on your system before using the
To change the compression level for a single partition of a partitioned table, you can use the
ALTER TABLE ... MODIFY PARTITIONcommand.
To change the compression level of a non-partitioned table use the
ALTER TABLE ... MOVEcommand with the
To perform DML statements against the table while the
ALTER TABLE ... MOVEcommand is running, you must also add the
To change the compression level for a tablespace, use the
This defines the default for new objects created in the tablespace. Existing objects are not changed or moved.
You can use Automatic Data Optimization (ADO) to create policies that automatically adjust the compression level.
4.4.4 Importing and Exporting Exadata Hybrid Columnar Compression Tables
You can use the
expdp commands to import and export Exadata Hybrid Columnar Compression tables.
Exadata Hybrid Columnar Compression tables can be imported using the
impdp command of the Data Pump Import utility. By default, the
impdp command preserves the table properties and the imported table is Exadata Hybrid Columnar Compression table. The tables can also be exported using the
On tablespaces not supporting Exadata Hybrid Columnar Compression, the
impdp command fails with the following error:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
You can import the Exadata Hybrid Columnar Compression table as an uncompressed table using the
TRANSFORM:SEGMENT_ATTRIBUTES=n option clause of the
An uncompressed or OLTP-compressed table can be converted to Exadata Hybrid Columnar Compression format during import. To convert a non-Exadata Hybrid Columnar Compression table to an Exadata Hybrid Columnar Compression table, do the following:
- Specify default compression for the tablespace using the
ALTER TABLESPACE ... SET DEFAULT COMPRESScommand.
- Override the
SEGMENT_ATTRIBUTESoption of the imported table during import.
4.4.5 Restoring an Exadata Hybrid Columnar Compression Table
The compressed table backup can be restored to a system that supports Exadata Hybrid Columnar Compression, or to a system that does not support Exadata Hybrid Columnar Compression.
When restoring a table with Exadata Hybrid Columnar Compression to a system that supports Exadata Hybrid Columnar Compression, restore the file using Oracle Recovery Manager (RMAN).
When an Exadata Hybrid Columnar Compression table is restored to a system that does not support Exadata Hybrid Columnar Compression, you must convert the table from Exadata Hybrid Columnar Compression to an uncompressed format.
Use the following steps to convert an Exadata Hybrid Columnar Compression table to an uncompressed format.
- Ensure that there is sufficient space to store the data in uncompressed format.
- Use RMAN to restore the Exadata Hybrid Columnar Compression tablespace.
- Alter the table compression from Exadata Hybrid Columnar Compression to
SQL> ALTER TABLE table_name MOVE ONLINE NOCOMPRESS;
Alternatively, you can use the following command to move the data in parallel:
SQL> ALTER TABLE table_name MOVE ONLINE NOCOMPRESS PARALLEL;
If the table partitioned, then alter the compression method for each partition separately. For example:
SQL> ALTER TABLE table_name MOVE PARTITION partition_name NOCOMPRESS ONLINE;
After you convert the table to an uncompressed format, you can optionally use another form of compression, such as OLTP compression or Oracle Database In-Memory compression.
4.5 Administering Oracle Database Features on Exadata
Use these guidelines to use and administer the following Oracle Database features in conjunction with Oracle Exadata Database Machine.
4.5.1 Using Indexes on Exadata
In the past, databases required indexes for good performance. However, Oracle Exadata Database Machine can deliver superior scan rates without using indexes.
Review the application execution plans that use indexes to determine if they would run faster with Exadata Smart Scan Offload. To determine if a scan would be faster when there is no index, make the index invisible to the optimizer. An invisible index is maintained by DML operations, but it is not used by the optimizer.
To make an index invisible, use the following command and substitute the name of the index in place of index_name:
SQL> ALTER INDEX index_name INVISIBLE;
4.5.2 Using SQL Tuning Advisor on Exadata
SQL Tuning Advisor takes one or more SQL statements as input and uses the Automatic Tuning Optimizer to perform SQL tuning on the statements.
The output of SQL Tuning Advisor is in the form of advice or recommendations, along with a rationale for each recommendation and its expected benefit. SQL Tuning Advisor provides information about the following:
Missing and stale statistics
Better execution plans
Better access paths and objects
Better SQL statements
System statistics measure the performance of CPU and storage so that the optimizer can use these inputs when evaluating plans. The database automatically gathers default system statistics, called noworkload statistics, at the first instance startup. System statistics ensure the optimizer is aware of Oracle Exadata Database Machine performance.
Use the following SQL command to see if Oracle Exadata-specific statistics have been gathered.
SELECT pname, PVAL1 FROM aux_stats$ WHERE pname='MBRC';
PVAL1 is null or not set, then the system statistics have not been gathered manually and the default system statistics are being used.
It is not recommended to gather system statistics in Exadata mode for all Exadata environments. Oracle recommends using the defaults for system statistics in most cases. If you have acceptable performance with your current execution plans, then do not gather system statistics in Exadata mode. If the cardinality estimates in your execution plans are accurate, but suffer from the optimizer over-estimating the cost of a full table scan where the full scan performs better, then you should gather system statistics in Exadata mode. For new applications or deployments where the impact can be assessed from the beginning, and dealt with easily if there is a problem, you can gather system statistics in Exadata mode.
The following SQL command gathers system statistics in Exadata mode:
If your new application or new deployment testing reveals that the default system statistics performed as well or better than using manually collected system statistics, you can reset system statistics to their default values using the
DBMS_STATS.DELETE_SYSTEM_STATS procedure. After executing this procedure, shut down and then reopen the database.
Oracle Exadata System Software must be using the latest patch bundle.
4.5.3 Using SQL Plan Management on Exadata
SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by recording and evaluating the execution plans of SQL statements over time.
With SQL Plan management, you build SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system, such as software upgrades or the deployment of new application modules.
You can also use SQL plan management to gracefully adapt to changes such as new optimizer statistics or indexes. You can verify and accept only plan changes that improve performance. SQL plan evolution is the process by which the optimizer verifies new plans and adds them to an existing SQL plan baseline.
Both SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans. Typically, you create SQL plan baselines before significant performance problems occur. SQL plan baselines prevent the optimizer from using suboptimal plans in the future. The database creates SQL profiles when you invoke SQL Tuning Advisor, which you do typically only after a SQL statement has shown high-load symptoms. SQL profiles are primarily useful by providing the ongoing resolution of optimizer mistakes that have led to suboptimal plans.
DBMS_SPM package supports the SQL plan management feature by providing an interface for you to perform controlled manipulation of plan history and SQL plan baselines maintained for various SQL statements. The
DBMS_SPM package provides procedures and functions for plan evolution.
Starting with Oracle Exadata System Software release 19.1, there is a new parameter
DBMS_SPM.CONFIGURE, which can be used with only Exadata Database Machine on-premises and Oracle Cloud deployments. The
AUTO_SPM_EVOLVE_TASK parameter can have one of three values:
ON: The feature is enabled. The SPM evolve advisor creates a task to periodically manage the SQL plan history. The task determines whether there are alternatives and if SQL execution plans should be evolved and accepted. The task runs outside the normal maintenance window in a similar manner to high-frequency statistics gathering.
OFF: The feature is disabled. This is the default value.
AUTO: Oracle Database decides when to use the feature.