A statement's cost is the amount of resources consumed at execution time. That's impossible to calculate exactly and for all time, but we can make a stab at it.
The relative unit weights vary with time, but here's an approximation , based on Oracle's CBO weights for disk accesses versus cached accesses .
When a DBMS estimates cost for one statement, it lacks full information about what statements will occur before and after at execution time. Therefore, it can only guess what will be in cache, whether processors will be available, how much locking will be going on, and so forth; all of these items depend on activity, and the optimizer doesn't base cost estimates on activity. It bases them, as we've seen, on rules and database contents. (That's why DBAs have to monitor activity all the time.) When executing SQL statements, then, the decisions made by the DBMS are strongly influenced by the optimizer's picture of the database contents. This picture, or data model, is used to estimate the costs of the various access plans that could be used to resolve a specific SQL statement. A key element in the data model is a set of statistics about the data, stored in the system catalog. A change in the data statistics can result in a change in the access plan selected as the most efficient method of getting the data you need.
|