SPM explain example (oracle-samples#154)

nigelbayliss · web-flow · commit c6e444e1cc67 · 2021-06-08T08:25:29.000-07:00
* security update

* security update

* security update

* SPA helper example

* Explain version script
diff --git a/optimizer/spm_19c_debug/in_cache/README.md b/optimizer/spm_19c_debug/in_cache/README.md
@@ -1,14 +1,14 @@
 This directory contains an example of deugging a SQL plan baseline in Oracle Database 19c.
 
-Note: these scripts use SQL performance analyzer. 
-
+Note: spb.sqlc and spb_noex.sql use SQL performance analyzer (SPA)
+      
 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.
+In most cases, you can use spb_explain.sql (EXPLAIN) or spb_noex.sql (SPA version) - which explain 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.
+Alternatively, if you want to parse and test execute the SQL statement, use spb.sql (uses SPA)
 
 ### DISCLAIMER
 
@@ -20,6 +20,5 @@ Alternatively, if you want to test execute the SQL statement, use spb.sql instea
 ### WARNING
 
 *  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 
+*  Check the license user manual for your database version if you want to use SPA versions
 *  Oracle Database 19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/
diff --git a/optimizer/spm_19c_debug/in_cache/spm_explain.sql b/optimizer/spm_19c_debug/in_cache/spm_explain.sql
@@ -0,0 +1,58 @@
+--
+-- This script explains a SQL statement in the cursor cache
+-- and produces a hint report - useful for diagnosing 
+-- SQL plan baseline issues
+--
+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
+--
+-- Get the SQL ID to test
+--
+accept sqlid prompt 'Enter the SQL ID: '
+
+--
+-- Check it's in cache
+--
+BEGIN
+  select count(*) into :ccount from v$sqlarea where sql_id = '&sqlid';
+  IF :ccount = 0
+  THEN
+    RAISE_APPLICATION_ERROR(-20002, 'SQL ID not found');
+  END IF;
+END;
+/
+
+--
+-- Spool the report
+--
+spool spm_report
+
+alter session set "_sql_plan_management_control"=4;
+
+--
+-- Explain plan
+--
+declare
+  stmt clob; 
+begin
+  select sql_fulltext into stmt from v$sqlarea where sql_id = '&sqlid';
+  execute immediate 'explain plan for '||stmt;
+end;
+/
+
+alter session set "_sql_plan_management_control"=0;
+
+select * from table(dbms_xplan.display(format=>'hint_report'));
+
+spool off
