source
Find the SQL Id from AWR or ADDM/OEM session .
1)
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '2utumtd0rxyjs',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '2utumtd0rxyjs_tuning_task11',
description => 'Tuning task1 for statement 2utumtd0rxyjs');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
2)
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2utumtd0rxyjs_tuning_task11');
3)
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('2utumtd0rxyjs_tuning_task11') from dual;
4)
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;
5)
execute dbms_sqltune.drop_tuning_task('6rrj026359w6w_tuning_task11');
-------Example :
Last login: Thu Apr 26 16:17:29 2018 from 172.20.210.31
[root@smssvr ~]# su - oracle
[oracle@smssvr ~]$ sqlplus smsuser/smsuser
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 9 13:45:32 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '2utumtd0rxyjs',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '2utumtd0rxyjs_tuning_task11',
description => 'Tuning task1 for statement 2utumtd0rxyjs');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/ 2 3 4 5 6 7 8 9 10 11 12
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2utumtd0rxyjs_tuning_task11');
PL/SQL procedure successfully completed.
SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('2utumtd0rxyjs_tuning_task11') from dual;SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 2utumtd0rxyjs_tuning_task11
Tuning Task Owner : SMSUSER
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 500
Completion Status : COMPLETED
Started at : 07/09/2018 13:46:41
Completed at : 07/09/2018 13:46:41
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SMSUSER
SQL ID : 2utumtd0rxyjs
SQL Text : select * from (select * from PBL_SMS_MSG where MSG_SENT_FLAG='0'
and MOD(SMS_ID,5)= 2 order by SMS_ID asc) temp_tbl WHERE rownum
<= 50 ORDER BY rownum
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 96.51%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
'2utumtd0rxyjs_tuning_task11', task_owner => 'SMSUSER', replace
=> TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
Executing this query parallel with DOP 32 will improve its response time
96.51% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.55% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
2- 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: 96.53%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SMSUSER.IDX$$_0D3B0001 on SMSUSER.PBL_SMS_MSG("SMS_ID");
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
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.
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 3 of the execution plan.
The optimizer cannot merge a view that contains an "ORDER BY" clause unless
the statement is a "DELETE" or an "UPDATE" and the parent query is the top
most query in the statement.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
-----------
Plan hash value: 1665088640
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 20750 | 11890 (1)| 00:02:23 |
| 1 | SORT ORDER BY | | 50 | 20750 | 11890 (1)| 00:02:23 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 236 | 97940 | 11889 (1)| 00:02:23 |
|* 4 | SORT ORDER BY STOPKEY| | 236 | 54516 | 11889 (1)| 00:02:23 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
|* 5 | TABLE ACCESS FULL | PBL_SMS_MSG | 236 | 54516 | 11888 (1)| 00:02:23 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=50)
4 - filter(ROWNUM<=50)
5 - filter("MSG_SENT_FLAG"=0 AND MOD("SMS_ID",5)=2)
2- Using New Indices
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
--------------------
Plan hash value: 1607373448
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 20750 | 412 (1)| 00:00:05 |
| 1 | SORT ORDER BY | | 50 | 20750 | 412 (1)| 00:00:05 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 51 | 21165 | 411 (1)| 00:00:05 |
|* 4 | TABLE ACCESS BY INDEX ROWID| PBL_SMS_MSG | 4711 | 1062K| 411 (1)| 00:00:05 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
|* 5 | INDEX FULL SCAN | IDX$$_0D3B0001 | 2882 | | 38 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=50)
4 - filter("MSG_SENT_FLAG"=0)
5 - filter(MOD("SMS_ID",5)=2)
3- Using Parallel Execution
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
---------------------------
Plan hash value: 1687965353
----------------------------------------------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ
|IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 50 | 20750 | 414 (1)| 00:00:05 |
| | |
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 50 | 20750 | 414 (1)| 00:00:05 |
| | |
|* 2 | COUNT STOPKEY | | | | | |
| | |
| 3 | PX COORDINATOR | | | | | |
| | |
| 4 | PX SEND QC (ORDER) | :TQ10001 | 236 | 97940 | 413 (1)| 00:00:05 | Q1,01
| P->S | QC (ORDER) |
| 5 | VIEW | | 236 | 97940 | 413 (1)| 00:00:05 | Q1,01
| PCWP | |
|* 6 | SORT ORDER BY STOPKEY | | 236 | 54516 | 413 (1)| 00:00:05 | Q1,01
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
| PCWP | |
| 7 | PX RECEIVE | | 50 | 20750 | 414 (1)| 00:00:05 | Q1,01
| PCWP | |
| 8 | PX SEND RANGE | :TQ10000 | 50 | 20750 | 414 (1)| 00:00:05 | Q1,00
| P->P | RANGE |
|* 9 | SORT ORDER BY STOPKEY| | 50 | 20750 | 414 (1)| 00:00:05 | Q1,00
| PCWP | |
| 10 | PX BLOCK ITERATOR | | 236 | 54516 | 412 (0)| 00:00:05 | Q1,00
| PCWC | |
|* 11 | TABLE ACCESS FULL | PBL_SMS_MSG | 236 | 54516 | 412 (0)| 00:00:05 | Q1,00
| PCWP | |
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=50)
6 - filter(ROWNUM<=50)
9 - filter(ROWNUM<=50)
11 - filter("MSG_SENT_FLAG"=0 AND MOD("SMS_ID",5)=2)
DBMS_SQLTUNE.REPORT_TUNING_TASK('2UTUMTD0RXYJS_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
