Oracle SQL Profile

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*/

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