A detailed introduction to the 3 Implementation plan control methods based on hint in Oracle _oracle

Hint (hint) is undoubtedly the most basic way to control the implementation of the plan; by embedding the optimizer directive directly in the SQL statement, and then making the optimizer select hint specified execution path when executing the statement, the most benefit is convenience and rapidity, and the system is very high, I usually prefer this approach when fine-tuning some of the SQL statement execution plans, but despite this, there are still a lot of problems that hint can not ignore in using;

There are some notable rules in the process of using hint, first of all is to accurately identify the corresponding query block, if you need to use the annotation can be declared in hint; aliases are used to refer to objects that use aliases, and references such as "User name. Object" are not allowed. These are a few of my usual mistakes, in fact, it does not matter carefully, but the most depressing is the use of the process of hint without any hint information can be referenced!! For example, the use of invalid hint,oracle in a statement does not give you any relevant error information, whereas these hint are silently ignored in execution, as if nothing had happened.

Here, I don't want to discuss how to use hint correctly, I want to say that there are still many mechanisms in Oracle to control the execution plan, and there are three hint control methods based on the optimizer in 11g:

1.OUTLINE (outline)
2.SQL Profile (Profiles)
3.SQL BASELINE (Baseline)

The use of these methods is hint more systematic and complete, and their appearance greatly improves the practicability of this ancient control mode of hint.

OUTLINE (outline)

The principle of outline is to parse the execution plan of an SQL statement, in which a set of hints that can be used to force the optimizer to select an execution plan is determined, and then the hints is saved, and the optimizer ignores the current statistics factor when the "same" query occurs the next time. Select the hints recorded in outline to execute the query, to control the execution plan.

There are usually two ways to create a outline, one that uses the Create outline statement, and the other is the use of the exclusive DBMS_OUTLN package, where we need to specify the full query statement when using the Create outline method:

Copy Code code as follows:

Sql> Create outline My_test_outln for category test on
2 Select COUNT (*) from scott.emp;

Outline created.

By contrast, dbms_outln. The Create_outline method allows you to create outline by using the hash value of the SQL statement that has been saved in the cache, and is therefore more commonly used, following is the signature:

Copy Code code as follows:

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, in which only one category can be used in a session, and the selection of the classification is determined by the parameter use_stored_outlines, and the default value of the parameter is false, which means that outline is not applicable. Set to True to select the outline under the default category, and if you need to use outline under a non-default category, you can set the parameter value to the name of the corresponding category.

A view of OUTLINE can often be queried dba_outlines,dba_outline_hints, and three tables are used to save OUTLINE information outln users in the database, where ol# records the full definition of each OUTLINE.

Copy Code code as follows:

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 outline in the current system for the hints that have been used for outline:
[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 the corresponding outline on the application of 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 full instance of the execution plan:

Copy Code code as follows:

--Execute 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 the query
Note that Hash_value and child_number here are not dbms_outln. Create_outline parameter values, which are just the values of the execution plans saved in plan_table!!!
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)

Rows selected.

--Get the Hash_value and child_number of the query SQL statement through the V$sql view
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 the outline, specify the default defaults category
sql> exec dbms_outln.create_outline (2463917246,0, ' DEFAULT ');

Pl/sql procedure successfully completed.

--The session level setting Use_stored_outlines parameter is true, enabling outline
Sql> ALTER session SET Use_stored_outlines=true;

Session altered.

--Re-executing the query, you can see that the plan is consistent with the original, while the note in the execution plan shows the use of 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
Bytes received via sql*net from client
1 sql*net roundtrips To/from Client
Sorts (memory)
0 Sorts (disk)
1 rows processed

Using outline under non-default classification

Copy Code code as follows:

--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 outline using the test category
Sql> ALTER session SET Use_stored_outlines=test;

Session altered.

--The execution plan note shows the use of 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 |    |     1   (0) | 00:00:01 |
-------------------------------------------------------------------

Note
-----
  -outline "my_test_outln" used to 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

Some things to note about outline:

1,outline matching SQL statements is somewhat similar to the similar in the cusor_sharing parameter, which means that even SQL statements with different hash values may use the same outline, such as:

Copy Code code as follows:

Sql> ALTER session SET Use_stored_outlines=test;

Session altered.

--Using a different SQL statement also uses the same outline as before
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 |    |     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 can see that 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  & nbsp;         0 SELECT COUNT (*) from scott.emp

2,dbms_outln. The Hash_value in Create_outline is the hash value of the SQL statement, not the sql_id, nor the hash_value of the execution plan.

3,dbms_outln. Create_outline cannot customize the name of the outline as the Create outline statement, the outline name created by the system is automatically generated and needs to be modified manually using ALTER outline statements.

4, you can query the SQL statements that have been recorded in the outline through the Outline_sid and Outline_category fields in V$sql.

Copy Code code as follows:

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 profiles)

SQL profile is essentially the equivalent of outline's upgraded version, and it is also the most powerful, often in contact with SQL Tuning Advisor (SQL Tuning Advisor,sta), the same as outline, SQL Profile also consists of a series of hint, using SQL profile We can apply these hint in the background while the SQL statement is executing to maintain the stability of the execution plan, in fact, relatively outline it also has some unique advantages, For example, allow profiles to be applied to multiple SQL statements by ignoring constants, and you can combine any hint collection with a specified SQL statement!!

In the process of using SQL profile, the parameter sqltune_category implements the same functionality as the User_stored_outline parameter in outline, where the profile is also created by default into the defaults category. Enable the SQL profile for the corresponding classification by specifying a different category name for the sqltune_category parameter; Typically, we use STA to create profiles, which in fact directly and indirectly use Dbms_sqltune. Import_sql_profile procedure, the calling signature is as follows:

Copy Code code 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 done by specifying a hint set for sql_text, not hash_value in outline, and the type of profile field displays the Sqlprof_attr,profile_ used XML fields also need to populate the hint set by getting the Other_xml field of the V$sql_plan view, but unfortunately the important process of this profile is not mentioned in the official file, so it is not possible to learn more about its usage details, or it is recommended to use STA to complete the SQL Profile creation. Kerry Osborne has used this process to create SQL profiles through sql_id, while giving a solution that enforces the execution plan by customizing the Hint collection with import_sql_profile processes. can access kerryosborne.oracle-guy.com for more information "

Baseline (BASELINE)

Baseline is more like a performance metric, Oracle will maintain and eliminate system degradation through baselines, at the core of a set of hint with a specific name and associated with a particular statement, which can match the SQL statement like a profile. While the control of the plan is not as flexible as the profile, it is still an important way to limit the instability of the plan, following are some of the 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 collection.
3, the baseline holds the hint and the hash_value of the execution plan, so the optimizer also needs to verify that a corresponding plan exists when deciding whether to adopt a baseline.
4, you can automatically create a baseline for each SQL statement that is executed by setting Optimizer_capture_sql_plan_baselines to True, and the baseline is not 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 a cached SQL statement.
7, in 11g, the default is to maintain the stability of the execution plan using the existing baseline.

To create a baseline for the specified SQL statement

Copy Code code as follows:

--Still using the sample query in outline


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)

Rows selected.

--Create baseline, note that the parameters are 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 see that the baseline sql_plan_bmwra43zx42kr695cc014 is shown in the note for the execution plan output
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
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