Tune SQL query running good but having performance issue on another in Oracle

1. Index Usage

Need to check the Execution plan Which will let us know the execution plan.

SQL> variable var number

SQL> begin

  2  :var := 100;

  3  end;

  4  /

PL/SQL procedure successfully completed.


SQL> select employee_id,first_name from hr.employees where department_id = :var;



EMPLOYEE_ID FIRST_NAME

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

        108 Nancy

        109 Daniel

        110 John

        111 Ismael

        112 Jose Manuel

        113 Luis


6 rows selected.


SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'BASIC'));

PLAN_TABLE_OUTPUT

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

EXPLAINED SQL STATEMENT:

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

select employee_id,first_name from hr.employees where department_id =

:var

Plan hash value: 235881476

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

| Id  | Operation                           | Name              |

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

|   0 | SELECT STATEMENT                    |                   |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |

|   2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |

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


Note:  Check the execution plan use same indexes. Check the index is present on problematic database.

2. Outline Data/hints:
We can generate execution plan with outline data option to check the difference between hints used.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'OUTLINE BASIC NOTE'));



PLAN_TABLE_OUTPUT

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

EXPLAINED SQL STATEMENT:

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

select employee_id,first_name from hr.employees where department_id =

:var


Plan hash value: 235881476

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

| Id  | Operation                           | Name              |

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

|   0 | SELECT STATEMENT                    |                   |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |

|   2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |

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


Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('18.1.0')

      DB_VERSION('18.1.0')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))

      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMPLOYEES"@"SEL$1")

      END_OUTLINE_DATA

  */

We can compare the hints/Outline of both Server used and try to use the hints in select query in problematic database which cause performance issue by copying outline data and use that as hint in another database as :

--- Used in Problamtic database to verify the performance


Select   /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('18.1.0')

      DB_VERSION('18.1.0')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))

      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMPLOYEES"@"SEL$1")

      END_OUTLINE_DATA

  */ 

 from hr.employees where department_id =  
:var

3. Statistics:

Copy paste the statistics of table from test environment and tried to execute the SQL