--: Automatic tuning Program
Begin
Dbms_workload_repository.create_snapshot ();
End
Select/*+ Result_cache */COUNT (*) from (SELECT * from Hj.dbtan where object_type= ' TABLE ' ORDER by 1 desc, 2 ASC, 3 desc , 4 ASC);
Begin
Dbms_workload_repository.create_snapshot ();
End
--ft8s1pfmz9ph0
SET Serveroutput on
DECLARE
l_sql_tune_task_id VARCHAR2 (100);
BEGIN
l_sql_tune_task_id: = Dbms_sqltune.create_tuning_task (
Begin_snap = 21,
End_snap = 22,
sql_id = ' Ft8s1pfmz9ph0 ',
Scope = Dbms_sqltune.scope_comprehensive,
Time_limit = 300,
Task_name = ' Ft8s1pfmz9ph0d_awr_tuning_task ',
Description = ' Tuning task for statement f3hc7r4trnn1d in AWR. ');
Dbms_output.put_line (' l_sql_tune_task_id: ' | | l_sql_tune_task_id);
END;
Begin Dbms_sqltune.execute_tuning_task (task_name = ' ft8s1pfmz9ph0d_awr_tuning_task '); end;
SELECT dbms_sqltune.report_tuning_task (' Ft8s1pfmz9ph0d_awr_tuning_task ') as recommendations from dual;
General Information Sections
-------------------------------------------------------------------------------
Tuning Task Name:ft8s1pfmz9ph0d_awr_tuning_task
Tuning Task Owner:sys
Workload Type:single SQL Statement
Scope:comprehensive
Time Limit (seconds): 300
Completion status:completed
Started at:09/01/2014 14:55:32
Completed at:09/01/2014 14:55:44
-------------------------------------------------------------------------------
Schema Name:sys
SQL Id:ft8s1pfmz9ph0
SQL text:select/*+ Result_cache */COUNT (*) from (SELECT * FROM
Hj.dbtan where object_type= ' TABLE ' ORDER by 1 DESC, 2 ASC, 3
DESC, 4 ASC)
-------------------------------------------------------------------------------
Findings section (2 findings)
-------------------------------------------------------------------------------
1-statistics finding
---------------------
The table "HJ" has not yet been parsed. " Dbtan ".
Recommendation
--------------
-Consider collecting optimizer statistics for this table.
Execute dbms_stats.gather_table_stats (ownname = ' HJ ', tabname =
' Dbtan ', estimate_percent = dbms_stats. Auto_sample_size,
Method_opt = ' For all COLUMNS SIZE AUTO ');
Rationale
---------
In order to select a good execution plan, the optimizer needs the most up-to-date statistics for this table.
2-index finding (see Explain plans section below)
--------------------------------------------------
You can improve the execution plan for this statement by creating one or more indexes.
Recommendation (estimated benefit:95.98%)
------------------------------------------
-Consider running to improve access guidance for physical schema design or create a recommended index.
CREATE INDEX HJ. idx$$_00360001 on HJ. Dbtan ("object_type");
Rationale
---------
Creating a recommended index can significantly improve the execution plan for this statement. However, use typical SQL workloads to run the "Access Guide"
may be preferable than a single statement. This approach allows for comprehensive index recommendations, including the overhead of calculating index maintenance and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN Plans Section
-------------------------------------------------------------------------------
1-original
-----------
Plan Hash value:1782547706
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 4706 (1) | 00:00:57 |
| 1 | RESULT CACHE | 81z3k6zbauk9s2c83c03s270ja | | | | |
| 2 | SORT AGGREGATE | | 1 | 11 | | |
|* 3 | TABLE ACCESS full| Dbtan | 40006 | 429k| 4706 (1) | 00:00:57 |
--------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
3-filter ("object_type" = ' TABLE ')
Result Cache Information (identified by Operation ID):
------------------------------------------------------
1-column-count=1; Dependencies= (HJ. Dbtan); Attributes= (Single-row); Parameters= (NLS); Name= "SELECT/*+ Result_cache */COUNT (*) from (SELECT * from Hj.dbtan where object_type= ' TABLE ' ORDER by 1 DESC, 2 ASC, 3 DESC, 4 as
2-using New Indices
--------------------
Plan Hash value:2810514733
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 189 (1) | 00:00:03 |
| 1 | RESULT CACHE | 27yjysxpdun18b2utun82bynny | | | | |
| 2 | SORT AGGREGATE | | 1 | 11 | | |
|* 3 | INDEX RANGE scan| idx$$_00360001 | 40006 | 429k| 189 (1) | 00:00:03 |
-------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
3-access ("object_type" = ' TABLE ')
Result Cache Information (identified by Operation ID):
------------------------------------------------------
1-column-count=1; Dependencies= (HJ. Dbtan); Attributes= (Single-row, ordered); Parameters= (NLS); Name= "SELECT/*+ Result_cache */COUNT (*) from (SELECT * from Hj.dbtan where object_type= ' TABLE ' ORDER by 1 DESC, 2 ASC, 3 DESC, 4 as "
-------------------------------------------------------------------------------
Begin Dbms_sqltune.drop_tuning_task (task_name = ' ft8s1pfmz9ph0d_awr_tuning_task '); end;
Oracle 11g Automatic Tuning
