Recently we had a question in the Oracle Communities as to whether it was possible to create an Enterprise Manager Information Publisher report to display V$pgastat values retrieved from a target database over time.
Information Publisher reports can seem a bit confusing because they can be run against different database targets so they may give the impression that they're actually running SQL on those remote database targets. In fact the Information Publisher reports, never actually run any sql on a remote target database. They are designed to report on information held in the Enterprise Manager Repository views. The EM repository views contain all kinds of different information. For example, metric views, job views etc. This information was originally collected by the management agents, and has been uploaded to the Management Repository.
But what happens if the management repository tables don't include the information that you wish to see in your report? In this example the customer wanted to report on all the information from v$pgastat. EM doesn't collect this automatically. It collects the metric 'PGA Allocated' which is the total PGA allocated (from v$sysmetric summary) but it does not collect the other PGA stats. So we can create a metric extension which will collect this information. A metric extension is a custom written metric. It's similar to the 'user defined metric' which existed in 11g, but more powerful. Once the metric extension has been tested and deployed, it can be collected on multiple different databases. The data which the metric extension collects, will be collected by the remote agents at the collection interval you specify, and uploaded to the Enterprise Manager repository (just like a 'normal' metric'). Once the data is in the repository, then an Information publisher report can be created to report on it. Let’s look at how this can be done.
A) Create the metric extension
1) Launch the 'metric extension' menu from:
2) From the metric extension page, click on "Create"
3) Fill in the 'general properties' for example:
Go to next
4) Fill in the 'adapter' properties. This is a sql metric extension, so it will use a sql query.
Below is the full query that will run on the remote database target, to collect the V$pgastat information. The full query is:
select sum (case when name='extra bytes read/written' then value else 0 end) ExtrabytesReadWritten,
sum (case when name='recompute count (total)' then value else 0 end) RecomputeCount,
sum (case when name='total PGA inuse' then value else 0 end) totPGAinuse,
sum (case when name='process count' then value else 0 end) processCount,
sum (case when name='over allocation count' then value else 0 end) OverAllocCount,
sum (case when name='PGA memory freed back to OS' then value else 0 end) PGAMemtoOS,
sum (case when name='maximum PGA used for manual workareas' then value else 0 end) MaxPGAmanualWork,
sum (case when name='cache hit percentage' then value else 0 end) cacheHitPercent,
sum (case when name='aggregate PGA target parameter' then value else 0 end) aggregPGATarget,
sum (case when name='aggregate PGA auto target' then value else 0 end) aggregatePGAautoTarget,
sum (case when name='total PGA allocated' then value else 0 end) TotalPGAAlloc,
sum (case when name='total freeable PGA memory' then value else 0 end) totalFreePGAmem,
sum (case when name='max processes count' then value else 0 end) maxProcCount,
sum (case when name='total PGA used for auto workareas' then value else 0 end) TotalPGAAutoWork,
sum (case when name='maximum PGA used for auto workareas' then value else 0 end) maxPGAUsed,
sum (case when name='bytes processed' then value else 0 end) bytesProcessed,
sum (case when name='global memory bound' then value else 0 end) GlobalMemBound,
sum (case when name='maximum PGA allocated' then value else 0 end) MaxPgaAlloc,
sum (case when name='total PGA used for manual workareas' then value else 0 end) TotalPGAManWork
Ensure that this query can run on the remote database (the database which you want to collect the V$pgastat info from) as the dbsnmp user (the dbsnmp user is normally the user set for the monitoring credentials)
5) Define the columns which will be returned by the query. The query has a column alias which needs to be used as the 'Name' and you can use what you want for the "Display Name". In this particular example, all these columns are data columns. If you would like users to be able to specify a threshold in EM (for example, to receive an alert if the v$pgastat values cross a user specified threshold), then you need to add a suitable comparison operator. For example:
It is necessary to add a column for each column which is returned by the query (and in the same order). Example screenshot after all columns are added:
Here I’ve just scrolled down to the bottom of the screen, to show all the columns
6) In the credential screen you can either specify a set of credentials to test with or you can use the 'default monitoring credentials'. These are set from
- setup/security/Preferred credentials
- Highlight the 'database instance' target type
- and click on 'manage preferred credentials'
The default monitoring credentials can be set here. Note that the 'default monitoring credentials' will only be used, if there are no target preferred credentials set. So if an individual database has target preferred credentials set here, these will be used.
click on 'next'
7) In the 'test' screen, we will test the new metric extension against a database target. Click on 'add' to add a target. Choose a remote database (where you are interested in collecting this V$pgastat information). Highlight the row, and click on 'run test'. The test will generate output on the screen - eg.
This shows that the metric extension has returned the correct rows for the target.
View the summary screen:
scroll down for the bottom..
8) Now the metric extension has been created it needs to be saved as a deployable draft.
- Highlight the metric extension
- Go to Actions/Save as deployable draft
9) Now deploy the metric extension to a database target
- Highlight the metric extension
- Go to Actions/Deploy to targets
- Choose a database target to deploy to
Click on submit
A message should show:
Metric Extension ME$rachelPGASTATS deploy operation successfully submitted
10) Go to the home page of the database where the metric extension was deployed. In this example, it's the 'rachel' database.
11) Go to
- Oracle Database (drop down menu)
- Monitoring/Metric and collection settings
- By default this will show the 'metrics with thresholds' view. For example:-
Use the drop down menu to change to the view from 'metrics with thresholds' to 'all metrics'. This is because 'metrics with thresholds' only shows metrics where thresholds have been set (usually the thresholds are set out of the box, or they may have been set by an EM administrator). When the 'all metrics' view is chosen, the new metric extension should be seen. Scroll down to locate the metric extension.
12) We can double check that this metric is scheduled on the agent and see when the metric will be collected by going to the agent machine (of the database where the metric extension is deployed) and issuing the command:
emctl status agent scheduler | grep ME$*
Note: the metric extensions will be prefixed with "ME$*"
The scheduler will show the metric, and the timestamp of the next collection time. The metric can be forced to run immediately, instead of waiting for the next collection time, by doing:
emctl control agent runCollection <dbname>:oracle_database <metricName>
When this command is run against a metric extension, the metric extension name must be enclosed in single speech marks. For example:-
After running a manual collection it is also a good idea to force that collection to be uploaded to the repository so that we can check the data in the repository straight away. To do this, use the command:
emctl upload agent
13) Now that the metric has been collected and uploaded, we can check that the metric data exists in the repository database. In this example, the sql is run against the repository database from Enterprise Manager using SQL*Worksheet.
Here we can see that there are two collection samples uploaded to the repository.
14) Notice that once data has been collected we can access charts and historical information about the data collected by this new metric extension. For example:
- go to database home page/monitoring/all metrics
- scroll down to the metric 'rachelPGASTATS'
- click on the arrow to the left to expand
- Highlight a row - for example 'aggregate PGA auto target'
It is possible to view this data in 'real time', 'last 24 hours' , 'last 7 days', 'last 31 days' etc. (although you would have to until 7 days had passed to see the last 7 days worth of data etc)
15) At this stage the metric extension is still a 'deployable draft'. This is fine for testing. But if you want to share it between EM administrators, or use it in a monitoring template, it needs to be published. This can be done from:
Enterprise/Monitoring/Metric Extensions/Highlight the metric extension and choose “Actions/Publish Metric Extension”.
For more information on the metric extension lifecycle see:
Enterprise Manager Cloud Control Administrator's Guide
9 Using Metric Extensions
9.2 Metric Extension Lifecycle
B) Create an Information Publisher report based on the information in the repository. Now that the data is in the repository, we would like to create a report to display this data.
1) For the purposes of this demonstration , it is not necessary to write our own sql to extract the metric data from the repository because the report provides a 'metric details' element which can be used. To create the report
- Go to Enterprise/Reports/Information Publisher Reports
- Click on 'create'
- The 'general' tab is shown
- Fill in the general details, title, category etc.
When finished, go to the next 'Elements' tab
2) From the 'Elements' tab click on 'add'. This will bring up a list of the elements for that target type. 'Elements' provide an easy way to create the report, for example, when using an element you do not need to know the repository table which holds the metric data details. When the 'metric details' element is chosen for example, it will present a list of metrics to choose from. In this way, it is not necessary to write any customized sql. The different elements which can be used are shown here:
3) Select "Metric Details" and click on 'continue', the screen will look as follows:
4) Click on 'set parameters' and scroll down the list to find the first metric that you want to include in the report, for example:
5) Click on 'continue' and click on 'add' to repeat the process. If you want to display charts for all the V$PGASTAT queries which we collected, the screen should look like this:
6) Click on 'preview' to see the report:
The preview will ask for a database target. Make sure that the target where the metric extension is deployed is chosen.
If you’re happy with the report, then scroll down to the bottom and click on
“Return to Edit Report Definition:<your report name>” and click on ‘OK’ to save.
The report should look like this:
(I added all the charts to this report, but the screenshot only shows the first three). Remember it will only be possible to run this report (and retrieve data) for the database targets which have the metric extension deployed to them. I have not covered the scheduling of the report, but it is possible to have the report scheduled to run at a particular time, and to receive an email of the report at specific intervals. This is covered more in the documentation.
See Enterprise Manager Cloud Control Administrator’s Guide.
Chapter 26. Using Information Publisher – 26.4 Scheduling reports.