使用LogMiner工具

1、准备测试数据

create table hy19 as select * from dba_objects;

 

2、创建索引并分析

 

CREATE INDEX INX_19 ON SCOTT.HY19(OBJECT_ID);

ANALYZE TABLE SCOTT.HY19 COMPUTE STATISTICS;

 

 

3. 执行SQL语句，模拟一个性能低下的执行计划

SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19  WHERE OBJECT_ID=1;

（此处用别名访问表时发现hint不起作用）

SQL> SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19  WHERE OBJECT_ID=1;

 

 

Execution Plan

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

Plan hash value: 3188441247

 

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    87 |   159   (2)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| HY19 |     1 |    87 |   159   (2)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID"=1)

 

4、创建优化任务：

–使用SQL TEXT

 

DECLARE

  my_task_name VARCHAR2(30);

  my_sqltext   CLOB;

BEGIN

  my_sqltext := 'SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19  WHERE OBJECT_ID=1';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

         sql_text    => my_sqltext,

         user_name   => 'SCOTT',

         scope       => 'COMPREHENSIVE',

         time_limit  => 60,

         task_name   => 'tuning_task_1',

         description => 'Task to tune a query on a specified table');

END;

/

–使用SQL ID:

DECLARE

 my_task_name VARCHAR2(50);

 my_sql_id   VARCHAR2(64);

BEGIN

 my_sql_id := 'gh991ctttx3k7';

 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

         sql_id    => my_sql_id,

         scope       => 'COMPREHENSIVE',

         time_limit  => 60,

         task_name   => 'rockey_sql_tuning_task_001',

         description => 'Task to tune a query on a specified table');

END;

 

/

5、执行优化任务：

BEGIN

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'rockey_sql_tuning_task_001');

end;

/

 

6、查看优化建议：

SET WRAP ON

SET LONG 10000

SET LONGCHUNKSIZE 1000

SET LINESIZE 130

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'rockey_sql_tuning_task_001') from DUAL;

或者

SELECT dbms_advisor.GET_TASK_REPORT(task_name)

  FROM dba_advisor_tasks

 where task_name = 'rockey_sql_tuning_task_001';

 

7、SQL语句绑字SQL Profile：

begin

  dbms_sqltune.accept_sql_profile(task_name   => 'rockey_sql_tuning_task_001',

                                  name        => 'my_sql_profile_001');

end;

/

 

8. 再次执行相同的语句，验证结果

SQL> SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19  WHERE OBJECT_ID=1;

Execution Plan

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

Plan hash value: 2066044106

 

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

 

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

     |

 

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

 

|   0 | SELECT STATEMENT            |        |     1 |    87 |     2   (0)| 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| HY19   |     1 |    87 |     2   (0)| 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | INX_19 |     1 |       |     1   (0)| 00:00:01 |

 

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

 

 

Predicate Information (identified by operation id):

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

 

   2 - access("OBJECT_ID"=1)

这里可以看出，Oracle已经使用了不同的执行计划

 

9、查看语句是否使用了SQL Profile：

select sql_text, sql_id, sql_profile, executions, plan_hash_value

  from v$sql

 where sql_profile is not null;

 

10、其它常用功能：

删除优化任务：

begin

dbms_sqltune.drop_tuning_task('rockey_sql_tuning_task_001');

end;

/

 

begin

dbms_advisor.delete_task('rockey_sql_tuning_task_001');

end;

/

删除SQL Profile：

BEGIN

  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile_001');

END;

/

与优化任务相关的常用视图：

select * from dba_sql_profiles;

 

select * from DBA_ADVISOR_TASKS;

############

SQL Profiles可以看作是SQL语句的统计信息。只是这个统计信息对特定SQL语句才能起作用，不对会语句的对象、其它语句产生影响。

使用SQL Profiles前要用SQL Tuning Advisor收集对语句的优化建议，再根据优化建议创建SQL Profiles。

SQL Profiles 使用也比较灵活，可以在会话级、系统级应用。

语句绑定SQL Profile后，测试了下SQL Profile与Bind Peeking的关系。测试发现，Bind Peeking的特性还是会起作用。这从另一方面说明SQL Profile与OUTLINE的不同：绑定OUTLINE后，执行计划是被固化的；绑定SQL Profile后，执行计划不是不变，而是优化器在执行该语句时，会参考SQL Profile中的信息。

############

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26764973/viewspace-1450742/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26764973/viewspace-1450742/