Costing and Selectivity

According to the CPU costing model:

Cost = ( #SRds * sreadtim + #MRds * mreadtim + #CPUCycles / cpuspeed ) / sreadtim


#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second

cardinality = num_rows * density.

Further Reading : Cost Based Optimizer (CBO) Overview (Doc ID 10626.1)

Commonly used terminology :

predicate =
A WHERE condition in SQL.

selectivity =
The first measure, selectivity, represents a fraction of rows from a row set. The selectivity of a predicate in a SQL statement is used to estimate the cost of a particular access path.
Selectivity is a measure ranging between 0 and 1, simplistically defined as 1/NDV where NDV stands for Number of Distinct Values.

cardinality =
Cardinality represents the number of rows in a row set.
Cardinality is defined as the number of rows expected to be fetched by a predicate or execution step.
Cardinality is calculated as the number of rows in the table divided by the number of distinct values in the column.
The cardinality of a predicate can be defined as the selectivity times the number of rows in the table.

cost =
The cost represents (and has always represented) the optimizer’s best estimate of the time it will take to execute the statement.

aggregate views / aggregation =

transformation =
Sometimes, the optimizer (Query Transformer) transforms one such statement into another that achieves the same goal if the second statement can be executed more efficiently.

instantiate =

histograms =
A histogram partitions the values in the column into bands, so that all column values in a band fall within the same range.

index fast fulll scan =

parallel execution =

extent boundaries blocks =

bind peeking =

stale statistics =

parse =

skew =


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s