Database Reference
In-Depth Information
INST SERVICE INTSIZE_CSEC ELAPSEDPERCALL CPUPERCALL DBTIMEPERCALL
---- ---------- ------------ -------------- ---------- -------------
1 PO 511 45824.8171 19861.5611 45824.8171
1 PO 5979 32517.4693 13470.9456 32517.4693
2 PO 512 89777.4994 41022.0318 89777.4994
2 PO 5938 50052.119 23055.3317 50052.119
...
Columns callspersec and dbtimepersec indicate the throughput metrics of a service.
SQL> select inst_id,service_name, intsize_csec, callspersec,
dbtimepersec
from gv$servicemetric where service_name='PO'
order by inst_id, 2,3;
INST SERVICE INTSIZE_CSEC CALLSPERSEC DBTIMEPERSEC
---- ---------- ------------ ----------- ------------
1 PO 512 1489.64844 4099.97602
1 PO 6003 1200.56638 4068.117
...
Note that these metrics are indicative of an average of performance statistics of all the workloads connecting to
that service in an instance. As averages can be misleading, it is important to understand that in a few cases listeners
can redirect the connections to instances that may not be optimal for that service.
Load Balancing Goals
Services can be designed with specific goals, to maximize either response time or throughput. Service metrics are
factored to identify best-instance matching with the service goals. Two attributes of a service definition specify the
service goal; namely, GOAL and CLB_GOAL.
•
Attribute GOAL can be set to a value of either LONG or SHORT. The value of LONG implies
that the connections are long-lived, such as connections originating from a connection pool.
When the attribute GOAL is set to LONG, connections are evenly distributed among available
instances, meaning that the listener tries to achieve an equal number of connections in all
instances where services are available. An attribute value of SHORT triggers runtime load
balancing based upon performance metrics.
•
If the attribute GOAL is set to SHORT, then only the CLB_GOAL attribute is applicable. If the
attribute CLB_GOAL is set to SERVICE_TIME, then connections are redirected to the instance
that can provide better response time for that service. If CLB_GOAL is set to THROUGHPUT,
then connections are redirected to the instance that can provide better throughput for that
service.
If the attribute CLB_GOAL is set to response_time, then service metrics with names ending with percall are
considered to measure goodness of an instance for a service. If the attribute CLB_GOAL is set to THROUGHPUT, then
metrics ending with persec are considered to measure goodness of an instance for a service. Table
4-1
summarizes
these service attributes and offers a short recommendation.
