Monthly Archives: 8 月 2014

性能调优工具05–SQL Profiles

Posted on by
SQL Profiles

  1. SQL Profiles介绍:
    1. SQL Profiles是在10g中被引进的新特性,主要是通过DBMS_SQLTUNE包或者EM来管理,它是AST(Automatic SQL Tuning)过程的一部分;也在10g版本中使用较多,11g中更多使用SPM;
    2. Automatic SQL Tuning:
      1. 因为缺少必要的信息,查询优化器对sql语句可能会产生不准确的评估(比如返回的记录数等),从而导致生成一个较差的执行计划;传统的处理方法是手动添加hints来改变执行计划,但是对于打包的应用程序,无法修改相应的sql语句,所以只能等应用程序提供商来解决这个问题;
      2. Automatic SQL Tuning使用SQL Profile的特性解决了这个问题;Automatic Tuning Optimizer会为每条sql语句创建一个包含辅助信息(主要通过动态采用和部分执行技术收集附加信息)的SQL Profile;查询优化器可以计算出更精确的cardinality/selectivity/cost的评估,从而生成更优的执行计划;
      3. 在产生SQL Profile期间,Automatic Tuning Optimizer也会为了与优化器参数匹配从而执行SQL语句的历史信息,比如把optimizer_mode参数从all_rows修改到first_rows;
    3. SQL Profile:
      1. SQL Profile是存储在数据字典中的一系列的信息(主要是包括在AST过程中针对较差执行计划的一些修正信息),它可以使查询优化器针对某条sql生成更优的执行计划;
      2. SQL Profile并不包含独立的执行计划,只是优化器在选择执行计划时除了参考数据库配置/绑定变量值/优化器统计信息/数据集等信息之外还会参考SQL Profile中的附加信息;
      3. 一段时间后,SQL Profile的内容可能会过期,需要运行AST重新生成SQL Profile;
      4. tips:SQL Profile并不会像Stored Outlines一样固定SQL语句的执行计划;即便拥有相同SQL Profile,由于表的增长或者索引的创建删除,执行计划也会改变;
    4. 如何控制SQL Profile的范围:
      1. SQL Profile的范围由category属性控制,它决定了哪些用户会话可以应用这些profile;
      2. 可以通过dba_sql_profiles字典来查看(SELECT NAME, category FROM dba_sql_profiles);
      3. 默认情况下,所有的profiles都属于DEFAULT分类,表示所有的初始化参数sqltune_category=DEFAULT(这也是默认值)的用户会话都可使用这些profile;
      4. 可以通过修改sqltune_category参数来控制profile使用的范围,从而可以在发布之前进行测试;
    5. 可以使用SQL Profile的语句:
      1. SELECT语句;
      2. UDPATE语句;
      3. INSERT INTO SELECT语句;
      4. DELETE语句;
      5. CTAS语句;
      6. MERGE语句(包含UPDATE或者INSERT操作);
    6. Sql Profiles是对OUTLINES的增强,并且具有自己的特点:
      1. SQL Profiles更容易生成,更改和控制;
      2. SQL Profiles对SQL语句的支持更广泛;
    7. 使用SQL Profiles的目的:
      1. 稳定或者”锁定”SQL的执行计划;
      2. 在不能修改应用程序中SQL的情况下,使SQL按照指定的执行计划运行;
  2. 管理SQL Profiles:
    1. 首先用户需要有CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, ALTER ANY SQL_PROFILE权限;
    2. 从STA优化结果中接受一个Profile:DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name,object_id,name,description,category,task_owner,replace,force_match,profile_type),例子<<如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理>>;
    3. 修改Profile:DBMS_SQLTUNE.ALTER_SQL_PROFILE (name,attribute_name,value);
    4. 删除Profile:DBMS_SQLTUNE.DROP_SQL_PROFILE (name,ignore);
    5. SQL Profile的迁移,例子<<How to Move SQL Profiles from One Database to Another>>;
    6. 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles,这种方法创建的SQL Profiles和Outlines一样,能够稳定SQL的执行计划,例子;
      1. 稳定现有的SQL的执行计划;
      2. 在不能修改SQL的情况下改变并固定SQL的执行计划,即使原来的SQL使用了hints;
