Hi,

I will explain How to Create SQL Plan Baselines Using Enterprise Manager Cloud Control in this article.

 

 

 

Create SQL Plan Baselines in Oracle

 

 

You should read the following article before this, if you don’t know what is the SQL Plan Baseline.

SQL Plan Management Using SQL Plan Baselines in Oracle | Oracle Database Performance Tuning Tutorial -14

 

 

Create SQL Plan Baselines Using Enterprise Manager Cloud Control

You can create, drop, enable,disable and load, unload the SQL Plan baselines using Enterprise manager Cloud control as follows.

 

 

 

You can access All SQL Profiles, SQL Patches and SQL Plan Baselines in this page.

 

Click the SQL Plan Baseline to access the SQL Plan Baseline page.

 

You can see the existing SQL Plan Baselines and manage them in this page.

 

load a SQL Plan Baseline in Oracle

 

Now there are no SQL Plan Baselines, lets go to load a SQL Plan Baseline.

 

Click the Load button firstly.

 

 

You can Load Plans both from SQL Tuning Sets and from Cursor cache.

 

Firstly, I have selected second option, and will load plans from cursor cache. Click the Search  button to select any SQL_ID, or you can give any SQL_ID manually.

 

SQL Plan Baselines job has been submitted.

 

 

Now its status is pending, click the job name to see details.

 

Job details are as follows.

 

 

 

 

Or you can load plans from SQL Tuning Set ( STS ) as follows.

 

After this step you can see the SQL Plan baseline page again, two Jobs for SQL Plan Baselines are seen in this page.

 

You can review the job detail by clicking Job name.

 

After job is completed, you can see all SQL Plan baselines as follows.

SQL Plan Baselines’ Attributes as follows. Their attributes Accepted and Enabled now. You can change them as follows.

Second SQL Plan Baseline Fixed Attribute is NO, you can change it as follows.

 

 

 

After FIXED attributed is changed as YES, related SQL is run and I have checked it with the following SQL.

select inst_id,SQL_PLAN_BASELINE ,sql_fulltext ,trunc(elapsed_time/decode(executions, 0, 1, executions)/1000000) elapsed_time, plan_hash_value,hash_value, child_number, first_load_time,
last_load_time, executions, buffer_gets, trunc(buffer_gets/decode(executions, 0, 1, executions)) gets_per_exec, trunc(rows_processed/decode(executions, 0, 1,
executions)) rows_return_per_exec
from gv$sql where sql_id='dz3rmaqvta9uq' order by last_load_time desc;

 

Related SQL Statement is using the SQL Plan Baseline as follows.

 

 

 

Evolve SQL Plan Baselines

You can evolve the SQL Plan Baselines in which Plans are not Accepted yet.

You can evolve unaccepted the SQL Plan baselines by clicking the Evolve button, and you can evolve them as follows.

 

I will continue to explain Performance tuning tutorial in the next articles.

 

Next post link about Performance Tuning Tutorial is as follows.

What is the Optimizer Hints and How to Use Hints in Oracle | Oracle Database Performance Tuning Tutorial -16

 

 

 

 

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following Tutorial Series.

 

Performance Tuning and SQL Tuning Tutorial in the Oracle Database