Oracle storage outline (storedoutline)

The oracle storage outline (storedoutline) is used to provide stable execution plans. Starting from oracle11g, it is gradually replaced by the SQL plan. The following is a specific example of the storage outline.

The oracle storage outline is used to provide stable execution plans. Starting from oracle 11g, it is gradually replaced by the SQL plan. The following is a specific example of the storage outline.

The Oracle storage outline is used to provide stable execution plans. Starting from oracle 11g, it is gradually replaced by the SQL plan. The following describes the specific process of the storage outline. The results are tested in oracle 11g r2.

-- Create test tables and Indexes
Create table oln_test as select * from dba_tables;
Set autotrace on;

SQL> create index idex_oln on oln_test (TABLE_NAME );

SQL> select OWNER from oln_test where table_name = 'oln _ test ';
----------------------------------------------------------
Plan hash value: 3038230087

Bytes ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------
| 0 | select statement | 1 | 34 | 1 (0) | 00:00:01 |
| 1 | table access by index rowid | OLN_TEST | 1 | 34 | 1 (0) | 00:00:01 |
| * 2 | index range scan | IDEX_OLN | 1 | 1 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------

SQL> select/* + FULL (oln_test) */OWNER from oln_test where table_name = 'oln _ test ';

----------------------------------------------------------
Plan hash value: 1307524366

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
------------------------------------------------------------------------------
| 0 | select statement | 1 | 34 | 13 (0) | 00:00:01 |
| * 1 | table access full | OLN_TEST | 1 | 34 | 13 (0) | 00:00:01 |
------------------------------------------------------------------------------
-- Generate outline
-- Create the OUTLINE for ORIGINALSQL
Create or replace outline oln_to ON
Select OWNER from oln_test where table_name = 'oln _ test ';

-- Create the OUTLINE for HINTSQL
Create or replace outline oln_hint ON
Select/* + FULL (oln_test) */OWNER from oln_test where table_name = 'oln _ test ';
-- Exchange outline

Method 1: directly update the DBA_OUTLINES table (not officially recommended by oracle)
SQL> conn/as sysdba
UPDATE DBA_OUTLINES
Set name = DECODE (NAME, 'oln _ hint', 'oln _ to', 'oln _ to', 'oln _ hint ')
Where name in ('oln _ to', 'oln _ hint ');
Commit;

-- Verification result, outline used
SQL> select OWNER from oln_test where table_name = 'oln _ test ';
----------------------------------------------
Plan hash value: 1307524366

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
------------------------------------------------------------------------------
| 0 | select statement | 33 | 1122 | 13 (0) | 00:00:01 |
| * 1 | table access full | OLN_TEST | 33 | 1122 | 13 (0) | 00:00:01 |
------------------------------------------------------------------------------
Note
-----
-Outline "OLN_HINT" used for this statement

Sometimes the memory needs to be refreshed
Alter system flush shared_pool;

-- Method 2 is replaced by private outline(Recommended)


SQL> create private outline MY_to from oln_to;

SQL> create private outline MY_hint from oln_hint;

-- It must use the same session as the preceding command.
Conn/as sysdba
Update ol $ HINTS
SET OL_NAME = DECODE (OL_NAME, 'My _ hint', 'My _ to', 'My _ to', 'My _ hint ')
WHERE OL_NAME IN ('My _ to', 'My _ hint ');
Commit;

Set linesize 250;
Col HINT_TEXT format a100;
Select OL_name, HINT_TEXT from ol $ hints;
-- Refresh the outline information in the memory
Execute dbms_outln_edit.refresh_private_outline ('My _ ');
Execute dbms_outln_edit.refresh_private_outline ('My _ hint ');
-- Create or update public outline
Create or replace outline OLN_TO from private MY_TO;

-- Test the use of outline
-- Alter system set use_stored_outlines = DEFAULT;
Select OWNER from oln_test where table_name = 'oln _ test ';

-- Drop the temporary OUTLINE HINTSQL
Drop outline oln_hint;

Exec dbms_outln.drop_by_cat (cat => 'default ');

--- 10 Gb or later versions can use SQL in the shared pool to produce outline
Select owner from oln_test where table_name = 'oln _ test ';
Select SQL _id, hash_value, child_number, SQL _text from v $ SQL where SQL _text like 'select count (*) from oln_test % ';

SQL> -- to workaround Bug 5454975 fixed 10.2.0.4
SQL> alter session set create_stored_outlines = true;

Exec dbms_outln.create_outline ('20140901', 0 );

SQL> exec dbms_outln.create_outline (hash_value = & gt; 646164864, child_number = & gt; 0 );

Select count (*) from wj. OLN_TEST

Select count (*) from wj. oln_test;

,