1 2 Previous Next 18 Replies Latest reply on Apr 28, 2008 1:40 AM by 26741

    total cost in explain plan

      I am learning to read explain plan now. Our db is 10g RAC with two nodes and db server is MS 2003 server. My questions are:
      1) Is this correct the first line cost in explain plan represent total cost for the whole query?
      2) If the first question is true how to explain the detailed line cost is bigger than the total cost as following?

      Operation                    Object Name                    Rows     Bytes     Cost
      SELECT STATEMENT Optimizer Mode=ALL_ROWS                         1           2747
      SORT AGGREGATE                                        1      20      
      CONNECT BY WITH FILTERING                                        
      INDEX RANGE SCAN               INSPECTION.INSPECTION_COMPD_INDX02     1      26      3
      NESTED LOOPS                                        
      BUFFER SORT                                        
      CONNECT BY PUMP                                        
      TABLE ACCESS BY INDEX ROWID     INSPECTION.INSPECTION               7 K     152 K     2747
      INDEX FULL SCAN               INSPECTION.INSPECTION_I_PARENT_ID     3 M     64 M     14313

      Thank you so much for your help,

        • 1. Re: total cost in explain plan
          Generally speaking the first cost returned in an explain plan is the query final cost figure.

          Being you are on 10g you should be using dbms_xplan to generate your explain plan. The output is nicely labled.

          HTH -- Mark D Powell --
          • 2. Re: total cost in explain plan
            Thanks, Mark. Here is the nice version of the explain plan. Do you know why the sub title is bigger than total? Thanks.

            SQL> /


            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
            | 0 | SELECT STATEMENT | | 1 | 20 | 2747 (1)|
            | 1 | SORT AGGREGATE | | 1 | 20 | |
            |* 2 | CONNECT BY WITH FILTERING | | | | |
            |* 3 | INDEX RANGE SCAN | INSPECTION_COMPD_INDX02 | 1 | 26 | 3 (0)|
            | 4 | NESTED LOOPS | | | | |
            | 5 | BUFFER SORT | | | | |
            | 6 | CONNECT BY PUMP | | | | |

            | 7 | TABLE ACCESS BY INDEX ROWID| INSPECTION | 7821 | 152K| 2747 (1)|
            |* 8 | INDEX RANGE SCAN | INSPECTION_COMPD_INDX10 | 6407 | | 21 (0)|
            | 9 | INDEX FULL SCAN | INSPECTION_I_PARENT_ID | 3360K| 64M| 14313 (2)|

            Predicate Information (identified by operation id):

            2 - access("PARENT_ID"=PRIOR "ID")
            3 - access("C"."ID"=76801811)
            8 - access("PARENT_ID"=PRIOR "ID")
            • 3. Re: total cost in explain plan
              Probably a bug. There are oddities every now and then usually realated to some specific operation appearing in the plan.

              I looked in Jonathan Lewis's Cost-Based Oracle Fundamentals but naturally I could not find an example and do not want to spend any real time on finding confirmation.

              -- Mark D Powell --
              • 4. Re: total cost in explain plan
                PS - Run your traditional explain SQL and see if the total cost matches. If it does not then the bug could be in dbms_xplan though I suspect the error is in the costing process.
                • 5. Re: total cost in explain plan
                  Charles Hooper
                  Probably a bug. There are oddities every now and
                  then usually realated to some specific operation
                  appearing in the plan.

                  I looked in Jonathan Lewis's Cost-Based Oracle
                  Fundamentals but naturally I could not find an
                  example and do not want to spend any real time on
                  finding confirmation.

                  -- Mark D Powell --

                  Is this one of the possible causes that you were trying to locate?

                  Charles Hooper
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.
                  • 6. Re: total cost in explain plan
                    Charles, it will do. My guess is that the issue is related to the connect by pump but I do not spend the kind of time and effort Jonathan has and does on trying to understand the details of explan plan. I just know there are cases where the total cost isn't what you would expect. In those cases I just try to understand the basics of what the plan is telling me and in cases where tuning is desired I look to see if I can find another approach that will get me better results.

                    -- Mark D Powell --
                    • 7. Re: total cost in explain plan
                      Hi Mark,

                      If you can't tell from performance point of view which query is better do you pay attention to rows, bytes and cost to each execution step in the explain plan?


                      • 8. Re: total cost in explain plan

                        First I usually try to determine if the join order is correct.

                        Then I look for how Oracle is accessing the next table.

                        I look for cases where changing the join ordering or providing a more focused access (index) might provide better filtering or reduced IO.

                        I look for where pre-joining data (inline view) or pulling a subquery up (convert into join or pull up into inline view to pre-filter a join step) will provide earlier filtering in the result set. By pre-filter or pre-join I mean where say the join order is A to B to C with a subquery that maybe an inline view with the subquery on B would result in a smaller result set when A goes to B and then to C.

                        I look at what the plan is doing and then try to consider what should it be doing and what else could we potentially do. Because I do not tune every day I cannot remeber all the details of why the optimizer is wrong. I just try to recognize that it is wrong, hopefully where, and try to see what I can do to fix it.

                        The more time you spend tuning the better you get at it. When slack time exists I read books Like Cost-Based Fundamentals (Lewis), which I recommend, to try to gain knowledge. Even when you cannot remember all the details certain points stick that help when you do get an opportunity to tune or as developers say, "there is something wrong with the database this query is running too slow."

                        HTH -- Mark D Powell --
                        • 9. Re: total cost in explain plan
                          Jonathan Lewis

                          Sometimes there are just oddities with no explanation. In the case of the connect by query, there's also the possibility that part of the plan simply doesn't run. If you run the query and collect execution stats (see http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/ ) you can see things like this:
                          | Id  | Operation                  | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                          |   1 |  SORT AGGREGATE            |                    |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
                          |*  2 |   CONNECT BY WITH FILTERING|                    |      1 |        |    512 |00:00:00.01 |       3 |  9216 |  9216 | 8192  (0)|
                          |*  3 |    INDEX UNIQUE SCAN       | SYS_IOT_TOP_102488 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
                          |   4 |    NESTED LOOPS            |                    |      3 |        |     45 |00:00:00.01 |       2 |       |       |          |
                          |   5 |     BUFFER SORT            |                    |      3 |        |     10 |00:00:00.01 |       0 |  9216 |  9216 | 8192  (0)|
                          |   6 |      CONNECT BY PUMP       |                    |      3 |        |     10 |00:00:00.01 |       0 |       |       |          |
                          |*  7 |     INDEX RANGE SCAN       | SYS_IOT_TOP_102488 |     10 |      1 |     45 |00:00:00.01 |       2 |       |       |          |
                          |   8 |    INDEX FULL SCAN         | SYS_IOT_TOP_102488 |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
                          Note how the 'starts' of that last line is zero - it never happened. This be associated with the fact that, in your case, the optimizer assumes it won't contribute to the total cost even though it reports the possible action.

                          Jonathan Lewis
                          • 10. Re: total cost in explain plan
                            Thanks Jonathan, for your kind response.

                            I know you are a guru in Oracle CBO and I would like another favor from you. We have a developer here think that DBAs can make things happen. His query is against a single table and his result set is around 300,000 records and he want to sort this by two columns (transaction_id desc, id desc) which id is the primary key in the table. The query does not take a long time but the sort part is the problem. I don’t know if there are any tricks in tuning “order by clause”. I also send my question to LazyDBA and posted my question in this Forum but no luck. I really hope that you can give me an answer on if this is doable or not. His query is:

                            select * from event where log_id = 1 and date_posted > trunc(sysdate-1) order by transaction_id desc, id desc

                            Many many thanks to you!

                            • 11. Re: total cost in explain plan
                              If you request data in sorted order then you have to pay the price of the sort.

                              The most important thing a DBA can do to ensure good
                              sort response is make sure the temporary tablespace is
                              created accross several high speed physical drives as
                              practical when the database logical devices are
                              designed and defined to the OS.

                              Next the temporary tablespace should be created as a
                              true temporary tablespace using tempfiles rather than
                              datafiles. Sites that upgrade through several releases
                              in place sometimes fail to make the conversion.

                              Potentailly you can adjust the sort_area_size but I
                              suggest you not mention this to the developer. There is
                              absolutely no good reason to encourage bad practice.
                              Generally speaking you would alter the sort_area_size
                              for a specific task only in batch may using the database
                              wide setting for all OTLP work and using a larger size
                              in batch on some of the larger jobs.

                              Just make sure the query is an efficient as possible and
                              returns only the necessary data then let the sort step
                              do its thing.

                              Note that when you run an unsorted query Oracle can start
                              returning data to you as soon as it has a buffer full
                              or resutl set but just because you see data on the screen
                              does not mean that Oracle has completed the query.
                              With a sort Oracle must find all the data that will be
                              in the result set and depending on the sort logic will
                              not be able to return rows until the sort is at least
                              partially complete at best.

                              This means the difference in true run time may not be as
                              great as it seems just from watching the query start
                              spitting out data on your screen.

                              Anyhow tell the developer if the query is tuned to run as well
                              as it can without the order by then the result is probably
                              as good as he or she can get.

                              HTH -- Mark D Powell --

                              Message was edited by: Mark to add CR for readability
                              • 12. Re: total cost in explain plan
                                Chris Slattery
                                is a sort on txn_id really needed ? for 300k rows ? sounds unlikely to go onto a screen ...
                                • 13. Re: total cost in explain plan
                                  Thank you, Mark and Chris, for your comments.

                                  I am not sure how much a DBA can do to tune a query. I just want to know if I have used all the tricks out there. Before I posted my question I have talked with the developer if he can add another filter in the where clause to reduce the result set or get rid of the order by clause but he accept none. Oh well, I think I feel better now.

                                  Thanks again and have a great weekend!!

                                  • 14. Re: total cost in explain plan
                                    I am not sure how much a DBA can do to tune a query.
                                    Just FYI, using optimizer plan stability (stored outlines) and 10g/11g SQL profiles, you can change anything in an execution plan!

                                    It's great for vendor packages too, where you cannot alter the SQL directly. I have my notes here:


                                    Hope this helps. . .

                                    Donald K. Burleson
                                    Oracle Press author
                                    Author of "Oracle Tuning: The Definitive Reference":
                                    1 2 Previous Next