Sundaramurthy Blog

Oracle -CBO

Cost Based Optimizer (CBO) – The CBO checks several possible execution plans and selects the one
with the lowest cost, where cost relates to system resources

SQL> select * from aux_stats$;

SNAME PNAME PVAL1 PVAL2
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 10/3/2008 7:27
SYSSTATS_INFO DSTOP 10/3/2008 9:19
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1825.687
SYSSTATS_MAIN IOSEEKTIM 8.1
SYSSTATS_MAIN IOTFRSPEED 27272.386
SYSSTATS_MAIN SREADTIM 1.092
SYSSTATS_MAIN MREADTIM 9.133
SYSSTATS_MAIN CPUSPEED 1832
SYSSTATS_MAIN MBRC 123
SYSSTATS_MAIN MAXTHR 104851456
SYSSTATS_MAIN SLAVETHR

13 rows selected.

–  SREADTIM—single block read time (milliseconds)—is the average time Oracle takes to read a single block.
–  MREADTIM—multiblock read time (milliseconds)—is the average time taken to read sequentially.
–  MBRC—multiblock read count—is the blocks, on average, read during multiblock sequential reads.
–  MAXTHR—maximum I/O system throughput—is captured only if the database runs parallel queries.
–  SLAVETHR—maximum slave I/O throughput—is captured only if the database runs parallel queries.

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

cpuspeedNW – Represents noworkload CPU speed
ioseektim  – I/O seek time equals seek time + latency time + operating system overhead time.
iotfrspeed – I/O transfer speed is the rate at which an Oracle database
can read data in the single read request.

MBRC = dbf_mbrc
sreadtim = ioseektim + db_block_size / iotfrspeed
mreadtim = ioseektim + dbf_mbrc * db_block_size / iotfrspeed

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: