coe_xfr_sql_profile.sql和coe_load_sql_profile.sql

①、 coe_load_sql_profile.sql

可以使用coe_load_sql_profile.sql脚本直接固定执行计划，该脚本也可以实现直接把sqlprofile直接迁移到其它库中。

很多DBA习惯于使用coe_xfr_sql_profile.sql脚本来固定SQL执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间SQL执行计划的固定。最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。

需要注意的是，该脚本不能以SYS用户执行，否则会报如下的错误：

DECLARE

ERROR at line 1:

ORA-19381: cannot create staging table in SYS schema

ORA-06512: at "SYS.DBMS_SMB", line 313

ORA-06512: at "SYS.DBMS_SQLTUNE", line 6306

ORA-06512: at line 64

示例如下：

1.建立测试表和数据

SYS@dlhr> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

LHR@dlhr> create table scott.test as select * from dba_objects;

Table created.

LHR@dlhr> create index scott.idx_test_01 on scott.test(object_id);

Index created.

LHR@dlhr> exec dbms_stats.gather_table_stats('scott','test',cascade=>true);

PL/SQL procedure successfully completed.

LHR@dlhr> update scott.test set object_id=10 where object_id>10;

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> select OBJECT_ID ,count(1) from scott.test group by OBJECT_ID;

OBJECT_ID COUNT(1)

---------- ----------

6 1

7 1

5 1

8 1

3 1

2 1

10 87076

4 1

9 1

9 rows selected.

2.执行查询语句

执行原有的查询语句，查看执行计划发现走索引，实际上这时表中大部分行的OBJECT_ID都已经被更新为10，所以走索引是不合理的。

LHR@dlhr> set autot traceonly explain stat

LHR@dlhr>

LHR@dlhr> select * from scott.test where object_id=10;

87076 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3384190782

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 98 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 1 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=10)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

13060 consistent gets

0 physical reads

0 redo size

9855485 bytes sent via SQL*Net to client

64375 bytes received via SQL*Net from client

5807 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

87076 rows processed

LHR@dlhr> select /*+ full(test)*/* from scott.test where object_id=10;

87076 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 217508114

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 98 | 351 (2)| 00:00:05 |

|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 351 (2)| 00:00:05 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=10)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

6973 consistent gets

0 physical reads

0 redo size

4159482 bytes sent via SQL*Net to client

64375 bytes received via SQL*Net from client

5807 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

87076 rows processed

3.查询上面两个语句的SQL_ID、PLAN_HASH_VALUE

LHR@dlhr> set autot off

LHR@dlhr>

LHR@dlhr> col sql_text format a100

LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select * from scott.test where object_id=10%';

SQL_TEXT SQL_ID PLAN_HASH_VALUE

---------------------------------------------------------------------------------------------------- ------------- ---------------

select * from scott.test where object_id=10 cpk9jsg2qt52r 2317948335

LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%';

SQL_TEXT SQL_ID PLAN_HASH_VALUE

---------------------------------------------------------------------------------------------------- ------------- ---------------

select /*+ full(test)*/* from scott.test where object_id=10 06c2mucgn6t5g 1357081020

4.把coe_load_sql_profile.sql放在$ORACLE_HOME/rdbms/admin下，或者放在/tmp下都可以。

5.使用coe_load_sql_profile.sql脚本

[ZHLHRSPMDB2:oracle]:/oracle>cd /tmp

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 09:15:14 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

LHR@dlhr > @/home/oracle/coe_load_sql_profile.sql

Parameter 1:

ORIGINAL_SQL_ID (required)

Enter value for 1: cpk9jsg2qt52r

Parameter 2:

MODIFIED_SQL_ID (required)

Enter value for 2: 06c2mucgn6t5g

PLAN_HASH_VALUE AVG_ET_SECS

-------------------- --------------------

1357081020 .058

Parameter 3:

PLAN_HASH_VALUE (required)

Enter value for 3: 1357081020

Values passed to coe_load_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ORIGINAL_SQL_ID: "cpk9jsg2qt52r"

MODIFIED_SQL_ID: "06c2mucgn6t5g"

PLAN_HASH_VALUE: "1357081020"

