oracle sql plan baseline,sql plan baseline使用心得

###再次执行sql时已经能用到了这条sql

plan baseline了

variable

v_objid number;

exec

:v_objid:=500;

select

count(*) from scott.t1 where object_id

set

autotrace traceonly;

select

count(*) from scott.t1 where object_id

Execution Plan

----------------------------------------------------------

Plan hash

value: 4020739011

--------------------------------------------------------------------------------

--

| Id  | Operation         | Name         | Rows

| Bytes | Cost (%CPU)| Time

|

--------------------------------------------------------------------------------

--

|   0 | SELECT STATEMENT  |

|     1 |     6 |

5   (0)| 00:00:01

|

|   1 |

SORT AGGREGATE   |              |     1 |

6 |            |

|

|*  2 |

INDEX RANGE SCAN| IND_OBJID_T1 |

8893 | 53358 |     5   (0)| 00:00:01

|

--------------------------------------------------------------------------------

--

Predicate

Information (identified by operation id):

---------------------------------------------------

2 -

access("OBJECT_ID"

Note

-----

-SQL plan baseline

"SQL_PLAN_gm8nknf6mhghn28a6f5d9" used for this statement

Statistics

----------------------------------------------------------

27

recursive calls

16

db block gets

15

consistent gets

13

physical reads

3136

redo size

527

bytes sent via SQL*Net to client

520

bytes received via SQL*Net from client

2

SQL*Net roundtrips to/from client

0

sorts (memory)

0

sorts (disk)

1

rows processed

(3)通过dbms_sqltune对SQL语句进行调优，并接受其调优建议

这里沿用本文第一部分对于select count(*) from scott.t1 where object_id in (select object_id

from scott.t2)语句生成的两条sql plan baseline，作如下处理：删除走索引的那条plan(只保留FTS)->使用sql tuning advisor对语句进行调优->接受advisor使用索引访问的建议

###人工删除掉走索引的sql

plan

set

numformat 9999999999999999999999999

col

sql_handle format a20

col

creator format a5

col

sql_text format a50

col

created        format a30

col

last_modified  format a30

col

last_executed  format a30

col

last_verified  format a30

set

linesize 180

select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified

from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';

--删除其中使用索引的那条

set

serveroutput on

declare

result_int

pls_integer;

begin

result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11');

dbms_output.put_line(result_int);

end;

/

--删除成功只剩一条FTS的plan

select

sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified

from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';

###执行dbms_sqltune，生成并接受优化建议

--生成tuning任务

declare

my_task_name

varchar2(30);

my_sqltext clob;

begin

my_sqltext:='select

count(*) from scott.t1 where object_id in (select object_id from scott.t2)';

my_task_name:=dbms_sqltune.create_tuning_task(sql_text=>my_sqltext,user_name=>'SCOTT',scope=>'COMPREHENSIVE',time_limit=>60,task_name=>'scott_sql_tune_1',description=>'tune

1');

end;

/

--执行tuning任务

begin

dbms_sqltune.execute_tuning_task(task_name=>'scott_sql_tune_1');

end;

/

###查看sqltune报告，截取了相关内容

set

long 9000

set

longchunksize 1000

set

linesize 800

select

dbms_sqltune.report_tuning_task('scott_sql_tune_1') from dual;

1- Original With Adjusted Cost

------------------------------

Plan hash value: 1240933221

DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |

|     1 |     9 |

462   (2)| 00:00:06 |

|   1 |  SORT AGGREGATE       |

|     1 |     9 |            |          |

|*  2 |   HASH JOIN RIGHT SEMI|      |

3 |    27 |   462

(2)| 00:00:06 |

|   3 |    TABLE ACCESS FULL  | T2

|    99 |   297 |

5   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | T1

|   177K|  1042K|

455   (1)| 00:00:06 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')

2- Using SQL Profile

--------------------

Plan hash value: 2406492491

-------------------------------------------------------------------------------------

| Id  | Operation            | Name         | Rows

| Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |              |     1 |

9 |    56   (