I have taken the sql id of a particular query as follows:
select sql_id, sql_text from v$sql where sql_text like '%alarm%';
Using the sql_id above, I have created a SQL tuning set for a particular sql statement as below:
DECLARE
tune_task VARCHAR2(30);
BEGIN
tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '3hay9aw39zpma', task_name => 'tune_test2', description => 'Provide sql ID');
end;
/
I have then executed the tuning task created above as below:
exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test2');
I have viewed the Report of the above execution as below:
set long 10000 longchunksize 10000
set linesize 132 pagesize 200
select dbms_sqltune.report_tuning_task('tune_test2') from dual;
Now the report is as follows:
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_TEST2')
------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tune_test2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/20/2014 06:20:00
Completed at : 03/20/2014 06:20:01
-------------------------------------------------------------------------------
Schema Name: OMCDBSYS
SQL ID : 3hay9aw39zpma
SQL Text : SELECT alarmId, managedObjectClass, managedObjectInsta,
eventType, eventTime, probableCauseOID, additionalText,
specificProblemsID, perceivedSeverity, trendIndication,
triggeredThreshold, observedValue, thresholdLevel,
notificationIdenti, proposedRepairActi, sbsOriginator,
sbsOtherInfo, sbsSwOrFileVersion, symbolicName, operatorInfo,
operatorName, acknowledgementTim, alarmCategoryLevel,
alignmentFlag, entryTime, ceasedTime, omcregionid, alarmState,
technology, unackTime, unackOperator, sequencenumber,
probablecause, specificproblems, manualkey FROM ACTIVEALARMS
WHERE :"SYS_B_0"=:"SYS_B_1" AND alarmId IN
(:"SYS_B_2",:"SYS_B_3",:"SYS_B_4") AND operatorName =
:"SYS_B_5" AND acknowledgementTim = TO_DATE(:"SYS_B_6")
-------------------------------------------------------------------------------
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: 95.61%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2',
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
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(us): 3087 197 93.61 %
CPU Time(us): 3000 0 100 %
User I/O Time(us): 0 0
Buffer Gets: 91 4 95.6 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 0 0
Fetches: 0 0
Executions: 1 1
Notes
-----
1. The original plan was first executed to warm the buffer cache.
2. Statistics for original plan were averaged over next 9 executions.
3. The SQL profile plan was first executed to warm the buffer cache.
4. Statistics for the SQL profile plan were averaged over next 9 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2906863065
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 311 | 26 (4)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 311 | 26 (4)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 265 | 25 (4)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | FMG_0 | 1 | 184 | 24 (5)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PROBABLECAUSE | 1 | 81 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PROBABLE_PRI | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | SPECIFICPROBLEMS | 1 | 46 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SPECIFIC_PRI | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:SYS_B_0=:SYS_B_1)
4 - filter("FMG"."OPERATORNAME"=:SYS_B_5 AND ("FMG"."ALARMID"=:SYS_B_4 OR
"FMG"."ALARMID"=:SYS_B_3 OR "FMG"."ALARMID"=:SYS_B_2) AND
"FMG"."ACKNOWLEDGEMENTTIM"=TO_DATE(:SYS_B_6))
6 - access("FMG"."PROBABLECAUSEOID"="PROB"."PROBABLECAUSEOID"(+))
8 - access("FMG"."SPECIFICPROBLEMSID"="SPEC"."ERRORID"(+))
2- Using SQL Profile
--------------------
Plan hash value: 3739326896
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 311 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 311 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 265 | 2 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| FMG_0 | 1 | 184 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | FMG0_PK | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | PROBABLECAUSE | 1 | 81 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PROBABLE_PRI | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | SPECIFICPROBLEMS | 1 | 46 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SPECIFIC_PRI | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:SYS_B_0=:SYS_B_1)
5 - filter("FMG"."OPERATORNAME"=:SYS_B_5 AND
"FMG"."ACKNOWLEDGEMENTTIM"=TO_DATE(:SYS_B_6))
6 - access("FMG"."ALARMID"=:SYS_B_2 OR "FMG"."ALARMID"=:SYS_B_3 OR
"FMG"."ALARMID"=:SYS_B_4)
8 - access("FMG"."PROBABLECAUSEOID"="PROB"."PROBABLECAUSEOID"(+))
10 - access("FMG"."SPECIFICPROBLEMSID"="SPEC"."ERRORID"(+))
-------------------------------------------------------------------------------
In the Findings section, we could see the following:
Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2',
task_owner => 'SYS', replace => TRUE);
What would be in this profile. How to see the recommendations this profile provides for the query.
Thanks in advance.