sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle使用sql profile处理未绑定变量的SQL的执行计划

时间:2016-12-12 21:31   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于改变SQL语句的执行计划的文章,详细介绍使用sql profile处理未绑定变量的SQL的执行计划。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 使用sql profle进行偷梁换柱的小例子

我们知道outline 可以用来固定sql的执行计划,但是针对未使用绑定变量的sql来讲,可能就不行了。

我最近遇到一个case,有个sql消耗很大,没有使用绑定变量,但是执行计划经常发生变化,那么怎么弄呢?

我们知道10g开始oracle 提供了sql profile功能,我们可以利用该功能来进行处理。

由于涉及到客户信息,不便透露,所以我这里用创建一个测试表来进行简单的实验:


SQL> conn roger/roger
Connected.

SQL> DROP TABLE t1;
TABLE dropped.

SQL> ALTER system FLUSH shared_pool;
System altered.

SQL> ALTER system FLUSH buffer_cache;
System altered.

SQL> CREATE TABLE t1 AS SELECT * FROM dba_objects;
TABLE created.

SQL> SELECT COUNT(1) FROM t1;

  COUNT(1)
----------
     51033

SQL> UPDATE t1 SET object_id=2000 WHERE object_id >30000;
21580 ROWS updated.

SQL> commit;
Commit complete.

SQL> analyze TABLE t1 compute statistics FOR TABLE FOR ALL indexes FOR ALL indexed COLUMNS;
TABLE analyzed.

SQL> SET autot traceonly EXP

SQL> ALTER system FLUSH shared_pool;
System altered.

SQL> ALTER system FLUSH buffer_cache;
System altered.

SQL> SELECT owner,object_name FROM t1 WHERE object_id=1000;       

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 190799060

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    32 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    32 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

