Usage and precautions of Oracle Outline

Overview
Oracle Outline is used to maintainSQLAn execution plan tool. We can use the outline tool to prevent SQL Execution plans fromDatabaseEnvironment Changes (such as statistical information and some parameters.
Outline is mainly used in the following scenarios:
1.
To avoid serious performance degradation in some SQL statements after upgrade and cannot be optimized in a short time,
We can use the outline function to implement the SQL Execution Plan in the original production database on the new database.
2.
To avoid performance degradation caused by changes in SQL Execution plans due to inaccurate statistical data (such as the failure to collect statistics on tables or indexes in a timely manner.
3.
This prevents the optimizer from generating different execution plans due to different database versions and configurations for applications with large-scale distribution and implementation.
4.
Some bugs cause the optimizer to generate poor execution plans. Before fixing the bug, we can use outline to force the SQL Execution Plan to be correct.
Outline stores the hint of the execution plan in the outline table. When an SQL statement is executed, Oracle compares it with the SQL statement in outline. If the SQL statement contains an outline statement, the execution plan is generated using the hint.
Precautions for using Outline
Note the following when using Outline.
1.
The Outln user is a very important system user, and its importance is the same as sys and system. We do not recommend that you delete outln under any circumstances. Otherwise, database errors may occur.
2.
The optimizer generates an execution plan using Outline, provided that all hints in outline are valid. For example, if an index is not created, the hint of the index is invalid, so that the SQL outline plan will not be used.
3.
Outline cannot be used when the parameter Cursor_sharing is set to force.
4.
Literial SQL is not highly shared, and Outline is better at binding variables to SQL statements. For literial SQL, each SQL statement must generate an outline.
5.
To create an outline, you must have the create any outline permission.
6.
From the CBO point of view, the statistical information of database tables and indexes is constantly changing as the data volume changes. A fixed execution plan is not necessarily the best execution plan in some periods. Therefore, the use of outline depends on the specific situation.
Outline example
This document illustrates how to use outline and migrate the content of outline from 8i10g.
The procedure is described by scott.
Create under scott user in 8i, 10 GBTestTable to describe the use of outline.
Login as scott
Create table t_test (col1 varchar2 (2 ));
1.
Determine the db of the 8i production database. The listener is disabled.
2.
Start the 8i production database instance.
3.
The 8i database uses the system user to log on and grant the create any outline permission to the SQL Execution user.
Grant create any outline to scott;
4.
Use the scott user to log on to the 8i database.
Create outline t_ol1 for category special on select * from t_test where col1 = '00 ';
T_ol1 à outline name
(Note that each outline must use a unique name and cannot be repeated)
Special à outline class (category)
Select * from t_test where col1 = '00'; à SQL statement to save outline
5.
10g, 8i Database Unlock and modify outlin user password. Note that the password of the outln user can be modified, but the outln user cannot be deleted.
Alter user outln identified by outln account unlock;
6.
Use the outln user in the 8i library to export outline data.
Exp outln/outln tables = ol/$ hints file = ol. dmp log = ol_exp.log
Copy the export data to the machine where the 10 Gb library is located.
7.
Use outln user to import outline data in 10 Gb Library
Imp outln/outln file = ol. dmp ignore = y log = ol_imp.log
8.
Use the sys user to update the signature of ouline in the 10 Gb Library
Connect sys/manager
Exec dbms_outln.update_signatures;
Enable stored outline
Alter system set use_stored_outlines = special;
À specify outline category
9.
Check whether outline is used
Connect scott/tiger
Create index I _test on t_test (col1 );
À create an index to change the execution plan
Explain plan for select * from t_test where col1 = '00 ';
@? /Rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
Plan hash value: 4036493941
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | select STATEMENT | 1 | 3 | 1200 (4) | 00:00:17 |
| * 1 | table access full | T_TEST | 1 | 3 | 1200 (4) | 00:00:17 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("COL1" = '00 ')
Note
-----
-Outline "OL1" used for this statement
À
Note: The Execution Plan indicates that online is used.
17 rows selected.
This indicates that outline is enabled.
If there is no outline, indexes should be used. The execution plan is as follows.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 614253159
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
---------------------------------------------------------------------------
| 0 | select STATEMENT | 1 | 3 | 3 (0) | 00:00:01 |
| * 1 | index range scan | I _TEST | 1 | 3 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("COL1" = '00 ')
Outline Maintenance
Stop db using the outline function:
Alter system set use_stored_outlines = false;
Disable/enable specific outline:
Alter outline ol_name disable;
Alter outline ol_name enable;
Delete outline category:
9i, 10g: exec dbms_outln.drop_by_cat ('category _ name ');
8i: exec outln_pkg.drop_by_cat ('category _ name ');
Outline View
Dba_outlines
Check whether outline exists
Select
Name, category, owner from dba_outlines;
Dba_outline_hints
This view lists the hints content of outline.