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?
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