SQL>BEGIN

2 IF :sql_text IS NULL THEN

3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_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 modified SQL_ID &&modified_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>

SQL>SET ECHO OFF;

0001 BEGIN_OUTLINE_DATA

0002 IGNORE_OPTIM_EMBEDDED_HINTS

0003 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

0004 DB_VERSION('11.2.0.3')

0005 ALL_ROWS

0006 OUTLINE_LEAF(@"SEL$1")

0007 FULL(@"SEL$1" "TEST"@"SEL$1")

0008 END_OUTLINE_DATA

dropping staging table "STGTAB_SQLPROF_CPK9JSG2QT52R"

staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" did not exist

creating staging table "STGTAB_SQLPROF_CPK9JSG2QT52R"

packaging new sql profile into staging table "STGTAB_SQLPROF_CPK9JSG2QT52R"

PROFILE_NAME

------------------------------

CPK9JSG2QT52R_1357081020

SQL>REM

SQL>REM SQL Profile

SQL>REM ~~~~~~~~~~~

SQL>REM

SQL>SELECT signature, name, category, type, status

2 FROM dba_sql_profiles WHERE name = :name;

SIGNATURE NAME CATEGORY TYPE STATUS

-------------------- ------------------------------ ------------------------------ ------- --------

10910590721604799112 CPK9JSG2QT52R_1357081020 DEFAULT MANUAL ENABLED

SQL>SELECT description

2 FROM dba_sql_profiles WHERE name = :name;

DESCRIPTION

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ORIGINAL:CPK9JSG2QT52R MODIFIED:06C2MUCGN6T5G PHV:1357081020 SIGNATURE:10910590721604799112 CREATED BY COE_LOAD_SQL_PROFILE.SQL

SQL>SET ECHO OFF;

****************************************************************************

* Enter LHR password to export staging table STGTAB_SQLPROF_cpk9jsg2qt52r

****************************************************************************

Export: Release 11.2.0.3.0 - Production on Tue Sep 12 10:39:16 2017

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Note: grants on tables/views/sequences/roles will not be exported

Note: indexes on tables will not be exported

Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...

. . exporting table STGTAB_SQLPROF_CPK9JSG2QT52R 1 rows exported

Export terminated successfully without warnings.

If you need to implement this Custom SQL Profile on a similar system,

import and unpack using these commands:

imp LHR file=STGTAB_SQLPROF_cpk9jsg2qt52r.dmp tables=STGTAB_SQLPROF_cpk9jsg2qt52r ignore=Y

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (

profile_name => 'CPK9JSG2QT52R_1357081020',

replace => TRUE,

staging_table_name => 'STGTAB_SQLPROF_cpk9jsg2qt52r',

staging_schema_owner => 'LHR' );

END;

adding: coe_load_sql_profile_cpk9jsg2qt52r.log (deflated 76%)

adding: STGTAB_SQLPROF_cpk9jsg2qt52r.dmp (deflated 89%)

adding: coe_load_sql_profile.log (deflated 62%)

deleting: coe_load_sql_profile.log

coe_load_sql_profile completed.

SQL>

6.查看产生的sql profile，此时原语句在不加hint的情况下也走全表扫了
select * from dba_sql_profiles;

SQL>set line 9999

SQL> SELECT b.name,to_char(d.sql_text) sql_text, extractvalue(value(h),'.') as hints

2 FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A,

SYS.SQLOBJ$ B,

4 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),

5 '/outline_data/hint'))) h

where a.signature = b.signature

7 and a.category = b.category

8 and a.obj_type = b.obj_type

9 and a.plan_id = b.plan_id

10 and a.signature=d.signature

11 and D.name = 'CPK9JSG2QT52R_1357081020';

NAME SQL_TEXT HINTS

------------------------------ -------------------------------------------------- --------------------------------------------------

CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 BEGIN_OUTLINE_DATA

CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 IGNORE_OPTIM_EMBEDDED_HINTS

CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 DB_VERSION('11.2.0.3')

CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 ALL_ROWS

CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 OUTLINE_LEAF(@"SEL$1")

CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 FULL(@"SEL$1" "TEST"@"SEL$1")

CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 END_OUTLINE_DATA

7.验证SQL Profile是否生效

SYS@dlhr> set autot traceonly explain stat

SYS@dlhr> select * from scott.test where object_id=10;

87076 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 217508114

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 98 | 351 (2)| 00:00:05 |

|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 351 (2)| 00:00:05 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=10)

Note

-----

- SQL profile "CPK9JSG2QT52R_1357081020" used for this statement

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

6973 consistent gets

0 physical reads

0 redo size

4159482 bytes sent via SQL*Net to client

64375 bytes received via SQL*Net from client

5807 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

87076 rows processed

sql profile改变hint的执行计划以及coe脚本实现sql id中无PLAN HASH VALUE的sql profile生成

发表于 2015 年 06 月 01 日由惜分飞

在sql profile中使用的过程中,有以下几个问题,这里通过测试确认了几个问题:
1.coe_xfr_sql_profile可以执行sql_id中无PLAN HASH VALUE的执行计划
2.在sql使用过程中,sql profile是否会覆盖hint,通过测试证明sqlprofile可以覆盖hint的执行计划
3.coe_load_sql_profile可以使用于通过修改hint(改变sql id,然后通过指定两次不同的sql id实现sql profile固定hint的sql的执行计划)

测试sqlprofile会影响hint

SQL> create table t_xifenfei as select object_id,object_name from user_objects;                   Table created.                   SQL> create index idx_t_xifenfei_id on t_xifenfei(OBJECT_ID);                   Index created.                   SQL> create index idx_t_xifenfei2_id on t_xifenfei(OBJECT_ID,1);                   Index created.                   ---使用hint等方式确定三种方式执行计划         SQL> SET LINES 150         SQL> SET AUTOT  ON         SQL> SET PAGES 150         SQL> select * from t_xifenfei t where OBJECT_ID=10;    <---默认使用IDX_T_XIFENFEI_ID index                   no rows selected                             Execution Plan         ----------------------------------------------------------         Plan hash value: 308895000                   -------------------------------------------------------------------------------------------------         | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |         -------------------------------------------------------------------------------------------------         |   0 | SELECT STATEMENT            |                   |     1 |    79 |     1   (0)| 00:00:01 |         |   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI        |     1 |    79 |     1   (0)| 00:00:01 |         |*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI_ID |     1 |       |     1   (0)| 00:00:01 |         -------------------------------------------------------------------------------------------------                   Predicate Information (identified by operation id):         ---------------------------------------------------                      2 - access("OBJECT_ID"=10)                   Note         -----            - dynamic sampling used for this statement (level=2)                             Statistics         ----------------------------------------------------------                   6  recursive calls                   0  db block gets                  13  consistent gets                   1  physical reads                   0  redo size                 410  bytes sent via SQL*Net to client                 513  bytes received via SQL*Net from client                   1  SQL*Net roundtrips to/from client                   0  sorts (memory)                   0  sorts (disk)                   0  rows processed                   SQL> SET AUTOT OFF                   SQL> SET LINES 150         SQL> SET AUTOT  ON         SQL> SET PAGES 150         SQL> select /*+ INDEX(T idx_t_xifenfei2_id)*/* from t_xifenfei t where OBJECT_ID=10;           <---指定使用idx_t_xifenfei2_id index                   no rows selected                             Execution Plan         ----------------------------------------------------------         Plan hash value: 2143066642                   --------------------------------------------------------------------------------------------------         | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |         --------------------------------------------------------------------------------------------------         |   0 | SELECT STATEMENT            |                    |     1 |    79 |     1   (0)| 00:00:01 |         |   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI         |     1 |    79 |     1   (0)| 00:00:01 |         |*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI2_ID |     1 |       |     1   (0)| 00:00:01 |         --------------------------------------------------------------------------------------------------                   Predicate Information (identified by operation id):         ---------------------------------------------------                      2 - access("OBJECT_ID"=10)                   Note         -----            - dynamic sampling used for this statement (level=2)                             Statistics         ----------------------------------------------------------                   4  recursive calls                   0  db block gets                  11  consistent gets                   1  physical reads                   0  redo size                 410  bytes sent via SQL*Net to client                 513  bytes received via SQL*Net from client                   1  SQL*Net roundtrips to/from client                   0  sorts (memory)                   0  sorts (disk)                   0  rows processed                   SQL> SET AUTOT OFF                   SQL> SET LINES 150         SQL> SET AUTOT  ON         SQL> SET PAGES 150         SQL> select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xifenfei t where OBJECT_ID=10;           <---指定使用idx_t_xifenfei1_id index                   no rows selected                             Execution Plan         ----------------------------------------------------------         Plan hash value: 308895000                   -------------------------------------------------------------------------------------------------         | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |         -------------------------------------------------------------------------------------------------         |   0 | SELECT STATEMENT            |                   |     1 |    79 |     1   (0)| 00:00:01 |         |   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI        |     1 |    79 |     1   (0)| 00:00:01 |         |*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI_ID |     1 |       |     1   (0)| 00:00:01 |         -------------------------------------------------------------------------------------------------                   Predicate Information (identified by operation id):         ---------------------------------------------------                      2 - access("OBJECT_ID"=10)                   Note         -----            - dynamic sampling used for this statement (level=2)                             Statistics         ----------------------------------------------------------                   4  recursive calls                   0  db block gets                  11  consistent gets                   0  physical reads                   0  redo size                 410  bytes sent via SQL*Net to client                 513  bytes received via SQL*Net from client                   1  SQL*Net roundtrips to/from client                   0  sorts (memory)                   0  sorts (disk)                   0  rows processed                   SQL> SET AUTOT OFF                   --查询三种情况下sql_id         SQL> col SQL_TEXT for a50         SQL> SELECT SQL_TEXT,SQL_ID FROM V$SQL WHERE sql_text like 'select%OBJECT_ID=10';                   SQL_TEXT                                           SQL_ID         -------------------------------------------------- -------------         select /*+ INDEX(T idx_t_xifenfei2_id)*/* from t_x 5291sfrd2p35y         ifenfei t where OBJECT_ID=10                   select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xi 143q33ff4f06w         fenfei t where OBJECT_ID=10                   select * from t_xifenfei t where OBJECT_ID=10      b5zuac0zqm9nw                   --使用sqlprofile固定其他两个未使用index IDX_T_XIFENFEI2_ID的sql使用该索引         SQL> DECLARE           2   SQL_FTEXT CLOB;           3   BEGIN           4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'b5zuac0zqm9nw';           5   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(           6     SQL_TEXT => SQL_FTEXT,           7     PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")'),           8     NAME => 'PROFILE_b5zuac0zqm9nw',           9     REPLACE => TRUE,           10    FORCE_MATCH => TRUE           11  );          12   END;          13   /                   PL/SQL procedure successfully completed.                   SQL>         SQL> DECLARE           2    SQL_FTEXT CLOB;           3   BEGIN           4   SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '143q33ff4f06w';           5   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(           6     SQL_TEXT => SQL_FTEXT,           7     PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")'),           8      NAME => 'PROFILE_143q33ff4f06w',           9     REPLACE => TRUE,          10     FORCE_MATCH => TRUE          11   );          12   END;         13   /                   PL/SQL procedure successfully completed.                   --验证查询效果         SQL> SET LINES 150         SQL> SET AUTOT  ON         SQL> SET PAGES 150         SQL> select * from t_xifenfei t where OBJECT_ID=10;                   no rows selected                             Execution Plan         ----------------------------------------------------------         Plan hash value: 2143066642                   --------------------------------------------------------------------------------------------------         | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |         --------------------------------------------------------------------------------------------------         |   0 | SELECT STATEMENT            |                    |     1 |    79 |     1   (0)| 00:00:01 |         |   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI         |     1 |    79 |     1   (0)| 00:00:01 |         |*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI2_ID |     1 |       |     1   (0)| 00:00:01 |         --------------------------------------------------------------------------------------------------                   Predicate Information (identified by operation id):         ---------------------------------------------------                      2 - access("OBJECT_ID"=10)                   Note         -----            - dynamic sampling used for this statement (level=2)            - SQL profile "PROFILE_b5zuac0zqm9nw" used for this statement  <--使用sql profile                             Statistics         ----------------------------------------------------------                  37  recursive calls                   0  db block gets                  23  consistent gets                   1  physical reads                   0  redo size                 410  bytes sent via SQL*Net to client                 513  bytes received via SQL*Net from client                   1  SQL*Net roundtrips to/from client                   1  sorts (memory)                   0  sorts (disk)                   0  rows processed                   SQL> select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xifenfei t where OBJECT_ID=10;                   no rows selected                             Execution Plan         ----------------------------------------------------------         Plan hash value: 2143066642                   --------------------------------------------------------------------------------------------------         | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |         --------------------------------------------------------------------------------------------------         |   0 | SELECT STATEMENT            |                    |     1 |    79 |     1   (0)| 00:00:01 |         |   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI         |     1 |    79 |     1   (0)| 00:00:01 |         |*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI2_ID |     1 |       |     1   (0)| 00:00:01 |         --------------------------------------------------------------------------------------------------                   Predicate Information (identified by operation id):         ---------------------------------------------------                      2 - access("OBJECT_ID"=10)                   Note         -----            - dynamic sampling used for this statement (level=2)            - SQL profile "PROFILE_143q33ff4f06w" used for this statement                   <--使用sql profile,hint未被正常使用，证明sql profile影响hint，使得sql使用sql profile而不hint                             Statistics         ----------------------------------------------------------                  10  recursive calls                   0  db block gets                  16  consistent gets                   1  physical reads                   0  redo size                 410  bytes sent via SQL*Net to client                 513  bytes received via SQL*Net from client                   1  SQL*Net roundtrips to/from client                   0  sorts (memory)                   0  sorts (disk)                   0  rows processed         

这里可以知道,在有sqlprofile的情况下,可以影响以前的hint提示,使得hint无效，继续使用sql profile,感谢北京–weejar的试验证明

使用coe_load_sql_profile方式指定修改sql后的执行计划

SQL> @/tmp/coe_load_sql_profile.sql                   Parameter 1:         ORIGINAL_SQL_ID (required)                   Enter value for 1: b5zuac0zqm9nw                   Parameter 2:         MODIFIED_SQL_ID (required)                   Enter value for 2: 5291sfrd2p35y                                  PLAN_HASH_VALUE          AVG_ET_SECS         -------------------- --------------------                   2143066642                 .004                   Parameter 3:         PLAN_HASH_VALUE (required)                   Enter value for 3: 2143066642                   Values passed to coe_load_sql_profile:         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~         ORIGINAL_SQL_ID: "b5zuac0zqm9nw"         MODIFIED_SQL_ID: "5291sfrd2p35y"         PLAN_HASH_VALUE: "2143066642"                   SQL>BEGIN           2    IF :sql_text IS NULL THEN           3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_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 modified SQL_ID &&modified_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>         SQL>SET ECHO OFF;         0001 BEGIN_OUTLINE_DATA         0002 IGNORE_OPTIM_EMBEDDED_HINTS         0003 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')         0004 DB_VERSION('11.2.0.4')         0005 ALL_ROWS         0006 OUTLINE_LEAF(@"SEL$1")         0007 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")         0008 END_OUTLINE_DATA         dropping staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW"         creating staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW"         packaging new sql profile into staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW"                   PROFILE_NAME         ------------------------------         B5ZUAC0ZQM9NW_2143066642         SQL>REM         SQL>REM SQL Profile         SQL>REM ~~~~~~~~~~~         SQL>REM         SQL>SELECT signature, name, category, type, status           2    FROM dba_sql_profiles WHERE name = :name;                              SIGNATURE NAME                           CATEGORY                       TYPE    STATUS         -------------------- ------------------------------ ------------------------------ ------- --------          6715790053022671751 B5ZUAC0ZQM9NW_2143066642       DEFAULT                        MANUAL  ENABLED         SQL>SET ECHO OFF;                   ****************************************************************************         * Enter CHF password to export staging table STGTAB_SQLPROF_b5zuac0zqm9nw         ****************************************************************************                   Export: Release 11.2.0.4.0 - Production on Mon Jun 1 00:10:11 2015                   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.                   Password:                   Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production         With the Partitioning, OLAP, Data Mining and Real Application Testing options         Export done in US7ASCII character set and AL16UTF16 NCHAR character set         server uses ZHS16GBK character set (possible charset conversion)         Note: grants on tables/views/sequences/roles will not be exported         Note: indexes on tables will not be exported         Note: constraints on tables will not be exported                   About to export specified tables via Conventional Path ...         . . exporting table   STGTAB_SQLPROF_B5ZUAC0ZQM9NW          1 rows exported         Export terminated successfully without warnings.                             If you need to implement this Custom SQL Profile on a similar system,         import and unpack using these commands:                   imp CHF file=STGTAB_SQLPROF_b5zuac0zqm9nw.dmp tables=STGTAB_SQLPROF_b5zuac0zqm9nw ignore=Y                   BEGIN         DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (         profile_name => 'B5ZUAC0ZQM9NW_2143066642',         replace => TRUE,         staging_table_name => 'STGTAB_SQLPROF_b5zuac0zqm9nw',         staging_schema_owner => 'CHF' );         END;         /                   updating: coe_load_sql_profile_b5zuac0zqm9nw.log (deflated 76%)         updating: STGTAB_SQLPROF_b5zuac0zqm9nw.dmp (deflated 89%)           adding: coe_load_sql_profile.log (deflated 62%)                   deleting: coe_load_sql_profile.log                             coe_load_sql_profile completed.                   SQL>SET LINES 150         SQL>SET AUTOT  ON         SQL>SET PAGES 150         select * from t_xifenfei t where OBJECT_ID=10;SQL>                   no rows selected                             Execution Plan         ----------------------------------------------------------         Plan hash value: 2143066642                   --------------------------------------------------------------------------------------------------         | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |         --------------------------------------------------------------------------------------------------         |   0 | SELECT STATEMENT            |                    |     6 |   474 |     3   (0)| 00:00:01 |         |   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI         |     6 |   474 |     3   (0)| 00:00:01 |         |*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI2_ID |     2 |       |     2   (0)| 00:00:01 |         --------------------------------------------------------------------------------------------------                   Predicate Information (identified by operation id):         ---------------------------------------------------                      2 - access("OBJECT_ID"=10)                   Note         -----            - SQL profile "B5ZUAC0ZQM9NW_2143066642" used for this statement           <------sql直接使用coe_load_sql_profile固定执行计划成功                             Statistics         ----------------------------------------------------------                   7  recursive calls                   0  db block gets                   7  consistent gets                   1  physical reads                   0  redo size                 410  bytes sent via SQL*Net to client                 513  bytes received via SQL*Net from client                   1  SQL*Net roundtrips to/from client                   0  sorts (memory)                   0  sorts (disk)                   0  rows processed         

