Database Reference
In-Depth Information
Table 7-1.
CBO Cost Calculation Placeholders
Placeholder
Meaning
SRds
Single block reads.
sreadtim
Single block read time.
MRds
Multi block reads.
mreadtim
Multi block read time, i.e., the time it takes for a multi block read request to
complete. The number of blocks requested in a multi block read request is
limited by the parameter
DB_FILE_MULTIBLOCK_READ_COUNT
.
CPU Cycles
Number of CPU cycles required to execute a statement.
cpuspeed
Number of instructions per second.
1
The following query result is from a system where all workload statistics parameters except
SLAVETHR (the parallel execution slave throughput) were set:
1
SQL> SELECT * FROM sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------- ---------- ------- ----------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 11-29-2007 13:49
SYSSTATS_INFO DSTOP 11-29-2007 13:49
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 841.336
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 4
SYSSTATS_MAIN MREADTIM 10
SYSSTATS_MAIN CPUSPEED 839
SYSSTATS_MAIN MBRC 14
SYSSTATS_MAIN MAXTHR 8388608
SYSSTATS_MAIN SLAVETHR
Workload parameters are reproduced in bold. MBRC is the actual multi block read count
derived from statistics collected in X$ fixed tables. MBRC is limited by the parameter
DB_FILE_
MULTIBLOCK_READ_COUNT
and is usually somewhat lower than this parameter.
DB_FILE_MULTIBLOCK_
READ_COUNT
had the value 16 on the system used for the example and the actual MBRC was 14.
MAXTHR is the maximum I/O throughput. SLAVETHR is the average parallel execution slave I/O
throughput. SREADTIM and MREADTIM have already been covered by Table 7-1.
1.
Event 10053 trace files from Oracle10g Release 2 contain lines such as this:
CPUSPEED: 839 millions instructions/sec
This seems to indicate that the DBMS measures CPU speed by calculating how many instructions
complete per second. The kind of instruction used for this purpose is undocumented.