DBMS_SQLTUNE.REPORT_TUNING_TASK('sql5_604y8n0jfdr6u')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql5_604y8n0jfdr6u
Tuning Task Owner : SYS
Workload Type : Single SQL
Statement
Scope
: COMPREHENSIVE
Time Limit(seconds): 600
Completion Status : COMPLETED
Started at :
06/21/2017 12:11:59
Completed at : 06/21/2017
12:20:14
-------------------------------------------------------------------------------
Schema Name: Scott
SQL ID : 604y8n0jfdr6u
SQL Text : select * from table1
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans
section below)
--------------------------------------------------------
A potentially better execution plan was
found for this statement.
Recommendation (estimated benefit:
63.54%)
------------------------------------------
- Consider accepting the recommended
SQL profile.
execute
dbms_sqltune.accept_sql_profile(task_name =>
'sql5_604y8n0jfdr6u', 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 (s):
.261283 .115704
55.71 %
CPU Time (s):
.258624
.109908 57.5 %
User I/O Time (s):
0
0
Buffer Gets:
88828
32377 63.55 %
Physical Read Requests:
0
0
Physical Write Requests:
0
0
Physical Read Bytes:
0
0
Physical Write Bytes:
0
0
Rows Processed:
7633
7633
Fetches:
7633
7633
Executions:
1
1
Notes
-----
1. Statistics for the original plan
were averaged over 4 executions.
2. Statistics for the SQL profile plan
were averaged over 9 executions.
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:
82.5%)
-----------------------------------------
- Consider running the Access Advisor
to improve the physical schema design
or creating the recommended
index. If you choose to create the
recommended index, consider
dropping the index "SCOTT"."AVRPP_FK_I"
because it is a prefix of the
recommended index.
create index SCOTT.IDX$$_2075701 on CRAMMER.TABLE_O
("RPPLANID","TABLE2RELATION","TABLE2DIMOBJECT","TABLEID");
- Consider running the Access Advisor
to improve the physical schema design
or creating the recommended
index.
create index SCOTT.IDX$$_207570002 on
SCOTT.TABLE2_R("TABLE22OBJECTTWO","TABLE22OBJECTONE");
- Consider running the Access Advisor
to improve the physical schema design
or creating the recommended
index.
create index
SCOTT.IDX$$_207570003 on SCOTT.TABLE_ALL_O("EMP");
- Consider running the Access Advisor
to improve the physical schema design
or creating the recommended
index.
create index SCOTT.IDX$$_207570004 on SCOTT.NODE123_O("NODE123ID");
CONT.....
Reference :-
Oracle Support
