Oracle sql porfile的使用

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5773076.html

Oracle SQL Profile的作用

(1)锁定或者说是稳定执行计划。

(2)在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行

SQL Profile对于一下类型语句有效：
     SELECT语句；
     UPDATE语句；
     INSERT语句（仅当使用SELECT子句时有效）；
     DELETE语句；
     CREATE语句（仅当使用SELECT子句时有效）；
     MERGE语句（仅当作UPDATE和INSERT操作时有效）。

使用SQL Profile所在用户还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。

使用outline来确定sql profile执行计划。演示过程如下

SQL> create table t_tables as
  2  select t2.object_id table_id,t1.OWNER table_owner,t1.TABLE_NAME table_name,
  3           TABLESPACE_NAME,NUM_ROWS from dba_tables t1,dba_objects t2 where t1.table_name=t2.object_name;

Table created.

SQL>  create table t_objects as select object_id,OWNER,object_name,object_type,DATA_OBJECT_ID from dba_objects;

Table created.


SQL> select count(*) from t_objects;

  COUNT(*)
----------
     99648

SQL> create index t_objects_idx on t_objects(object_id);

Index created.
  

搜集表的统计信息后
SQL> exec dbms_stats.gather_table_stats(user,'t_objects',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t_tables',cascade=>true,method_opt=>'for all columns size 1');


PL/SQL procedure successfully completed.

来看sql语句：

select t1.*,t2.owner from t_tables t1,t_objects t2 where t1.table_name like '%T1%' and t1.table_id=t2.object_id;

执行计划

SQL> set autot traceonly
SQL> select t1.*,t2.owner from t_tables t1,t_objects t2 where t1.table_name like '%T1%' and t1.table_id=t2.object_id;

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3978708767

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   341 | 16027 |   166   (4)| 00:00:02 |
|*  1 |  HASH JOIN         |           |   341 | 16027 |   166   (4)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| T_TABLES  |   171 |  6156 |     7   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T_OBJECTS | 99648 |  1070K|   157   (2)| 00:00:02 |
--------------------------------------------------------------------------------

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

   1 - access("T1"."TABLE_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."TABLE_NAME" LIKE '%T1%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        713  consistent gets
          0  physical reads
          0  redo size
        897  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)
          8  rows processed

执行计划中表的连接是hash join，改变两个表的连接为NESTED LOOPS ，来看看执行计划的变化

SQL> explain plan for select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner 
  2          from t_tables t1,t_objects t2 
  3          where t1.table_name like '%T1%' and t1.table_id=t2.object_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3307320638

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   341 | 16027 |   521   (1)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS     |     2 |    22 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |               |   341 | 16027 |   521   (1)| 00:00:07 |
|*  3 |    TABLE ACCESS FULL        | T_TABLES      |   171 |  6156 |     7   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T_OBJECTS_IDX |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("T1"."TABLE_NAME" LIKE '%T1%')
   4 - access("T1"."TABLE_ID"="T2"."OBJECT_ID")

17 rows selected.

看到使用hints后执行计划已经改变，继续往下

SQL> explain plan for
  2  select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner 
  3             from t_tables t1,t_objects t2 
  4             where t1.table_name like '%T1%' and t1.table_id=t2.object_id;

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display(null,null,'outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3307320638

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   341 | 16027 |   521   (1)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS     |     2 |    22 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |               |   341 | 16027 |   521   (1)| 00:00:07 |
|*  3 |    TABLE ACCESS FULL        | T_TABLES      |   171 |  6156 |     7   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T_OBJECTS_IDX |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      INDEX(@"SEL$1" "T2"@"SEL$1" ("T_OBJECTS"."OBJECT_ID"))
      FULL(@"SEL$1" "T1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   3 - filter("T1"."TABLE_NAME" LIKE '%T1%')
   4 - access("T1"."TABLE_ID"="T2"."OBJECT_ID")

33 rows selected.

使用hint产生的outline data数据来稳定sql的执行计划，编写sql profile

SQL> declare
  2   v_hints sys.sqlprof_attr;
  3   begin
  4   v_hints:=sys.sqlprof_attr(
  5        'BEGIN_OUTLINE_DATA',
  6        'USE_NL(@"SEL$1" "T2"@"SEL$1")', -------?a??ê?óéóúhint2úéú,??êμ?ò??Dèòaμ??íê??a??
  7        'LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")',
  8        'INDEX(@"SEL$1" "T2"@"SEL$1" ("T_OBJECTS"."OBJECT_ID"))',
  9        'FULL(@"SEL$1" "T1"@"SEL$1")',
 10        'OUTLINE_LEAF(@"SEL$1")',
 11        'ALL_ROWS',
 12        'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')',
 13        'IGNORE_OPTIM_EMBEDDED_HINTS',
 14        'END_OUTLINE_DATA');
 15  dbms_sqltune.import_sql_profile(
 16  'select t1.*,t2.owner from t_tables t1,t_objects t2 
 17     where t1.table_name like ''%T1%'' and t1.table_id=t2.object_id',
 18  v_hints,'SQLPROFILE_dbyang',                 --sql profile ??3?
 19  force_match=>true,replace=>true);
 20  end;
 21  /

PL/SQL procedure successfully completed.

再回过来看看执行计划

SQL> set autot traceonly
SQL> select t1.*,t2.owner from t_tables t1,t_objects t2 where t1.table_name like '%T1%' and t1.table_id=t2.object_id;

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3307320638

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   341 | 16027 |   521   (1)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS     |     2 |    22 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |               |   341 | 16027 |   521   (1)| 00:00:07 |
|*  3 |    TABLE ACCESS FULL        | T_TABLES      |   171 |  6156 |     7   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T_OBJECTS_IDX |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - filter("T1"."TABLE_NAME" LIKE '%T1%')
   4 - access("T1"."TABLE_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SQLPROFILE_dbyang" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         39  consistent gets
          0  physical reads
          0  redo size
        897  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)
          8  rows processed

Note
-----
   - SQL profile "SQLPROFILE_dbyang" used for this statement
可以看到sql profile使用outline固定执行计划

最后，如果要删除sql profile，使用如下过程
BEGIN 
   DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SQLPROFILE_dbseek'); 
 END; 
 /

---The end