OracleSQLProfile

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

本篇文章来简单看下如何使用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*/

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：