3 Replies Latest reply on Aug 4, 2017 9:16 PM by Vadim Tropashko-Oracle

    total cost of a query


      hi, experts, I would like to know the total cost of the sql query from explain plan function in sql developer

      from the sample below, is the total cost = 6 in the hierarchy, total cost =  first row ?? if yes , how come some cost > 1st row if 1st row is total cost.

      or I need to sum all numbers in each row under COST column?



      thank you very much!

      Mara Dona

        • 1. Re: total cost of a query

          I recommend reading this white paper






          But basically the plan has a cost of 1140


          There a HASH GROUP BY series of steps with a total cost of 1140, of which the HASH JOIN piece has a cost of 792...and so on and so forth.


          Your plan show in sqldev looks...weird. What version are you running?

          1 person found this helpful
          • 2. Re: total cost of a query

            Aside from the immediate question, you need to be aware that the cost is just a synthetic number generate by the optimizer to help it to evaluate and choose a plan.  Within a given plan, it can be useful for seeing where the expensive parts are, but if you are thinking of using it to compare one query against another you can forget it.

            1 person found this helpful
            • 3. Re: total cost of a query
              Vadim Tropashko-Oracle

              Your suspicion is correct, the cost should increment when descending to the tree root, therefore, what you are experiencing is a bug. Please note, that your execution plan is rather exotic; normally joins (nested loops) are chained so that the nested join produces tuples one level up for the next join. Those nested loops at the same level are confusing because I can't possibly suggest any interpretation how this plan is executed. However, even with this disclaimer, you can still progress troubleshooting performance problem. Just run autotrace to gather performance statistics, and check if you have bottlenecks on the execution plan (execution statistics numbers are real, unlike cardinality and cost estimates). If your query runs too long, then cancel it after 5 min or so, you'll still get partial execution statistics.

              1 person found this helpful