Oracle固定执行计划之SQL PROFILE概要文件详解

当前第2页 返回上一页

查找执行SQL的SQL_ID

tpcc@TOADDB> select sql_id,sql_text from v$sqlwhere sql_text like '%t1.object_name%';
SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id
18bphz37dajq9
select /*+ use_nl(t1 t2) index(t2) */t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and
t1.object_id=t2.object_id

运行存储过程如下：

var tuning_task varchar2(100); 
DECLARE 
 l_sql_id v$session.prev_sql_id%TYPE; 
 l_tuning_task VARCHAR2(30); 
 BEGIN 
 l_sql_id:='4zbqykx89yc8v'; 
 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id =>l_sql_id); 
 :tuning_task:=l_tuning_task; 
 dbms_sqltune.execute_tuning_task(l_tuning_task); 
 dbms_output.put_line(l_tuning_task); 
 END;
/ 

TASK_114

PL/SQL procedure successfully completed.

查看task的名字

tpcc@TOADDB> print tuning_task;
TUNING_TASK
----------------------------------------------------------------------------------------------------
TASK_114

查看执行报告

set long 99999
col comments format a200
SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual; 
COMMENTS
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_114
Tuning Task Owner : TPCC
Workload Type : Single SQL Statement
Scope     : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at   : 03/06/2016 05:27:21
Completed at  : 03/06/2016 05:27:24
-------------------------------------------------------------------------------
Schema Name: TPCC
SQL ID   : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
    and t1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain planssection below)
--------------------------------------------------------
 Apotentially better execution plan was found for this statement.
 Recommendation (estimated benefit: 83.08%)
 ------------------------------------------
 -Consider accepting the recommended SQL profile.
 executedbms_sqltune.accept_sql_profile(task_name => 'TASK_114',
    task_owner =>'TPCC', replace => TRUE);
 Validation results
 ------------------
 TheSQL profile was tested by executing both its plan and the original plan
 andmeasuring their respective execution statistics. A plan may have been
 only partially executed if the other could be run to completion in lesstime.
        Original Plan With SQL Profile % Improved
        ------------- ---------------- ----------
 Completion Status:    COMPLETE  COMPLETE
 Elapsed Time (s):   .012865 .004556  64.58 %
 CPUTime (s):     .0124  .0045  63.7%
 User I/O Time (s):      0     0
 Buffer Gets:        1787    302  83.1%
 Physical Read Requests:   0     0
 Physical Write Requests:   0     0
 Physical Read Bytes:     0     0
 Physical Write Bytes:    0     0
 Rows Processed:    42     42
 Fetches:        42     42
 Executions:       1     1
 Notes
 -----
 1.Statistics for the original plan were averaged over 10 executions.
 2.Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation  | Name | Rows | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT |  | 42 | 1680 | 498 (1)| 00:00:01 |
|* 1| HASH JOIN   |   | 42 | 1680 | 498 (1)| 00:00:01 |
|* 2| TABLE ACCESS FULL| T1 | 42 | 1218 | 68 (0)| 00:00:01 |
| 3| TABLE ACCESS FULL| T2 | 92021 | 988K| 430 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 2- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS
    NOT NULL)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)|Time  |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT  |   | 42 | 1680 | 152 (0)|00:00:01 |
| 1| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
| 2| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
|* 3| TABLE ACCESS FULL   | T1  | 42 | 1218 | 68 (0)| 00:00:01 |
|* 4| INDEX RANGE SCAN   |T2_IDX |  1 |  | 1 (0)| 00:00:01 |
| 5| TABLE ACCESS BY INDEX ROWID| T2  | 1 | 11 |  2  (0)|00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 3- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS NOT
    NULL)
 4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------

接受分析建议

报告中给出了执行方法，如上红色部分

接受报告的建议，验证一下如下：

tpcc@TOADDB> execute dbms_sqltune.accept_sql_profile(task_name=> 'TASK_114',task_owner => 'TPCC', replace => TRUE);
PL/SQL procedure successfully completed.

执行测试

再执行原先命令如下：

tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id;
42 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)|Time  |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT  |   | 42 | 1680 | 152 (0)|00:00:01 |
| 1| NESTED LOOPS      |   | 42| 1680 | 152 (0)|00:00:01 |
| 2| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
|* 3| TABLE ACCESS FULL   | T1  | 42 | 1218 | 68 (0)| 00:00:01 |
|* 4| INDEX RANGE SCAN   |T2_IDX |  1 |  | 1 (0)| 00:00:01 |
| 5| TABLE ACCESS BY INDEX ROWID| T2  | 1 | 11 |  2  (0)|00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 3- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS NOT
    NULL)
 4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
 - SQL profile"SYS_SQLPROF_01534b8309b90000" used for this statement
 - this is an adaptive plan
Statistics
----------------------------------------------------------
   35 recursive calls
   0 dbblock gets
   317 consistent gets
   1 physical reads
   0 redosize
  2350 bytes sent via SQL*Net toclient
   573 bytes received via SQL*Net from client
   4 SQL*Net roundtrips to/from client
   1 sorts (memory)
   0 sorts (disk)
   42 rowsprocessed

启用了PROFILE，PS：如果执行中多加几个空格，并不会影响PROFILE的生效的。

5.  维护操作

禁用命令
如下：

begin 
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_01534b8309b90000', 
attribute_name => 'status', 
value   => 'disabled'); 
end; 
/

启用命令
如下：

begin 
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_01534b8309b90000', 
attribute_name => 'status', 
value   => 'enabled'); 
end; 
/

查看使用的PROFILE

如下：

SQL>SELECT task_name,status FROMUSER_ADVISOR_TASKS ;

删除PROFILE

BEGIN 
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01534b8309b90000'); 
END; 

总结

以上所述是小编给大家介绍的Oracle固定执行计划之SQL PROFILE概要文件 ，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

标签：Oracle

返回前面的内容

相关阅读 >>

oracle 触发器的使用小结

oracle怎么删除所有表

oracle中的的hint详解

使用zabbix监控oracle数据库的方法详解

oracle表分区的概念及操作

小白学oracle第二关之第一个oracle数据库表的创建

深入浅析oracle数据库管理之创建和删除数据库

navicat连接oracle详细教程

oracle数据库中表空间的基本管理操作小结

oracle11g卸载完整图文教程

更多相关阅读请进入《oracle》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » Oracle固定执行计划之SQL PROFILE概要文件详解