Database Reference
In-Depth Information
Optimizer Statistics
One of the core tasks for an administrator of any Oracle database is ensuring that the optimizer has representative
statistical data about the objects that database applications access in order to determine the cost of, and therefore
optimize, execution plans appropriately. In the absence of representative statistics, Oracle is much more likely to
choose inefficient access paths. To this end, Oracle supplies a built-in statistics-gathering job for Enterprise Manager
repository statistics maintenance. This job implements a statistics-gathering policy that allows for more flexibility
than the database built-in stats-gathering job.
The job is controlled by the contents of the
EM_STATS_MONITOR
table, which contains a subset of the tables used
by Enterprise Manager together with preference values to supply to the
DBMS_STATS
package. In this release, the only
preferences overridden by the stats-gathering job are the percentage of changed rows required before an object is
considered stale and the granularity at which to gather statistics.
This single scheduler job is logically organized into two separate statistics-gathering activities:
•
A daily run, which ensures that all stale Enterprise Manager object stats are updated
EM_STATS_MONITOR
table
•
A second bi-hourly run, which considers the top stale objects in the
for stats collection
The daily task runs between midnight and 1 AM in the time zone of the database server. The second job runs
every 2 hours and is designed to meet the following objectives:
•
Ensure that volatile tables have current statistics
•
Ensure that provision is made for site-specific needs
In the case of the bi-hourly collection, it may well be that the statistics-gathering regime is overly aggressive. Few
sites will really need bi-hourly runs against repository tables. You may, therefore, wish to modify the schedule of the
EM_GATHER_SYSMAN_STATS
scheduled task, but if you do so, make sure that it still executes between midnight and 1 AM
so as to ensure that the overnight collection still occurs.
Repository Views
When all is said and done, Enterprise Manager stores its data in a set of tables. Access to these base tables
is provided by a set of repository views, which have been documented in Chapter 18 of the
Oracle
Enterprise Manager Cloud Control Extensibility Programmer's Reference
(available online at
http://docs.oracle.com/cd/E24628_01/doc.121/e25161/views.htm#sthref1292
)
. Note that this reference is to
the 12.1.0.2 version of the documentation. If you are still on 12.1.0.1, you need to look at Chapter 17 of the equivalent
document.
■
Some of the views you may be interested in are documented in the extensibility development Kit (edK), so you
may also need to install that to see the documentation for those views. that's done by selecting the Setup menu from the
console, choosing extensibility
➤
development Kit, and following the instructions documented there.
Note
Obviously, with a product as large as Enterprise Manager, there are an equally large number of views that you
can look at, so all are not documented here. The important thing to be aware of is how to find more information on
them, and that's all documented in the aforementioned chapter. Depending on your requirements, you may also want
to investigate the use of BI Publisher, the new reporting capability with EM12c that allows highly formatted custom
reports against the EM repository. (Again, EM includes a restricted-use license for BI Publisher for use against the EM
repository. Use against other data sources will require a BI Publisher license.)
