1 2 Previous Next 19 Replies Latest reply: Jul 31, 2013 6:35 AM by michaelrozar17 Go to original post RSS
      • 15. Re: Which execution plan is best?
        Jonathan Lewis

        michaelrozar17 wrote:

         

        Can you please answer this.

        1. What are the measure to determine a efficient plan ?

        2. Do the statistics information from AUTOTRACE: db block gets and consistent gets play vital role in affecting performance of a query?

        The first question is not a trivial one. If you want to decide which of two plans is the better execution plan simply be reading it, you need to know how much data Oracle will have to pick up at each step, how widely scattered the data is and therefore how many blocks will have to be examined and the method of examination; then you have to know how many of those blocks might be cached and how many will have to be read from disc when the query actually runs, and then you have to consider the impact of multiple sessions running the query at similar times. You also have the problem that the run-time engine is allowed to use different mechanical methods to execute the query, and has some run-time mechanisms that change the workload dramatically without there being any clues visible in the plan that this is possible.

         

        For a starting point on how to design efficient SQL, there's an article I wrote a few years ago on a SQL Server website that might help:

        https://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

         

        The second question - those stats do show you one component of the work done by the query, but two queries with the same counts for db block gets and consistent gets could have dramatically different response times - which might be due to extreme differences in the number (and nature) of the physical reads, and the amount of CPU time used.

         

        Regards

        Jonathan Lewis

        Now on Twitter: @jloracle

        • 16. Re: Which execution plan is best?
          michaelrozar17

          Thanks for the link.

          but two queries with the same counts for db block gets and consistent gets could have dramatically different response times

          Can i say that db block gets and consistent gets are measures to be considered while tuning apart from others( like -Join method, CPU resource availability, Physical I/O's etc)? If so then how can we reduce the counts of these two components in a AUTOTRACE's result or what steps we must take such that their count is minimal. Im under the impression that consistent get is the reads made from Undo segment and db block get is the reads made from the blocks in buffer cache. Please correct me if Im wrong.

          • 17. Re: Which execution plan is best?
            Jonathan Lewis

            michaelrozar17 wrote:

             

            Thanks for the link.

            but two queries with the same counts for db block gets and consistent gets could have dramatically different response times

            Can i say that db block gets and consistent gets are measures to be considered while tuning apart from others( like -Join method, CPU resource availability, Physical I/O's etc)? If so then how can we reduce the counts of these two components in a AUTOTRACE's result or what steps we must take such that their count is minimal. Im under the impression that consistent get is the reads made from Undo segment and db block get is the reads made from the blocks in buffer cache. Please correct me if Im wrong.

             

            They can be considered as helpful measures - they indicate the number of "different" locations you are visiting in the database, and typically give you a clue when you are doing too much work for the required result. Consistent gets don't necessarily require visits to the undo; db block gets are block visits that have to see the current version of a block - typically to change it, though that's not always true.  For some detailed comments see: Consistent Gets – 2 | Oracle Scratchpad

             

            Regards

            Jonathan Lewis

            Now on Twitter: @jloracle

            • 18. Re: Which execution plan is best?
              Mac_Freak_Rahul

              Hi Raghav,

               

              Based on my experience, I have never relied on the explain plan since it depends on the tables that are coming into picture(considering that you are joining all tables coming from the same schema), here are a few points that you can analyze in order to depict the right execution path:

               

              1) you got to have the right idea about the cardinality(meaning what is the relationship between 2 entities 1-1,1-many,many-1 or many-many) when you are joining two tables, if you are joining many tables, break it down to individual joins and analyze each individually.

              2) taking an example of fact tables getting joined with dimension tables, there could be 2 cases where in the first case every record from the fact table needs to be included in the result set(typical example of outer joins), then the other case could be that only fact rows that find matches with the dimension tables are need to be included in the final result set, so in this case you can need to determine your driving or leading table

              3) understanding of these 5 concepts, basically their algorithms are very important ie:

              Joins:

              a) Hash joins

              b) sort merge join

              c) Nested loop joins

              Indexes:

              d) Bitmap indexes

              e) B tree indexes

              4) Knowing the amount of data in your individual tables is going to help you decide which joins should be used in order to get most efficient performance, along with details of how much memory you have in your system global area for your joining operations(quickly you can have a look at your init.ora file), also need to estimate how much space you have in your temp tablespace, (since I have seen ppl using the system table space which is ultimately going to kill the performance)

              5) Manually calculating cost based on these operations like how many full table scans, sort area need, disk IO needed, memory available in SGA for your operation etc is going to help you build your own execution plan.

              5) Partitioning a table which means you can divide you table into data segments and further you can push your data into more than 1 disk(I hope you are getting my point, not going in too much detail)

              6) defragmentation of data @ data base file level can drastically improve performance so its a good idea to bring the data blocks together by tables in your database file

              7) finally I would say for a poor performing query, I would like to analyze each and every point mentioned above , build an execution plan all by myself and  suggest the optimizer using hints, untill I was aware of all the stuff that I mentioned I used to feel that hints are just a hit and trial but if you know all these above points, you can actually suggest very good points to your optimizer since you know your data model better than the optimizer.

               

              I hope it helps.

               

              Regards

              Rahul

              • 19. Re: Which execution plan is best?
                michaelrozar17

                It's very informative. Thank you for your patience response.

                1 2 Previous Next