Databases Reference
In-Depth Information
6.
Execute the query in step 3 again:
SET AUTOT TRACE EXP
SELECT /*+ INDEX (CN COUNTRIES_PK) */
C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME,
C.CUST_STREET_ADDRESS, C.CUST_POSTAL_CODE, C.CUST_CITY,
C.CUST_STATE_PROVINCE, CN.COUNTRY_NAME
FROM sh.CUSTOMERS C, sh.COUNTRIES CN
WHERE C.COUNTRY_ID = CN.COUNTRY_ID;
SET AUTOT OFF
7.
Clean the session state, drop the outline, and revoke the grant:
ALTER SESSION SET USE_STORED_OUTLINES = FALSE;
CONNECT / AS SYSDBA
DROP OUTLINE CUST_LIST_OUTLINE;
REVOKE CREATE ANY OUTLINE FROM sh;
How it works...
The execution plan for the same statement may change due to different reasons, such
as changes to the schema objects, different parameters, changes in data, accuracy of
statistics, presence of histograms, and so on. Another big issue can be the upgrade to a
newer database release.
Over time, different techniques were used to manage SQL Plan's
stability in the Oracle database. In Oracle 10
g
, SQL Profiles and
SQL Tuning Sets were launched, and, starting with Oracle 11
g
,
Oracle SQL Plan Management was introduced.
We can migrate stored outlines to SQL Plan Management using
the
DBMS_SPM.MIGRATE_STORED_OUTLINE
procedure.
If we want to avoid changes to the execution plans, we can store them in stored outlines—the
plans in the stored outlines don't change, and the optimizer uses them to generate equivalent
execution plans.
In step 2, we execute a query, and the corresponding execution plan provides the full table
scan of the
CUSTOMERS
and
COUNTRIES
tables. We can see the execution plan generated
by the optimizer in the following screenshot:
