The Triora Group

Recently I was working on a workflow mailer problem, which meant that I needed to use Oracle’s Application Manager tool. If you don’t know about this tool, then you need to pull out your System Administrator Guide for either Release 11i or Release 12, and read up on it. Oracle has put considerable effort into making this tool a useful interface for looking at the E-Business Suite. So naturally, while tooling around between the screens, I began to wonder how Oracle was deriving the various “pots” of metrics that it was using for screens like the one below:

But how could I find out where those pots of information were stored? One of the first places I started looking was in the database. What tables and / or views relate to that interface? The following query got me started: 

select table_name from dba_tables

where table_name like ‘FND%’; 

With 590 tables returned I needed to narrow that pool. Adding the following predicate got me much closer: 

select table_name from dba_tables

where table_name like ‘FND%’and table_name like ‘%OAM%’; 

This narrowed the list of tables to 78. Being an inquisitive sort, and not having to worry about causing poor performance, I ran a “select *” command against  each table to see what interesting information it contained. But that may have to wait until another article. For this go ’round, the most interesting tables were: 

FND_OAM_METS_TL

FND_OAM_METVAL 

These tables contain the composite information presented in the screen above. The FND_OAM_METVAL table contains the values and short names while the FND_OAM_METS_TL table contains the display name as well as the description. To give you a quick-start, I included the code to get that information: 

select fomt.metric_display_name, fomt.description,

fom.metric_short_name, fom.metric_value

from apps.fnd_oam_metval fom, apps.fnd_oam_mets_tl fomt

where fom.metric_short_name = fomt.metric_short_name; 

This made me wonder if there is a view with similiar information. The following query hones in on the view: 

select view_name from dba_views

where view_name like ‘FND%’ and view_name like ‘%OAM%’; 

This gives us the FND_OAM_METVAL_VL view. It appears that Oracle has provided a faster access path for the information we are interested in. Again, the simple starter code is below: 

select metric_display_name,metric_short_name,description,metric_value

from apps.FND_OAM_METVAL_VL; 

Now what? The code shown in this blog is not meant to be a replacement for OAM (Oracle Applications Manager). But it can be a short cut. One of the ways I have employed this information is as a historical reference. I can take the metrics I am interested in and insert them into a table along with a time stamp that gives me some historical information about E-Business Suite metrics. From there I can derive a baseline of utilization or normalcy, and set an Alert to page or email me when my threshold for a metric of my choosing is crossed. Using this “set it and forget it” method, I can be notified when the usages are outside of my norm and immediately log into the OAM interface and drill down on these metrics. There is a lot of information here and we should use it to our advantage.

Maybe in a later blog I’ll differentiate AMP (Application Management Pack) data from that in OAM.  

As always, we’d love to hear from anyone that has questions or would like us to address E-Business Suite information. Send us an email or comment on our blogs and we’ll do our best to get an answer to you.

If you enjoyed this post, make sure you subscribe to my RSS feed!