使用sql profile优化sql语句
对于复杂的SQL语句优化，可以借助sql 自动优化顾问生成sql profile，以下介绍使用sql优化顾问生成sql profile优化sql语句
1.低效的sql语句
需有优化的sql语句
update DATAUPLOAD a
set a.d502_1 =
(select count(1)
from D502341222
where INPUT_DATE between
to_date('2015-08-30', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2015-08-31', 'yyyy-mm-dd hh24:mi:ss'))
where a.sareacode = '341222';
2.优化前执行计划
查看优化前SQL执行计划
select * from table(dbms_xplan.display_awr('c7y7hxp8gtn48'));
Plan hash value: 3868656972
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 11 | 573K (2)| 01:54:38 |
| 1 | UPDATE | DATAUPLOAD | | | | |
|* 2 | INDEX UNIQUE SCAN | PK_DATAUPLOAD | 1 | 11 | 0 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
| 3 | SORT AGGREGATE | | 1 | 8 | | |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| D502341222 | 11 | 88 | 573K (2)| 01:54:38 |
------------------------------------------------------------------------------------
3.执行自动优化任务
declare
my_task_name varchar2(30);
begin
--dbms_sqltune.drop_tuning_task(task_name => 'test_sql_tuning_task');
my_task_name := dbms_sqltune.create_tuning_task(
begin_snap => 23230, --awr报告开始snap
end_snap => 23236, --awr报告结束snap
sql_id => 'c7y7hxp8gtn48',
plan_hash_value => null,
scope => 'comprehensive',
time_limit => 600,
task_name => 'test_sql_tuning_task',
description => 'tune the bad sql'
);
dbms_sqltune.execute_tuning_task (task_name => 'test_sql_tuning_task');
end;
/
eg:
SQL> declare
2 my_task_name varchar2(30);
3 begin
4 --dbms_sqltune.drop_tuning_task(task_name => 'test_sql_tuning_task');
5 my_task_name := dbms_sqltune.create_tuning_task(
6 begin_snap => 23230,
7 end_snap => 23236,
8 sql_id => 'c7y7hxp8gtn48',
9 plan_hash_value => null,
10 scope => 'comprehensive',
11 time_limit => 600,
12 task_name => 'test_sql_tuning_task',
13 description => 'tune the bad sql'
14 );
15 dbms_sqltune.execute_tuning_task (task_name => 'test_sql_tuning_task');
16 end;
17 /
PL/SQL procedure successfully completed.
4.查看结果
set long 99999
col comments format a180
select dbms_sqltune.report_tuning_task('test_sql_tuning_task') from dual;
eg:
SQL> set long 99999
SQL> col comments format a180
SQL> select dbms_sqltune.report_tuning_task('test_sql_tuning_task') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test_sql_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status : COMPLETED
Started at : 09/02/2015 15:41:19
Completed at : 09/02/2015 15:43:52
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SJPT
SQL ID : c7y7hxp8gtn48
SQL Text : update DATAUPLOAD a set a.d502_1=
(select count(1) from D502341222 where INPUT_DATE
between
to_date('2015-08-30','yyyy-mm-dd hh24:mi:ss') and
to_date('2015-08-31', 'yyyy-mm-dd hh24:mi:ss'))
where a.sareacode='341222'
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
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<=10%)
---------------------------------------
- Consider accepting the recommended SQL profile.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
execute dbms_sqltune.accept_sql_profile(task_name =>
'test_sql_tuning_task', 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
------------- ---------------- ----------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000031 .000024 22.58 %
CPU Time (s): .001 0 100 %
User I/O Time (s): 0 0
Buffer Gets: 1 1 0 %
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
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3868656972
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 11 | 573K (2)| 01:54:38 |
| 1 | UPDATE | DATAUPLOAD | | | | |
|* 2 | INDEX UNIQUE SCAN | PK_DATAUPLOAD | 1 | 11 | 0 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
| 3 | SORT AGGREGATE | | 1 | 8 | | |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| D502341222 | 11 | 88 | 573K (2)| 01:54:38 |
------------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SAREACODE"='341222')
4 - filter(TO_DATE('2015-08-30','yyyy-mm-dd
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
hh24:mi:ss')<=TO_DATE('2015-08-31','yyyy-mm-dd hh24:mi:ss'))
5 - filter("INPUT_DATE">=TO_DATE('2015-08-30','yyyy-mm-dd hh24:mi:ss') AND
"INPUT_DATE"<=TO_DATE('2015-08-31','yyyy-mm-dd hh24:mi:ss'))
2- Using SQL Profile
--------------------
Plan hash value: 821387520
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 11 | 1 |
| 1 | UPDATE | DATAUPLOAD | | | |
|* 2 | INDEX UNIQUE SCAN | PK_DATAUPLOAD | 1 | 11 | |
| 3 | SORT AGGREGATE | | 1 | 8 | |
|* 4 | TABLE ACCESS FULL| D502341222 | 11 | 88 | 316K|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SAREACODE"='341222')
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
4 - filter("INPUT_DATE">=TO_DATE('2015-08-30','yyyy-mm-dd
hh24:mi:ss') AND "INPUT_DATE"<=TO_DATE('2015-08-31','yyyy-mm-dd
hh24:mi:ss'))
-------------------------------------------------------------------------------
SQL>
5.接受sql profile
execute dbms_sqltune.accept_sql_profile(task_name => 'test_sql_tuning_task', task_owner => 'SYS', replace => TRUE);
eg:
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'test_sql_tuning_task', task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.
SQL>
6.再次查看执行计划
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 11 | 1 |
| 1 | UPDATE | DATAUPLOAD | | | |
|* 2 | INDEX UNIQUE SCAN | PK_DATAUPLOAD | 1 | 11 | |
| 3 | SORT AGGREGATE | | 1 | 8 | |
|* 4 | TABLE ACCESS FULL| D502341222 | 11 | 88 | 316K|
---------------------------------------------------------------------
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27571661/viewspace-1789522/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/27571661/viewspace-1789522/