Configuring and using SQL Baselines

We have seen the importance of execution plan stability in Managing stored outlines.

The disadvantage (by design) of using stored outlines is in the rigidity—we are sure that our execution plans don't change—so the performance doesn't deteriorate. However, due to schema or data changes, there could be a better execution plan. We are bound to our execution plan that is stored in the outlines, and we cannot benefit from the improvements.

SQL Plan Management with SQL Plan Baselines, a feature new to Oracle Database 11g, helps us in obtaining planning stability without losing the opportunity for performance improvements.

Getting ready

To create SQL Baselines we need the ADMINISTER SQL MANAGEMENT OBJECT privilege.

Connect as SYSDBA and grant permission to the user SH:

CONNECT / AS SYSDBA
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO SH;

How to do it...

The following steps will show how to configure and use SQL Baselines:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh...