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
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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导出输入密码的步骤）：