Querying the Oracle Management Repository

The Enterprise Manager Cloud Control 12c is an amazing tool allowing the DBA to display a lot of informations from the console . But there are also a lot of repository views that may be consulted by the DBA to build a lot of various reports.

The Oracle Management Service collects a huge amount of raw data from the agents installed on each managed host you administer with EM 12c.

Those raw data are inserted in various tables like EM_METRIC_VALUES for example. Enterprise Manager aggregates those management data by hour and by day. Those raw data are kept 7 days; the one hour aggregated data are kept 31 days, while one day aggregated data are kept one year.

A good way to get information in the Oracle Management Repository is to build queries against the GC%METRIC% views. The GC_METRIC% views are built from EM_METRIC_VALUES. EM_METRIC_ITEMS and EM_METRIC_KEYS tables and the GC$METRIC% views are built from the GC_METRIC% views. There are a lot of GC%METRIC% views :

SQL> select distinct view_name from user_views where view_name like 'GC%METRIC%' order by 1;

GC$METRIC_CATEGORIES
GC$METRIC_COLUMNS
GC$METRIC_COLUMNS_TARGET 
GC$METRIC_ERROR_CURRENT
GC$METRIC_ERROR_HISTORY
GC$METRIC_GROUPS
GC$METRIC_GROUPS_TARGET
$METRIC_KEYS
GC$METRIC_LATEST
GC$METRIC_STR_VALUES
GC$METRIC_STR_VALUES_LATEST
GC$METRIC_VALUES
GC$METRIC_VALUES_DAILY
GC$METRIC_VALUES_HOURLY
GC$METRIC_VALUES_LATEST
GC$TARGET_METRIC_COLLECTIONS
GC_METRIC_COLLECTIONS
GC_METRIC_COLUMNS
GC_METRIC_COLUMNS_TARGET
GC_METRIC_COMPOSITE_KEYS
GC_METRIC_GROUPS
GC_METRIC_GROUPS_TARGET
GC_METRIC_KEYS
GC_METRIC_LATEST
GC_METRIC_LATEST_WO_TGT
GC_METRIC_STATS_METRIC
GC_METRIC_STATS_METRIC_DAILY
GC_METRIC_STATS_TARGET
GC_METRIC_STATS_TARGET_DAILY
GC_METRIC_STR_VALUES
GC_METRIC_STR_VALUES_LATEST
GC_METRIC_STR_VALUES_WO_TGT
GC_METRIC_THRESHOLDS
GC_METRIC_VALUES
GC_METRIC_VALUES_DAILY
GC_METRIC_VALUES_HOURLY
GC_METRIC_VALUES_LATEST
GC_METRIC_VAL_HOURLY_WO_TGT
GC_RAT_TRIAL_METRIC_VALUES
GC_RAT_TRIAL_TARGET_METRICS

For example if we are interested by the load for a host, you can use the console:

You can also find this information by querying the OMR in the view gc$metric_columns for an entity of type host and a metric_group_label of type Load:

SQL> select entity_type,metric_column_label,metric_group_label
from gc$metric_columns
where entity_type = 'host'
and metric_group_label = 'Load';

host       Active Logical Memory, Kilobytes              Load
host       Active Memory, Kilobytes                      Load
host       CPU Interrupt Time (%)                        Load
host       CPU Queue Length                              Load
host       CPU Utilization (%)                           Load
host       CPU in I/O Wait (%)                           Load
host       CPU in System Mode (%)                        Load
host       CPU in User Mode (%)                          Load
host       Free Memory (%)                               Load
host       Free Memory, Kilobytes                        Load
host       Logical Free Memory (%)                       Load
host       Memory Page Scan Rate (per second)            Load
host       Memory Utilization (%)                        Load
host       Page Transfers Rate                           Load
host       Run Queue Length (1 minute average,per core)  Load
host       Run Queue Length (15 minute average,per core) Load
host       Run Queue Length (5 minute average,per core)  Load
host       Swap Free (KB)                                Load
host       Swap Utilization (%)                          Load
host       Swap Utilization, Kilobytes                   Load
host       Total Processes                               Load
host       Total Users                                   Load

We can sort the metrics collected by entity type for example host :