使用coe_load_sql_profile也可以sql_id中没有PLAN HASH VALUE的执行计划,另外还可以实现直接把sqlprofile直接迁移到其他库中

coe_xfr_sql_profile固定没有sql_id没有直接PLAN HASH VALUE的执行计划

SQL> @/tmp/coe_xfr_sql_profile                   Parameter 1:         SQL_ID (required)                   Enter value for 1: b5zuac0zqm9nw                             PLAN_HASH_VALUE AVG_ET_SECS         --------------- -----------               308895000        .005                   Parameter 2:         PLAN_HASH_VALUE (required)                   Enter value for 2: 2143066642     <---该PLAN_HASH_VALUE不存在该sql_id对应的PLAN_HASH_VALUE中                   Values passed to coe_xfr_sql_profile:         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~         SQL_ID         : "b5zuac0zqm9nw"         PLAN_HASH_VALUE: "2143066642"                   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_b5zuac0zqm9nw_2143066642.sql         on TARGET system in order to create a custom SQL Profile         with plan 2143066642 linked to adjusted sql_text.                             COE_XFR_SQL_PROFILE completed.         SQL>@coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql         SQL>REM         SQL>REM $Header: 215187.1 coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql 11.4.4.4 2015/06/01 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_b5zuac0zqm9nw_2143066642.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 b5zuac0zqm9nw based on plan hash         SQL>REM   value 2143066642.         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_b5zuac0zqm9nw_2143066642.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_b5zuac0zqm9nw_2143066642');         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 * from t_xifenfei t where OBJECT_ID=10]');          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.4')]',          20  q'[DB_VERSION('11.2.0.4')]',          21  q'[ALL_ROWS]',          22  q'[OUTLINE_LEAF(@"SEL$1")]',          23  q'[INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_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_b5zuac0zqm9nw_2143066642',          31  description => 'coe b5zuac0zqm9nw 2143066642 '||: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.                   SQL>WHENEVER SQLERROR CONTINUE         SQL>SET ECHO OFF;                               SIGNATURE         ---------------------           6715790053022671751                                        SIGNATUREF         ---------------------            445801536248906164                             ... manual custom SQL Profile has been created                             COE_XFR_SQL_PROFILE_b5zuac0zqm9nw_2143066642 completed         SQL>set autot on         SQL>set lines 150         SQL>set pages 150         SQL>select * from t_xifenfei t where OBJECT_ID=10;                   no rows selected                             Execution Plan         ----------------------------------------------------------         Plan hash value: 2143066642                   --------------------------------------------------------------------------------------------------         | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |         --------------------------------------------------------------------------------------------------         |   0 | SELECT STATEMENT            |                    |     6 |   474 |     3   (0)| 00:00:01 |         |   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI         |     6 |   474 |     3   (0)| 00:00:01 |         |*  2 |   INDEX RANGE SCAN          | IDX_T_XIFENFEI2_ID |     2 |       |     2   (0)| 00:00:01 |         --------------------------------------------------------------------------------------------------                   Predicate Information (identified by operation id):         ---------------------------------------------------                      2 - access("OBJECT_ID"=10)                   Note         -----            - SQL profile "coe_b5zuac0zqm9nw_2143066642" used for this statement                             Statistics         ----------------------------------------------------------                   7  recursive calls                   0  db block gets                   7  consistent gets                   1  physical reads                   0  redo size                 410  bytes sent via SQL*Net to client                 513  bytes received via SQL*Net from client                   1  SQL*Net roundtrips to/from client                   0  sorts (memory)                   0  sorts (disk)                   0  rows processed                   SQL>         

