3.4.4 Using Exadata System Statistics

System statistics measure the performance of CPU and storage so that the Oracle Database optimizer can use these inputs when evaluating SQL execution plans.

During first instance startup, Oracle Database automatically gathers default system statistics, which are also known as noworkload statistics. However, you can also gather Exadata-specific system statistics. The Exadata system statistics ensure that the SQL optimizer factors in the performance characteristics of Oracle Exadata Database Machine.

Use the following SQL command to see if Exadata-specific statistics are in use.

SQL> SELECT pname, PVAL1 FROM aux_stats$ WHERE pname='MBRC';

If PVAL1 is NULL, then the default system statistics are in use.

The following command gathers Exadata system statistics for use by the database optimizer:

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');

If required, you can revert back to the default system statistics by using the DBMS_STATS.DELETE_SYSTEM_STATS procedure and then restarting the database.

For new applications or deployments, you should perform pre-production system testing to compare the default system performance with the performance using Exadata system statistics.

For existing applications or deployments, Exadata system statistics should be generated and tested on an equivalent test system before they are first used on a production system. This approach mitigates the risk of an unexpected performance regression on a production system.

If an equivalent test system is unavailable, you can perform more realistic testing by copying the production Exadata system statistics to your test system. The following outlines the procedure:

1. On the production system, create a statistics table.

   For example:

   SQL> exec DBMS_STATS.CREATE_STAT_TABLE(stattab => 'exadata_stats');

2. On the production system, gather the Exadata system statistics into the newly created table.

   For example:

   SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'EXADATA', stattab => 'exadata_stats');

   Statistics stored in a user-defined statistics table are not used by the system. So, gathering these statistics does not impact the production system performance.

3. Copy the statistics table from the production system to the test system.

   You can copy the table by various means, including data pump export and import.

4. On the test system, import the statistics table into the Oracle data dictionary.

   For example:

   SQL> exec DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => 'exadata_stats');