If you have a query that performs well in in one environment (such as dev) but performs poorly in a different environment (such as prod), it might be because Oracle selects a good plan in dev, and a bad plan in prod, and it might be necessary to migrate a baseline for the good plan to prod.
That’s not the *only* reason why a plan might perform well in one environment vs another…for example, a dev database might have a smaller amount of data; therefore, queries might run faster. But if you want to try to get an apples-to-apples comparison, one way would be to capture a baseline from the environment where the query is performing well, and migrate that baseline to the other environment. This article covers how to do that.
Capture the Baseline
First, go to the environment where the query is running slowly. If the execution plan is still in the shared pool, you can create a baseline from the plan using the DBMS_SPM package.
If there are multiple execution plans that you like, you might capture them all by just providing the SQL_ID; or, if there’s one plan that looks particularly efficient, you can specify both the SQL_ID and the PLAN_HASH_VALUE.
How do I know which plan is most efficient?
It’s not a for-sure way to know, but one way is to look at the ration of BUFFER_GETS to EXECUTIONS in v$sql. So if I run the following query:
select sql_id, plan_hash_value, buffer_gets / executions
from v$sql
where sql_id = 'b0dyf1abgpqxj';…if it gives me the following result:
SQL_ID PLAN_HASH_VALUE BUFFER_GETS/EXECUTIONS
------------- --------------- ----------------------
b0dyf1abgpqxj 2988802544 4712
b0dyf1abgpqxj 3662895672 6372
b0dyf1abgpqxj 1475840701 3210.5Then I would be able to *ROUGHLY* judge that plan hash value 1475840701 is my more efficient plan. I put a huge emphasis on ROUGHLY here because one plan might be for a plan that uses a full table scan, another might be for a plan that uses an index. Maybe one plan uses a highly selective bind variable, and another plan uses a less-selective bind variable. There’s a lot of things to consider. In this specific example, though, the buffer gets are all roughly in the same ball park. There’s not a huge difference–I would expect these all to perform roughly the same. I’m just using this as an approximate example.
So once I’ve determined which my more efficient plans are that I want to baseline, I want to actually create baselines for those plans. Suppose, for this example, that I wanted to baseline the plans with hash value 2988802544 and 1475840701. I could run something like this:
declare
v_plans_loaded pls_integer := 0;
begin
v_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
sql_id => 'b0dyf1abgpqxj'
, plan_hash_value => 1475840701
, fixed => 'YES'
, enabled => 'YES'
);
v_plans_loaded := v_plans_loaded + dbms_spm.load_plans_from_cursor_cache(
sql_id => 'b0dyf1abgpqxj'
, plan_hash_value => 2988802544
, fixed => 'YES'
, enabled => 'YES'
);
end;
/You can now see the baselines we created in the DBA_SQL_PLAN_BASELINES view:
select *
from dba_sql_plan_baselines;Create a Staging Table and Export The Baselines To The Table
Create a staging table for your baselines. Here, I’ve called my MY_BASELINES:
begin
dbms_spm.create_stgtab_baseline(
table_name => 'MY_BASELINES'
, table_owner => 'C##KALEY'
, tablespace_name => ''
);
end;
/This creates a “regular ol’ table” owned by C##KALEY called MY_BASELINES. In just a moment, we’ll export our baselines to this newly created table.
Look up the SQL_HANDLE and PLAN_NAME from the DBA_SQL_PLAN_BASELINES view for the baselines you wish to export. Use these values to identify which baselines you want to export into your staging table.
set serveroutput on
declare
v_number_of_baselines_packed pls_integer;
begin
v_number_of_baselines_packed := dbms_spm.pack_stgtab_baseline(
table_name => 'MY_BASELINES'
, table_owner => 'C##KALEY'
, sql_handle => 'SQL_5775d58189244058'
, plan_name => 'SQL_PLAN_5fxfph64k8h2s4e729bd3'
);
v_number_of_baselines_packed := v_number_of_baselines_packed +
dbms_spm.pack_stgtab_baseline(
table_name => 'MY_BASELINES'
, table_owner => 'C##KALEY'
, sql_handle => 'SQL_5775d58189244058'
, plan_name => 'SQL_PLAN_5fxfph64k8h2s5db7f4b1'
);
dbms_output.put_line('Loaded ' || v_number_of_baselines_packed || ' plans');
end;
/If you’ve run this, your table should be packed, and ready to transport to the new environment!
Transport Your Table To the New Environment
There’s probably lots of ways to do this. I use SQL Developer, so it’s really easy for me to get the table definition and grab the inserts for the table.
To get the table definition:
select dbms_metadata.get_ddl('TABLE', 'MY_BASELINES', 'C##KALEY')
from dual;To get the actual data from the table (note: this probably only works in SQL developer or SQLcl)
set sqlformat insert
select *
from c##kaley.my_baselines;These two queries should give you a script that you can use to export your staging table.
Create Your Staging Table and Migrate the Baseline
Run your script to create your table and insert your records into the table in your other environment (remember to add a commit at the end).
Once you’ve created your new table, you can unpack it with a command similar to the follwing:
declare
v_number_of_baselines_unpacked pls_integer := 0;
begin
v_number_of_baselines_unpacked := dbms_spm.unpack_stgtab_baseline(
table_owner => 'C##KALEY'
, table_name => 'MY_BASELINES'
);
dbms_output.put_line('Unpacked ' || v_number_of_baselines_unpacked || ' baselines');
end;
/Now, you know how to migrate a baseline from dev to prod!
Help! Migrating the Baselines Have Caused Problems!!
You can quickly look up the SQL_HANDLE and PLAN_NAME in DBA_SQL_PLAN_BASELINES, and drop any baselines with the following:
declare
v_dummy number;
begin
v_dummy := dbms_spm.drop_sql_plan_baseline(
sql_handle => 'PUT_SQL_HANDLE_HERE'
, plan_name => 'PUT_PLAN_NAME_HERE'
);
end;
/Free Oracle SQL Tuning Guide
Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