通过验证，证明在sql_id中没有对应的PLAN HASH VALUE之时，也可以通过coe_xfr_sql_profile指定PLAN HASH VALUE来固定某个sql_id的执行计划.

以前写过相关关于sql profile的文章: sql profile 使用, 执行计划改变导致数据库负载过高

sql profile 使用
执行计划中常见index访问方式
创建包含null值index
select max(id),min(id) from table优化
hint指定index的深入理解
Oracle直方图理解与实验
_optimizer_null_aware_antijoin和not in效率
11g中 connect by 语句执行计划改变
含is null sql语句优化
Bind Variable Peeking 测试
ORACLE 12C TOP N SQL实现分页功能
10g新增列方式指定HINT

系统迁移导致SQL性能下降，神马原因？

2016-08-01 老虎刘老虎刘谈SQL优化

今天病休，还好有存货。

宝岛某客户，系统迁移后（主机迁移，数据库版本相同，都是11.2.0.3），某个SQL性能严重下降，执行时间由原系统的的0.001秒，变成了131秒多，严重影响了业务的正常使用：

（注：其中#1执行计划是在使用了sql profile固定后生成的，新系统开始只有一个#2的执行计划）

SQL代码如下：

SELECT *

FROM MB_PN_MESSAGE

WHERE (UDID = : 1 OR CUST_ID = : 2) AND

