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

    total cost of a query

    3506385

      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?

      askroacle.jpg

       

      thank you very much!

      Mara Dona

        • 1. Re: total cost of a query
          thatJeffSmith-Oracle

          I recommend reading this white paper

           

          http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

           

          cost.PNG

           

          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
            EdStevens

            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