Note: to add to the below context , we have to look for the best execution plan from the past history usually available under sysaux tablespace . For a temporary solution or volatile solution you can use SQL profiling to fix the plan and later run the SQL trace 10053 to get to know the detailed analysis of the bad execution of sql statement.
Let's say you have a query that has been running well for a long time. However, after a recent database upgrade or patch, the query starts to run slowly. You check the statistics and confirm that they are up to date, and you also verify that there are no changes in the query syntax or parameters.
You suspect that the issue is caused by a change in the optimizer's behavior after the database upgrade or patch. In this case, you can use SQL plan baselines to force the optimizer to use a previously known good execution plan.
Use the DBMS_SQLTUNE.CREATE_SQLSET procedure to create a SQL tuning set for the query.
Run the SQL tuning advisor on the SQL tuning set to generate a list of potential SQL profiles.
Use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept the SQL profile with the best performance.
Create a SQL plan baseline for the query using the accepted SQL profile.
Once you have created a SQL plan baseline for the query, you can force the optimizer to use the baseline by using the OPTIMIZER_USE_SQL_PLAN_BASELINE hint in the query.
where 'baseline_name' is the name of the SQL plan baseline you created earlier.
By using SQL plan baselines, you can force the optimizer to use a previously known good execution plan, even if the optimizer's behavior has changed after a database upgrade or patch.
Here are some basic tips to work with Execution Plan stability, using baselines.
==========================================================
Use the below queries to see the available execution plans and see which plan was running fine.
1. @?/rdbms/admin/awrsqrpi.sql ---> This will generate the html page for the required query based on the SQLID and its awr history.
or
2. dbms_xplan.display_awr()
Ex: select * from TABLE(dbms_xplan.display_awr('47qjdv3ncanhr'));
or
3. USING GRID Control 12c
To gather the history of a SQL execution and the plans used during those runs, obtain the SQL Id to be evaluated, connect to the GRID Control 12c:
select the Targets/Databases -->Select the database -->Performance/SQL/Search SQL-->
Check AWR Snapshots -->Enter the SQL ID in the SQL ID filed / Search-->
Verify the executions and the different Hash Plans used.
The ones with the Smallest Elapsed Times are the best execution Plans for the SQL.
If the HASH Plan is still in the Cursor Cache it can be created as a baseline and instructed to run every time that SQL ID is loaded to the Shared Pool.
If the HASH Plan is no longer in the Cursor Cache, then it is still possible to load the HASH Plan to a Sql Tuning Set and create a baseline from the STS and assign it the SQL ID as well. Take note of the Snap ID (from the GRID SQL Search above) for the desired HASH Plan
HASH /SQL plan needed found in the Cursor Cache
Now you know which hash plan hash to be fixed. Now follow the below example. If the needed plan is found in the cursor cache then it is very simple to create a baseline and fixing the plan for the SQL query.
Ex: Determined the Hash Plan: 2601263939 is the best to run against the SQL ID: 47qjdv3ncanhr
1.Create the Baseline:
var v_num number;
exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id =>'47qjdv3ncanhr',plan_hash_value => 2601263939);
OR
Example from Internet for script:
SQL> !cat create_baseline.sql
var ret number
exec :ret := dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value);
SQL> @create_baseline
Enter value for sql_id: 47qjdv3ncanhr
Enter value for plan_hash_value: 2601263939
2. Verify the baseline got created or not
=================================
SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_4bd90f15ef3c1f10 SQL_PLAN_4rq8g2rrms7sh3cc6a555 YES YES NO
To see all the details, this will create a file with all the baseline info.:
spool baseline_plan.txt
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_4bd90f15ef3c1f10', format=>'basic'));
verify the spool file to confime the SQL ID and the HASH associated with it.
3.TO MODIFY A SQL PLAN BASELINE
var v_num number;
exec :v_num:=dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle =>'SQL_4bd90f15ef3c1f10',plan_name => 'SQL_PLAN_4rq8g2rrms7sh3cc6a555', attribute_name=> 'FIXED', attribute_value => 'YES');
Attributes
· enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
· fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
· autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
· plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
· description : Used to amend the SQL plan description, up to a maximum of 30 character.
Sometimes the required HASH / SQL plan will not be present in the Cursor Cache, then you have to load it from a AWR snapshots.
Steps are as below:
======================================
To load plans to the cursor cache from awr snapshots:
1. -- Drop SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'SAMPLE_TUNING_SET');
END;
2. -- Create SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'SAMPLE_TUNING_SET',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
3.-- Populate STS from AWR using a time duration when the desired plan was used.
Retrieve the begin Snap ID from the same session described in the GRID Contol above or by : SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;
Note: Specify the sql_id in the basic_filter (other predicates are available, see desc dba_hist_snapshot) if necessary.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>1477, end_snap=>1478,basic_filter=>'sql_id = ''9n82zq1gkpg2t''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'SAMPLE_TUNING_SET', populate_cursor=>cur);
CLOSE cur;
END;
/
4. -- List out SQL Tuning Set contents to check we got what we wanted
SELECT
first_load_time,
executions as execs,
parsing_schema_name,
elapsed_time / 1000000 as elapsed_time_secs,
cpu_time / 1000000 as cpu_time_secs,
buffer_gets,
disk_reads,
direct_writes,
rows_processed,
fetches,
optimizer_cost,
sql_plan,
plan_hash_value,
sql_id,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'SAMPLE_TUNING_SET'));
5.-- Finally create the baseline from the STS:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'SAMPLE_TUNING_SET',
basic_filter=>'plan_hash_value = ''1117073691'''
);
END;
/
6.-- Verify the baseline got created and modify it if necessary
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
exec :v_num:=dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle =>'SQL_ab2ab5c194ee0fc8',plan_name => 'SQL_PLAN_aqapps6afw3y81722054c', attribute_name=> 'FIXED', attribute_value => 'YES');
7.-- Verify all details for the new Baseline:
spool baseline_plan.txt
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SQL_ab2ab5c194ee0fc8',
format=>'basic'));
Comments
Post a Comment