Shulou(Shulou.com)06/01 Report--

We all hope that the correct execution plan can be produced for all the SQL,CBO executed in the Oracle database, but this is not the case in reality. Due to a variety of reasons (such as inaccurate statistics of the objects involved in the target SQL, or congenital defects in some cost calculation formulas within the CBO, etc.), sometimes CBO produces inefficient or even wrong execution plans. In particular, the initial execution plan generated by the CBO for the target SQL is correct, and then due to some reason (such as the change of statistics, etc.), the CBO re-produces the wrong execution plan, which often leads to an order of magnitude increase in the execution time of the target SQL, and often makes us very confused: why is this SQL running so well that it is suddenly unacceptably slow? In fact, the sudden decline in SQL execution efficiency is often due to a change in the target SQL execution plan.

We certainly hope that such a change will never happen, that all SQL running in the Oracle database will have a correct and stable execution plan, but in fact, this was difficult to do before the advent of Oracle 11g SPM (SQL Plan Management). So now second, if there has been a change in the execution grave, that is, the CBO has produced a wrong execution plan, how should we correct it?

In our case, we usually re-collect statistics or modify the target SQL (such as adding Hint to the target SQL, etc.) to correct the wrong execution plan. But sometimes re-collecting statistics will not solve the problem, and what's worse, in many cases, there is no way to modify the SQL text of the target SQL (for example, the system developed by a third party cannot modify the source code, or the target SQL is dynamically generated by the foreground framework, etc.), so what should we do in this case?

In Oracle 10g/11g and later versions, we can use SQL Profile or SPM (SQL Plan Management) to solve the problem of the above execution plan changes, and use them to adjust and stabilize the execution plan of the target SQL.

This article describes the use of SQL Profile to steadily execute the plan:

The SQL Profile in Oracle 10g (literally translated as "SQL profile") can be said to be the evolution of Stored Outline (literally translated as "storage summary") in Oracle 9i. The functions that can be realized by Stored Outline can also be fully realized by SQL Profile.

Compared with Stored Outline, SQL Profile has the following advantages:

Easier to generate, change, and control

Better support for SQL statements, that is to say, a wider range of applications.

With SQL Profile, you can easily achieve the following two purposes:

Lock in or stabilize the execution plan

Causes the target SQL statement to run according to the specified execution plan if the SQL text of the target SQL cannot be modified.

There are two types of SQL Profile: one is Automatic, and the other is Manual. These two types are described below:

1. SQL Profile of Automatic type

A SQL Profile of type Automatic is actually some additional adjustment information for the target SQL, which is stored in the data dictionary. When the SQL Profile of Automatic type is available, the Oracle will make corresponding adjustments to the statistical information involved in the target SQL when it generates the execution plan, so it can avoid the wrong execution plan to some extent. You don't have to worry about the accuracy of Automatic-type SQL Profile, because Oracle will use techniques that are similar to dynamic adoption techniques to ensure that these additional adjustments are relatively accurate.

The SQL Profile of Automatic type does not lock the execution plan of the target SQL like Stored Outline, because the essence of the SQL Profile of Automatic type is some additional adjustment information for the target SQL. These additional adjustment information needs to work with the relevant statistical information of the original target SQL to get a new implementation plan, that is, once the statistical information of the original SQL is changed, even if the original Automatic type SQL Profile has not changed. The execution of the SQL may also change. In this sense, a SQL Profile of type Automatic does not fully stabilize the execution plan of the target SQL, although it can be used to adjust the execution plan.

Look at an example of using a SQL Profile of type Automatic to adjust the execution plan without changing the SQL text of the target SQL:

Create a test table and related actions:

Zx@MYDB > create table T1 (n number); Table created.zx@MYDB > declare 2 begin 3 for i in 1.. 10000 loop 4 insert into T1 values (I); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed.zx@MYDB > select count (*) from T1; COUNT (*)-10000zx@MYDB > create index idx_t1 on T1 (n) Index created.zx@MYDB > exec dbms_stats.gather_table_stats (ownname= > USER,tabname= >'T1 idx_t1 where methodopt = > 'for all columns size 1 where methodopt = > true); PL/SQL procedure successfully completed.zx@MYDB > select / * + no_index (T1 idx_t1) * / * from T1 where nyst1; N- 1

As can be seen from the above display, the target SQL is a full table scan (Table Access Full) of table T1, this execution plan is obviously wrong, and the correct execution here should be the index range scan (Index Range Scan) of the index IDX_T1. Let's use SQL Tuning Advisor to generate a SQL Profile of type Automatic for this SQL.

