Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, amir.

Asked: October 08, 2025 - 1:26 pm UTC

Last updated: October 09, 2025 - 2:44 am UTC

Version: oracle db 19.21

You Asked

Hi Dear Experts,
I want to create a SQL PLAN baseline for one of the My system query in a two-node RAC 19.21. With user SYS and inside a PDB with the following commands

var v_num number; 
exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '0b3...............',plan_hash_value => 2170529430 ,fixed=>'YES');


It gives a successfull response. Of course, the output of the above command is returned as 0. And when I check the Plan baselines with the following command, I see that nothing new has been created:

select * from dba_sql_plan_baselines


I check with the following command and see that plan_hash_value equal to 2170529430 exists in memory:


select sql_id, plan_hash_value, parsing_schema_name, sql_text 
from v$sql where sql_id = '0b3...............';


What is the problem?

and Connor said...

The command will always "work". "v_num" is the number of plans loaded

SQL> var v_num number;
SQL> exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'nonsense',plan_hash_value => 2170529430 ,fixed=>'YES');

PL/SQL procedure successfully completed.

SQL> print v_num

     V_NUM
----------
         0


Check v_num first ... then we can look further

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library