(MB_PN_MESSAGE.APP IS NULL OR MB_PN_MESSAGE.APP = 'MB')

AND MESSAGE_DATE <= SYSDATE

ORDER BY MESSAGE_DATE DESC ,MESSAGE_NUMBER DESC;

迁移后新系统的执行计划为：

根据谓词条件，使用message_date字段上的索引明显是非常差的选择。

原系统的执行计划是：

原系统SQL执行计划使用了选择性好的UDID和CUST_ID两个字段上独立索引，做Bitmap OR操作，这个计划才是最优的。

发现这个区别后，我们马上使用coe_xfr_sql_profile.sql脚本，将原系统的执行计划导出（生成sql脚本），然后在新的系统上执行该SQL，这样就通过sql profile，将新系统的执行计划与原系统保持了一致。快速恢复系统运行正常后，接下来我们就可以从容地对这个问题的原因进行分析了。

因为MESSAGE_DATE字段创建的索引为降序索引，DDL如下：

CREATE INDEX "INHBMGR"."MB_PN_MESSAGE_MESSAGE_DATE" ON "INHBMGR"."MB_PN_MESSAGE" ("MESSAGE_DATE" DESC)

我们团队的资深专家很快就把问题怀疑到bug上，经过一番努力搜索，发现一个疑似的bug：

