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.
:)
