It's Still All About The Data

I was recently confronted with another situation where the interface rendered data useless.

A colleague was looking at an execution plan displayed in Oracle Grid Control. Most of the columns (operation, rows, bytes, cost) are either self explanatory or well known. The one that really bothered my colleague was cpu cost for the sort order by operation. The index scan and table look up were measured in the 10s of 1000s, but the sort order by was being measured in 1,000,000s (actually close to 6,000,000). Cost and io cost were less than 20, so the large number really stood out.

What is the unit of measure? According to the documentation for v$sql_plan, the column cpu_cost means "CPU cost of the operation as estimated by the optimizer's cost-based approach". No mention of a unit of measure. And a cpu cost of almost 6,000,000 to sort an expected 12 rows adds 1 to the statement cost.

A number without a unit of measure is just a number.

The second frustration with Grid Control was on the statistics page for a sql statement. I was looking at the top resource consuming user statement and found the information related to executions, cpu time, logical and physical reads, etc. Quite interesting data, but ultimately useless information. The interface fails to provide any temporal context to the numbers.

8,000,000,000 logical reads is certainly impressive, as are 600,000,000 executions. But what is the sampling time frame? An hour, a day, since the last hard parse, since instance startup? Without the relevant time information, the impact of optimization cannot be known.

Temporal context is critical to time relevant numbers.