a. First create an auto-tuning task called my_sql_tuning_task_2:

Zx@MYDB > declare 2 my_task_name varchar2 (30); 3 my_sqltext clob; 4 begin 5 my_sqltext:='select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1; 6 my_task_name:=dbms_sqltune.create_tuning_task (7 sql_text= > my_sqltext, 8 user_name= > USER, 9 scope= > 'COMPREHENSIVE', 10 time_limit= > 60, 11 task_name= >' my_sql_tuning_task_1', 12 description= > Task to tune a query on table T1'); 13 end 14 / PL/SQL procedure successfully completed. Zx@MYDB > select task_name,status,execution_start,execution_end from user_advisor_log TASK_NAME STATUS EXECUTION_START EXECUTION_END -my_sql_tuning_task_1 INITIAL

Note: when creating a task, you can use SQL to create it, which can be used in the case of long SQL text. Refer to the official documentation for details.

b. Perform the above automatic adjustment tasks

Zx@MYDB > begin 2 dbms_sqltune.execute_tuning_task (task_name= > 'my_sql_tuning_task_1'); 3 end; 4 / zx@MYDB > zx@MYDB > select task_name,status,execution_start,execution_end from user_advisor_log TASK_NAME STATUS EXECUTION_START EXECUTION_END -my_sql_tuning_task_1 COMPLETED 2017-02-28 10:59:43 2017-02-28 10:59:44PL/SQL procedure successfully completed.

c. View the adjustment results of the above automatic tasks

Zx@MYDB > set long 9000zx@MYDB > set longchunksize 1000zx@MYDB > set linesize 800zx@MYDB > select dbms_sqltune.report_tuning_task ('my_sql_tuning_task_1') from dual

As you can see from the above adjustments, Oracle now tells us that it has found a better execution plan for the target SQL and has created an Automatic type SQL Profile for that SQL. If we accept the SQL Profile using accecp_sql_profile, the response time of the target SQL will be increased by 86.24%, the logical read will be increased by 95%, and the execution plan of the target SQL will be changed from the original full table scan to the index range scan after accepting the SQL Profile.

The adjustment result produced by the above SQL Profile of type Automatic is exactly what we want, so now we just need to accept the SQL Profile as prompted by Oracle:

Zx@MYDB > execute dbms_sqltune.accept_sql_profile (task_name = > 'my_sql_tuning_task_1', task_owner = > ZX', replace = > TRUE,force_match= > true); PL/SQL procedure successfully completed.

After accepting this SQL Profile, let's take a look at the effect and execute the target SQL again:

Zx@MYDB > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1; N1

Notice that there is something like "SQL profile SYS_SQLPROF_015a82b353490000 used for this statement" in the Note section, which means that the SQL Profile we just accepted has worked, and the name of the SQL Profile is SYS_SQLPROF_015a82b353490000. It can also be seen from the implementation plan that the implementation plan has indeed changed.

In addition, the default value of the input parameter force_match of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE is FALSE, which means that SQL Profile will only be applied if the SQL text matches exactly. In this case, as long as the SQL text of the target SQL changes a little, the original SQL Profile will lose its effect. If set to TRUE, even if there is a change in SQL, SQL Profile will be enforced.

Delete SQL Profile

Zx@MYDB > exec dbms_sqltune.drop_sql_profile ('SYS_SQLPROF_015a82b353490000'); PL/SQL procedure successfully completed.

2. SQL Profile of Manual type

A SQL Profile of type Manual is essentially a combination of Hint, which actually comes from the Hint combination of the Outline Data part of the execution plan. SQL Profile of type Manual can also adjust its execution plan without changing the SQL text of the target SQL, and more importantly, SQL Profile of type Manual can play a good role in stabilizing the execution plan of the target SQL, which is not available in SQL Profile of type Automatic.

Look at an example of a fixed execution plan using a SQL Profile instance of type Manual. Use the T1 table above, delete the SQL Profile above, and execute SQL again.

Zx@MYDB > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1; N1

From the above output, we can see that the execution plan is still running a full table scan.

Now let's create a SQL Profile of type Manual. Coe_xfr_sql_profile.sql, a script on MOS, is used here. This script is used to create a SQL Profile of type Manual by specifying the Hint combination of the Outline Data part of the specified execution plan of SQL from Shared Pool, AWR Repository.

The steps to use the coe_xfr_sql_profile.sql script are

Use coe_xfr_sql_profile.sql for the target SQL to generate script A that generates SQL Profile of its Manual type.

Rewrite the text of the target SQL and use the appropriate Hint in it until the SQL that joins the Hint can get out of the execution plan we want. Then use the script coe_xfr_sql_profile.sql for the SQL with the appropriate Hint to generate script B that can generate its Manual type SQL Profile.

Replace the Hint combination of the Outline Data part of script A with the Hint combination of the Outline Data part of script B.

Execute script A to generate a SQL Profile of type Manual for the original target SQL.

Now rewrite the above SQL to force the index to go:

Zx@MYDB > select / * + index (T1 idx_t1) * / * from T1 where nasty 1; N1

You can see the SQL Id and the corresponding Plan hash value from the execution plan.

SQL Id:6chcc0pvvhqqm Plan hash value:3617692013 with full table scan

Index scanned SQL Id:2ufquy7xs5nm5 Plan hash value:1369807930

a. First use coe_xfr_sql_profile.sql to generate the script corresponding to the full table scan SQL

Zx@MYDB > @ scripts/coe_xfr_sql_profile.sqlParameter 1:SQL_ID (required) Enter value for 1: 6chcc0pvvhqqmPLAN_HASH_VALUE AVG_ET_SECS--3617692013 .002 Parameter 2:PLAN_HASH_VALUE (required) Enter value for 2: 3617692013Values passed to coe_xfr_sql_profile:~~SQL_ID: "6chcc0pvvhqqm" PLAN_HASH_VALUE: " 3617692013 "SQL > BEGIN 2 IF: sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR (- 20100) 'SQL_TEXT for SQL_ID & & sql_id. Was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).); 4 END IF; 5 END; 6 / SQL > SET TERM OFF;SQL > BEGIN 2 IF: other_xml IS NULL THEN 3 RAISE_APPLICATION_ERROR (- 20101, 'PLAN for SQL_ID & & sql_id. And PHV & & plan_hash_value. Was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).); 4 END IF; 5 END; 6 / SQL > SET TERM OFF;Execute coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sqlon TARGET system in order to create a custom SQL Profilewith plan 3617692013 linked to adjusted sql_text.COE_XFR_SQL_PROFILE completed.