Bug 11072246 : NON OPTIMAL INDEX IS USED WHEN INDEX COLUMN IS IN DESC ORDER

Bug 11072246 - Wrong Cardinality estimations for columns with DESC indexes (Doc ID 11072246.8)

bug的描述与现场的情况基本一致，但是，大家心中又有一个疑问，既然是bug，为什么在原系统上没有触发？而再看bug的修复情况，在11.2.0.3版本的某个patch set已经得到修复，难道说两个系统的patch set不一致？

这个时候，我做了一个test case，在11.2.0.3 （没有任何patch）和12.1.0.1上分别执行SQL，对比执行计划后，基本可以确定为bug：

在客户现场仔细对比原系统和新系统的patch信息后，确实是新系统的patch比原系统要少，而缺少的patch恰恰就导致了SQL触发了bug。我们在测试环境也证明了这一点：11.2.0.3.7打11072246 patch前后的对比与现场情况完全一致。

总结：

系统迁移，不光数据库的大版本要相同，patch也要保持一致。

coe_xfr_sql_profile.sql脚本最适合在这种情况下使用。

生产系统SQL执行计划突然变差怎么办？

2016-07-19 老虎刘老虎刘谈SQL优化

由于各种各样的原因，DBA有时会遇到SQL执行计划突然变差的情况，导致CPU和IO资源消耗过高，整个系统性能下降。

很多人遇到这种情况的通常做法是，立即收集表的统计信息，让优化器重新对SQL做硬解析，期待能够恢复原来的执行计划。

但是，这样做有一些问题：

1、

如果是大表，收集统计信息的时间会比较长，而且执行计划变差一般伴随着CPU利用率高和IO繁忙，这个时间会更长；

2、

有些DBA在收集统计信息时，没有使用no_invalidate=>false选项，即使收集了统计信息，执行计划却没有立即改变。因为该参数的默认值是AUTO_INVALIDATE，优化器会选择5个小时内的某个时间点来对SQL重新做硬解析。因为不了解这个参数，有人还会在收集完统计信息后flush shared_pool来强制对所有SQL做硬解析。

3、

有些SQL执行计划改变是跟统计信息没有关系的，即使重新收集了统计信息，执行计划还是无法恢复正常。

遇到执行计划突然变差，刘老师的建议是：先用SQL profile（10g及以上版本）固定执行计划为原来正常的执行计划，让业务先恢复正常，再慢慢查找原因。

很多DBA习惯于使用coe_xfr_sql_profile.sql脚本来固定sql 执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间sql执行计划的固定。

最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。

具体步骤如下：

1、用DBA权限的用户登录sqlplus （不能是sys用户，可以是system用户）

2、执行脚本 SQL>coe_load_sql_profile.sql

3、输入第一个参数：需要恢复执行计划的sql_id

4、输入第二个参数：再输入一次相同的sql_id

5、此时会显示该sql_id对应的几个执行计划的plan_hash_value，第三个参数需要你选择最优执行计划对应的那个plan_hash_value

6、最后一步，输入连接sqlplus用户的密码，导出sql profile信息到一个表。如果不需要导出sql profile信息，最后一步exp操作可以从原脚本中屏蔽（注释掉以HOS exp开头那一行）。

下面是一个具体的实例截图（没有最后做exp导出输入密码的步骤）：

coe_xfr_sql_profile.sql和coe_load_sql_profile.sql

sql profile改变hint的执行计划以及coe脚本实现sql id中无PLAN HASH VALUE的sql profile生成

更多相关文章

随机推荐