Oracle Database Discussions

Hi,

One of my Sql query is taking 65 mins to complete, Please check the explain plan and sql tuning adviser results.

please advise

EXPLAIN PLAN :

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 2327451813

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

| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                                                                 | |    1 | 103 |   769   (0)| 00:00:10 |

|   1 |  NESTED LOOPS                                                 |                       |    1 | 103 |   769   (0)| 00:00:10 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| ID_TICKET_DETAILS             |    1 | 91 |   768   (0)| 00:00:10 |

|*  3 |    INDEX SKIP SCAN                              | ID_TKT_DEPT_IDX_01               1 | |   767   (0)| 00:00:10 |

|*  4 |   TABLE ACCESS BY INDEX ROWID| ID_CREDIT_CARD_MASTER   |        1 | 12 | 1   (0)| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN                    | CARD_KEY                             |   1 |       |     0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("TICKET_CARD_TYPE" IS NOT NULL AND "TICKET_DAYEND_REFERENCE"=:B4 AND

"TICKET_REFERENCE_AMOUNT_1"=0 AND "TICKET_COMPANY"=:B1 AND "TICKET_DAYEND_TYPE"=:B3)

   3 - access("TICKET_STATUS"=:B2)

filter("TICKET_STATUS"=:B2)

   4 - filter("CARD_POSTING"='A')

   5 - access("CARD_COMPANY"=:B1 AND "TICKET_CARD_TYPE"="CARD_TYPE")

Note

-----

   - SQL profile "SYS_SQLPROF_014fd7f5d9444004" used for this statement

26 rows selected.

SQL TUNING ADVISOR

Select Type Findings Recommendations Rationale Benefit (%) New Explain Plan Index The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.AE001T3.ID_TICKET_DETAILS ("TICKET_DAYEND_REFERENCE", "TICKET_REFERENCE_AMOUNT_1") 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. 99.34 Miscellaneous SQL Profile "SYS_SQLPROF_014fd7f5d9444004" exists for this statement and was ignored during the tuning process.