Oracle Outline usage

Oracle Outline usage

I. Overview

Oracle Outline, also known as the storage Outline in Chinese, is the earliest mechanism to control SQL Execution plans based on prompts. It is also the only tool in 9i and earlier versions that can be used to stabilize and control SQL Execution plans.

Outline is a collection of hints (TIPS). More specifically, outline can lock the execution plan of a given SQL statement to keep its execution plan stable, regardless of how the database environment changes (such as statistical information and some parameters)

Note:

1. after 10 Gbit/s, oracle successively released SQL profile and SQL baseline to control SQL Execution plans. In addition, the outline tool is basically obsolete and not maintained by Oracle, the 10g and 11g versions can still be used, and this feature has been used very well.
2. We recommend that you use SQL profile or SQL baseline after 10 Gb.
3. As outline is rarely used at present, this article also tries to introduce some practical features.

Ii. Operating Mechanism
Outline stores the hint set of the execution plan in the outline table (data dictionary ). When SQL Parsing is executed, Oracle compares the SQL statement with the SQL statement in outline. If the SQL statement has an outline saved, the specified execution plan is generated through the hint set saved.
Note:
1. During SQL parsing, the SQL text is used to match the text saved by the data dictionary outline. Here, the matching method is to remove SQL spaces and compare the results after ignoring the SQL case differences.
2. For example, the select * from dual and SELECT * FROM dual statements use the same outline.

Iii. application 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. To avoid performance degradation caused by poor SQL Execution plans due to Bind Peeking.
4. Avoid database versions, configurations, and other differences among large-scale distributed applications from which the optimizer generates different execution plans.
5. 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.
6. When the early optimizer version is switched from rule to cbo mode, it is used to maintain business stability (the execution plan is stable) during the transition period)

Note:

In any database, most of the SQL statement execution plans should be automatically generated by the optimizer and run efficiently, but only a few of them need to be generated using various tools (outine, SQL profile) to lock the execution plan

Iv. Notes

1. outline exists in outln users. Outln users are very important system users. They are of the same importance 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 through Outline, provided that all hints in outline are valid.
3. You can enable outline only when the use_stored_outlines parameter is set.
4. the SQL statement with the literal value is not highly shared (no bound variable is used), and the SQL statement for variable binding in Outline is better. For SQL statements that use the literal value, each SQL statement must generate an outline.
5. The create any outline or execute_catelog_role permission is required to create an outline.
6. From the CBO perspective, the statistical information of database tables and indexes is constantly changing with the change of data volume. A fixed execution plan is not necessarily the best execution plan in some periods. Therefore, the use of outline depends on the specific situation.
7. The first time you apply Outline (alter system), this operation will generate a Library cache pin. Exercise caution.
8. 10.2.0.4 outline bug 6455659
9. The use_stored_outlines parameter is invalid after restart and needs to be reset.
10. When the objects on which outline depends are deleted, outline will not be automatically deleted.

V. outline-related views

• Two Basic Views: dba_outlines and dba_outline_hints
• Three underlying tables: ol $, ol $ hints, and ol $ nodes

6. Use outline

I. Create an outline

There are three methods to create an outline. The following is a brief introduction.

1. Create an outline for each SQL statement executed by the session or even the entire system. You can set the following parameters for the session level and system level respectively.

Alter system set create_stored_outlines = TRUE;

Alter session set create_stored_outlines = TRUE;

Note: basically no database will do this, so we will not test this method;

2. manually create outline to CREATE the outline of a given SQL statement, as shown below:

CREATE or replace OUTLINE outline_dh_test for category test on select * from dh_stat where id = 11;

Or

CREATE or replace OUTLINE outline_dh_test1 on select * from dh_stat where id = 11;

Example:

SQL> CREATE or replace OUTLINE outline_dh_test FOR CATEGORY test on select * from dh_stat where id = 11;

Outline created.
SQL & gt; set linesize 200 pagesize 999
SQL> set long 30
SQL> set long 50
SQL> select name, owner, category, used, SQL _text from dba_outlines;
Name owner category used SQL _TEXT
------------------------------------------------------------------------------------------------
OUTLINE_DH_TEST dbmon test unused select * from dh_stat where id = 11
SQL> select name, hint from dba_outline_hints;
NAME HINT
--------------------------------------------------------------------------------
OUTLINE_DH_TEST FULL (@ "SEL $1" "DH_STAT" @ "SEL $1 ")
OUTLINE_DH_TEST OUTLINE_LEAF (@ "SEL $1 ")
OUTLINE_DH_TEST ALL_ROWS
OUTLINE_DH_TEST OPT_PARAM ('_ optimizer_use_feedback' 'false ')
OUTLINE_DH_TEST OPT_PARAM ('_ optimizer_adaptive_cursor_sharing ''fa
OUTLINE_DH_TEST OPT_PARAM ('_ optimizer_extended_cursor_sharing_rel'
OUTLINE_DH_TEST OPT_PARAM ('_ bloom_pruning_enabled' 'false ')
OUTLINE_DH_TEST OPT_PARAM ('_ gby_hash_aggregation_enabled' 'false ')
OUTLINE_DH_TEST OPT_PARAM ('_ optimizer_extended_cursor_sharing ''no
OUTLINE_DH_TEST OPT_PARAM ('_ bloom_filter_enabled' 'false ')
OUTLINE_DH_TEST OPT_PARAM ('_ optimizer_null_aware_antijoin' 'false'
OUTLINE_DH_TEST OPT_PARAM ('_ optim_peek_user_binds' 'false ')
OUTLINE_DH_TEST DB_VERSION ('11. 2.0.1 ')
OUTLINE_DH_TEST OPTIMIZER_FEATURES_ENABLE ('11. 2.0.1 ')
OUTLINE_DH_TEST IGNORE_OPTIM_EMBEDDED_HINTS
15 rows selected.
Note:

• If the outline category is not specified, the default value is default, and the default category cannot be specified during creation (an error is reported ).
• This method is not very convenient, because the entire SQL text must be used as part of the statement, which may cause the statement cannot be shared. Therefore, this method is rarely used.

3. From 10 Gb onwards, you can create an outline by referencing an existing SQL statement in the Shared Pool

Exec DBMS_OUTLN.create_outline (hash_value => 1752921103, child_number => 0, category => 'test ');

Note:
• This method cannot specify the specific outline name, which is automatically generated by the system. You can modify it by alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2.
• If a category is not specified, the default category is used by default. When a category is created here, the default category cannot be specified (an error is reported ).
• This method is generally used when we use an outline fixed execution plan.
• A simple example is provided to help you better understand it.

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page