Oracle11g Automatic Optimization

Oracle11g Automatic Optimization
--: Automatic Plan Optimization
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
Rochelle 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 = & gt; 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 SECTION
-------------------------------------------------------------------------------
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: 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". "DBTAN" has not been analyzed ".
Recommendation
--------------
-Collect the program statistics for the 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
---------
To select a good execution plan, the optimizer requires the latest statistics for this table.
2-Index Finding (see explain plans section below)
--------------------------------------------------
You can improve the execution plan of this statement by creating one or more indexes.
Recommendation (estimated benefit: 95.98%)
------------------------------------------
-Consider running access guides that can improve the design of physical solutions or create recommended indexes.
Create index HJ. IDX $ _ 00360001 on HJ. DBTAN ("OBJECT_TYPE ");
Rationale
---------
Creating recommended indexes can significantly improve the execution plan of this statement. However, run the "access guide" with a typical SQL workload"
It may be more desirable than a single statement. Using this method, you can obtain a comprehensive index proposal, including the overhead of index maintenance and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1-Original
-----------
Plan hash value: 1782547706
Bytes --------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------------------
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
2-Using New Indices
--------------------
Plan hash value: 2810514733
Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------------
| 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 |
Bytes -------------------------------------------------------------------------------------------------
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"
-------------------------------------------------------------------------------

Begin DBMS_SQLTUNE.drop_tuning_task (task_name => 'ft8s1pfmz9ph0d _ AWR_tuning_task '); end;