Re: How to find the Overall cost of a query?

Yes, time it with a stopwatch! Then ask the user/business if the runtime is acceptable or not. If the time is acceptable, go on to the next problem. If not, enable extended sql_trace, run the statement/application/process, identify the component that is consuming the most time and focus on tuning it.

>From a user/business perspective, they understand 2 things
1) Did the statement/process perform the correct actions (return the correct data, update the date correctly, etc.)? 2) Did the statement/process perform the correct actions in an acceptable amount of time?

They could care less about cost, cardinality, bchr, etc. So don't approach performance in those terms.

Cost != Time

Explain Plan Cost is an estimate, not an actual. Execution plans tell the actuals.
For actuals, you need to get the STAT lines in an extended sql_trace file. If (quite often when) the Cardinality estimates and row actuals differ, there might be an issue with the CBO and the statistics/inputs that it uses.

IIRC, even in 8, cost was not purely single block i/o. For example, the cost of a full table scan is the number of blocks to be read divided by a factor determined by db_file_multiblock_read_count. Received on Tue Dec 20 2005 - 09:53:01 CST