New Features in Oracle Database 10g Release 2

You can do online redefinition on clustered tables, MV logs, advanced queuing tables, and still preserve database statistics in Release 2. With this feature of preserving statistics, table analysis after reorganization is not needed, thereby improving the application availability. Also, the V$SQLSTATS view has been added to improve performance.

In previous Oracle releases, lock statistics would lock the statistics based on table usage in the application. This meant that you could not get to any statistics including table statistics, column statistics, histograms, and statistics on all the dependent indexes. Also, the database server provided the lock and unlock feature in the optimizer statistic area. In Oracle Database 10g Release 2, you can lock the statistics for a full volatile table and unlock for an empty table using the locking wizard.

You can restore the statistics of the database and related objects to a specified timestamp in the past within the AWR retention period using the OEM Restore Statistics wizard. (In the OEM, click Maintenance, and then click Manage Optimizer Statistics.) See the Operations section of Figure 6.7 for details.

Figure 6.7. The Restore Statistics wizard in the OEM.

You can also gather statistics for fixed and dictionary objects in the optimizer statistic area using the OEM Gather Statistics wizard. (In the OEM, click Maintenance, and then click Manage Optimizer Statistics.) See Figure 6.8 for details.

Figure 6.8. The Gather Statistics wizard in the OEM.

A time out feature has been introduced to flush SQL statistics every five seconds for a running SQL statement. When running, this feature will keep the SQL statistics current at all times.

For earlier releases of Oracle, DBAs using the OEM to check the status of the database would be running SQL calls to collect performance statistics. If the database was hung or running slowly, these SQL calls would further deteriorate the database performance. In Release 2, the OEM can collect key performance statistics of the database without making SQL queries. The OEM attaches to the SGA directly, reads statistics from the shared memory, and populates the database performance screens.