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