Oracle SQL Profile

本篇文章来简单看下如何使用sql profile来绑定正确的执行计划，10gR2出现这个功能还是蛮实用的，当然11g可以使用baseline(我之前写过一篇文章)，

当然我觉得这两种都挺好。

我们可能经常会遇到一些大表比如上T的，走错执行计划，比如全表扫描，那系统基本处于Hang的状态，那么这时候收集统计信息可能会很慢，

即使你采样的比例设置的很小。所以使用profile和baseline是个不错的选择。

一，创建测试环境

SQL> create table test (n number );
Table created.

declare
begin
for i in 1 .. 10000
loop
insert into test values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.

create index test_idx on test(n);
Index created.

SQL> exec dbms_stats.gather_table_stats('LEO','TEST');
PL/SQL procedure successfully completed.

二，测试sql

var v varchar2(5);
exec :v :=1;
set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=:v;
         N
----------
         1
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=TO_NUMBER(:V))
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

三，使用profile来固定和优化sql
当然如上是全表扫描，很显然不是最优的，下面使用profile来固定和优化sql

1.Create tuning task

SQL> declare
  2    my_task_name VARCHAR2(30);
  3    my_sqltext CLOB;
  4    begin
  5        my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=:v';
  6       my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7       sql_text => my_sqltext,
  8       user_name => 'LEO',--Username for whom the statement is to be tuned
  9        scope => 'COMPREHENSIVE',
 10        time_limit => 60,
 11       task_name => 'my_sql_tuning_task_5',
 12       description => 'Task to tune a query on a specified table');
 13  end;
 14  /
PL/SQL procedure successfully completed.

/*+如上是使用sql text，实际环境中还是使用sql_id，还是更方便点，下面看如何使用sql_id*/

select sql_id from v$sql where upper(sql_text) like upper('%select /*+ no_index(test test_idx)%');
sql_id
------
brg4wn3kfzp34
SQL> declare
  2    my_task_name VARCHAR2(30);
  3    my_sqltext CLOB;
  4    my_sqlid varchar2(50);
  5    my_plan_hash_value varchar2(50);
  6    begin
  7        my_sqlid := 'brg4wn3kfzp34';--如上SQL的sql_id
  8        my_plan_hash_value :='1357081020';--如上sql的hash_value
  9        my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
 10        sql_id => my_sqlid,
 11        plan_hash_value =>my_plan_hash_value,
 12        scope => 'COMPREHENSIVE',
 13        time_limit => 60,
 14        task_name => 'my_sql_tuning_task_5',
 15        description => 'Task to tune a query on a specified table');
 16  end;
 17  /

2.execute tuning task

SQL> begin
  2   DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_5');
  3   end;
  4 /
PL/SQL procedure successfully completed.

3.report tuning task

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_5') from DUAL;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_5
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Execution Count    : 2
Current Execution  : EXEC_91
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 07/19/2012 20:45:42
Completed at       : 07/19/2012 20:45:43
-------------------------------------------------------------------------------
Schema Name: LEO
SQL ID     : brg4wn3kfzp34
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=:v
-------------------------------------------------------------------------------
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: 95.02%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_5', task_owner => 'SYS', replace => TRUE);
  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.
                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time(us):                 642               168      73.83 %
  CPU Time(us):                    1200                 0        100 %
  User I/O Time(us):                  0                 0
  Buffer Gets:                       20                 1         95 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1
  Notes
  -----
  1. The original plan was first executed to warm the buffer cache.
  2. Statistics for original plan were averaged over next 9 executions.
  3. The SQL profile plan was first executed to warm the buffer cache.
  4. Statistics for the SQL profile plan were averaged over next 9 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=TO_NUMBER(:V))

2- Using SQL Profile
--------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N"=TO_NUMBER(:V))
-------------------------------------------------------------------------------

可以看到如上信息，下面我们acctpt此profile：

4.Accept recommended SQL Profile

SQL> DECLARE
  2   my_sqlprofile_name VARCHAR2(30);
  3   begin
  4   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  5   task_name => 'my_sql_tuning_task_5',
  6   name => 'my_sql_profile5',
  7   replace => TRUE,
  8   force_match =>TRUE);
  9   end;
 10   /
PL/SQL procedure successfully completed.

注:如上选项中force_match是强制所有的此类SQL，对于不同的字面值都可以转换成绑定变量，让此类SQL都使用此执行几乎.
再次查询如下sql:

SQL> l
  1* select /*+ no_index(test test_idx) */ * from test where n=:v
SQL> /
         N
----------
         1
Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N"=TO_NUMBER(:V))
Note
-----
   - SQL profile "my_sql_profile5" used for this statement
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
          7  consistent gets
          1  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Note部分显示使用了sql profile：my_sql_profile5
Note
-----
   - SQL profile "my_sql_profile5" used for this statement
还有更多选项和方法可以查看online document，此简单记录之……