檢視自動sql調優作業，最佳化sql訪問路徑
1.自動sql調優作業
1.1 檢查sql自動作業是否啟用
select client_name,status,consumer_group
from dba_autotask_client
order by client_name;
eg:
SQL> col client_name for a80
SQL> set linesize 400
SQL> select client_name,status,consumer_group
2 from dba_autotask_client
3 order by client_name;
CLIENT_NAME STATUS CONSUMER_GROUP
-------------------------------------------------------------------------------- -------- ------------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP
sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP
1.2 檢視維護視窗詳細資訊
select window_name,to_char(window_next_time,'dd-mm-yy hh24:mi:ss')
,sql_tune_advisor,optimizer_stats,segment_advisor
from dba_autotask_window_clients;
eg:
QL> select window_name,to_char(window_next_time,'dd-mm-yy hh24:mi:ss')
2 ,sql_tune_advisor,optimizer_stats,segment_advisor
3 from dba_autotask_window_clients;
WINDOW_NAME TO_CHAR(WINDOW_NE SQL_TUNE OPTIMIZE SEGMENT_
------------------------------ ----------------- -------- -------- --------
MONDAY_WINDOW 17-08-15 22:00:00 ENABLED ENABLED ENABLED
TUESDAY_WINDOW 18-08-15 22:00:00 ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW 12-08-15 22:00:00 ENABLED ENABLED ENABLED
THURSDAY_WINDOW 13-08-15 22:00:00 ENABLED ENABLED ENABLED
FRIDAY_WINDOW 14-08-15 22:00:00 ENABLED ENABLED ENABLED
SATURDAY_WINDOW 15-08-15 06:00:00 ENABLED ENABLED ENABLED
SUNDAY_WINDOW 16-08-15 06:00:00 ENABLED ENABLED ENABLED
7 rows selected.
1.3 檢視自動SQL調優給出的建議
set linesize 80 pagesize 0 long 100000
select dbms_auto_sqltune.report_auto_tuning_task from dual;
eg:
oracle@hprac4[/home/oracle]$more sqlad1.txt
SQL> set linesize 80 pagesize 0 long 100000
SQL> select dbms_auto_sqltune.report_auto_tuning_task from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Execution Count : 30
Current Execution : EXEC_39112
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 08/11/2015 22:00:03
Completed at : 08/11/2015 22:22:58
Number of Candidate SQLs : 234
Cumulative Elapsed Time of SQL (s) : 233721
-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 234
Number of SQLs in the Report : 32
Number of SQLs with Findings : 32
Number of SQLs with SQL profiles recommended : 12
Number of SQLs with Index Findings : 29
-------------------------------------------------------------------------------
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID SQL ID statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
178208 a6aayrmg25a89 99.99%
178246 751yghaxx0bmn 99.99%
178317 9ys4u8vydcuhc 99.99%
178318 g3g4dpgsr3szs 99.99%
178321 12bph595sy0zj 99.99%
178322 ds4udjxf6bsfv 99.99%
178350 1hs50996guvrw 99.99%
178351 afqvfsg5wc6th 99.99%
178352 49m7ktc2ttqf1 99.99%
178358 3thpk91jyn8q0 99.99%
178366 0qnbfm3fmxmzf 99.99%
178373 6yrptpkuq53wy 99.99%
178374 6hk6x92zajdm4 99.99%
178393 a6d81qubv04ku 99.99%
178394 06fayxra859px 99.99%
178407 2ghh87hxfcxvc 99.99%
178408 amkfm2czx5tg3 99.99%
178409 a8hxx5ur2gk49 99.99%
178420 btngpv0f9zw1k 99.99%
178421 bqdkzympzx1ht 99.99%
178214 dd3bt8n8c0k74 97.84% 88.25%
178382 awtd2pfngyxv7 97.84% 89.94%
178222 cwzhk1xqxc7z5 97.75% 92.23%
178223 4yyvfh4w8fj6d 97.70% 97.75%
178379 dzuf1ykatp7mv 97.61% 97.75%
178232 dqmtsgsr8869q 97.55% 70.45%
178380 40f9c1jdznnvm 97.36% 50.22%
178229 dnntfaf3sjfy5 97.18% 82.22%
178202 cxdmtu3yzsq03 95.99% 55.68%
178227 7knrr307h6btw <=10.00%
178349 7pdmbksk2v8g7 <=10.00%
178391 2yqa2t68z87r8 <=10.00%
-------------------------------------------------------------------------------
Tables with New Potential Indices (ordered by schema, number of times, table)
-------------------------------------------------------------------------------
Schema Name Table Name Index Name Nb Time
--------------------------- --------------------------- -------------- --------
SJPT D506341226 IDX$$_00010001 9
D502341221 IDX$$_0001000F 3
D502341222 IDX$$_0001000A 3
D505340621 IDX$$_0001001C 3
D505341221 IDX$$_00010013 3
D502341225 IDX$$_0001001F 2
D505341222 IDX$$_00010002 2
D505341225 IDX$$_0001000C 2
D505341621 IDX$$_0001001A 2
-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 178208
Schema Name: SJPT
SQL ID : a6aayrmg25a89
SQL Text : update DATAUPLOAD a set a.d505_3=
(select count(1) from D505341222 where OPERATE_DATE
between
to_date('2015-08-10','yyyy-mm-dd hh24:mi:ss') and
to_date('2015-08-11', 'yyyy-mm-dd hh24:mi:ss'))
where a.sareacode='341222'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SJPT.IDX$$_00010002 on SJPT.D505341222("OPERATE_DATE");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1430004486
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| -------------------------------------------------------------------------
------------- | 0 | UPDATE STATEMENT | | 1 | 11 | 5
85K (2)| 01:57:06 | | 1 | UPDATE | DATAUPLOAD | |
| | | |* 2 | INDEX UNIQUE SCAN | PK_DATAUPLOAD | 1
| 11 | 0 (0)| 00:00:01 | | 3 | SORT AGGREGATE |
| 1 | 8 | | |
|* 4 | FILTER | | | | |
| |* 5 | TABLE ACCESS FULL| D505341222 | 10 | 80 | 585K (2
)| 01:57:06 | ------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SAREACODE"='341222')
4 - filter(TO_DATE('2015-08-10','yyyy-mm-dd
hh24:mi:ss')<=TO_DATE('2015-08-11','yyyy-mm-dd hh24:mi:ss'))
5 - filter("OPERATE_DATE">=TO_DATE('2015-08-10','yyyy-mm-dd hh24:mi:ss')
AND "OPERATE_DATE"<=TO_DATE('2015-08-11','yyyy-mm-dd hh24:mi:ss'))
2- Using New Indices
--------------------
Plan hash value: 3424034849
--------------------------------------------------------------------------------
------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time | ------------------------------------------------------------------
-------------------- | 0 | UPDATE STATEMENT | | 1 | 7
5 | 6 (17)| 00:00:01 | | 1 | UPDATE | DATAUPLOAD |
| | | |
|* 2 | INDEX UNIQUE SCAN | PK_DATAUPLOAD | 1 | 75 | 0 (0)| 00:0
0:01 | | 3 | SORT AGGREGATE | | 1 | 8 |
| | |* 4 | FILTER | | | |
| | |* 5 | INDEX RANGE SCAN| IDX$$_00010002 | 10 | 8
0 | 4 (0)| 00:00:01 | ----------------------------------------------------
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SAREACODE"='341222')
4 - filter(TO_DATE('2015-08-10','yyyy-mm-dd
hh24:mi:ss')<=TO_DATE('2015-08-11','yyyy-mm-dd hh24:mi:ss'))
5 - access("OPERATE_DATE">=TO_DATE('2015-08-10','yyyy-mm-dd hh24:mi:ss')
AND "OPERATE_DATE"<=TO_DATE('2015-08-11','yyyy-mm-dd hh24:mi:ss'))
1.4 生成調優指令碼
select task_name,execution_start from dba_advisor_log
where task_name='SYS_AUTO_SQL_TUNING_TASK'
order by 2;
eg:
SQL> select task_name,execution_start from dba_advisor_log
2 where task_name='SYS_AUTO_SQL_TUNING_TASK'
3 order by 2;
TASK_NAME EXECUTION_ST
------------------------------ ------------
SYS_AUTO_SQL_TUNING_TASK 11-AUG-15
SQL>
set linesize 132 pagesize 0 long 10000
select dbms_sqltune.script_tuning_task('SYS_AUTO_SQL_TUNING_TASK')
from dual;
eg:
SQL> set linesize 132 pagesize 0 long 10000
select dbms_sqltune.script_tuning_task('SYS_AUTO_SQL_TUNING_TASK')
SQL> 2 from dual;
-----------------------------------------------------------------
-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations --
-- made by the SQL tuning advisor. --
-- --
-- NOTE: this script may need to be edited for your system --
-- (index names, privileges, etc) before it is executed. --
-----------------------------------------------------------------
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178222, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178391, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178229, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178380, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178379, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178232, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178202, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178382, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178214, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178349, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178227, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178223, replace =>
TRUE);
create index SJPT.IDX$$_00010013 on SJPT.D505341221("OPERATE_DATE");
create index SJPT.IDX$$_0001001A on SJPT.D505341621("OPERATE_DATE");
create index SJPT.IDX$$_0001001B on SJPT.D505341621("OPERATE_DATE");
create index SJPT.IDX$$_00010003 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010004 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010007 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_0001000C on SJPT.D505341225("OPERATE_DATE");
create index SJPT.IDX$$_00010018 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_0001000A on SJPT.D502341222("INPUT_DATE");
create index SJPT.IDX$$_00010014 on SJPT.D505341221("OPERATE_DATE");
create index SJPT.IDX$$_00010002 on SJPT.D505341222("OPERATE_DATE");
create index SJPT.IDX$$_00010009 on SJPT.D505341222("OPERATE_DATE");
create index SJPT.IDX$$_0001001C on SJPT.D505340621("OPERATE_DATE");
create index SJPT.IDX$$_0001000D on SJPT.D505341225("OPERATE_DATE");
create index SJPT.IDX$$_00010016 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_0001001E on SJPT.D505340621("OPERATE_DATE");
create index SJPT.IDX$$_00010010 on SJPT.D502341221("INPUT_DATE");
create index SJPT.IDX$$_0001000F on SJPT.D502341221("INPUT_DATE");
create index SJPT.IDX$$_00010008 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010001 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_0001001D on SJPT.D505340621("OPERATE_DATE");
create index SJPT.IDX$$_00010017 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010005 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010015 on SJPT.D505341221("OPERATE_DATE");
create index SJPT.IDX$$_00010012 on SJPT.D502341222("INPUT_DATE");
create index SJPT.IDX$$_00010011 on SJPT.D502341221("INPUT_DATE");
create index SJPT.IDX$$_0001000B on SJPT.D502341222("INPUT_DATE");
create index SJPT.IDX$$_00010020 on SJPT.D502341225("INPUT_DATE");
create index SJPT.IDX$$_0001001F on SJPT.D502341225("INPUT_DATE");
2. SQL調優集
從AWR報告中檢視消耗資源多的SQL語句
----檢視快照-----
select snap_id from dba_hist_snapshot order by 1;
------8200,8201為快照號,查詢在此快照之間磁碟讀最多的前10語句----
select sql_id,substr(sql_text,1,200),disk_reads,
cpu_time,elapsed_time
from table(dbms_sqltune.select_workload_repository(8200,
8201,null,null,'disk_reads',null,null,null,10))
order by disk_reads desc;
1.1 檢查sql自動作業是否啟用
select client_name,status,consumer_group
from dba_autotask_client
order by client_name;
eg:
SQL> col client_name for a80
SQL> set linesize 400
SQL> select client_name,status,consumer_group
2 from dba_autotask_client
3 order by client_name;
CLIENT_NAME STATUS CONSUMER_GROUP
-------------------------------------------------------------------------------- -------- ------------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP
sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP
1.2 檢視維護視窗詳細資訊
select window_name,to_char(window_next_time,'dd-mm-yy hh24:mi:ss')
,sql_tune_advisor,optimizer_stats,segment_advisor
from dba_autotask_window_clients;
eg:
QL> select window_name,to_char(window_next_time,'dd-mm-yy hh24:mi:ss')
2 ,sql_tune_advisor,optimizer_stats,segment_advisor
3 from dba_autotask_window_clients;
WINDOW_NAME TO_CHAR(WINDOW_NE SQL_TUNE OPTIMIZE SEGMENT_
------------------------------ ----------------- -------- -------- --------
MONDAY_WINDOW 17-08-15 22:00:00 ENABLED ENABLED ENABLED
TUESDAY_WINDOW 18-08-15 22:00:00 ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW 12-08-15 22:00:00 ENABLED ENABLED ENABLED
THURSDAY_WINDOW 13-08-15 22:00:00 ENABLED ENABLED ENABLED
FRIDAY_WINDOW 14-08-15 22:00:00 ENABLED ENABLED ENABLED
SATURDAY_WINDOW 15-08-15 06:00:00 ENABLED ENABLED ENABLED
SUNDAY_WINDOW 16-08-15 06:00:00 ENABLED ENABLED ENABLED
7 rows selected.
1.3 檢視自動SQL調優給出的建議
set linesize 80 pagesize 0 long 100000
select dbms_auto_sqltune.report_auto_tuning_task from dual;
eg:
oracle@hprac4[/home/oracle]$more sqlad1.txt
SQL> set linesize 80 pagesize 0 long 100000
SQL> select dbms_auto_sqltune.report_auto_tuning_task from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Execution Count : 30
Current Execution : EXEC_39112
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 08/11/2015 22:00:03
Completed at : 08/11/2015 22:22:58
Number of Candidate SQLs : 234
Cumulative Elapsed Time of SQL (s) : 233721
-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 234
Number of SQLs in the Report : 32
Number of SQLs with Findings : 32
Number of SQLs with SQL profiles recommended : 12
Number of SQLs with Index Findings : 29
-------------------------------------------------------------------------------
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID SQL ID statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
178208 a6aayrmg25a89 99.99%
178246 751yghaxx0bmn 99.99%
178317 9ys4u8vydcuhc 99.99%
178318 g3g4dpgsr3szs 99.99%
178321 12bph595sy0zj 99.99%
178322 ds4udjxf6bsfv 99.99%
178350 1hs50996guvrw 99.99%
178351 afqvfsg5wc6th 99.99%
178352 49m7ktc2ttqf1 99.99%
178358 3thpk91jyn8q0 99.99%
178366 0qnbfm3fmxmzf 99.99%
178373 6yrptpkuq53wy 99.99%
178374 6hk6x92zajdm4 99.99%
178393 a6d81qubv04ku 99.99%
178394 06fayxra859px 99.99%
178407 2ghh87hxfcxvc 99.99%
178408 amkfm2czx5tg3 99.99%
178409 a8hxx5ur2gk49 99.99%
178420 btngpv0f9zw1k 99.99%
178421 bqdkzympzx1ht 99.99%
178214 dd3bt8n8c0k74 97.84% 88.25%
178382 awtd2pfngyxv7 97.84% 89.94%
178222 cwzhk1xqxc7z5 97.75% 92.23%
178223 4yyvfh4w8fj6d 97.70% 97.75%
178379 dzuf1ykatp7mv 97.61% 97.75%
178232 dqmtsgsr8869q 97.55% 70.45%
178380 40f9c1jdznnvm 97.36% 50.22%
178229 dnntfaf3sjfy5 97.18% 82.22%
178202 cxdmtu3yzsq03 95.99% 55.68%
178227 7knrr307h6btw <=10.00%
178349 7pdmbksk2v8g7 <=10.00%
178391 2yqa2t68z87r8 <=10.00%
-------------------------------------------------------------------------------
Tables with New Potential Indices (ordered by schema, number of times, table)
-------------------------------------------------------------------------------
Schema Name Table Name Index Name Nb Time
--------------------------- --------------------------- -------------- --------
SJPT D506341226 IDX$$_00010001 9
D502341221 IDX$$_0001000F 3
D502341222 IDX$$_0001000A 3
D505340621 IDX$$_0001001C 3
D505341221 IDX$$_00010013 3
D502341225 IDX$$_0001001F 2
D505341222 IDX$$_00010002 2
D505341225 IDX$$_0001000C 2
D505341621 IDX$$_0001001A 2
-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 178208
Schema Name: SJPT
SQL ID : a6aayrmg25a89
SQL Text : update DATAUPLOAD a set a.d505_3=
(select count(1) from D505341222 where OPERATE_DATE
between
to_date('2015-08-10','yyyy-mm-dd hh24:mi:ss') and
to_date('2015-08-11', 'yyyy-mm-dd hh24:mi:ss'))
where a.sareacode='341222'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SJPT.IDX$$_00010002 on SJPT.D505341222("OPERATE_DATE");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1430004486
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| -------------------------------------------------------------------------
------------- | 0 | UPDATE STATEMENT | | 1 | 11 | 5
85K (2)| 01:57:06 | | 1 | UPDATE | DATAUPLOAD | |
| | | |* 2 | INDEX UNIQUE SCAN | PK_DATAUPLOAD | 1
| 11 | 0 (0)| 00:00:01 | | 3 | SORT AGGREGATE |
| 1 | 8 | | |
|* 4 | FILTER | | | | |
| |* 5 | TABLE ACCESS FULL| D505341222 | 10 | 80 | 585K (2
)| 01:57:06 | ------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SAREACODE"='341222')
4 - filter(TO_DATE('2015-08-10','yyyy-mm-dd
hh24:mi:ss')<=TO_DATE('2015-08-11','yyyy-mm-dd hh24:mi:ss'))
5 - filter("OPERATE_DATE">=TO_DATE('2015-08-10','yyyy-mm-dd hh24:mi:ss')
AND "OPERATE_DATE"<=TO_DATE('2015-08-11','yyyy-mm-dd hh24:mi:ss'))
2- Using New Indices
--------------------
Plan hash value: 3424034849
--------------------------------------------------------------------------------
------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time | ------------------------------------------------------------------
-------------------- | 0 | UPDATE STATEMENT | | 1 | 7
5 | 6 (17)| 00:00:01 | | 1 | UPDATE | DATAUPLOAD |
| | | |
|* 2 | INDEX UNIQUE SCAN | PK_DATAUPLOAD | 1 | 75 | 0 (0)| 00:0
0:01 | | 3 | SORT AGGREGATE | | 1 | 8 |
| | |* 4 | FILTER | | | |
| | |* 5 | INDEX RANGE SCAN| IDX$$_00010002 | 10 | 8
0 | 4 (0)| 00:00:01 | ----------------------------------------------------
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SAREACODE"='341222')
4 - filter(TO_DATE('2015-08-10','yyyy-mm-dd
hh24:mi:ss')<=TO_DATE('2015-08-11','yyyy-mm-dd hh24:mi:ss'))
5 - access("OPERATE_DATE">=TO_DATE('2015-08-10','yyyy-mm-dd hh24:mi:ss')
AND "OPERATE_DATE"<=TO_DATE('2015-08-11','yyyy-mm-dd hh24:mi:ss'))
1.4 生成調優指令碼
select task_name,execution_start from dba_advisor_log
where task_name='SYS_AUTO_SQL_TUNING_TASK'
order by 2;
eg:
SQL> select task_name,execution_start from dba_advisor_log
2 where task_name='SYS_AUTO_SQL_TUNING_TASK'
3 order by 2;
TASK_NAME EXECUTION_ST
------------------------------ ------------
SYS_AUTO_SQL_TUNING_TASK 11-AUG-15
SQL>
set linesize 132 pagesize 0 long 10000
select dbms_sqltune.script_tuning_task('SYS_AUTO_SQL_TUNING_TASK')
from dual;
eg:
SQL> set linesize 132 pagesize 0 long 10000
select dbms_sqltune.script_tuning_task('SYS_AUTO_SQL_TUNING_TASK')
SQL> 2 from dual;
-----------------------------------------------------------------
-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations --
-- made by the SQL tuning advisor. --
-- --
-- NOTE: this script may need to be edited for your system --
-- (index names, privileges, etc) before it is executed. --
-----------------------------------------------------------------
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178222, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178391, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178229, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178380, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178379, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178232, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178202, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178382, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178214, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178349, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178227, replace =>
TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK', object_id => 178223, replace =>
TRUE);
create index SJPT.IDX$$_00010013 on SJPT.D505341221("OPERATE_DATE");
create index SJPT.IDX$$_0001001A on SJPT.D505341621("OPERATE_DATE");
create index SJPT.IDX$$_0001001B on SJPT.D505341621("OPERATE_DATE");
create index SJPT.IDX$$_00010003 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010004 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010007 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_0001000C on SJPT.D505341225("OPERATE_DATE");
create index SJPT.IDX$$_00010018 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_0001000A on SJPT.D502341222("INPUT_DATE");
create index SJPT.IDX$$_00010014 on SJPT.D505341221("OPERATE_DATE");
create index SJPT.IDX$$_00010002 on SJPT.D505341222("OPERATE_DATE");
create index SJPT.IDX$$_00010009 on SJPT.D505341222("OPERATE_DATE");
create index SJPT.IDX$$_0001001C on SJPT.D505340621("OPERATE_DATE");
create index SJPT.IDX$$_0001000D on SJPT.D505341225("OPERATE_DATE");
create index SJPT.IDX$$_00010016 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_0001001E on SJPT.D505340621("OPERATE_DATE");
create index SJPT.IDX$$_00010010 on SJPT.D502341221("INPUT_DATE");
create index SJPT.IDX$$_0001000F on SJPT.D502341221("INPUT_DATE");
create index SJPT.IDX$$_00010008 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010001 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_0001001D on SJPT.D505340621("OPERATE_DATE");
create index SJPT.IDX$$_00010017 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010005 on SJPT.D506341226("REDEEM_DATE");
create index SJPT.IDX$$_00010015 on SJPT.D505341221("OPERATE_DATE");
create index SJPT.IDX$$_00010012 on SJPT.D502341222("INPUT_DATE");
create index SJPT.IDX$$_00010011 on SJPT.D502341221("INPUT_DATE");
create index SJPT.IDX$$_0001000B on SJPT.D502341222("INPUT_DATE");
create index SJPT.IDX$$_00010020 on SJPT.D502341225("INPUT_DATE");
create index SJPT.IDX$$_0001001F on SJPT.D502341225("INPUT_DATE");
2. SQL調優集
從AWR報告中檢視消耗資源多的SQL語句
----檢視快照-----
select snap_id from dba_hist_snapshot order by 1;
------8200,8201為快照號,查詢在此快照之間磁碟讀最多的前10語句----
select sql_id,substr(sql_text,1,200),disk_reads,
cpu_time,elapsed_time
from table(dbms_sqltune.select_workload_repository(8200,
8201,null,null,'disk_reads',null,null,null,10))
order by disk_reads desc;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27571661/viewspace-1781823/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 真實世界SQL最佳化案例2_訪問路徑最佳化SQL
- oracle sql tuning 10 理解優化器訪問路徑OracleSQL優化
- SQL訪問路徑和表連線SQL
- oracle sql tuning 9--理解優化器訪問路徑OracleSQL優化
- Sql最佳化(二十二) 自動調優工具：sql tuning advisor和sql profile介紹SQL
- Sql最佳化(十九) 調優工具（2）sql_traceSQL
- SQL效能第2篇：查詢分析和訪問路徑制定SQL
- SQL調優SQL
- 【sql調優】動態取樣SQL
- 檢視SQL Server 效能優化工具SQLServer優化
- SQL調優13連問，收藏好！SQL
- 使用SQL調整顧問得到SQL優化建議SQL優化
- 【最佳化】INDEX FULL SCAN (MIN/MAX)訪問路徑Index
- SQL檢視SQL
- sql調優1SQL
- oracle sql調優OracleSQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- Oracle 訪問路徑Oracle
- 在EM中使用SQL Access Advisor（SQL訪問建議）優化SQLSQL優化
- 【優化】INDEX FULL SCAN (MIN/MAX)訪問路徑優化Index
- Teradata SQL調優SQL
- SQL優化一則：取消檢視合併SQL優化
- Oracle 調優確定存在問題的SQLOracleSQL
- SQL Server一次SQL調優案例SQLServer
- SQL Server 檔案路徑SQLServer
- 【SQL】17 SQL 檢視（Views）、SQL Date 函式、SQL NULL 值、SQLView函式Null
- SQL最佳化問題SQL
- T-SQL檢查停止的複製作業代理，並啟動SQL
- MySQL調優篇 | SQL調優實戰（5）MySql
- sql-server檢視SQLServer
- Oracle檢視TOP SQLOracleSQL
- Sql Server系列：檢視SQLServer
- Oracle SQL調優系列之SQL Monitor ReportOracleSQL
- MySQL索引和SQL調優MySql索引
- SQL調優真實案例SQL
- SQL效能調優綜述SQL
- [精華zt] SQL調優整理SQL
- “no_merge”hints優化檢視訪問低效問題優化