1 2 Previous Next 15 Replies Latest reply: Jan 5, 2013 2:03 PM by Randolf Geist Go to original post RSS
      • 15. Re: why different plan?
        Randolf Geist
        Oracle Explorer wrote:
        Any comments on this?
        You haven't mentioned a version. If you are on 11g and have Enterprise Edition + a Diagnostic Pack license, you could check the Active Session History (ASH) data for the particular execution in Production that lasted for several hours to get a clue which SQL plan line id was reported most. You can get that done automatically using my XPLAN_ASH tool: http://oracle-randolf.blogspot.com/search/label/XPLAN_ASH

        Without information like that you don't know where the excess time was spent, so you're mostly left with guesswork.

        One thing that hasn't been mentioned yet is the FILTER operation that you have in both execution plans, which theoretically runs the operations 9+10 (second child of FILTER) for every row produced by the first child operations of that FILTER.

        So in addition to the obvious problem that could be caused by the HASH JOIN/Nested Loop switch is that the built-in Filter subquery caching feature of Oracle could behave quite differently with different data sets.

        May be for the quick run the operations 9+10 had to only to be executed a couple of times and were cached well for the remaining rows whereas for the second run you might have been unlucky and it actually had to be executed for almost every single row - this can make quite a significant difference in run time.

        That would be something you could tell from the ASH analysis from 11g on.

        Hope this helps,
        Randolf
        1 2 Previous Next