Database Reference
In-Depth Information
ALL: It captures all the statistics including those as in the case of
TYPICAL plus the execution plans and timing information from
the operating system.
Oracle recommends setting this parameter to either TYPICAL or
ALL. There is one more parameter: DBIO_EXPECTED. Its
value is specified in micro seconds with a default value of
10,000. This parameter defines the average time it takes to
read/write a single database block. The value of 10,000
microseconds works for most of the hard drive. In hard drive
jargon it's mentioned as the seek time. Typical it's within the
range of 5000 to 15000 microseconds. To change the default
value, use the following command.
What is the Retention policy of gather statistics?
By default the snapshots are generated once every hour and
saved in AWR with the default retention period of 7 days. You
can over ride this default value either by using the Enterprise
Manger Database Control or by using the supplied package i.e.
DBMS_WORKLOAD_REPOSITORY.
In case of EM Database Control, click <Administration> tab then
<Automatic Workload Repository> link and finally <Edit> on
the main screen to get to 'Edit Settings' window. Here you can
change the retention policy as well as the frequency of statistics
collections (snapshots taken).
In case of supplied package, at the SQL prompt, execute the
following statement.
SQL> execute
dbms_workload_repository.modify_snapshot_settings
(interval=>60, retention=>43200);
Where 43200 hours = 30 days
Search WWH ::
Custom Search