SQL> select entity_type,metric_group_label
from gc$metric_columns
where entity_type = 'host'
group by entity_type,metric_group_label
order by 1,2;

host                                     All Processes
host                                     Battery details
host                                     Buffer Activity
host                                     CCCAppFileParser
host                                     CCCData_base
host                                     CCCData_group
host                                     CCCData_mapping
host                                     CCCData_obs
host                                     CCCData_queue
host                                     CCCData_warning
host                                     CCCWatchdog
host                                     CPU Usage
host                                     CPU Usage Internal
host                                     CPUs Details
host                                     Compute Node Temperature
host                                     DiscoverNow
….

Or an oracle database:

SQL> select entity_type,metric_group_label
from gc$metric_columns
where entity_type = 'oracle_database'
group by entity_type,metric_group_label
order by 1,2;

oracle_database                         ADDM Report - Global
oracle_database                         ALL Privileges
oracle_database                         ANY Privileges
oracle_database                         Access To Important Tables And Views
oracle_database                         Active Sessions by CPU and Wait Classes
oracle_database                         Alert Log
oracle_database                         Alert Log Content
oracle_database                         Alert Log Error Status
oracle_database                         Archive Area
oracle_database                         Archive Area - RAC Instance
oracle_database                         Audit Settings
oracle_database                         Audit Syslog Level
oracle_database                         AutoTask Client
oracle_database                         CPU Usage
oracle_database                         Collect SQL Response Time
oracle_database                         Connect Role
oracle_database                         Control files
oracle_database                         DB Alert Log
oracle_database                         DB Audit Files Permissions
oracle_database                         DB Control Files Permission
oracle_database                         DB Data Files Permissions
oracle_database                         DB InitParameter File Permissions
oracle_database                         DB Link Usage
oracle_database                         DB Password Setting
oracle_database                         DB Profile Setting
oracle_database                         DB Scheduler Jobs
oracle_database                         DBA Group Assignment
oracle_database                         DGPrimaryDBName
oracle_database                         Data Base parameter collection
oracle_database                         Data Failure
oracle_database                         Data Guard - 10.1 Database
oracle_database                         Data Guard - 9.2 Database
oracle_database                         Data Guard Failover
oracle_database                         Data Guard Fast-Start Failover
oracle_database                         Data Guard Fast-Start Failover Observer

So the main interest is to collect data from those views, for example we can look more precisely in the CPU consumption for a host:

SQL> select entity_name ,collection_time,min_value as min,avg_value as avg,max_value as max
from gc$metric_values_daily
where metric_group_name = 'Load'
and metric_column_name = 'cpuUtil'
and (entity_name like '%dbserver1%' )
order by 2,1 asc;

Entity_name                 collection_time         min         avg              max
dbserver1.localdomain         01-JUN-15            3.782      17.4089931       72.284
dbserver1.localdomain         02-JUN-15            2.788      11.3085764       38.61
dbserver1.localdomain         03-JUN-15            4.16       11.7149271       28.975
dbserver1.localdomain         04-JUN-15            5.526      11.6824271       29.451
dbserver1.localdomain         05-JUN-1             5.337      10.9915486       27.319
dbserver1.localdomain         06-JUN-15            5.664      16.7499653       45.798
dbserver1.localdomain         07-JUN-15            5.021      15.4098958       84.75
dbserver1.localdomain         08-JUN-15            4.664       9.7744375       28.159
dbserver1.localdomain         09-JUN-15            4.79        9.4026805       21.113
dbserver1.localdomain         10-JUN-15            5.17        9.5937881       31.238
dbserver1.localdomain         11-JUN-15            5.162       8.8185104       16.562
dbserver1.localdomain         12-JUN-15            5.666      11.0074132       33.058
dbserver1.localdomain         13-JUN-15            6.412      15.4432917       42.709
dbserver1.localdomain         14-JUN-15            5.897       9.6474548       40.575
dbserver1.localdomain         15-JUN-15            5.675      14.5982083       34.483

For a more precise measure you can select on gc$metric_values_hourly:

SQL> select entity_name,collection_time,min_value as min,avg_value as avg,max_value as max
from gc$metric_values_hourly
where metric_group_name = 'Load'
and metric_column_name = 'cpuUtil'
and (entity_name like 'dbserver1%' )
order by 1,2
/

