1

The sql statement had 2 plans, one is slower and one is faster.
By using the hash value, I tried to create a baseline, I get an error as below:

ERROR at line 1:
ORA-13767: End snapshot ID must be greater than begin snapshot ID.
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4715
ORA-06512: at line 21

and found out that the minimum and maximum snapshot id of the plan
that I wanted to create a baseline with was same.

What will be the way to create a baseline with the plan I want in this case?

Thank you for your help!

Keibee
  • 45
  • 1
  • 5

1 Answers1

1

-- in order to avoid an: ORA-13767: End snapshot ID must be greater than begin snapshot ID. -- if the snap_ids are the same, then lower the begin snap id by one

BEGIN -- If it's not in V$SQL we will have to query the workload repository select count(*) into cnt from V$SQLSTATS where sql_id = '&&sqlid';

IF (cnt > 0) THEN :task_name := dbms_sqltune.create_tuning_task(sql_id => '&&sqlid',scope => DBMS_SQLTUNE.scope_comprehensive,time_limit => 7200); ELSE select min(snap_id) into bsnap from dba_hist_sqlstat where sql_id = '&&sqlid';

select max(snap_id) into esnap
from   dba_hist_sqlstat
where  sql_id = '&&sqlid';

-- in order to avoid an: ORA-13767: End snapshot ID must be greater than begin snapshot ID.
-- if the snap_ids are the same, then lower the begin snap id by one
if (:bsnap = :esnap) then
   :bsnap := :bsnap - 1;
end if;

:task_name := dbms_sqltune.create_tuning_task(begin_snap => :bsnap,
                                              end_snap => :esnap,
                                              time_limit => 7200,
                                              sql_id => '&&sqlid',
                                              scope => DBMS_SQLTUNE.scope_comprehensive);

END IF;

dbms_sqltune.execute_tuning_task(:task_name);

Ravin Maharaj
  • 11
  • 1