As you can see from the output, a script named coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql is generated.

b. Use coe_xfr_sql_profile.sql to generate the script corresponding to index scan SQL

SQL > @ scripts/coe_xfr_sql_profile.sqlParameter 1:SQL_ID (required) Enter value for 1: 2ufquy7xs5nm5PLAN_HASH_VALUE AVG_ET_SECS--1369807930 .001Parameter 2:PLAN_HASH_VALUE (required) Enter value for 2: 1369807930Values passed to coe_xfr_sql_profile:~~SQL_ID: "2ufquy7xs5nm5" PLAN_HASH_VALUE: "1369807930" SQL > BEGIN 2 IF: sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR (- 20100 'SQL_TEXT for SQL_ID & & sql_id. Was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).); 4 END IF; 5 END; 6 / SQL > SET TERM OFF;SQL > BEGIN 2 IF: other_xml IS NULL THEN 3 RAISE_APPLICATION_ERROR (- 20101, 'PLAN for SQL_ID & & sql_id. And PHV & & plan_hash_value. Was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).); 4 END IF; 5 END; 6 / SQL > SET TERM OFF;Execute coe_xfr_sql_profile_2ufquy7xs5nm5_1369807930.sqlon TARGET system in order to create a custom SQL Profilewith plan 1369807930 linked to adjusted sql_text.COE_XFR_SQL_PROFILE completed.

As you can see from the output, a script named coe_xfr_sql_profile_2ufquy7xs5nm5_1369807930.sql is generated.

c. Replace the Hint combination of the Outline Data part of the generated script with the script created by Mr. Cheng, that is, the red box part of the following figure

d. Execute coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql script

