You can create Metric Extensions to leverage Database Management as the central monitoring tool for your Managed Databases.

To create a Metric Extension:

1. Sign in to the Oracle Cloud Infrastructure console.
2. Open the navigation menu, click Observability & Management. Under Database Management, click Administration.
3. On the left pane, click Metric Extensions and select a compartment in the Compartment drop-down list. The Metric Extension should be created in the compartment in which the database resides or is monitored in.
4. Click Create Metric Extension.
5. In the Create Metric Extension panel:
   1. Provide the following Metric Extension properties:
      1. Name: Enter a name for the Metric Extension. The Metric Extension name is automatically prefixed with ME_.
      2. Display name: Enter a display name for the Metric Extension, if different from the name. The Display name can have spaces and numbers.
      3. Description: Optionally, enter a description for the Metric Extension.
   2. Provide collection method properties to specify the database resource type, such as the CDB, PDB, or non-CDB for which the Metric Extension is being created, and other properties:
      1. Resource type: Select the database resource type for which you're creating a Metric Extension.
      2. Collection method: Select the SQL collection method to execute custom SQL queries against Managed Databases, returning results in a metric table.
      3. Collection frequency: Select options to specify how often the Metric Extension must be collected.
      4. SQL query: Enter the SQL query. For example:

         select a.ename, (select count(*) from emp p where p.mgr=a.empno) directs from emp a

         PL/SQL statements are also supported, and if used, the Out parameter position and Out parameter type properties must be populated. Bind variables can be passed to the SQL query using In parameter properties.

         Select Upload file above the SQL query field to select or drag and drop the SQL script to execute and provide the relevant input-output parameter properties.

      5. In parameter: Optionally, enter the bind variable if using the SQL query option or input parameters if using the SQL script option.
         Note
         
         In parameter supports the use of instance property place holder, which can be used to replace the placeholder with the actual instance property value of the Managed Database for which the metric is being collected. For example, for oracle_psft, db_service_name is an instance property. It can be passed as an In parameter value within a pair of % (percentage) symbols, %db_service_name%.
      6. Out parameter position: Optionally, enter the position number of the output parameter.
      7. Out parameter type: Optionally, select the type of output parameter. This field is only enabled if the position number of the output parameter is specified in the Out parameter position field.
   3. Provide the following metric and dimension properties:
      1. Name: Enter the name of the metric or dimension in PascalCase. For example, CpuUtilization and TotalDatabaseSize.
         Note
         
         Metrics in a Metric Extension with the draft status can have the same names for the same resource type, but not when the Metric Extension is published. If a particular metric name for a resource type is already taken and is used in a published Metric Extension, then the same cannot be used by another metric for the same resource type. For example, if there is a published Metric Extension ME_FirstMetricExtension for a particular resource type and it has a metric named MetricFirst, then you cannot publish another Metric Extension, ME_SecondMetricExtension for the same resource type with a metric also named MetricFirst.

         This constraint is not applicable to hidden metrics and dimensions.

      2. Display name: Optionally, enter a display name for the metric or dimension, if different from the name. The Display name can have spaces and numbers.
      3. Is dimension?: Select Yes if you're defining a metric dimension and No if you're defining a metric.

         A dimension is a qualifier for a metric and it should be a unique value for each row in the metric collection results. For example, a metric could be the percentage of storage space utilized, and the dimension is the name of the file system.

      4. Is hidden?: Select Yes if the metric will only be used in compute expressions, which means that the metric is used only as a value to compute another metric or it is not required to be sent to the Oracle Cloud Infrastructure Monitoring service. Otherwise, select No.
      5. Value type: Select the value type. All non-hidden metrics must be numeric; a hidden metric or dimension can be String or Number.
      6. Unit: Optionally, enter the unit associated with the metric. Depending on the metric, possible units could be latency in seconds, milliseconds, microseconds, minutes, or frequency in Hertz or percentage. This field is only enabled for metrics and is greyed out for dimensions.
      7. Category: Select the type of metric data the metric is collecting: Availability, Capacity, Load, Utilization. This field is only enabled for metrics that are not hidden and is greyed out for dimensions and for metrics that are hidden.
      8. Compute expression: Optionally, enter compute expressions to calculate the value of a metric based on mathematical or logical operations performed on other metrics or dimensions within the same Metric Extension. Compute expressions require at least one other metric to be defined first, and can only include those other metrics that have already been defined in the Metric Extension. For additional details, see Compute expressions.
      9. Add another metric/dimension: Click to add another metric or dimension to the Metric Extension.
6. Click one of the following options to create the Metric Extension:
   • Create and test: Click to test the Metric Extension against one or more Managed Databases and verify the values returned are correct. Based on the values returned, you can continue to edit and test in an iterative manner.
     Note
     
     When testing a Metric Extension the Management Agent is restarted. It's recommended to test Metric Extensions on non-production databases.
   • Create: Click to save the Metric Extension definition and test against Managed Databases at a later time.

WITH wait_stats AS (
SELECT
inst_id,
wait_class,
time_waited_fg
FROM
TABLE ( gv$(CURSOR(
SELECT
to_number(userenv('INSTANCE')) AS inst_id,
wait_class,
time_waited_fg / 100 AS time_waited_fg
FROM
v$system_wait_class
WHERE
wait_class <> 'Idle'
)) )
), inst_list AS (
SELECT
instance_number,
instance_name,
host_name
FROM
TABLE ( gv$(CURSOR(
SELECT
instance_number,
instance_name,
host_name
FROM
v$instance
)) )
)
SELECT
inst.instance_number instance_number,
inst.instance_name instance_name,
inst.host_name host_name,
ws.wait_class wait_class,
ws.time_waited_fg time_waited_fg
FROM
wait_stats ws,
inst_list inst
WHERE
inst.instance_number = ws.inst_id