1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle SQL picking incorrect plan for new Partition in tables

Discussion in 'System Administration & Application DBA' started by araj99, Nov 14, 2013.

  1. araj99

    araj99 Guest

    Hi All,
    I am Raj, an application developer and new to forums. I am currently using oracle as DB and performing development using SQL for data processing. from last 1 week, came across as issue as below and struggling to find reason.

    Problem Statement:
    When there were no Stats on table/s, query was doing dynamic sampling and giving correct execution plan.
    but after the stats are updated and loaded with new data/partition, tried accessing this partition query fails and does not do dynamic sampling.

    1) I have recreated all these 5 tables
    2) No stats gathered so last_analyzed & no of rows was null
    3) Created partitions and loaded data
    4) When i run the query and see the plan, it was not using merge join and used hash join which is good and at the end of plan it showed dynamic sampling level=2 used
    5) Query gave good result though i add additional partitions and pls note all partition / table stats shows null

    Today morning

    6) I noticed stats gathered on those tables for yesterday's date (nobody done manual gather) and stats upto-date for partitions created till yesterday
    7) I ran the query against partitions which created yesterday (where stats gathered automatically) and running with good plan
    Now The problem:
    8) Created a new partition and loaded data
    9) But query picking bad plan against this new partition and in the plan it is not using dynamic sampling.