SQL> SELECT owner,object_name FROM t1 WHERE object_id=2000;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 21774 |   680K|   198   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T1   | 21774 |   680K|   198   (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   1 - FILTER("OBJECT_ID"=2000)

我这里的目的就是需要在执行第2个sql时,其执行计划也走index,怎么办呢?这里我想到的办法就是利用sql profile来进行偷梁换柱,首先我们需要查到sql语句对应的sql_id。

SQL> SELECT sql_id,sql_text FROM v$sqlarea WHERE sql_text LIKE '%select owner%';

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------
b2dzpxjnh0dq2
SELECT owner,object_name FROM t1 WHERE object_id=1000
.....省略部分信息

55hpqz51suxax
SELECT owner,object_name FROM t1 WHERE object_id=2000

.....省略部分信息
7 ROWS selected.

获取到sql_id以后,我们用sqlt中的脚本来创建sql profile,注意,因为我们这里是需要用object_id=1000的执行计划来替换object_id=2000的执行计划,所以我们这里需要用到第一个sql_id:

SQL> @coe_xfr_sql_profile.SQL

Parameter 1:
SQL_ID (required)

Enter VALUE FOR 1: b2dzpxjnh0dq2
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      190799060

Parameter 2:
PLAN_HASH_VALUE (required)

Enter VALUE FOR 2: 190799060

VALUES passed TO coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "b2dzpxjnh0dq2"
PLAN_HASH_VALUE: "190799060"

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_b2dzpxjnh0dq2_190799060.SQL
ON TARGET system IN ORDER TO CREATE a custom SQL Profile
WITH plan 190799060 linked TO adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

在当前目前会生成一个如下的文件:

[ora10g@killdb utl]$ ls -ltr coe_x*.sql
-rw-r--r--  1 ora10g oinstall 18248 May  2 08:27 coe_xfr_sql_profile.sql
-rw-r--r--  1 ora10g oinstall  3405 Sep 13 08:00 coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql

我们需要的文件就是coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql,该文件的命名格式一目了然,那就是:
coe_xfr_sql_profile+sql_id+plan_hash_value。


我们这些需要偷梁换柱,简单一点讲就是用第一个sqlid的东西进行替换,变成第2个sql_id的,那么需要修改哪些东西呢?

我们这里把上面脚本coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql中的一些内容进行替换,替换如下部分即可:

1. 替换sql_id
  b2dzpxjnh0dq2 替换成 55hpqz51suxax

2. 将force_match改成true(默认是false)
改变以后,我们随便创建一个col_xfr_sql_profile_55hpqz51suxax.sql文件,内容如下:

SPO coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.log;
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
REM
REM $Header: 215187.1 coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql 11.4.4.4 2012/09/13 carlos.sierra $
REM
REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql
REM
REM DESCRIPTION
REM   This script is generated by coe_xfr_sql_profile.sql
REM   It contains the SQL*Plus commands to create a custom
REM   SQL Profile for SQL_ID b2dzpxjnh0dq2 based on plan hash
REM   value 190799060.
REM   The custom SQL Profile to be created by this script
REM   will affect plans for SQL commands with signature
REM   matching the one for SQL Text below.
REM   Review SQL Text and adjust accordingly.
REM
REM PARAMETERS
REM   None.
REM
REM EXAMPLE
REM   SQL> START coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql;
REM
REM NOTES
REM   1. Should be run as SYSTEM or SYSDBA.
REM   2. User must have CREATE ANY SQL PROFILE privilege.
REM   3. SOURCE and TARGET systems can be the same or similar.
REM   4. To drop this custom SQL Profile after it has been created:
REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_b2dzpxjnh0dq2_190799060');
REM   5. Be aware that using DBMS_SQLTUNE requires a license
REM      for the Oracle Tuning Pack.
REM   6. If you modified a SQL putting Hints in order to produce a desired
REM      Plan, you can remove the artifical Hints from SQL Text pieces below.
REM      By doing so you can create a custom SQL Profile for the original
REM      SQL but with the Plan captured from the modified SQL (with Hints).
REM
WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM
VAR signature NUMBER;
VAR signaturef NUMBER;
REM
DECLARE
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
PROCEDURE wa (p_line IN VARCHAR2) IS
BEGIN
DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
END wa;
BEGIN
DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
-- SQL Text pieces below do not have to be of same length.
-- So if you edit SQL Text (i.e. removing temporary Hints),
-- there is no need to edit or re-align unmodified pieces.
wa(q'[select owner,object_name from t1 where object_id=1000 ]');
DBMS_LOB.CLOSE(sql_txt);
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.5')]',
q'[OPT_PARAM('_optim_peek_user_binds' 'false')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
:signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'coe_55hpqz51suxax_190799060',
description => 'coe 55hpqz51suxax 190799060 '||:signature||' '||:signaturef||'',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
DBMS_LOB.FREETEMPORARY(sql_txt);
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
PRINT signature
PRINT signaturef
PRO
PRO ... manual custom SQL Profile has been created
PRO
SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";
SPO OFF;
PRO
PRO COE_XFR_SQL_PROFILE_55hpqz51suxax_190799060 completed
然后我们执行col_xfr_sql_profile_55hpqz51suxax.sql 脚本即可,如下:


SQL>@col_xfr_sql_profile_55hpqz51suxax.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql 11.4.4.4 2012/09/13 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM   carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM   coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM   This script is generated by coe_xfr_sql_profile.sql
SQL>REM   It contains the SQL*Plus commands to create a custom
SQL>REM   SQL Profile for SQL_ID b2dzpxjnh0dq2 based on plan hash
SQL>REM   value 190799060.
SQL>REM   The custom SQL Profile to be created by this script
SQL>REM   will affect plans for SQL commands with signature
SQL>REM   matching the one for SQL Text below.
SQL>REM   Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM   None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM   SQL> START coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql;
SQL>REM
SQL>REM NOTES
SQL>REM   1. Should be run as SYSTEM or SYSDBA.
SQL>REM   2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM   3. SOURCE and TARGET systems can be the same or similar.
SQL>REM   4. To drop this custom SQL Profile after it has been created:
SQL>REM  EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_b2dzpxjnh0dq2_190799060');
SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM  for the Oracle Tuning Pack.
SQL>REM   6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM  Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM  By doing so you can create a custom SQL Profile for the original
SQL>REM  SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>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 owner,object_name from t1 where object_id=1000 ]');
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('10.2.0.5')]',
20  q'[OPT_PARAM('_optim_peek_user_binds' 'false')]',
21  q'[ALL_ROWS]',
22  q'[OUTLINE_LEAF(@"SEL$1")]',
23  q'[INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))]',
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_55hpqz51suxax_190799060',
31  description => 'coe 55hpqz51suxax 190799060 '||:signature||' '||:signaturef||'',
32  category    => 'DEFAULT',
33  validate    => TRUE,
34  replace     => TRUE,
35  force_match => TRUE /* 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.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
-Oracleoracleplus.net--------------------
11682228169837078685


           SIGNATUREF
---------------------
  7649568398284820358


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_55hpqz51suxax_190799060 completed

最后我们来看看是否达到我们需要的效果了,如下:

SQL> SET LINES 160
SQL> SET autot traceonly
SQL> SELECT owner,object_name FROM t1 WHERE object_id=2000;

21581 ROWS selected.


Execution Plan
----------------------------------------------------------
Plan hash VALUE: 190799060

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 21699 |   741K|   519   (0)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        | 21699 |   741K|   519   (0)| 00:00:07 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_ID | 21699 |       |    52   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=2000)

Note
-----
   - SQL profile "coe_55hpqz51suxax_190799060" used FOR this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3312  consistent gets
         13  physical reads
          0  redo SIZE
     815439  bytes sent via SQL*Net TO client
      16218  bytes received via SQL*Net FROM client
       1440  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
      21581  ROWS processed

我们可以看到,我们的目的已经达到了。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle使用sql profile处理未绑定变量的SQL的执行计划

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1393.html

Oracle研究中心

关键词:

Oracle sql profile

Oracle改变未绑定变量的SQL的执行计划