Optimizer Choosing Nested-Loop Joins Instead of Hash-Joins

In one of my databases, one application query suddenly started to pick Nested-Loop joins instead of Hash-Joins and took almost 6 hours to complete which gets completed in less than 10 secs with Hash-Joins.
The same query in another similar database with same configuration and same data is doing fine and using hash joins. There is no difference in data/stats/OS/init parameter etc. (Though I know that no two databases are same)

About the query:
-- It is a simple select statement which selects data from a complex view.
-- The view comprises of 5 different tables, four of which have more than 15K rows and one have less     then 50 rows.
-- Statistics are up-to-date in both databases.

I can see the optimizer behavior using 10053 event for the next run of this query but want to know what else can be checked to know why the plan changed suddenly, in this case, before using 10053 event.

Your valuable inputs on this!!!!

 

10 thoughts on “Optimizer Choosing Nested-Loop Joins Instead of Hash-Joins

  1. Thanks Yasser,

    But the pga usage does not impact the hash joins, if the hash table is not able to fit in the memory, optimizer will break it into partitions and move the partitions into disk.
    Conceptually, it should not cause change in optimizer behavior.

    Regards,
    Saurabh Sood

  2. Verify DBA_TAB_COL_STATISTICS in both database for those tables to get information on histograms.

    select owner,job_name,schedule_name,enabled,state,last_start_date from DBA_SCHEDULER_JOBS

  3. @Ganesh: The global stats were up-to-date for both the databases, though I did not check the col stats.

    But the automated gather_stats_job ran next day as its schedule and after that the optimizer picked the correct plan..

    So it was still unknown why the optimizer changed its behavior suddenly???

Leave a Reply