Entity_name              collection_time                min            avg        max
….
dbserver1.localdomain   15-JUN-15 14:00:00            15.23            18.65     22.025
dbserver1.localdomain   15-JUN-15 15:00:00             7.844            9.17     11.406
dbserver1.localdomain   15-JUN-15 16:00:00             8.635           10.76     13.625
dbserver1.localdomain   15-JUN-15 17:00:00             7.928            9.57     16.607
dbserver1.localdomain   15-JUN-15 18:00:00             8.439           13.39     21.518
dbserver1.localdomain   15-JUN-15 19:00:00             7.722           10.56     14.767
dbserver1.localdomain   15-JUN-15 20:00:00             7.993            9.09     12.934
dbserver1.localdomain   15-JUN-15 21:00:00             7.449            9.20     12.2
dbserver1.localdomain   15-JUN-15 22:00:00             8.001           10.47     16.328
dbserver1.localdomain   15-JUN-15 23:00:00             7.366            8.77     14.51
….

We can also display those values in the EM12c console:

Thus there are great possibilities to query the gc$metric_values% views in order to build reports. For example if we look at the different metric_group_label for an oracle database entity:

SQL> select distinct metric_group_label from gc$metric_values_daily
2   where entity_type = 'oracle_database' order by 1;

Alert Log Error Status
Archive Area
CPU Usage
Database Job Status
Database Limits
Database Size
Deferred Transactions
Dump Area
Efficiency
Flash Recovery
Global Cache Statistics
Interconnect Traffic
Invalid Objects by Schema
Memory Usage
Messages per buffered queue
Messages per buffered queue per subscriber
Messages per persistent queue
Messages per persistent queue per subscriber
OCM Instrumentation
Optimal SGA
PGA Allocated
Recovery Area
Response
SCN Growth Statistics
SCN Instance Statistics
SCN Max Statistics
SGA Pool Wastage
Segment Advisor Recommendations
Space usage by buffered queues
System Response Time Per Call
Tablespace Allocation
Tablespaces Full
Throughput
User Audit
User Block
User Locks
Wait Bottlenecks
Waits by Wait Class
backup_archive_log_age
datafile_backup
flashback available
incident_meter
max number of datafiles

Then we can ask for more precise measures:

SQL> select distinct key_part_1 from gc$metric_values_daily 
where metric_group_name = 'wait_sess_cls' 
order by 1;

Administrative
Application
Cluster
Commit
Concurrency
Configuration
Idle
Network
Other
Queueing
Scheduler
System I/O
User I/O

And we can write a SQL request to analyse the User I/O wait:

SQL> select collection_time,
min_value as min_wait ,
avg_value as avg_wait ,
max_value as max_wait
from gc$metric_values_daily
where metric_group_name = 'wait_sess_cls'
and metric_column_name = 'dbtime_waitclass_pct'
and (entity_name like 'dbserver1%' )
and key_part_1 = 'User I/O'
order by 1,2 asc;

Collection_time       min_wait          avg_wait          max_wait
01-JUN-15            .190094016        45.7777238        86.9431254
02-JUN-15            .026723742        49.4623178        98.6886502
03-JUN-15            .09020789          32.6063701        98.7430495
04-JUN-15            .066328202        26.342438          97.1319971
05-JUN-15            .032959651        22.7424382        96.1533236
06-JUN-15            .574570414        22.2140084        93.9315499
07-JUN-15            .022970399        20.6773665        88.9020253
08-JUN-15            .058117038        17.5328715        98.5629756
09-JUN-15            .080441214        20.5507072        98.4615166
10-JUN-15            .057529812        19.1080933        97.4292408
11-JUN-15            .038726475        21.6932703        96.48269
12-JUN-15            .106343267        25.0541945        89.5404451
13-JUN-15            .257816863        13.5371153        80.1841142
14-JUN-15            .004258955        16.5949771        84.7824337
15-JUN-15            .020058791        19.1270265        89.8952919

Naturally we find back those I/O values in the EM12c console:

Conclusion: querying the OMR views might allow Oracle DBA to build interesting reports on Hosts and Databases activities.