oracle 提供了优化建议功能包DBMS_SQLTUNE,该包可以帮助我们分析SQL,并提供优化建议。
Sql_Profile是用来影响数据库执行计划生成的一组信息文件的集合,可以在不改变原有SQL语句的前提下,达到类似HINTS改变其执行计划的目的。
原有执行计划
alter session set statistics_level=all;
set serveroutput off
select * from test.emp where ename='SCOTT' and DEPTNO=20;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k1gbrapm7zpd, child number 0
-------------------------------------
select * from test.emp where ename='SCOTT' and DEPTNO=20
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ENAME"='SCOTT' AND "DEPTNO"=20))
下面就用DBMS_SQLTUNE优化该SQL
--1.赋予用户ADVISOR权限
grant ADVISOR to test;
--2.创建sql tuning任务
conn test/test