Database Reference
In-Depth Information
the table EM_INT_PARTITIONED_TABLES contains the default retention times for various interval-partitioned
tables supplied as part of the repository.
Note
Queue Management
Enterprise Manager makes significant use of Oracle's in-database queuing technology (Oracle Streams Advanced
Queuing, commonly referred to as AQ). This technology can require some care and maintenance to ensure that
performance remains at optimal levels. This is especially true if you chose to implement an Oracle Real Application
Clusters solution for your repository database. AQ queues are implemented using a variety of database technologies,
including index-organized tables. If message dequeuing does not take place efficiently or completely, then over time
the underlying data structures may grow significantly, and message enqueue and dequeue times may increase.
Enterprise Manager uses queues heavily, specifically for metric load and upload and for notifications. The first
indication you will likely have of a problem in this area will be that there is a persistent and increasing upload backlog,
or a delay in notifications. Unfortunately, both of these symptoms can also have other causes.
The initial database setup for Oracle Enterprise Manager doesn't specifically configure the database as per the
general guidance for AQ. In particular, AQ will benefit from a dedicated memory pool called the Streams pool. In a
default installation, you will likely find this pool size unset, and so only a minimal memory allocation will be used for
Advanced Queuing. I recommend you set the initialization parameter STREAMS_POOL_SIZE to a value of 100MB as a
starting point (the AQ documentation suggests 20MB, but in my experience, that is undersized for busy systems). You
can monitor memory usage of the streams pool by using the query in Listing 3-2.
Listing 3-2. Memory Usage of the Streams Pool
SELECT name,round(sum(bytes)/1024/1024,1) memory_mb
FROM v$sgastat
WHERE pool = 'streams pool'
GROUP BY name;
If you chose a RAC implementation for your repository database, you may also wish to configure instance affinity
for your queue tables to minimize intra-instance block pinging. You should, of course, verify with Oracle Support
that any such modification to the queue definition will be supported. In order to perform this task, you will need the
AQ_ADMINISTRATOR_ROLE role and execute rights on the SYS.DBMS_AQADM package. You assign a preferred instance by
using code like that in Listing 3-3.
Listing 3-3. Altering a Queue Table's Instance Affinity
BEGIN
SYS.DBMS_AQADM.ALTER_QUEUE_TABLE(
queue_table => 'SYSMAN.EM_CNTR_QTABLE',
primary_instance => 1);
END;
/
Setting the instance affinity controls only which QMn process consumes messages and doesn't prevent other
instances from using the queue.
Note
 
 
Search WWH ::




Custom Search