sql profile

最新推荐文章于 2024-02-01 11:25:02 发布

wluckdog

最新推荐文章于 2024-02-01 11:25:02 发布

阅读量829

点赞数

分类专栏：优化

本文链接：https://blog.csdn.net/wll_1017/article/details/12169155

版权

优化专栏收录该内容

7 篇文章 0 订阅

订阅专栏

老熊对sqlprofile的阐述：
SQL Profiles可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现，而SQL Profiles具有Outlines不具备的优化，个人认为最重要的有2点：
SQL Profiles更容易生成、更改和控制。
SQL Profiles在对SQL语句的支持上做得更好，也就是适用范围更广。

现在我在使用Outlines的场合，均使用SQL Profiles来替代。有一次准备对1条SQL语句使用Outline进行执行计划的稳定，结果使用Outline之后，系统出现大量的library cache latch的争用，不得不关闭Outline的使用，但是改用SQL Profiles不再有这个问题。这或许是个BUG，不过既然能用SQL Profiles代替，也就没再深入去研究这个问题。
使用SQL Profiles无非是两个目的：
锁定或者说是稳定执行计划。
在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。

那么SQL Profile到底是什么？在我看来，SQL Profile就是为某一SQL语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划。这些说法显得比较枯燥，还是来看看下面的测试。

create table t1 as select object_id,object_name from dba_objects ；
SQL> select * From t1 where  object_id=100;
create index t1_idx on t1(object_id); 

 
create index t2_idx on t2(object_id);  
执行计划
----------------------------------------------------------
Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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




SQL> select /*+ full(t1)*/* From t1 where  object_id=100;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    79 |    71   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    79 |    71   (2)| 00:00:01 |
--------------------------------------------------------------------------



SQL> set autot off
SQL> select sql_id from v$sqlarea  where sql_text like 'select /*+ full(t1)*/*%'

SQL_ID
-------------
26kupg2tdb6a4

SQL>  select * from table(dbms_xplan.display_cursor('26kupg2tdb6a4',null,'outline'));


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

SQL_ID  26kupg2tdb6a4, child number 0
-------------------------------------
select /*+ full(t1)*/* From t1 where  object_id=100

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    71 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    79 |    71   (2)| 00:00:01 |
--------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter("OBJECT_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


已选择36行。


SQL> declare
  2   v_hints sys.sqlprof_attr;
  3   begin
  4   v_hints:=sys.sqlprof_attr(
  5  'BEGIN_OUTLINE_DATA',
  6  'IGNORE_OPTIM_EMBEDDED_HINTS',
  7  'OPTIMIZER_FEATURES_ENABLE(''11.2.0.1'')',
  8  'DB_VERSION(''11.2.0.1'')',
  9  'ALL_ROWS',
 10  'OUTLINE_LEAF(@"SEL$1")',
 11  'FULL(@"SEL$1" "T1"@"SEL$1")',
 12  'END_OUTLINE_DATA'
 13  );
 14  dbms_sqltune.import_sql_profile(
 15  'select * From t1 where  object_id=100;',
 16  v_hints,'test',                 --sql profile 名称
 17  force_match=>true,replace=>true);
 18  end;
 19  /

PL/SQL 过程已成功完成。

SQL> set autot trace
SQL> select *From t1;

已选择50000行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50889 |  3926K|    71   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 50889 |  3926K|    71   (2)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3566  consistent gets
          0  physical reads
          0  redo size
    1972209  bytes sent via SQL*Net to client
      37078  bytes received via SQL*Net from client
       3335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50000  rows processed



删除sql profile：

BEGIN   
DBMS_SQLTUNE.DROP_SQL_PROFILE( name =>'test' );  
END;