The ORACLE-BASE Blog

I had an odd issue with a baseline recently in 19c (19.32). I thought I would share it in case someone else comes across a similar issue.

The initial problem

We had a SQL statement that was running fine on non-production environments, but was getting a really odd execution plan in production. The non-production environments are clones of production, so I would not expect a substantial difference. After a lot of messing about I just decided to create a baseline on the non-production system and transfer it to the production system, so I could use the good execution plan there. I’ve done this a bunch of times and it’s really easy.

Transferring a baseline

In the source database create a staging table.

drop table if exists spm_staging_tab purge;

begin
  dbms_spm.create_stgtab_baseline(
    table_name      => 'spm_staging_tab',
    table_owner     => 'mydbauser',
    tablespace_name => 'users');
end;
/

Create a baseline from the SQL statement in the cursor cache.

set serveroutput on
declare
  l_plans_loaded  pls_integer;
begin
  l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '55bug173g2fpq');
    
  dbms_output.put_line('Plans Loaded: ' || l_plans_loaded);
end;
/

Get the sql_handle for the baseline.

select sql_handle, plan_name, enabled, accepted, created
from   dba_sql_plan_baselines
order by created;

Pack the baseline into the staging table.

set serveroutput on
declare
  l_plans_packed  pls_integer;
begin
  l_plans_packed := dbms_spm.pack_stgtab_baseline(
    table_name      => 'spm_staging_tab',
    table_owner     => 'mydbauser',
    sql_handle      => 'SQL_ce25ee1b063a418f');

  dbms_output.put_line('Plans Packed: ' || l_plans_packed);
end;
/

Transfer the staging table to the destination database.

copy to mydbauser@proddb replace spm_staging_tab using select * from mydbauser.spm_staging_tab;

Unpack the baseline in the destination database.

set serveroutput on
declare
  l_plans_unpacked  pls_integer;
begin
  l_plans_unpacked := dbms_spm.unpack_stgtab_baseline(
    table_name      => 'spm_staging_tab',
    table_owner     => 'mydbauser',
    creator         => 'mydbauser',
    sql_handle      => 'SQL_ce25ee1b063a418f');

  dbms_output.put_line('Plans Unpacked: ' || l_plans_unpacked);
end;
/

Flush the existing cursor from the shared pool.

select sql_id, address, hash_value
from   v$sqlarea
where  sql_id = '55bug173g2fpq';

SQL_ID           ADDRESS                HASH_VALUE
________________ ___________________ _____________
55bug173g2fpq    000000012285C0A8       3337697974

SQL>

exec sys.dbms_shared_pool.purge('000000012285C0A8,3337697974', 'c');

At this point running the statement again should mean it will use the baseline. That’s just worked most times I’ve done this. This time it didn’t. After running the statement, the following query would show the SQL_PLAN_BASELINE column as null.

select sql_id, sql_plan_baseline
from   v$sql
where  sql_id = '55bug173g2fpq';

Why didn’t it use the baseline?

There are some obvious things that might prevent a baseline being used.

• Structural differences between the schemas, like missing indexes or differences in object definitions can mean the plan is impossible to replicate.
• Some optimizer hints in the SQL will prevent the baseline from being used. I’ve seen “/*+ opt_param(‘_optimizer_use_feedback’ ‘false’) */” prevent a baseline from being used before.

Unfortunately the only way to see why a baseline is not used is to trace the statement.

-- Identify the trace file.
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- Enable trace.
ALTER SESSION SET EVENTS 'trace [SQL_Compiler.*]';

-- Run your query.
@query.sql

-- Turn the trace off
ALTER SESSION SET EVENTS 'trace [SQL_Compiler.*] off';

The resulting trace file is quite large, but you can jump to the section labelled “START SPM Plan Dump”. I could see the baseline was not used because it couldn’t be reproduced, but I couldn’t see why in the trace. I’ve seen why a failure happened before, but I couldn’t see why this time.

Was it working in the non-production environment?

I started to raise a service request with Oracle Support to see if they could see why the baseline was not being used. There’s a lot of stuff in the trace file, and maybe I was just missing something obvious. During the process of getting together the information, the notes mentioned checking the baseline was being used in the source system. That struck me as odd, as I had created the baseline from a working statement, but I flushed the statement from the shared pool in the non-production system, and sure enough the baseline wasn’t used when I ran the statement.

I traced the statement in the non-production system, and I could see the baseline was not used, but like the production system, I couldn’t see why it was failing from the trace.

When I looked at the baselines in the non-production system again I could see a second baseline had been created for the statement I captured, but it was not accepted, so I evolved the baseline to accept it and dropped the original baseline I had captured.

set serveroutput on
declare
  l_output  clob;
begin
  l_output := dbms_spm.evolve_sql_plan_baseline(
    sql_handle      => 'SQL_ce25ee1b063a418f');

  dbms_output.put_line('Output: ' || l_output);
end;
/

declare
  l_plans_altered  pls_integer;
begin
  l_plans_altered := dbms_spm.drop_sql_plan_baseline(
    sql_handle      => 'SQL_ce25ee1b063a418f',
    plan_name       => 'SQL_PLAN_cw9gf3c33nhcg99f2df97');

  dbms_output.put_line('Plans Altered: ' || l_plans_altered);
end;
/

The new baseline looked like it had a good execution plan, and more importantly I could see the baseline was being used by the system. The SQL_PLAN_BASELINE column was populated as expected.

select sql_id, sql_plan_baseline
from   v$sql
where  sql_id = '55bug173g2fpq';

Also, the REPRODUCED column of the DBA_SQL_PLAN_BASELINES view was marked as YES. Annoyingly it is marked as YES for a newly created baseline until you run the statement, then gets marks as NO if it fails to be used.

Transfer the new baseline

With the new baseline working in non-production, I dropped the relevant baseline in the prod system, and transferred the new baseline. The SQL statement used the new baseline and the query ran well. Happy days.

Final Thoughts

Baselines are really useful for locking down good execution plans. I try not to use them all the time, but when we have statements that flip-flop between good and bad plans, they are really helpful. It is also really nice to be able to transfer them between databases. If you are nervous about the impact of a patch or upgrade, baselines are a really neat way of locking down your execution plans.

I’ve seen a baseline not being used when there is an obvious problem, like schema differences or a hint that prevents the baseline being used. I can’t say I’ve noticed a situation like this before. Every other time I’ve captured and transferred a baseline it’s just worked.

I guess the main take home message for me is to check the baseline is actually working, rather than assuming it will just work. Stupid me. 🙂

From an Oracle perspective, diagnosing a baseline problem is really ugly. Having to trace the statement is annoying. Trying to understand what the trace file means is annoying. It would be really good if there was a human readable explanation for why REPRODUCED=NO in the DBA_SQL_PLAN_BASELINES view, so a regular mortal could understand what is happening. Just saying. 🙂

Cheers

Tim…

Reference: The code snippets in this post were taken from a post I wrote about 18 years ago (SQL Plan Management in Oracle Database 11g Release 1). How time flies. 🙂