———————- 如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理 ———————-
— 1.实验环境;
SQL> SELECT * FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

— 2.创建测试表;
conn / as sysdba
CREATE TABLE t1 AS SELECT object_id, object_name FROM dba_objects WHERE rownum <= 50000;
CREATE TABLE t2 AS SELECT * FROM dba_objects;
CREATE INDEX idx_t2_oid ON t2(object_id);

BEGIN
dbms_stats.gather_table_stats(USER, ‘T1’, cascade => TRUE, method_opt => ‘for all columns size 1’);
dbms_stats.gather_table_stats(USER, ‘T2’, cascade => TRUE, method_opt => ‘for all columns size 1’);
END;
/

— 3.查看SQL的执行计划和统计信息,并选择不同的访问路径;

1.查看SQL的执行计划;
     1.从执行结果发现:这条sql在两个表上都是全表扫描,在T1上因为有like条件,所以很正常;在T2表上应该有其它的访问路径;
     2.在执行计划ID=2那一行,CBO评估表T1 LIKE ‘%T1%’的返回结果为2500行,即T1表总记录的5%(类似于LIKE等谓词的默认选择率为5%);如果采用两个表采用INDEX RANGE SCAN + NESTED LOOPS连接的话,CBO估算的成本会高于FULL TABLE SCAN + HASH JOIN;

SQL> SET AUTOT ON;
SQL> SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

……
26 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1838229974

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |  2500 |    97K|   212   (3)| 00:00:03 |
|*  1 |  HASH JOIN         |      |  2500 |    97K|   212   (3)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 72500 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

Statistics
———————————————————-
304  recursive calls
0  db block gets
974  consistent gets
0  physical reads
0  redo size
1402  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
5  sorts (memory)
0  sorts (disk)
26  rows processed

2.加hints改变sql的访问路径查看成本;
     1.从执行计划发现:CBO评估的成本为5061,远远高于原来的212;
     2.从统计信息发现:加了hint之后的逻辑读只有288,远远低于原来的974;
     3.说明由于ORACLE过高的估计了T1表返回的结果集行数,进而过高的估计了NEST LOOP的成本,导致没有选择最优的执行计划;
     4.tips:也可以使用EXPLAIN PLAN FOR然后SELECT * FROM TABLE(DBMS_XPLAN.display())的方法查看执行计划;
