Detailed introduction of three execution plan control methods based on hint in Oracle
- 2021-12-13 10:07:15
- OfStack
hint (prompt) is undoubtedly the most basic way to control the execution plan; By embedding optimizer instructions directly in SQL statements, Then, the optimizer forcibly selects the execution path specified by hint when the statement is executed. The biggest advantage of this use mode is convenience and rapidity, and the system is also very high. Usually, when fine-tuning some SQL statement execution plans, I will prefer this mode, but despite this, there are still many problems that can not be ignored in the use of hint;
There are some noteworthy rules in the process of using hint. First of all, it is necessary to accurately identify the corresponding query block. If you need to use comments, you can also declare them in hint; For the use of alias object 1 law to use alias to reference, and such as "user name. Object" reference is not allowed, these are my usual mistakes, in fact, careful 1 point is not related, but the most depressing is the use of hint process without any hint information can be referred to! ! For example, if an invalid hint is used in a statement, Oracle will not give you any relevant error message. On the contrary, these hint will be silently ignored during execution as if nothing had happened. .
At this point, I don't want to discuss how to use hint correctly. What I want to say is that in Oracle, there are still many mechanisms to control the execution plan. In 11g, there are three execution plan control methods based on the optimizer hint:
1. OUTLINE (Outline)
2. SQL PROFILE (Profile)
3. SQL BASELINE (baseline)
Compared with hint, these methods are more systematic and complete, and their appearance greatly improves the practicability of hint, an ancient control method.
OUTLINE (Outline)
The principle of OUTLINE is to analyze the execution plan of SQL statement. In this process, a set of hints which can effectively force the optimizer to select a certain execution plan is determined, and then these hints are saved. When the next "same" query occurs, the optimizer will ignore the current statistical information factors and select hints recorded in OUTLINE to execute the query, so as to control the execution plan.
There are usually two ways to create OUTLINE, one is to use create outline statement, and the other is to use the exclusive DBMS_OUTLN package. When using Create outline mode, we need to indicate the complete query statement:
SQL> create outline my_test_outln for category test on
2 select count(*) from scott.emp;
Outline created.
In contrast, the DBMS_OUTLN.CREATE_OUTLINE mode allows outline to be created from the hash value of the SQL statement that has been saved in the cache, and is therefore more commonly used, with the following signature:
DBMS_OUTLN.CREATE_OUTLINE (
hash_value IN NUMBER,
child_number IN NUMBER,
category IN VARCHAR2 DEFAULT 'DEFAULT');
category is used to specify the classification of OUTLINE. Only one classification can be used in one session. The selection of classification is determined by the parameter USE_STORED_OUTLINES. The default value of this parameter is FALSE, which means that OUTLINE is not applicable. If TRUE is set, OUTLINE under DEFAULT classification is selected. If OUTLINE under non-DEFAULT classification is needed, this parameter value can be set to the name of the corresponding classification.
The view of OUTLINE can usually query DBA_OUTLINES and DBA_OUTLINE_HINTS. There are also three tables for saving OUTLINE information under OUTLN users in the database, among which OL # records the complete definition of every OUTLINE.
SQL> select TABLE_NAME,OWNER from all_tables where owner='OUTLN';
TABLE_NAME OWNER
------------------------------ ------------------------------
OL$ OUTLN
OL$HINTS OUTLN
OL$NODES OUTLN
-- Query the existing in the current system OUTLINE Already corresponds to OUTLINE Used hints :
[sql]
SQL> select category,ol_name,hintcount,sql_text from outln.ol$;
CATEGORY OL_NAME HINTCOUNT SQL_TEXT
---------- ------------------------------ ---------- --------------------------------------------------
TEST MY_TEST_OUTLN 6 select count(*) from scott.emp
DEFAULT SYS_OUTLINE_13080517081959001 6 select * from scott.emp where empno=7654
-- Query correspondence OUTLINE Applied on hints
SQL> select name, hint from dba_outline_hints where name = 'SYS_OUTLINE_13080517081959001';
NAME HINT
------------------------------ --------------------------------------------------------------------------------
SYS_OUTLINE_13080517081959001 INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
SYS_OUTLINE_13080517081959001 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_13080517081959001 ALL_ROWS
SYS_OUTLINE_13080517081959001 DB_VERSION('11.2.0.1')
SYS_OUTLINE_13080517081959001 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
SYS_OUTLINE_13080517081959001 IGNORE_OPTIM_EMBEDDED_HINTS
6 rows selected.
Use OUTLINE to lock the complete instance of the execution plan:
-- Execute a query
SQL> select * from scott.emp where empno=7654;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
-- View the execution plan for this query
-- Pay attention to the hash_value And child_number Inadmissible DBMS_OUTLN.CREATE_OUTLINE Parameter values, these are just PLAN_TABLE The value of the execution plan saved in! ! !
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 40t73tu9dst5y, child number 1
-------------------------------------
select * from scott.emp where empno=7654
Plan hash value: 2949544139
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7654)
19 rows selected.
-- Pass v$sql View get query sql Statement hash_value And child_number
SQL> select sql_id,hash_value,child_number,sql_text from v$sql
2 where sql_text like 'select * from scott.emp where empno%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ --------------------------------------------------
40t73tu9dst5y 2463917246 0 select * from scott.emp where empno=7654
-- Create OUTLINE Specified as the default DEFAULT Classification
SQL> exec dbms_outln.create_outline(2463917246,0,'DEFAULT');
PL/SQL procedure successfully completed.
-- SESSION Level setting USE_STORED_OUTLINES Parameter is TRUE , enable OUTLINE
SQL> ALTER SESSION SET USE_STORED_OUTLINES=TRUE;
Session altered.
-- Re-execute the query, and you can see the plan and the original 1 To, at the same time in the implementation of the plan Note Is shown in the outline "SYS_OUTLINE_13080517081959001"
SQL> set autotrace traceonly
SQL> select * from scott.emp where empno=7654;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7654)
Note
-----
- outline "SYS_OUTLINE_13080517081959001" used for this statement
Statistics
----------------------------------------------------------
1495 recursive calls
147 db block gets
262 consistent gets
5 physical reads
632 redo size
896 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
24 sorts (memory)
0 sorts (disk)
1 rows processed
Use OUTLINE under non-DEFAULT classification
-- View the currently available OUTLINE
SQL> select category,ol_name,hintcount,sql_text from outln.ol$;
CATEGORY OL_NAME HINTCOUNT SQL_TEXT
---------- ------------------------------ ---------- --------------------------------------------------
TEST MY_TEST_OUTLN 6 select count(*) from scott.emp
DEFAULT SYS_OUTLINE_13080517081959001 6 select * from scott.emp where empno=7654
-- Set the use of test Under classification OUTLINE
SQL> ALTER SESSION SET USE_STORED_OUTLINES=test;
Session altered.
-- Implementation plan Note Shows that the OUTLINE "MY_TEST_OUTLN"
SQL> set autotrace traceonly
SQL> select count(*) from scott.emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- outline "MY_TEST_OUTLN" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
148 db block gets
22 consistent gets
0 physical reads
540 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
A few considerations about OUTLINE:
1. The OUTLINE matching SQL statement is somewhat similar to similar in the cusor_sharing parameter, which means that even SQL statements with different hash values may use the same OUTLINE, such as:
SQL> ALTER SESSION SET USE_STORED_OUTLINES=test;
Session altered.
-- Use a different SQL Statement Also used the same as before OUTLINE
SQL> set autotrace traceonly
SQL> SELECT COUNT(*)FROM scott.emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- outline "MY_TEST_OUTLN" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- Query v$sql You can see that the two statements are different
SQL> select sql_id,hash_value,child_number,sql_text from v$sql
2 where sql_text like '%scott.emp%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ --------------------------------------------------
6xydpctfbwbm6 1555967590 0 select sql_id,hash_value,child_number,sql_text fro
m v$sql where sql_text like '%scott.emp%'
40t73tu9dst5y 2463917246 0 select * from scott.emp where empno=7654
abj9tmfcs15bm 2575340915 0 select count(*) from scott.emp
d16cs4nzg9vmk 1056239218 0 SELECT COUNT(*)FROM scott.emp
2. hash_value in DBMS_OUTLN. CREATE_OUTLINE is the hash value of the SQL statement, not sql_id, nor hash_value of the execution plan.
3. DBMS_OUTLN. CREATE_OUTLINE cannot customize the name of outline like Create outline statement. The name of outline created in this way is automatically generated by the system and can be modified manually by alter outline statement.
4. You can query the sql statement that has been recorded in the outline through the OUTLINE_SID and OUTLINE_CATEGORY fields in v $sql.
select sql_id,hash_value,child_number,OUTLINE_SID,OUTLINE_CATEGORY,sql_text from v$sql
where sql_text like '%scott.emp%'
SQL_ID HASH_VALUE CHILD_NUMBER OUTLINE_SID OUTLINE_CA SQL_TEXT
------------- ---------- ------------ ----------- ---------- ---------------------------------------------
6xydpctfbwbm6 1555967590 0 select sql_id,hash_value,child_number,sql_tex
t from v$sql where sql_text like '%scott.emp%'
40t73tu9dst5y 2463917246 0 DEFAULT select * from scott.emp where empno=7654
abj9tmfcs15bm 2575340915 0 TEST select count(*) from scott.emp
d16cs4nzg9vmk 1056239218 0 TEST SELECT COUNT(*)FROM scott.emp
SQL Profile (SQL Profile)
SQL Profile is basically equivalent to the upgraded version of OUTLINE, It is also the most powerful in function, Most of the time, it is only when you use SQL Optimization Consultant (SQL Tuning Advisor, STA). Same as OUTLINE, SQL Profile is also composed of 1 series hint, With SQL Profile, we can apply these hint in the background while executing SQL statements to maintain the stability of the execution plan. In fact, compared with OUTLINE, it also has one unique advantage, such as allowing profiles to be applied to multiple SQL statements by ignoring constants, and combining any hint set with specified SQL statements! !
In the process of using SQL Profile, the parameter SQLTUNE_CATEGORY realizes the same function as USER_STORED_OUTLINE parameter 1 in OUTLINE. At the same time, the profile will be created into DEFAULT classification by default, and SQL Profile of corresponding classification will be enabled by specifying different classification names for SQLTUNE_CATEGORY parameter; Usually we use STA to create profiles. In fact, these operations directly and indirectly use DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedures, and the call signature is as follows:
PROCEDURE IMPORT_SQL_PROFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
PROFILE SQLPROF_ATTR IN
NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
CATEGORY VARCHAR2 IN DEFAULT
VALIDATE BOOLEAN IN DEFAULT
REPLACE BOOLEAN IN DEFAULT
FORCE_MATCH BOOLEAN IN DEFAULT
PROCEDURE IMPORT_SQL_PROFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
PROFILE_XML CLOB IN
NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
CATEGORY VARCHAR2 IN DEFAULT
VALIDATE BOOLEAN IN DEFAULT
REPLACE BOOLEAN IN DEFAULT
FORCE_MATCH BOOLEAN IN DEFAULT
You can see that the creation of SQL Profile is accomplished by specifying the hint set for SQL_TEXT, It is not HASH_VALUE in OUTLINE, and the type display of PROFILE field uses SQLPROF_ATTR, and PROFILE_XML field also needs to fill hint set by obtaining OTHER_XML field of V $SQL_PLAN view. Unfortunately, the important process of this 1 profile is not mentioned in the official file, so it is impossible to know its use details in detail. In actual use, it is recommended to use STA to complete the creation of SQL Profile. Kerry Osborne used this process to create SQL Profile through SQL_ID, and gave a solution to customize hint collection by using IMPORT_SQL_PROFILE process to force change of execution plan. "You can visit kerryosborne. oracle-guy. com for details."
Baseline (BASELINE)
BASELINE is more like a performance indicator, oracle maintains and eliminates system performance degradation through baseline, The core of the baseline is a set of hint with a specific name and associated with a specific statement. It can match SQL statements like Profile 1. Although the control ability of the plan is not as flexible as Profile 1, it is still an important method to limit the instability of the plan. Here are some characteristics of the baseline:
1. There is no classification category in the baseline.
2. Each SQL statement can have multiple baselines, such as a fixed baseline set.
3. The baseline stores hint and hash_value of the execution plan, so the optimizer needs to verify whether there is a corresponding plan when deciding whether to adopt the baseline.
4. You can automatically create a baseline for every SQL statement that has been executed by setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true, and no baseline is created by default.
5. You can get the baseline you have created by querying the view DBA_SQL_PLAN_BASELINES.
6. Use the dbms_spm.load_plans_from_cursor_cache procedure to create a baseline for one cached SQL statement.
7. In 11g, the existing baseline is used by default to maintain the stability of the execution plan.
Creates a baseline for the specified SQL statement
-- Still use OUTLINE Sample query in
SQL> select * from scott.emp where empno=7654;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID 40t73tu9dst5y, child number 0
-------------------------------------
select * from scott.emp where empno=7654
Plan hash value: 2949544139
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7654)
19 rows selected.
-- Create BASELINE Note that the parameter is SQL_ID And PLAN_HASH_VALUE
SQL> var ret number
SQL> exec :ret := dbms_spm.load_plans_from_cursor_cache(-
> sql_id=>'&sql_id', -
> plan_hash_value=>&plan_hash_value,-
> fixed=>'&fixed');
Enter value for sql_id: 40t73tu9dst5y
Enter value for plan_hash_value: 2949544139
Enter value for fixed: NO
PL/SQL procedure successfully completed.
-- Run the query again to find the Note The baseline is used in SQL_PLAN_bmwra43zx42kr695cc014
SQL> set autotrace traceonly
SQL> select * from scott.emp where empno=7654;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7654)
Note
-----
- SQL plan baseline "SQL_PLAN_bmwra43zx42kr695cc014" used for this statement
Statistics
----------------------------------------------------------
747 recursive calls
14 db block gets
117 consistent gets
0 physical reads
2956 redo size
1028 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed