Why did the plan change
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Why did the plan change

  1. #1
    Join Date
    Aug 2002
    Posts
    56

    Why did the plan change

    Hi,
    I know I am going to be hounded for this, but my question is truly a generic one. But if you want specifics, then consider it to be a 10.2.0.4 database on linux machine.
    My question is how do I find out why a plan changed for a query. I know the probable causes can be data changes (which is not the case I am looking into) or stats change or index rebuild. But my question is how do I pin point the cause of a plan change (what views/reports can help). I know the plan has changed, now what caused it keeping in mind its not a data change and how do I find it out with the help of DBA_HIST or V$ views.

    Thanks
    When in doubt ...go to the basics!!

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by bhatia333 View Post
    I know I am going to be hounded for this, but my question is truly a generic one. But if you want specifics, then consider it to be a 10.2.0.4 database on linux machine.
    My question is how do I find out why a plan changed for a query. I know the probable causes can be data changes (which is not the case I am looking into) or stats change or index rebuild. But my question is how do I pin point the cause of a plan change (what views/reports can help). I know the plan has changed, now what caused it keeping in mind its not a data change and how do I find it out with the help of DBA_HIST or V$ views.
    Execution plan over time changes can be found by querying AWR's dba_hist_sql_plan view.

    Once you get the two execution plans you have to infer what happened from what you see e.g. an UNIQUE INDEX SCAN going FULL TABLE SCAN, or whatever differences you see.

    If data really didn't change at all and no configuration changes to the database happened my prime suspect would be performance stats.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width