Steps to perform performance tuning in oracle

Introduction to SQL Tuning

An important facet of database system performance tuning is the tuning of SQL statements. SQL tuning involves three basic steps:

• Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources, by reviewing past SQL execution history available in the system.
• Verifying that the execution plans produced by the query optimizer for these statements perform reasonably.
• Implementing corrective actions to generate better execution plans for poorly performing SQL statements.

Goals for Tuning

The objective of tuning a system is either to reduce the response time for end users of the system, or to reduce the resources used to process the same work. You can accomplish both of these objectives in several ways:

• Reduce the Workload
• Balance the Workload
• Parallelize the Workload

I)  First step is to create the tuning task.

You can create tuning tasks from the following:

-  SQL statement selected by SQL identifier from the cursor cache
-  SQL Tuning Set containing multiple statements
-  Text of a single SQL statement
-  SQL statement selected by SQL identifier from the Automatic Workload Repository.

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                           sql_id =>'604y8n0jfdr6u',
                           scope => 'COMPREHENSIVE',
                           time_limit => 600,
                           task_name => 'sql5_604y8n0jfdr6u',
                           description => 'Task to tune a query');
END;

II)  Execute the tuning task.

Begin
dbms_sqltune.Execute_tuning_task (task_name =>'sql5_604y8n0jfdr6u');
end;

III)  You can check the status of the tas using following query:

select * from dba_advisor_log where task_name like'%604y8n0jfdr6u%';

IV)  View the Recommendation

set long 100000
set linesize 5000
set pages 4000
select dbms_sqltune.report_tuning_task('sql5_604y8n0jfdr6u') from dual;

OUTPUT:

Output will gives you recommendation. Test that recommendation on lower environment . If successful then perform on productions.

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