Database Reference
In-Depth Information
the script
gather_get_system_stats.sql
in the source code depot of this chapter. The script
collects system statistics during several subsequent intervals and stores them in a statistics
table outside of the data dictionary. By default, it uses four intervals of 15 minutes each. It also
includes PL/SQL code for retrieving system statistics. The script does not affect any optimizer
parameters or decisions. Workload statistics must be imported into the data dictionary to affect
execution plans generated by the CBO.
When noworkload statistics are used, the 10053 system statistics trace file section looks
as follows:
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 485 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
The value of CPUSPEED depends on the hardware used, whereas the parameters
IOTFRSPEED and IOSEEKTIM have identical values on any UNIX or Windows port of
Oracle10
g
.
Object Statistics for Tables and Indexes
This section comprises statistics from
DBA_TABLES
,
DBA_TAB_COL_STATISTICS
and
DBA_INDEXES
. If
the statement accesses partitioned objects, statistics from
DBA_TAB_PARTITIONS
and
DBA_IND_
PARTITIONS
would be present. In case histograms have been created for some columns, statistics
from
DBA_TAB_HISTOGRAMS
would be displayed. In the excerpt that follows, the column
LOCATION_
ID
has a histogram with seven buckets, whereas column
DEPARTMENT_ID
does not have a histogram.
Merely columns that are candidates for filter or access predicates are listed. Abbreviations used
in this section and their meanings are depicted in Table 7-2.
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: JOBS Alias: J
#Rows: 19 #Blks: 5 AvgRowLen: 33.00
Column (#1): JOB_ID(VARCHAR2)
AvgLen: 8.00 NDV: 19 Nulls: 0 Density: 0.052632
Index Stats::
Index: JOB_ID_PK Col#: 1
LVLS: 0 #LB: 1 #DK: 19 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Table Stats::
Table: DEPARTMENTS Alias: D
#Rows: 27 #Blks: 5 AvgRowLen: 20.00
Column (#1): DEPARTMENT_ID(NUMBER)
AvgLen: 4.00 NDV: 27 Nulls: 0 Density: 0.037037 Min: 10 Max: 270
