Blog | May 22, 2014

Fix Unstable Plans by implementing Baselines

In the following blog we discuss one of several issues that can plague the health of large Oracle Databases in a Data Warehouse.

The database that we will discuss serves as both an ETL (Extract, Transform & Load) and a Reporting Instance for this multi-terabyte Data Warehouse.

The issue referenced is related to one of the SQL Queries in a time sensitive ETL process taking hours to complete as compared to previous days with runtimes measured in minutes.

Inventory provided for this issue:
1) Problematic SQL Text
2) Timing when this SQL executed within minutes
3) Timing when this SQL executed in hours.

Analysis done to find where the issue was:
1) Checked on the last_analyzed dates of the tables which were used in this problematic SQL.
2) It was found that one of the table used in this problematic SQL underwent changes i.e. the statistics of that tables were changed.
3) It was found from dba_hist* tables that the explain plan of this SQL got changed right after the table analysis.

The following steps were taken to resolve the issue:
1) The SQL tuning advisory was executed on the problematic SQL which also recommended reverting back to the OLD (good) explain plan.
2) We implemented the baselines for this SQL or reverted to the OLD (good) explain plan which was used by this SQL before table analysis.

Given below are the steps used to implement the baselines:
1) Create SQL Tuning Set (STS):
sqlset_name => 'STS_44x7rrb0qdv7d',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');

2) Populate STS from Automatic Workload Repository (AWR), using a time duration when the desired plan was used:
Cur sys_refcursor;
   OPEN cur FOR
dbms_sqltune.select_workload_repository(begin_snap=>22806, end_snap=>22976,basic_filter=>'sql_id = ''44x7rrb0qdv7d''',attribute_list=>'ALL')
         ) p;
     DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name=> 'STS_44x7rrb0qdv7d', populate_cursor=>cur);
  CLOSE cur;

3) List out the Baselines to see if the SQL Have any Baselines:
select count(*) FROM dba_sql_plan_baselines where sql_text like 'SELECT PA_ACCUMDETAILS_DET.DETAIL_KEY%' ;

4) Load desired plan from STS as SQL Plan Baseline:
sqlset_name => 'STS_44x7rrb0qdv7d',
basic_filter=>'plan_hash_value = ''278209765'''

5) List out the Baselines to see if the SQL Have any Baselines after step 4:
select count(*) FROM dba_sql_plan_baselines where sql_text like 'SELECT PA_ACCUMDETAILS_DET.DETAIL_KEY%' ;

Oracle will not always
choose the optimal explain plan based on table statistics; the cost based optimizers are getting smarter by looking at the new plans so it is a good idea to look into the plan changes over the time for a problematic query.

Note*: - All above tunings efforts were done in Oracle Database 11g Release 2 Enterprise

Reference Link:

About TriCore Solutions
TriCore Solutions, the application management experts, provides a full suite of scalable and reliable managed application, cloud, infrastructure hosting, and consulting services to enterprise organizations. The company delivers its services and the TriCore Trusted Promise to more than 250 companies worldwide to reduce costs, raise service levels, improve customer experience, increase business agility, and accelerate innovation, unlocking the business value from their IT investments. TriCore Solutions is headquartered in Boston, MA, with offices in India and throughout North America.