Forum Stats

  • 3,839,768 Users
  • 2,262,532 Discussions
  • 7,901,053 Comments

Discussions

How do we know that the cost of a sql query is high ?

gg
gg Member Posts: 154 Bronze Badge

Hi Everyone,

I have developed a sql query report in dev environment. After executing the query I am getting a cost of 3055 (1% CPU) and executing in 00:00:01 seconds. Is the cost high ? How can one determine whether the cost is high or low for a query ?

Also, how do I know whether the query will get similar cost in Production as well.


Thanks

Gautam

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    SQL Tuning guide:

    4.2.2.3 Cost

    The optimizer cost model accounts for the machine resources that a query is predicted to use.

    The cost is an internal numeric measure that represents the estimated resource usage for a plan. The cost is specific to a query in an optimizer environment. To estimate cost, the optimizer considers factors such as the following:

    • System resources, which includes estimated I/O, CPU, and memory
    • Estimated number of rows returned (cardinality)
    • Size of the initial data sets
    • Distribution of the data
    • Access structures

    Note:

    The cost is an internal measure that the optimizer uses to compare different plans for the same query. You cannot tune or change cost.

    The execution time is a function of the cost, but cost does not equate directly to time. For example, if the plan for query A has a lower cost than the plan for query B, then the following outcomes are possible:

    • A executes faster than B.
    • A executes slower than B.
    • A executes in the same amount of time as B.

    Therefore, you cannot compare the costs of different queries with one another. Also, you cannot compare the costs of semantically equivalent queries that use different optimizer modes.

    gg
  • BluShadow
    BluShadow Member, Moderator Posts: 42,150 Red Diamond


    Essentially you can't really look at cost to tell if a query is going to be performant or not (though an obviously very high cost would usually indicate a poorly performing query, but it's not easy to tell or compare)

    The only realistic time to consider the cost is if you have a poorly performing query and can note the cost of it, and then by doing something without altering the actual query, such as adding an index or gathering table statistics, and then checking the execution plan again you can see if the cost of the same query has altered for the better... then you'll know that the cost has been beneficial in helping you identify where performance improvements can be made (though obviously considering the downside to having too many indexes etc. on highly transactional tables etc. etc.)

    gg
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,834 Red Diamond

    Simply put: CBO costs are in Oracle Query Dollars. This currency cannot be converted to US Dollars, or Euros, or anything else.

    We are not even sure it is using real numbers. ;-)

    ggBluShadowEdStevens