Oracle SQL profile and baselines

The differences between SQL profile and baselines in Oracle® and explains how it works when tuning a query.

Optimizer, profile, and baseline

At a high level, these three elements work together as follows:

• The query optimizer uses information like system statistics, bind variables, and compilations to get the best plan for query execution. However, sometimes input defects lead to a sub-optimal plan.

• SQL profiles contain auxiliary information that mitigates this issue. They minimize these mistakes and help the optimizer select the best plan.

• SQL plan baselines for SQL statements consist of a set of accepted plans. After parsing the statement, the optimizer selects the best plan from the set of accepted plans. If the cost optimizer finds another good plan, it adds the new plan to the plan history. However, the optimizer doesn’t use the new plan until it verifies that it will perform better than the currently accepted plan.

Think of it this way: SQL profiles give information to the optimizer to help select the best plan but don’t force the optimizer to select any specific plan. SQL plan baselines limit the optimizer plan selection to a set of accepted plans. If you want to consider cost-based plans, include them in the accepted baseline set of plans.

Use SQL profiles if you want the optimizer to use a least-cost plan and need to use the latest statistics. 

Use baselines when you want to use one of a specific set of plans. 

If SQL plan baselines can’t get the best plan from the accepted set, use the SQL profile instead.

SQL Plan Management

SQL Plan Management (SPM) has the following components:

• Plan capture
• Plan selection
• Plan evolution

SPM PLAN CAPTURE

When you execute any statement, the system hard parses it and generates a cost plan according to the available SQL profile. After it selects a cost-based plan, it compares the plans available in the SQL plan baselines. If a cost-based generated plan matches one of the accepted plans, then you can use that plan. If the plan does not match, the system adds it to the plan baselines as an unaccepted plan.

SPM PLAN SELECTION

When you execute the SQL statement with a baseline plan, it opts for the best plan for that SQL. The optimizer uses the same process. The available SQL profile also affects the estimated cost for each plan and selects a plan accordingly.

SPM PLAN EVOLUTION

The last component of SPM is the evolution of unaccepted plans, which tests the unaccepted plans against the accepted plans. This process assesses the best plan considering time taken by a query and the CPU resources required. It accepts the best plan according to the cost of the query. If an SQL profile is available, then it affects the estimated cost.

Profile versus baseline comparison

The following table, from https://www.cnblogs.com/princessd8251/articles/3637461.html, compares SQL profiles and SQL plan baselines:

Source :- https://docs.rackspace.com/blog/oracle-sql-profile-and-baselines/

Conclusion

When you execute any SQL statement, the optimizer creates an execution plan to parse the query, retrieve the data from the hard disk, and place it in memory. The SQL profile and baselines guide the optimizer to choose the least costly plan in terms of time and CPU cost. A good SQL plan efficiently runs a query and provides the desired results faster.

Convert the Standby to a Snapshot Standby in EBSR12

Convert the Standby to a Snapshot Standby in EBSR12 

Convert the Standby to a Snapshot Standby

1. The first step is to configure the physical standby database to use flashback logging for flashback database operations.
   
2. A flashback database requires that the flashback logs reside in the Flash Recovery Area (FRA). Although the FRA may already be configured for your environment, the flashback logs can significantly increase FRA usage. It is not unheard of to double the size of the FRA to utilize flashback database, and so you need to take this into account.
   SQL> alter system set db_recovery_file_dest_size=32g scope=both;
SQL> alter system set db_recovery_file_dest='<shared location>' scope=both;
SQL> alter system set db_flashback_retention_target=1440 scope=both;

   Substitute an appropriate destination size for your system.

3. Stop redo apply on the physical standby database:
   SQL> alter database recover managed standby database cancel;
4. Turn on flashback logging:
   SQL> alter database flashback on;
5. Convert the standby database into a snapshot standby database:
   
   Note: On Oracle RAC systems, shut down all other instances except one, which is the one you will be using to execute the following command.
   SQL> alter database convert to snapshot standby;
6. Shut down the snapshot standby database and startup the database (it will be opened for read/write access):
   SQL> shutdown immediate;
SQL> startup

Revert the Physical Standby Database to its Original State

1. Change the standby database from snapshot mode back into standby mode using the following commands:
   
   Note: On Oracle RAC systems, shut down all except the main instance before running the following commands:
   SQL> startup mount force;
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
   Note: On Oracle RAC systems, restart all of the remaining Oracle RAC instances in mount mode.
   
   
2. Enable redo log apply:
   SQL> alter database recover managed standby database disconnect from session;