Figure 5 Configuring Services using DBCA
Users request to connect to a specific service when connecting. For instance, the TNSNAMES alias below requests a connection to the BATCHJOBS service; if we arrange for batch jobs to specify the BATCHJOBS TNS alias we can be assured that they will connect to the appropriate service and hence be allocated to the desired instances:
BATCHJOBS=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host3-vip)(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = batchjobs)
)
)
Services serve two main purposes in RAC:
When we make use of services, we are deliberately manipulating the workloads on each instance, so we’re unlikely to see the perfect balance that we might aspire to when every instance in the cluster is subject to the same workload. However, avoiding an overloaded instance within the cluster is just as important when using services.
We can view service workload through the GV$SERVICE_STATS view, which shows various workload statistics for each service. This query breaks down service CPU across the cluster, showing the percentage of total CPU is consumed by the service on the instance, and how the service workload is distributed across the cluster:
SQL> BREAK ON instance_name skip 1
SQL> COMPUTE SUM OF cpu_time ON instance_name
SQL>
SQL> WITH service_cpu AS (SELECT instance_name, service_name,
2 round(SUM(VALUE)/1000000,2) cpu_time
3 FROM gv$service_stats
4 JOIN
5 gv$instance
6 USING (inst_id)
7 WHERE stat_name IN ('DB CPU', 'background cpu time')
8 GROUP BY instance_name, service_name )
9 SELECT instance_name, service_name, cpu_time,
10 ROUND(cpu_time * 100 / SUM(cpu_time)
11 OVER (PARTITION BY instance_name), 2) pct_instance,
12 ROUND( cpu_time
13 * 100
14 / SUM(cpu_time) OVER (PARTITION BY service_name), 2)
15 pct_service
16 FROM service_cpu
17 WHERE cpu_time > 0
18 ORDER BY instance_name, service_name;
Instance Service Cpu Pct Of Pct of
Name Name secs Instance Service
-------- --------------- ----------- -------- -------
MELRAC1 MELRACDB 4,292 51.50 34.70
MEL_SRV3 619 7.42 100.00
MEL_SRV4 621 7.45 53.29
SYS$BACKGROUND 0 .00 42.86
SYS$USERS 2,802 33.62 1.32
******** -----------
sum 8,334
MELRAC2 MELRACDB 3,834 1.78 31.00
MEL_SRV1 990 .46 100.00
MEL_SRV4 544 .25 46.71
MEL_SRV5 1,228 .57 100.00
SYS$BACKGROUND 0 .00 28.57
SYS$USERS 208,954 96.94 98.32
******** -----------
sum 215,551
MELRAC3 MELRACDB 4,241 71.87 34.29
MEL_SRV2 385 6.52 100.00
MEL_SRV6 507 8.60 100.00
SYS$BACKGROUND 0 .00 28.57
SYS$USERS 768 13.01 .36
******** -----------
sum 5,901
Other views – such as GV$SERVICE_EVENT – provide additional perfo