SQL Tuning Advisor 

1. Ran the below query.

   ---->   select * from apps.table1;
              => Query takes 5 minutes;

2. Taken SQL_ID of the above query

     --->  select sql_id , sql_fulltext from v$sql where sql_fulltext like '%table1%';

3.  Running Tuning Advisor from back-end

SET LONG 10000;
SET PAGESIZE 9999
SET LINESIZE 155
set verify off
col recommendations for a150
accept task_name -
prompt 'Task_Name: '
DECLARE
ret_val VARCHAR2(4000);
BEGIN
ret_val := dbms_sqltune.create_tuning_task(task_name=>'&&Task_name', sql_id=>'&sql_id', time_limit=>&time_limit);

dbms_sqltune.execute_tuning_task('&&Task_name');
END;
/
SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations FROM dual;
undef task_name

Place the above query in sql file and run as sysdba

SQL > @tune.sql

Task_Name: Tune_ggz303zm7fsb4

Enter value for sql_id: ggz303zm7fsb4

Enter value for time_limit: 60

PL/SQL procedure successfully completed.

RECOMMENDATIONS

------------------------------------------------------------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : Tune_ggz303zm7fsb4

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : INTERRUPTED

Started at         : 09/15/2016 11:17:48

Completed at       : 09/15/2016 11:18:56

O/P :

===

-------------------------------------------------------------------------------

Error: ORA-13639: The current operation was interrupted because it timed out.

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: SYS

SQL ID     : ggz303zm7fsb4

SQL Text   : select count(*) from xxfmit.XXFMAR_CFORM_INVOICES_V

-------------------------------------------------------------------------------

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<=10%)

  ---------------------------------------

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

            'Tune_ggz303zm7fsb4', task_owner => 'SYS', replace => TRUE);

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: 98.91%)

  ------------------------------------------

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index ONT.IDX$$_D1C40003 on ONT.OE_ORDER_HEADERS_ALL(TO_CHAR("ORDER_

    NUMBER"),"ORG_ID");

  Rationale

  ---------

    Creating the recommended indices significantly improves the execution plan

    of this statement. However, it might be preferable to run "Access Advisor"

    using a representative SQL workload as opposed to a single statement. This

    will allow to get comprehensive index recommendations which takes into

    account index maintenance overhead and additional space consumption.

SOLUTION :

=========

I have created an index as per recommendation see below.

create index ONT.IDX$$_D1C40003 on ONT.OE_ORDER_HEADERS_ALL(TO_CHAR("ORDER_

    NUMBER"),"ORG_ID");

Now the query ran time reduced to 2 Minutes.

:)