SQL> SET AUTO ON;
SQL> SELECT /*+ use_nl(t1 t2) index(t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

26 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1164754026

——————————————————————————–
———-

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

——————————————————————————–
———-

|   0 | SELECT STATEMENT            |            |  2500 |    97K|  5061   (1)|
00:01:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |

|   2 |   NESTED LOOPS              |            |  2500 |    97K|  5061   (1)|
00:01:01 |

|*  3 |    TABLE ACCESS FULL        | T1         |  2500 | 72500 |    55   (4)|
00:00:01 |

|*  4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |

——————————————————————————–
———-

Predicate Information (identified by operation id):
—————————————————

3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Statistics
———————————————————-
1  recursive calls
0  db block gets
288  consistent gets
0  physical reads
0  redo size
1402  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
26  rows processed

— 4.使用STA调优这条sql;
1.查看这条sql的sql_id为’cy6x06rhrt54x’;
可以从v$sql或者dba_hist_sqltext视图中拿到sql_id;
2.可以使用工具包sqltrpt.sql来调优;
Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name                  : TASK_62
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 07/26/2014 10:45:03
Completed at                      : 07/26/2014 10:45:03
Number of SQL Profile Findings    : 1

——————————————————————————-
Schema Name: SYS

SQL ID     : cy6x06rhrt54x
SQL Text   : SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE
‘%T1%’ AND t1.object_id = t2.object_id

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 49.32%)

——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_62’, replace
=> TRUE);

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original With Adjusted Cost
——————————
Plan hash value: 1838229974

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |    26 |  1040 |   212   (3)| 00:00:03 |
|*  1 |  HASH JOIN         |      |    26 |  1040 |   212   (3)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |    26 |   754 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

2- Using SQL Profile
——————–
Plan hash value: 1164754026

——————————————————————————–
———-
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
——————————————————————————–
———-
|   0 | SELECT STATEMENT            |            |    26 |  1040 |   107   (2)|

00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |
|   2 |   NESTED LOOPS              |            |    26 |  1040 |   107   (2)|
00:00:02 |
|*  3 |    TABLE ACCESS FULL        | T1         |    26 |   754 |    55   (4)|
00:00:01 |
|*  4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |
——————————————————————————–
———-

Predicate Information (identified by operation id):

—————————————————

3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

3.从结果发现STA找到了最优的执行计划,T1表上的条件过滤后的结果集记录数为26,非常准确,所以可以选择接受这个SQL Profile;

SQL> EXECUTE dbms_sqltune.accept_sql_profile(task_name => ‘TASK_62’, REPLACE=> TRUE);

PL/SQL procedure successfully completed.

— 5.查看SQL Profile做了什么;
1.查看SQL Profile信息;
SQL>  SELECT NAME, CATEGORY, signature, sql_text FROM dba_sql_profiles;

NAME                           CATEGORY                        SIGNATURE
—————————— —————————— ———-
SQL_TEXT
——————————————————————————–
SYS_SQLPROF_0151dc24f6338000   DEFAULT                        8.9755E+18
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object
_id = t2.object_id

2.在sys.sqlprof$attr数据字典(11g中为SQLOBJ$,SQLOBJ$AUXDATA,SQLOBJ$DATA字典)中,有signature和attr_val两列重要信息;
     1.在10g之后的v$sql视图中有两列exact_matching_signature,force_matching_signature信息,用来表示sql的特征,可以用于查找没有使用绑定变量的sql语句;
     2.对于dbms_sqltune.accept_sql_profile过程中,如果force_match=>true的话表示dba_sql_profile中的signature由sql的force_matching_signature而来,否则就是exact_matching_signature;
     3.这也验证了SQL Profile比outlines好的一点,因为它也支持没有使用绑定变量的SQL;
     4.attr_val实际上就是一些hints,和outline本质是一样的,只是SQL Profile中的hints没有指定sql使用的索引和表连接的方式及顺序;这里仅仅提供了一些辅助信息,即评估了表返回记录的行数,与原始的评估返回的行数的放大/缩小倍数;
     5.计算0.0104*2500=26,就告诉CBO,T1表经过谓词过滤返回行数应该为评估的0.0104倍;
     6.这里也验证了SQL Profile并不会锁定SQL的执行计划,只是提供更多,更准确的统计信息给CBO参考;
SQL> SELECT * FROM sys.sqlprof$attr;

SIGNATURE   CATEGORY      ATTR#   ATTR_VAL
———– ——– ———-  ——————————————————————————–

8.9755E+18  DEFAULT           1  OPT_ESTIMATE(@”SEL$1″, TABLE, “T1″@”SEL$1”, SCALE_ROWS=0.0104)

3.修改谓词条件?如果执行sql:SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T2%’ AND t1.object_id = t2.object_id;
1.在10.2.0.1版本中:则还是会走没有使用SQL Profile的执行计划;
2.也许在其它的版本中会使用SQL Profile,但是评估的T1表返回的记录数应该和LIKE ‘%T1%’一样,以为这个SQL Profile是为这个条件生成的;
4.修改表的统计信息,进一步验证;
     1.将T1表的统计信息修改为500W,则Oracle会评估返回500W*5%*0.0104=2600行记录;执行计划变为FULL TABLE SCAN + HASH JOIN;
     2.虽然SQL Profile起了所用,但是并没有锁定执行计划;
SQL> EXEC dbms_stats.set_table_stats(‘SYS’, ‘T1’, numrows=>5000000);
SQL> EXPLAIN PLAN FOR SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

Explained.

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1838229974

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |  2600 |   101K|   401  (49)| 00:00:05 |
|*  1 |  HASH JOIN         |      |  2600 |   101K|   401  (49)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2600 | 75400 |   245  (79)| 00:00:03 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

Note
—–
– SQL profile “SYS_SQLPROF_0151dc24f6338000” used for this statement

———————- 如何使用SQL Tuning Advisor来生成SQL Profile,及SQL Profile原理 ———————-
———————- How to Move SQL Profiles from One Database to Another ———————-
— 1.实验环境;
1.源端数据库信息;
SQL> SELECT * FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

2.目标端数据库信息;
SQL> SELECT * FROM v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
PL/SQL Release 11.2.0.3.0 – Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

— 2.准备测试数据,在源端和目标端分别创建测试表,结果都是走全表扫描;
SQL> conn / as sysdba
SQL> ALTER USER hr IDENTIFIED BY oracle ACCOUNT UNLOCK;
SQL> conn hr/oracle
SQL> CREATE TABLE emp AS SELECT * FROM employees;
SQL> ALTER TABLE emp ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID) USING INDEX;
SQL> SET AUTOT ON
SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 3956160932

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     4 |   100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     4 |   100 |     3   (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMPLOYEE_ID”=198)

Statistics
———————————————————-
0  recursive calls
0  db block gets
5  consistent gets
0  physical reads
0  redo size
482  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

— 3.在源端使用STA进行SQL调优,然后并使用给出的建议生成一个SQL Profile;
DECLARE
my_sta_name        VARCHAR2(30);
my_sqltext         CLOB;
BEGIN
my_sqltext  := ‘SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198’;
my_sta_name := dbms_sqltune.create_tuning_task(sql_text => my_sqltext,
user_name => ‘HR’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘my_sql_tuning_task’);
dbms_sqltune.execute_tuning_task(task_name => my_sta_name);
dbms_sqltune.accept_sql_profile(task_name => my_sta_name, NAME => ‘my_sql_profile’);
END;
/
— 4.在源端查看相应的改变;
1.查看生成的profile信息;
SELECT * FROM dba_sql_profiles WHERE name = ‘my_sql_profile’;
2.查看sql的执行计划改变;
SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 2864363232

——————————————————————————–
———

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

——————————————————————————–
———

|   0 | SELECT STATEMENT            |           |     1 |     9 |     1   (0)| 0
0:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |     9 |     1   (0)| 0
0:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP_ID |     1 |       |     0   (0)| 0
0:00:01 |

——————————————————————————–
———

Predicate Information (identified by operation id):
—————————————————

2 – access(“EMPLOYEE_ID”=198)

Note
—–
– SQL profile “my_sql_profile” used for this statement

Statistics
———————————————————-
0  recursive calls
0  db block gets
2  consistent gets
0  physical reads
0  redo size
482  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

— 5.在源端打包SQL Profiles并导出;
1.打包SQL Profiles(表名和schema名严格区分大小写);
BEGIN
dbms_sqltune.create_stgtab_sqlprof(table_name => ‘TB_SQLP_STAGE’, schema_name => ‘HR’);
dbms_sqltune.pack_stgtab_sqlprof(profile_name => ‘my_sql_profile’,
staging_table_name => ‘TB_SQLP_STAGE’,
staging_schema_owner => ‘HR’);
END;
/
2.导出对应的stage table;
[oracle@singleton10g ~]$ expdp hr/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE;

Export: Release 10.2.0.1.0 – Production on Friday, 25 July, 2014 11:49:29

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “HR”.”SYS_EXPORT_TABLE_01″:  hr/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “HR”.”TB_SQLP_STAGE”                        9.257 KB       1 rows
Master table “HR”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/ORCL/dpdump/sqlp.dmp

Job “HR”.”SYS_EXPORT_TABLE_01″ successfully completed at 11:49:33
3.把dump文件放到目标端;
— 6.在目标端导入并解包SQL Profiles;
1.导入对应的stage table;
[oracle@singleton11g ~]$ impdp hr/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE

Import: Release 11.2.0.3.0 – Production on Fri Jul 25 11:52:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “HR”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_IMPORT_TABLE_01″:  hr/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “HR”.”TB_SQLP_STAGE”                        9.257 KB       1 rows
Job “HR”.”SYS_IMPORT_TABLE_01″ successfully completed at 11:53:05

2.解包SQL Profiles(对应的SQL Profile被添加到了数据字典中,可以通过dba_sql_profiles查看);
BEGIN
dbms_sqltune.unpack_stgtab_sqlprof(profile_name => ‘my_sql_profile’,
REPLACE => TRUE,
staging_table_name => ‘TB_SQLP_STAGE’,
staging_schema_owner => ‘HR’);
END;
/
— 7.在目标端查看对应的执行计划;
SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME
———– ——————–
198 Donald

Execution Plan
———————————————————-
Plan hash value: 2864363232

——————————————————————————–
———

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

——————————————————————————–
———

|   0 | SELECT STATEMENT            |           |     1 |     9 |     1   (0)| 0
0:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |     9 |     1   (0)| 0
0:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP_ID |     1 |       |     0   (0)| 0
0:00:01 |

——————————————————————————–
———

Predicate Information (identified by operation id):
—————————————————

2 – access(“EMPLOYEE_ID”=198)

Note
—–
– SQL profile “my_sql_profile” used for this statement

Statistics
———————————————————-
0  recursive calls
0  db block gets
5  consistent gets
0  physical reads
0  redo size
497  bytes sent via SQL*Net to client
419  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

———————- How to Move SQL Profiles from One Database to Another ———————-
———————- 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles ———————-
— 1.实验环境;
SQL> SELECT * FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

— 2.相关介绍;
1.存储过程:在帮助文档中并没有介绍,但是可以通过查看包体的信息;
SQL> desc dbms_sqltune
PROCEDURE IMPORT_SQL_PROFILE
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SQL_TEXT                       CLOB                    IN
PROFILE                        SQLPROF_ATTR            IN
NAME                           VARCHAR2                IN     DEFAULT
DESCRIPTION                    VARCHAR2                IN     DEFAULT
CATEGORY                       VARCHAR2                IN     DEFAULT
VALIDATE                       BOOLEAN                 IN     DEFAULT
REPLACE                        BOOLEAN                 IN     DEFAULT
FORCE_MATCH                    BOOLEAN                 IN     DEFAULT
2.SYS.SQLPROF_ATTR是一个VARCHAR2的集合类型;
SELECT * FROM dba_source WHERE owner = ‘SYS’ AND NAME = ‘SQLPROF_ATTR’;
TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)
— 3.还原环境;
BEGIN
dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_0151dc24f6338000’);
END;
/
BEGIN
dbms_stats.set_table_stats(‘SYS’, ‘T1’, numrows => 5000);
END;
/
— 4.稳定现有的SQL的执行计划;
1.查看sql的信息;
SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql;

SQL_ID        PLAN_HASH_VALUE
————- —————
SQL_TEXT
——————————————————————————–
cy6x06rhrt54x      1838229974
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object
_id = t2.object_id

2.10g开始,v$sql_plan中包括了SQL语句的outlines数据,即稳定执行计划的hints;
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(‘cy6x06rhrt54x’,NULL,’outline’));

PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID  cy6x06rhrt54x, child number 0
————————————-
SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND
t1.object_id = t2.object_id

Plan hash value: 1838229974

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |       |       |   212 (100)|          |

PLAN_TABLE_OUTPUT
——————————————————————————–
|*  1 |  HASH JOIN         |      |  2500 |    97K|   212   (3)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 72500 |    55   (4)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50350 |   540K|   155   (2)| 00:00:02 |
—————————————————————————

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “T1″@”SEL$1″)
FULL(@”SEL$1” “T2″@”SEL$1″)
LEADING(@”SEL$1” “T1″@”SEL$1” “T2″@”SEL$1″)
USE_HASH(@”SEL$1” “T2″@”SEL$1”)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):

1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)

3.SQLT工具中的coe_xfr_sql_profile.sql脚本:这个脚本可以用于从shared pool,awr中提取指定的SQL ID的Outline Data并创建SQL Profile;
     1.脚本需要输入sql的sql_id和plan_hash_value,然后会生成一个coe_xfr_sql_profile_${sql_id}_${plan_hash_value}.sql的脚本;
     2.脚本中的force_match参数默认是FALSE,可以修改为TRUE,也可以修改脚本其它的内容;
SQL>@/home/oracle/coe_xfr_sql_profile.sql cy6x06rhrt54x 1838229974

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
1838229974        .087
1164754026

Parameter 2:
PLAN_HASH_VALUE (required)

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : “cy6x06rhrt54x”
PLAN_HASH_VALUE: “1838229974”

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_cy6x06rhrt54x_1838229974.sql
on TARGET system in order to create a custom SQL Profile
with plan 1838229974 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL>@coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sql
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 t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1]’);
15  wa(q'[%’ AND t1.object_id = t2.object_id
16   ]’);
17  DBMS_LOB.CLOSE(sql_txt);
18  h := SYS.SQLPROF_ATTR(
19  q'[BEGIN_OUTLINE_DATA]’,
20  q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,
21  q'[OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1′)]’,
22  q'[ALL_ROWS]’,
23  q'[OUTLINE_LEAF(@”SEL$1″)]’,
24  q'[FULL(@”SEL$1″ “T1″@”SEL$1″)]’,
25  q'[FULL(@”SEL$1” “T2″@”SEL$1″)]’,
26  q'[LEADING(@”SEL$1” “T1″@”SEL$1” “T2″@”SEL$1″)]’,
27  q'[USE_HASH(@”SEL$1” “T2″@”SEL$1″)]’,
28  q'[END_OUTLINE_DATA]’);
29  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
30  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
31  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
32  sql_text    => sql_txt,
33  profile     => h,
34  name        => ‘coe_cy6x06rhrt54x_1838229974’,
35  description => ‘coe cy6x06rhrt54x 1838229974 ‘||:signature||’ ‘||:signaturef||”,
36  category    => ‘DEFAULT’,
37  validate    => TRUE,
38  replace     => TRUE,
39  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
40  DBMS_LOB.FREETEMPORARY(sql_txt);
41  END;
42  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

SIGNATURE
———————
8975541025552400288

SIGNATUREF
———————
3960696072677096522

… manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_cy6x06rhrt54x_1838229974 completed

— 5.在不能修改SQL的情况下改变并固定SQL的执行计划,即使原来的SQL使用了hints;
1.一般遇到sql不能修改,或者sql使用了错误hints的情况下有三种办法修改sql的执行计划;
     1.调整统计信息:不可靠,使用比较复杂,而且统计信息会自动收集;
     2.使用outline:比较复杂;
     3.使用SQL Profiles:本质和OUTLINE是一样的;
2.一般的工作流;
     1.取得原始的SQL文本,包括sql_id;
     2.构造一个与原始SQL在逻辑上,结构上完全相同的SQL(逻辑和结构相同即可,即sql解析的用户,引用对象的schema,甚至是谓词条件可以不同);
     3.执行我们构造的SQL,生成Outline Data;
     4.使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile;
3.构建SQL;
     1.FULL TABLE SCAN + HASH JOIN的方式:SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;
2.INDEX RANGE SCAN + NESTED LOOP的方式:SELECT /*+ sql_profile index(t1) use_nl(t1 t2) */  t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;
     3.查看sql_id和plan_hash_value;
SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE ‘%sql_profile%’ AND sql_id IN (‘8cukn1u7dxssh’,’6csfwp8k7qu49′)
2  ;

SQL_ID        PLAN_HASH_VALUE
————- —————
SQL_TEXT
——————————————————————————–
8cukn1u7dxssh      1838229974
SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM
t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id

6csfwp8k7qu49      1164754026
SELECT /*+ sql_profile index(t1) use_nl(t1 t2) */  t1.*, t2.owner FROM t1, t2 WH
ERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id

4.分别使用coe_xfr_sql_profile.sql脚本生产两个sql的sql_profile创建脚本;
SQL>@/home/oracle/coe_xfr_sql_profile.sql 8cukn1u7dxssh 1838229974 — coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql
SQL>@/home/oracle/coe_xfr_sql_profile.sql 6csfwp8k7qu49 1164754026 — coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql
5.然后把coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql中构建SYS.SQLPROF_ATTR的部分和force_match => TRUE地方替换掉coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql脚本中相应内容;
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]’,
q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,
q'[OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1′)]’,
q'[ALL_ROWS]’,
q'[OUTLINE_LEAF(@”SEL$1″)]’,
q'[FULL(@”SEL$1″ “T1″@”SEL$1″)]’,
q'[INDEX(@”SEL$1” “T2″@”SEL$1” (“T2″.”OBJECT_ID”))]’,
q'[LEADING(@”SEL$1″ “T1″@”SEL$1” “T2″@”SEL$1″)]’,
q'[USE_NL(@”SEL$1” “T2″@”SEL$1”)]’,
q'[END_OUTLINE_DATA]’);
6.然后执行,并查看执行计划;
SQL>EXPLAIN PLAN FOR SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;

