Concurrent request running abnormally for very long time without completion

1. Identified the long running query by taking trace of the concurrent request
2. Executed the tuning advisor for the problematic query using the following steps


Create Tuning Task:
===============
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ‘0j3dqg3a8aj8c’,
scope => ‘COMPREHENSIVE’,
time_limit => 3600,
task_name => ‘my_sql_tuning_task_1’,
description => ‘Tune query using sqlid’);
end;
/


Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_1’);
end;
/




SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100


–***************************
–TO GET SUMMARY INFORMATION
–***************************


SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task_1’) from DUAL;


–***************************
–TO GET DETAILED INFORMATION
–***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘my_sql_tuning_task_1′,’TEXT’,’ALL’,’ALL’) FROM DUAL;


Based on the output of the tuning advisor mentioned below( Points 1 to 9), we gathered the statistics for the stale indexes, with estimate of 99%. The request which was running normal without completion for more than 12 hours completed in 20 minutes.


——————————————————————————-
FINDINGS SECTION (11 findings)
——————————————————————————-


1- Statistics Finding
    ————————–
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_N9″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_N9′, estimate_percent =>99);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


2- Statistics Finding
    ————————–
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_N3″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_N3′, estimate_percent =>99);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


3- Statistics Finding
    ————————–
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_N8″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_N8′, estimate_percent =>99);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


4- Statistics Finding
    ————————–
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_N2″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_N2′, estimate_percent =>99);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


5- Statistics Finding
    ————————–
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_U1″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_U1′, estimate_percent =>99);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


6- Statistics Finding
    ————————-
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_N7″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_N7′, estimate_percent =>99);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


7- Statistics Finding
    ————————-
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_N6″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_N6′, estimate_percent =>99);


DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_1’)
—————————————————————————————————-


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


8- Statistics Finding
    ————————-
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_N5″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_N5′, estimate_percent =>99);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


9- Statistics Finding
    ————————-
Optimizer statistics for index “INV”.”MTL_RESERVATIONS_N4″ are stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => ‘INV’, indname =>’MTL_RESERVATIONS_N4′, estimate_percent =>99);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.


10- Statistics Finding
    ———————–—-
  Optimizer statistics for table “INV”.”MTL_RESERVATIONS” and its indices are
  stale.


  Recommendation
  ————–
  – Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => ‘INV’, tabname =>’MTL_RESERVATIONS’, estimate_percent =>99, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);


  Rationale
  ———
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.


11- 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 =>
            ‘my_sql_tuning_task_1’, task_owner => ‘SYS’, replace => TRUE);