sql最佳化：使用sql profile最佳化sql語句
使用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|
---------------------------------------------------------------------
對於複雜的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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用SQL Profile進行SQL最佳化案例SQL
- 最佳化sql語句SQL
- sql語句的最佳化SQL
- 效能最佳化之SQL語句最佳化SQL
- SQL Profiles與語句最佳化SQL
- SQL語句運算子最佳化SQL
- SQL語句最佳化技術分析SQL
- ORACLE常用SQL最佳化hint語句OracleSQL
- 資料庫最佳化技巧 - SQL語句最佳化資料庫SQL
- 對sql語句的最佳化問題SQL
- Oracle SQL語句最佳化技術分析OracleSQL
- straight_join最佳化sql語句AISQL
- oracle 通過sql profile為sql語句加hintOracleSQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- MySQL的SQL語句最佳化一例MySql
- Effective MySQL之SQL語句最佳化 小結MySql
- SQL最佳化案例-單表分頁語句的最佳化（八）SQL
- 透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- [轉]透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- sql最佳化SQL
- sql profile使用SQL
- 使用sql tuning advisor最佳化sqlSQL
- 【SQL最佳化】SQL最佳化的10點注意事項SQL
- 【SQL】10 SQL UPDATE 語句SQL
- 【SQL】11 SQL DELETE 語句SQLdelete
- SQL最佳化1SQL
- PL/SQL最佳化SQL
- sql最佳化（mysql）MySql
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- ORACLE SQL PROFILE使用OracleSQL
- sql profile的使用SQL
- SQL語句SQL
- Sql最佳化(二十二) 自動調優工具：sql tuning advisor和sql profile介紹SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- 第45期：一條 SQL 語句最佳化的基本思路SQL
- sql 中的with 語句使用SQL
- 【SQL】9 SQL INSERT INTO 語句SQL
- SQL最佳化案例-使用with as最佳化Subquery Unnesting（七）SQL