RAC Operational Practices - Expert Oracle RAC 12c - page 73

Table 4-1. Service Goals

GOAL CLB_GOAL Service metrics

LONG Ignored Suitable for long-lived connections such as connection pool connections.

SHORT Response_time Service is redirected to an instance that can provide better response_time.

Suitable for OLTP workload.

SHORT Throughput Service is redirected to an instance that can provide better throughput.

Suitable for DSS/Warehouse workload.

The MMON process calculates goodness and delta columns using service metrics. A lower value for the
goodness column indicates quality of service in that instance (in retrospect, it should have been called a badness
indicator). The delta column indicates how the value of the goodness column will change if a new connection is
added to that service. In this example output, PO service is defined with the GOAL attribute set to the LONG value,
and so the goodness column simply indicates the number of connections to that service in that instance. Since
instances 2 and 3 have lower values for the goodness column, a new connection to PO service will be redirected to
instance 2 or 3.

SQL> select inst_id, service_name, intsize_csec,goodness,delta
from gv$servicemetric
where service_name='PO' order by inst_id, 2,3;

INST SERVICE INTSIZE_CSEC GOODNESS DELTA
---- ---------- ------------ ---------- ----------
1 PO 514 1883 1
1 PO 5994 1883 1
2 PO 528 1820 1
2 PO 5972 1820 1

In versions 11.2 and earlier, the PMON process propagates the service metrics to the listeners registered in
local_listener and remote_listener initialization parameters. As the remote_listener specifies the address of SCAN
listeners and the local_listener parameter specifies the address of the VIP listener, the PMON process propagates the
service metrics to both SCAN and VIP listeners. You can trace listener registration using the following command.

alter system set events='immediate trace name listener_registration level 15';

The PMON trace file shows how the quality of service is propagated to listener processes. Here is an example
(output of PMON process sending the service quality to the listener process).

kmmgdnu: po
goodness=100, delta=10,
flags=0x4:unblocked/not overloaded, update=0x6:G/D/-

In version 12c, listener registration is performed by a new mandatory background process named LREG.
At database start, the LREG process polls the listeners specified in the local_listener and remote_listener parameters
to identify whether the listeners are running. If the listeners are available, then the services are registered to the
listeners by the LREG process. The earlier command to trace listener registration in version 11.2 is applicable to
version 12c also, but the listener registration details are written to an LREG trace file instead of a PMON trace file.