Zx@MYDB > @ coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sqlzx@MYDB > REMzx@MYDB > REM $Header: 215187.1 coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql 11.4.4.4 2017-02-28 carlos.sierra $zx@MYDB > REMzx@MYDB > REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.zx@MYDB > REMzx@MYDB > REM AUTHORzx@MYDB > REM carlos.sierra@oracle.comzx@MYDB > REMzx@MYDB > REM SCRIPTzx@MYDB > REM coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sqlzx@MYDB > REMzx@MYDB > REM DESCRIPTIONzx@MYDB > REM This script is generated by coe_xfr_sql_profile.sqlzx@MYDB > REM It contains the SQL*Plus commands to create a customzx@MYDB > REM SQL Profile for SQL_ID 6chcc0pvvhqqm based on plan hashzx@MYDB > REM value 3617692013.zx@MYDB > REM The custom SQL Profile to be created by this scriptzx@MYDB > REM will affect plans for SQL commands With signaturezx@MYDB > REM matching the one for SQL Text below.zx@MYDB > REM Review SQL Text and adjust accordingly.zx@MYDB > REMzx@MYDB > REM PARAMETERSzx@MYDB > REM None.zx@MYDB > REMzx@MYDB > REM EXAMPLEzx@MYDB > REM SQL > START coe_xfr_sql_profile_6chcc0pvvhqqm_3617692013.sql Zx@MYDB > REMzx@MYDB > REM NOTESzx@MYDB > REM 1. Should be run as SYSTEM or SYSDBA.zx@MYDB > REM 2. User must have CREATE ANY SQL PROFILE privilege.zx@MYDB > REM 3. SOURCE and TARGET systems can be the same or similar.zx@MYDB > REM 4. To drop this custom SQL Profile after it has been created:zx@MYDB > REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('coe_6chcc0pvvhqqm_3617692013') Zx@MYDB > REM 5.Be aware that using DBMS_SQLTUNE requires a licensezx@MYDB > REM for the Oracle Tuning Pack.zx@MYDB > REM 6.If you modified a SQL putting Hints in order to produce a desiredzx@MYDB > REM Plan, you can remove the artifical Hints from SQL Text pieces below.zx@MYDB > REM By doing so you can create a custom SQL Profile for the originalzx@MYDB > REM SQL but with the Plan captured from the modified SQL (with Hints) .zx @ MYDB > REMzx@MYDB > WHENEVER SQLERROR EXIT SQL.SQLCODE;zx@MYDB > REMzx@MYDB > VAR signature NUMBER;zx@MYDB > VAR signaturef NUMBER Zx@MYDB > REMzx@MYDB > DECLARE 2 sql_txt CLOB; 3 h SYS.SQLPROF_ATTR; 4 PROCEDURE wa (p_line IN VARCHAR2) IS 5 BEGIN 6 DBMS_LOB.WRITEAPPEND (sql_txt, LENGTH (p_line), p_line); 7 END wa; 8 BEGIN 9 DBMS_LOB.CREATETEMPORARY (sql_txt, TRUE); 10 DBMS_LOB.OPEN (sql_txt, DBMS_LOB.LOB_READWRITE); 11-SQL Text pieces below do not have to be of same length. 12-- So if you edit SQL Text (i.e. Removing temporary Hints), 13-- there is no need to edit or re-align unmodified pieces. 14 wa (Q'[select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1]'); 15 DBMS_LOB.CLOSE (sql_txt) 16 h: = SYS.SQLPROF_ATTR (17 Q'[BEGIN_OUTLINE_DATA]', 18 Q'[IGNORE_OPTIM_EMBEDDED_HINTS]', 19 Q'[OPTIMIZER_FEATURES_ENABLE ('11.2.0.1')]', 20 Q'[DB_VERSION ('11.2.0.1')]', 21 Q'[ALL_ROWS]', 22 Q'[OUTLINE_LEAF (@ "SEL$1")]' 23 q' [INDEX (@ "SEL$1"T1" @ "SEL$1" ("T1". "N"))]', 24 q' [END_OUTLINE_DATA]') 25: signature: = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_txt); 26: signaturef: = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_txt, TRUE) 27 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (28 sql_text = > sql_txt, 29 profile = > h, 30 name = > 'coe_6chcc0pvvhqqm_3617692013', 31 description = >' coe 6chcc0pvvhqqm 3617692013' | |: signature | |'| | signaturef | |', 32 category = > 'DEFAULT', 33 validate = > TRUE, 34 replace = > TRUE, 35 force_match = > FALSE / * TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) * /); 36 DBMS_LOB.FREETEMPORARY (sql_txt); 37 END; 38 / PL/SQL procedure successfully completed.zx@MYDB > WHENEVER SQLERROR CONTINUEzx@MYDB > SET ECHO OFF; SIGNATURE- 3589138201450662673 SIGNATUREF- 8068435081012723673 Manual custom SQL Profile has been createdCOE_XFR_SQL_PROFILE_6chcc0pvvhqqm_3617692013 completed

e. View the execution plan of the target SQL again after the execution is completed

Zx@MYDB > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1; N1

You can see from the execution plan that the INDEX RANGE SCAN has gone, and the note section prompts SQL profile coe_6chcc0pvvhqqm_3617692013 used for this statement that the SQL Profile was used when executing the sql.

If you want the SQL Profile to remain in effect when the SQL text of the target SQL changes, you need to modify the force_match= > true in the generated script.

Reference: "SQL Optimization based on Oracle"

Official document: http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF94854

Http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sqltun.htm#CHDGAJCI

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.