Explained.

SQL>SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1164754026

——————————————————————————–
———-

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

——————————————————————————–
———-

PLAN_TABLE_OUTPUT
——————————————————————————–
|   0 | SELECT STATEMENT            |            |   250 | 10000 |   554   (1)|
00:00:07 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |

|   2 |   NESTED LOOPS              |            |   250 | 10000 |   554   (1)|
00:00:07 |

|*  3 |    TABLE ACCESS FULL        | T1         |   250 |  7250 |    53   (0)|
00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————–

|*  4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |

——————————————————————————–
———-

Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–
3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Note
—–
– SQL profile “coe_8cukn1u7dxssh_1838229974” used for this statement

— 7.改变条件,再次查看执行计划,发现SQL的执行计划被固定了;
SQL>EXPLAIN PLAN FOR SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T2%‘ AND t1.object_id = t2.object_id;

Explained.

SQL>SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1164754026

——————————————————————————–
———-

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

——————————————————————————–
———-

PLAN_TABLE_OUTPUT
——————————————————————————–
|   0 | SELECT STATEMENT            |            |   250 | 10000 |   554   (1)|
00:00:07 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |     1 |    11 |     2   (0)|
00:00:01 |

|   2 |   NESTED LOOPS              |            |   250 | 10000 |   554   (1)|
00:00:07 |

|*  3 |    TABLE ACCESS FULL        | T1         |   250 |  7250 |    53   (0)|
00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————–

|*  4 |    INDEX RANGE SCAN         | IDX_T2_OID |     1 |       |     1   (0)|
00:00:01 |

——————————————————————————–
———-

Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–
3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T2%’)
4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Note
—–
– SQL profile “coe_8cukn1u7dxssh_1838229974” used for this statement

———————- 使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程手工创建SQL Profiles ———————-