5

I have written a complex oracle sql query and the explain plan stats look like this: Cost: 209,201 Bytes:187,944,150 Cardinality: 409,675

Now the DBA tuned the query and the stats look like this: Cost: 42,996 Bytes: 89,874,138 Cardinality: 209,226

My first question is, if the numbers are lower, does it automatically mean better performance? Which number is the most pertient?Cost/Cardinality/Bytes? My second question is: I understand cardinality is the number of rows read. But when i run the query, it returns '0' rows ! My impression was that Cardinality has to be same for two queries that are supposed to return same result sets. This I guess is wrong?

Tony L.
  • 17,638
  • 8
  • 69
  • 66
Victor
  • 16,609
  • 71
  • 229
  • 409
  • Cardinality is (simply put) the number of rows the optimizer _thinks_ will be needed; depending on lots of factors, including level of statistics, it could be wrong. – Ben Sep 13 '12 at 18:37
  • Thanks. Can you explain what is BYTES? – Victor Sep 13 '12 at 18:47
  • 1
    @Kaushik: From the documentation, `BYTES` is "Estimate by the query optimization approach of the number of bytes accessed by the operation." But that's misleading, it's really the number of bytes *returned* by the operation. For example, if you have a full table scan, it will read every byte in the table, but `BYTES` is the estimate of the size *after* any filters. http://docs.oracle.com/cd/E11882_01/server.112/e16638/ex_plan.htm#r22c1-t12 – Jon Heller Sep 14 '12 at 00:27

1 Answers1

4

Cost, bytes, cardinality... all are estimations according to inputs like statistics given to the optimizer. So they automatically mean nothing but can give an idea. In Oracle Performance Tuning Guide's words "It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption."

For 2nd question: Theoretically equivalent queries should return same cardinality. Your tables' statictics may be old.

heuristican
  • 294
  • 1
  • 8