Imagine you're having a productive day when suddenly, an SQL query decides to execute differently. The SQL that used to run smoothly now triggers an alert on your Enterprise Manager. Or perhaps someone calls, saying that their previously one-hour query now takes six hours, ending in a "snapshot too old" error. What should we do in these situations?
First, I want to thank my esteemed mentor Mohsen Bahadori and my dear friend Saeed Moradkhani for their guidance in writing this article.
SQL Plan Baseline is a collection of approved execution plans that Oracle uses to run queries. This feature helps prevent unwanted changes in execution plans.
After identifying the SQL ID of the rogue SQL, the first step is to check whether it was indeed running better before. Use the following query:
SELECT address, sql_id, first_load_time, plan_hash_value, elapsed_time, last_load_time, last_active_time, sql_plan_baseline, hash_value
FROM v$sql
WHERE sql_id='70zd67a9zq6w1';
To find the SQL ID, several methods exist. One is through Enterprise Manager's performance page, but I prefer finding it via V$session with details like user reports or repetitive execution.
If the above query shows multiple rows with different plan_hash_values and significantly different elapsed times, it’s time to set the better-performing plan. Sometimes, this issue arises due to stale statistics, which can be mitigated by gathering fresh statistics.
SELECT
s.sid,
s.serial#,
s.sql_id,
m.plan_hash_value
FROM
v$session s
JOIN
v$sql_monitor m
ON
s.sql_id = m.sql_id
WHERE
s.status = 'ACTIVE'
AND
m.status = 'EXECUTING'
AND
s.sql_id='70zd67a9zq6w1';
Next, look at the snapshots Oracle takes. Use the times extracted from the SQL query and last_active_time to find the relevant snap_ids from the dba_hist_snapshot view.
Creating and Loading SQL Sets
Create a SQL set, which acts like a container for the plan we want to use.
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'my_sqlset',
description => 'SQL Tuning Set for specific SQL_ID'
);
END;
Now, fill this SQL set with the information from the previous queries.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => <BEGIN_SNAP_ID>,
end_snap => <END_SNAP_ID>,
basic_filter => 'sql_id = ''70zd67a9zq6w1'' and plan_hash_value = 3404959673'
)
) P;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'my_sqlset',
populate_cursor => cur
);
END;
Next, load the SQL set with dbms_spm (SQL Plan Management).
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'my_sqlset'
);
END;
Verify that the plan is correctly set up with the following query:
SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_ID = '70zd67a9zq6w1';
For Oracle Version 12 and Above
If you're using Oracle version 12 or later, you can utilize simpler methods:
First, check if your query exists in AWR (Automatic Workload Repository):
SELECT * FROM DBA_HIST_SQL_PLAN
WHERE SQL_ID = 'exf6mxfttzn6r'
AND PLAN_HASH_VALUE = 3404959673;
If it exists, use the following command to load the plan:
now you can use from or With these steps, you can effectively manage and fix sudden changes in SQL execution plans, ensuring your queries run optimally.