In particular, look at the "Hint Report" section towards the end of the file.

Additional scripts are available in the "in_cache" directory. These scripts us SQL performance analyzer to make it east to check SQL statements in the cursor cache, without the need to run them in SQL plus.

This directory contains an example of deugging a SQL plan baseline in Oracle Database 19c.

Note: these scripts use SQL performance analyzer.

The scripts make it easy to check SQL statements in the cursor cache.

You can set up an example by running test_setup.sql in a DBA account. Be aware it will drop and create SQL plan baselines. A SQL ID is displayed at the end of the script. You can enter this SQL ID when you run the "spb" scripts.

In most cases, you can use spb_noex.sql - it explains the plan of the relevant SQL statement in the cursor cache.

Alternatively, if you want to test execute the SQL statement, use spb.sql instead.

* These scripts are provided for educational purposes only.

* They are NOT supported by Oracle World Wide Technical Support.

* The scripts have been tested and they appear to work as intended.

* You should always run scripts on a test instance.

* These scripts drop and create SQL plan baselines. For use on test databases

* You need a license to use SQL performance analyzer to use these scripts

* Check the license user manual for your database version

* Oracle Database 19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/

set echo off

set verify off

set feedback off

set long 10000000

set pagesize 10000

set linesize 250

set trims on

set tab off

column report format a200

whenever sqlerror exit

var ccount number

var spa_task_name varchar2(30);

var execution_name varchar2(30);

var report_text clob;

accept check prompt 'Enter YES/yes if you have a license to use SQL Performance Analyzer: ' default 'NO'

IF upper('&check') != 'YES'

THEN

RAISE_APPLICATION_ERROR(-20001, 'License not confirmed');

END IF;

END;

accept sqlid prompt 'Enter the SQL ID: '

select count(*) into :ccount from v$sql where sql_id = '&sqlid';

IF :ccount = 0

THEN

RAISE_APPLICATION_ERROR(-20002, 'SQL ID not found');

END IF;

END;

spool spm_report

exec :spa_task_name := dbms_sqlpa.create_analysis_task(sql_id => '&sqlid');

exec dbms_sqlpa.set_analysis_task_parameter(:spa_task_name, 'disable_multi_exec', 'TRUE');

alter session set "_sql_plan_management_control"=4;

exec :execution_name := dbms_sqlpa.execute_analysis_task (task_name => :spa_task_name, execution_type => 'execute');

alter session set "_sql_plan_management_control"=0;

exec :report_text := dbms_sqlpa.report_analysis_task (task_name => :spa_task_name, type => 'text', level => 'typical', section => 'all', execution_name => :execution_name);

select :report_text report from dual;

spool off

set echo off

set verify off

set feedback off

set long 10000000

set pagesize 10000

set linesize 250

set trims on

set tab off

column report format a200

whenever sqlerror exit

var ccount number

var spa_task_name varchar2(30);

var execution_name varchar2(30);

var report_text clob;

accept check prompt 'Enter YES/yes if you have a license to use SQL Performance Analyzer: ' default 'NO'

IF upper('&check') != 'YES'

THEN

RAISE_APPLICATION_ERROR(-20001, 'License not confirmed');

END IF;

END;

accept sqlid prompt 'Enter the SQL ID: '

select count(*) into :ccount from v$sql where sql_id = '&sqlid';

IF :ccount = 0

THEN

RAISE_APPLICATION_ERROR(-20002, 'SQL ID not found');

END IF;

END;

spool spm_report

exec :spa_task_name := dbms_sqlpa.create_analysis_task(sql_id => '&sqlid');

exec dbms_sqlpa.set_analysis_task_parameter(:spa_task_name, 'disable_multi_exec', 'TRUE');

alter session set "_sql_plan_management_control"=4;

exec :execution_name := dbms_sqlpa.execute_analysis_task (task_name => :spa_task_name, execution_type => 'explain plan');

alter session set "_sql_plan_management_control"=0;

exec :report_text := dbms_sqlpa.report_analysis_task (task_name => :spa_task_name, type => 'text', level => 'typical', section => 'all', execution_name => :execution_name);

select :report_text report from dual;

spool off

REPORT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : TASK_17952

Tuning Task Owner : ADHOC

Workload Type : Single SQL Statement

Execution Count : 1

Current Execution : EXEC_20550

Execution Type : EXPLAIN PLAN

Scope : COMPREHENSIVE

Completion Status : COMPLETED

Started at : 05/25/2021 08:17:28

Completed at : 05/25/2021 08:17:28

-------------------------------------------------------------------------------

Schema Name: ADHOC

SQL ID : 21y39zhmkh4pt

SQL Text : select /* SPM_TEST */ num from example_spm_table where id =

:bindv

Bind Variables :

1 - (NUMBER):100

-------------------------------------------------------------------------------

Explain Plan Statistics

-------------------------------------------------------------------------------

Parse Time (s): .000205

Optimizer Cost: 3

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

1- Original

-----------

Plan hash value: 2448381833

---------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EXAMPLE_SPM_TABLE | 1 | 8 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=:BINDV)

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1 (U - Unused (1))

---------------------------------------------------------------------------

1 - SEL$1 / EXAMPLE_SPM_TABLE@SEL$1

U - INDEX_RS_ASC(@"SEL$1" "EXAMPLE_SPM_TABLE"@"SEL$1" ("EXAMPLE_SPM_TABLE"."ID"))

-------------------------------------------------------------------------------

set echo on

set tab off

set pagesize 1000

set linesize 200

set trims on

var bindv number

var sqlid varchar2(50)

exec select 100 into :bindv from dual;

DECLARE

l_plans_dropped PLS_INTEGER;

FOR REC IN (SELECT DISTINCT SQL_HANDLE FROM DBA_SQL_PLAN_BASELINES where sql_text like '%SPM_TEST%')

LOOP

L_PLANS_DROPPED := DBMS_SPM.DROP_SQL_PLAN_BASELINE (

sql_handle => rec.sql_handle,

PLAN_NAME => NULL);

END LOOP;

END;

drop table example_spm_table purge;

create table example_spm_table (id number(10), num number(10), num2 number(10));

create unique index spm_tab_pk on example_spm_table(id);

create index spm_tab_num1 on example_spm_table(num1);

for i in 1..1000

loop

insert into example_spm_table values (i,i,i);

end loop;

end;

commit;

exec dbms_stats.gather_table_stats (ownname=>null,tabname=>'example_spm_table');

select /* SPM_TEST */ num from example_spm_table where id = :bindv;

declare

r number;

r:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (attribute_name=>'SQL_TEXT',attribute_value=>'select /* SPM_TEST */ num from example_spm_table where id = :bindv');

end;

drop index spm_tab_pk;

select /* SPM_TEST */ num from example_spm_table where id = :bindv;

exec select sql_id into :sqlid from v$sqlarea where sql_text = 'select /* SPM_TEST */ num from example_spm_table where id = :bindv';

select :sqlid from dual;

select sql_text,accepted from dba_sql_plan_baselines where sql_text like '%SPM_TEST%';

select sql_id from v$sql where sql_text = 'select /* SPM_TEST */ num from example_spm_table where id = :bindv';