How to Tune a Query in Oracle 11g Database

Please let me know how can I tune this query .
I am using oracle 11g database ,some times ,it execution time keep changing on same env for same data.like some time in 20 mins ,some time It take more than 6 hours .

Popular White Paper On This Topic

Respectfully, this is the 4th query you have posted here, with EXPLAIN output, asking for help tuning the query performance. In each case, you are reporting widely different run times (from minutes to hours).

I suggest you hire an Oracle performance tuning specialist to come into your shop for an examination of your environment, your database, your data and your query-generation processes. This specialist should be able to tune your queries and, for an extra fee, instruct you on how to tune it yourself.

What you are demonstrating is a systemic issue with poor performing queries. Since your situation is urgent, it will be well worth the cost to hire an expert to help you solve it.