1 Reply Latest reply on Aug 5, 2015 4:12 PM by mandevil

    How do i capture plan for a set of specific SQL in oracle

    hemantshah

      I am new to using DPA and currently we are migrating Oracle 11 to 12c and one of the task we want to do is for a given set of SQL that runs currently in Production we would like to capture existing plan and save it and then compare to 12c in case needed.

       

      In DPA ( Database Performance Analyzer) how do I get SQL Plan for a specific SQL ? From developers all I have is set of SQL which they say runs everyday in Production. How would I capture or get  SQL PLAN ?

       

      Any ideas and or suggestions ?

        • Re: How do i capture plan for a set of specific SQL in oracle
          mandevil

          So you will have to first find that SQL within your production instance within DPA.

          That may require you to set your context more specifically than just overall view of performance (meaning drill into a day on the trend view, set the interval (upper left) to 1 day, and click on that one bar).

          Then select one of the dimensions that will help you narrow your context down (if you know the program or db user or client machine the SQL runs from).

           

          Once you find the SQL statement in question, you can select the appropriate hash and click on the plan tab and click on the plan hash to view the execution plan.

          Save that off or note where you find it (note, detail like this rolls off after 30 days by default).  Then you can compare after the upgrade to 12c.