ORACLE uses STOREDOUTLINE to solidify execution plans-private and public

The storage outline can be used to fix the SQL Execution Plan. The storage outline consists of a series of optimizer hints related to SQL statements. When the storage outline is available, oracle automatically generates an execution plan for the corresponding SQL statement based on these hints. The storage outline can be divided into private and public. At the same time, we can divide the storage Outline into different categories and specify the storage outline to play a role, which will undoubtedly facilitate dba management. Once stored, the storage outline will not change with the subsequent system configuration or statistical information. (Although oracle 11g supports the storage outline, oracle does not recommend the storage outline, but uses SPM ).

Public and private

Oracle can create a public storage outline or a private storage outline. The syntax format is as follows:

Create a public storage outline. The information is stored in outln. When creating a private outline, the information is stored in the current user mode. Therefore, the current user must have the execution permission of DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES, to create related objects (this is not necessary because after 10.1, a temporary table object will be created under system to store the private storage outline ).

 

SQL> show userUSER is "OUTLN" SQL> l 1 * select object_name, object_type from user_objects order by region>/OBJECT_NAME OBJECT_TYPE specify parameter OL $ name indexol $ NODE_OL_NAME INDEXOL $ signature indexol $ HNT_NUM region $ LOBORA $ define PROCEDUREOL $ hints tableol $ NODES TABLEOL $ TABLE has selected 10 rows.

It can be seen from this that the public storage outline information is mainly stored in three tables: OL $, OL $ HINTS, and OL $ NODES.

First, let's create a public outline,Create any outline permission is required to CREATE an OUTLINE.

SQL> create public outline outline1 on select count (*) from t1 where object_id <100; create public outline outline1 on select count (*) from t1 where object_id <100 * 1st row error: ORA-18005: this operation requires the create any outline permission SQL> create public outline outline1 on select count (*) from t1 where object_id <100; the outline has been created.

If the storage outline plays a role, you need to set the use_stored_outlines parameter at the system or session level.

SQL> set autotrace on explainSQL> select count (*) from t1 where object_id <100; COUNT (*) ---------- 98 execution Plan ---------------------------------------------------------- Plan hash value: 3724264953 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | --------------------------------------------------------------------------- | 0 | select statement | 1 | 13 | 295 (1) | 00:00:04 | 1 | sort aggregate | 1 | 13 | * 2 | table access full | T1 | 12 | 156 | 295 (1) | 00:00:04 | identified by operation id: --------------------------------------------------------------- 2-filter ("OBJECT_ID" <100) Note ------dynamic sampling used for this st Atement (level = 2) SQL> create index i1 on t1 (object_id); the index has been created. SQL> select count (*) from t1 where object_id <100; COUNT (*) ---------- 98 execution Plan ------------------------------------------------------ Plan hash value: 3900446664 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 1 3 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | * 2 | index range scan | I1 | 98 | 1274 | 2 (0) | 00:00:01 | identified Predicate Information (identified by operation id): required 2-access ("OBJECT_ID" <100) Note ------dynamic sampling used for this statement (level = 2) SQL> alter sessio N set use_stored_outlines = true; the session has been changed. SQL> select count (*) from t1 where object_id <100; COUNT (*) ---------- 98 execution Plan ------------------------------------------------------ Plan hash value: 3724264953 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 295 (1) | 00:00:04 | 1 | sort aggregate | 1 | 13 | * 2 | table access full | T1 | 4411 | 57343 | 295 (1) | 00:00:04 | identified by operation id: ------------------------------------------------------------- 2-filter ("OBJECT_ID" <100) Note ----- -- outline "OUTLINE1" used for this statement

After use_stored_outlines is set to true at the system level,

 

 

SQL> show userUSER is "EASY2" SQL> select count (*) from t1 where object_id <100; COUNT (*) ---------- 98 execution Plan -------------------------------------------------------- Plan hash value: 3724264953 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 295 (1) | 00:00:04 | 1 | sort aggregate | 1 | 13 | * 2 | table access full | T1 | 4411 | 57343 | 295 (1) | 00:00:04 | identified by operation id: ------------------------------------------------------------- 2-filter ("OBJECT_ID" <100) Note ------outline "OUTLINE1" used for this statement

The following describes how to create a private storage outline. Before creating a private outline, run the following statement to create related objects in the current mode:

SQL> exec DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES; the PL/SQL process has been completed successfully. SQL> select * from tab; TNAME TABTYPECLUSTERID ------------------------------ ------- ---------- OL $ TABLEOL $ HINTS TABLEOL $ NODES TABLET1 TABLESQL> select count (*) from OL $; COUNT (*) ---------- 0

SQL> create private outline outline2 on select count (*) from t1 where object_id <100; the outline has been created. SQL> select count (*) from OL $; COUNT (*) ---------- 0SQL> select count (*) from SYSTEM. OL $; COUNT (*) ---------- 1

SESSION 1

 

 

SQL> select count (*) from t1 where object_id <100; COUNT (*) ---------- 98 execution Plan ------------------------------------------------------ Plan hash value: 3900446664 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 3 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | * 2 | index range scan | I1 | 4411 | 57343 | 3 (0) | 00:00:01 | identified by operation id: ------------------------------------------------------------- 2-access ("OBJECT_ID" <100) Note ------outline "OUTLINE2" used for this statement

SESSION 2:

SQL> alter session set use_private_outlines = TRUE; the session has been changed. SQL> select count (*) from t1 where object_id <100; COUNT (*) ---------- 98 execution Plan ------------------------------------------------------ Plan hash value: 3900446664 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | * 2 | index range scan | I1 | 98 | 1274 | 2 (0) | 00:00:01 | identified Predicate Information (identified by operation id): required 2-access ("OBJECT_ID" <100) Note ------dynamic sampling used for this statement (level = 2)

Summary: The storage outline of oracle can be divided into public and private. Information about the public outline is stored in the OUTLN user, and information about the private public is stored in the temporary table under the SYSTEM; because the private outline is stored in the dead table, it can only be used in the current session. To use the private outline, you need to set USE_PRIVATE_OUTLINES (system or session level, which is not the initialization parameter ), we also need to set USE_STORED_OUTLINES to use the public outline;