4 Administering Oracle Database on Exadata

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:

4.1.1 CELL_OFFLOAD_PROCESSING

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;
    

Note:

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.

4.1.2 CELL_OFFLOAD_PLAN_DISPLAY

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 AUTO, ALWAYS, or NEVER. The default value is AUTO.

  • AUTO instructs the SQL EXPLAIN PLAN command to display the predicates that can be evaluated as STORAGE only if a cell is present and if a table is on the cell.

  • ALWAYS produces changes to the SQL EXPLAIN PLAN command 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.

  • NEVER produces no changes to the SQL EXPLAIN PLAN command 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;

4.1.3 CELL_OFFLOAD_DECRYPTION

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 CELL_FLASH_CACHE segment storage option to override the automatic caching policy for Exadata Smart Flash Cache.

The CELL_FLASH_CACHE option supports the following settings:

  • 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 CELL_FLASH_CACHE.
  • 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 CREATE and 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

The 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:

  • Set the INMEMORY_SIZE database instance parameter to a value greater than zero.

  • Starting with Oracle Database version 19.8.0.0.200714, you can set INMEMORY_FORCE=cellmemory_level.

    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
NO CELLMEMORY Indicates a table is ineligible for the rewrite from 12.1.0.2 columnar flash cache format into the 12.2 Database In-Memory format.
CELLMEMORY and CELLMEMORY MEMCOMPRESS FOR CAPACITY 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 NO CELLMEMORY statement or to change the specified compression level. 
CELLMEMORY MEMCOMPRESS FOR QUERY This option indicates that In-Memory column store data should be compressed less than if MEMCOMPRESS FOR CAPACITY was specified. This option can provide an increase in query performance but requires almost twice as much flash space.
LOW and HIGH Not implemented at this time.

Example 4-4 Using CELLMEMORY and INMEMORY Options on the Same Table

You can use both INMEMORY and 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

Query the *_TABLES or *_TAB_PARTITIONS data dictionary views to determine whether a table or partitioned is compressed.

  • Query the *_TABLES data dictionary views to determine table compression.

    In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column.

    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 LOW
    For partitioned tables, the COMPRESSION column is NULL in the *_TABLES data dictionary views.
  • For partitioned tables, query the *_TAB_PARTITIONS data dictionary views.

    The COMPRESSION column of the *_TAB_PARTITIONS views indicates the table partitions that are compressed. The COMPRESS_FOR column 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 COMP_FOR_QUERY_HIGH to COMP_FOR_OLTP or COMP_NOCOMPRESS.

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;
You can use ALTER TABLE or MOVE PARTITION to 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 DBMS_REDEFINITION package.

    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 DBMS_REDEFINITION package.

  • To change the compression level for a single partition of a partitioned table, you can use the ALTER TABLE ... MODIFY PARTITION command.

  • To change the compression level of a non-partitioned table use the ALTER TABLE ... MOVE command with the COMPRESS FOR clause.

    To perform DML statements against the table while the ALTER TABLE ... MOVE command is running, you must also add the ONLINE clause.

  • To change the compression level for a tablespace, use the ALTER TABLESPACE command.

    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 impdp and 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 expdp command.

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 impdp command.

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:

  1. Specify default compression for the tablespace using the ALTER TABLESPACE ... SET DEFAULT COMPRESS command.
  2. Override the SEGMENT_ATTRIBUTES option 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.

  1. Ensure that there is sufficient space to store the data in uncompressed format.
  2. Use RMAN to restore the Exadata Hybrid Columnar Compression tablespace.
  3. Alter the table compression from Exadata Hybrid Columnar Compression to NOCOMPRESS:

    For example:

    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';

If the 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:

exec dbms_stats.gather_system_stats('EXADATA');

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.

Note:

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.

The 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 AUTO_SPM_EVOLVE_TASK for 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.