Databases Reference
In-Depth Information
7.
Query the data dictionary to see if there are execution plans that are not
yet accepted:
SELECT
SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_BASELINES;
Evolve the baseline with the new execution plan:
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_e6bd1707937cb2ca');
DBMS_OUTPUT.PUT_LINE(l_report);
END;
/
8.
Clean the database:
DECLARE
l_plan pls_integer;
BEGIN
l_plan := DBMS_SPM.DROP_SQL_PLAN_BASELINE( -
sql_handle => 'SYS_SQL_e6bd1707937cb2ca');
END;
/
DROP TABLE sh.MY_CUSTOMERS;
CONNECT / AS SYSDBA
REVOKE ADMINISTER SQL MANAGEMENT OBJECT FROM SH;
How it works...
From step 1 to step 3, we create a table
MY_CUSTOMERS
to test SQL Baselines.
In step 4, we execute a query on the table to retrieve the invalid customers. We inserted
a comment inside the query to easily identify it later.
