Database Reference
In-Depth Information
■
the gathering of system statistics doesn't invalidate the cursors stored in the library cache. as a result, the new
system statistics will only be used for SQL statements that will need to be hard parsed.
Note
The
dbms_stats
package provides the following subprograms (see Figure
7-1
):
gather_system_stats
gathers system statistics and stores them in either the data dictionary
or in a backup table.
•
delete_system_stats
deletes system statistics stored in either the data dictionary or a
backup table.
•
restore_system_stats
restores system statistics to the data dictionary.
•
export_system_stats
moves system statistics from the data dictionary to a backup table.
•
import_system_stats
moves system statistics from a backup table to the data dictionary.
•
get_system_stats
extracts system statistics stored in either the data dictionary or a
backup table.
•
set_system_stats
modifies system statistics stored in either the data dictionary or a
backup table.
•
Figure 7-1.
The
dbms_stats
package provides a comprehensive set of features to manage system statistics
By default, permission to execute the
dbms_stats
package is granted to public. As a result, every user can gather
system statistics. Nevertheless, only those holding the privileges provided by the
gather_system_statistics
role are
able to change the system statistics stored in the data dictionary. Unprivileged users can only store them in a backup
table. Per default, the
gather_system_statistics
role is provided through the
dba
role.
What System Statistics Are Available?
There are two kinds of system statistics:
noworkload statistics
and
workload statistics
. The main difference between
the two is the method used to measure the performance of the disk I/O subsystem. Whereas the former runs a
synthetic benchmark, the latter uses an application benchmark. In both cases the performance of the CPU is
computed with a synthetic benchmark. Before discussing in detail the difference between these two approaches, let's
