Basically what figured out database load balancing advisor and its what is hort and long service . Hence thought of documenting it .
In this Blog we will try to cover below points
1) How To Create and managed services
2) Services and load balancing advisor
3) Short and Long services .
4) run time load balancing
Create and Manage services :
srvctl add service -d PWIC -s CUHISTORY_SRVC -r PWIC1 -a PWIC2,PWIC3,PWIC4
srvctl add service -d QWIC -s CUHISTORY_SRVC -r QWIC1 -a QWIC2
srvctl add service -d db_unique_name -s service_name -r preferred_list [-a available_list]
srvctl config database -d ORCL
srvctl status service -d CCIHC
srvctl modify service -d PRDBC -s BATCHRPT_SRVC -n -i PRDBC2,PRDBC3 -a PRDBC
srvctl relocate service -d PRDBC -s BATCHRPT_SRVC -i PRDBC4 -t PRDBC2
crsctl relocate resource resource_name –n node_name
srvctl start service -d QGISC -s ora.QGISC.INS_FGEA.QGISC2.srv
srvctl start service -d PRDBC -s BATCHRPT_SRVC -i PRDBC3
srvctl start service –d pmpic –s pmpic_gen
srvctl stop service -d QGISC -s ora.QGISC.INS_FGEA.QGISC2.srv
srvctl stop service –d pmpic –s pmpic_gen
crsctl stop resource ora.QGISC.INS_WFC.QGISC2.srv
crsctl stop resource ora.QGISC.QGISC_INS_FGEA.QGISC2.srv
SELECT username, program, machine, service_name FROM gv$session;
select name,network_name,enabled , failover_method,failover_type ,failover_retries
,failover_delay from dba_services ;
select name,network_name,enabled ,goal,min_cardinality,max_cardinality from dba_services ;
select inst_id, name,network_name,blocked from gv$active_services ;
select service_id, name , goal from v$active_services;
show parameter service_name
SELECT sid, serial#,
client_identifier, service_name, action, module
FROM V$SESSION
select inst_id,service_id,name,network_name
from gv$services
order by 1,2;
pause
select inst_id,service_id,name,network_name
from gv$active_services
order by 1,2;
col name for a16
col network_name for a16
col failover_method for a16
col failover_type for a16
col service_name for a20
col stat_name for a35
pause see all services that are defined
select service_id,name,network_name,creation_date,
failover_type,failover_method
from dba_services;
select service_name,stat_name,value
from v$service_stats
order by service_name
/
select INST_ID,VALUE from gv$parameter where NAME='service_names';
alter system set service_names='CFEED_SRVC, BATCHRPT_SRVC,PRDBC.envoy.net, DBR_SRVC'
scope=memory sid='PRDBC2';
select inst_id, username, program , logon_time from gv$session where service_name =
'SLICER_SRVC' order by inst_id ;
lsnrctl services > ab.log
lsnrctl services | grep service2
srvctl status service -d PRDBC
srvctl config service -d PRDBC
srvctl modify service -d PRDBC -s BATCHRPT_SRVC -n -i PRDBC2,PRDBC3 -a PRDBC4
srvctl relocate service -d PRDBC -s BATCHRPT_SRVC -i PRDBC4 -t PRDBC2
srvctl start service -d PRDBC -s BATCHRPT_SRVC -i PRDBC3
Services and Load Balancing Advisor :
Load balancing distributes work across all of the available Oracle RAC database instances. Oracle recommends that applications use connection pools with persistent connections that span the instances that offer a particular service. When using persistent connections, connections are created infrequently and exist for a long duration. Work comes into the system with high frequency, borrows these connections, and exists for a relatively short duration. The load balancing advisory provides advice about how to direct incoming work to the instances that provide the optimal quality of service for that work. This minimizes the need to relocate the work later.
By using the Load Balancing Advisory and run-time connection load balancing goals, feedback is built in to the system. Work is routed to provide the best service times globally, and routing responds gracefully to changing system conditions. In a steady state, the system approaches equilibrium with improved throughput across all of the Oracle RAC instances.
Standard architectures that can use the load balancing advisory include connection load balancing, transaction processing monitors, application servers, connection concentrators, hardware and software load balancers, job schedulers, batch schedulers, and message queuing systems. All of these applications can allocate work.
The load balancing advisory is deployed with key Oracle clients, such as a listener, the JDBC universal connection pool, OCI session pool, Oracle WebLogic Server Active GridLink for Oracle RAC, and the ODP.NET Connection Pools. Third-party applications can also subscribe to load balancing advisory events by using JDBC and Oracle RAC FAN API or by using callbacks with OCI.
The load balancing advisory has the task of advising the direction of incoming work to the RAC instances that provide optimal quality of service for that work.
To test the load balancing, you can use the scripts in the appendix to apply load and the following section for monitoring.
-- view load balancing gaol of a current service
-- NONE means load balancing advisory is disabled
SELECT NAME, GOAL, CLB_GOAL FROM DBA_SERVICES;
-- SERVICE TIME: response-time based, like online shopping
Page 481 Oracle DBA Code Examples
execute dbms_service.modify_service (service_name => 'hrserv' -
, goal => dbms_service.goal_service_time -
, clb_goal => dbms_service.clb_goal_short);
-- THROUGHPUT: rat at which jobs are completed, like batch processing
execute dbms_service.modify_service (service_name => 'hrserv' -
, goal => dbms_service.goal_throughput -
, clb_goal => dbms_service.clb_goal_long);
-- NONE: LBA disabled
execute dbms_service.modify_service (service_name => 'hrserv' -
, goal => dbms_service.goal_none -
, clb_goal => dbms_service.clb_goal_long);
Monitoring Load Balancing Advisory
V$SERVICEMETRIC : service metrics on the most 5-second and 1-minute intervals
V$SERVICEMETRIC_HISTORY : recent history of the metric values
SELECT
service_name
,TO_CHAR(begin_time,'hh24:mi:ss') beg_hist
,TO_CHAR(end_time,'hh24:mi:ss') end_hist
,inst_id
,goodness
,delta
,flags
,cpupercall
,dbtimepercall
,callspersec
,dbtimepersec
FROM gv$servicemetric
WHERE service_name ='hrserv'
ORDER BY service_name, begin_time DESC, inst_id;
SELECT SERVICE_NAME,
to_char(BEGIN_TIME,'hh24:mi:ss') BEGIN_TIME,
to_char(END_TIME,'hh24:mi:ss') END_TIME,
INTSIZE_CSEC, GROUP_ID "Service Metric Group",
CPUPERCALL "CPU time per call",
DBTIMEPERCALL "Elapsed time per call",
CALLSPERSEC "User Calls/s",
DBTIMEPERSEC "DB Time/s"
from V$SERVICEMETRIC_HISTORY
order by SERVICE_NAME, BEGIN_TIME desc;
-- aggregated
SELECT
SERVICE_NAME,
GROUP_ID "Service Metric Group",
round(sum(CPUPERCALL),2) "CPU time per call",
round(sum(DBTIMEPERCALL),2) "Elapsed time per call",
round(sum(CALLSPERSEC),2) "User Calls/s",
round(sum(DBTIMEPERSEC),2) "DB Time/s"
from V$SERVICEMETRIC_HISTORY
group by SERVICE_NAME, GROUP_ID
order by SERVICE_NAME;
Short and Long services :
For each service, you can define the connection load-balancing goal that you want the listener to use. You can use a goal of either long or short for connection load balancing. These goals have the following characteristics:
Short Connections are distributed across instances based on the amount of time that the service is used. Use the short connection load-balancing goal for applications that have connections of brief duration.
Long Connections are distributed across instances based on the number of sessions in each instance, for each instance that supports the service. Use the long connection load-balancing goal for applications that have connections of long duration.
$ srvctl modify service -db db_unique_name -service oltpapp -clbgoal SHORT
$ srvctl modify service -db db_unique_name -service batchconn -clbgoal LONG
select service_name , CLB_GOAL from all_services;
Run Time Load balancing :
Typically, there are two types of load balancing:
Connection Load Balancing (CLB)
Run-time Load Balancing (RTLB)
RTLB has come into existence from 10gR2 which can be either set to “SERVICE_TIME” or “THROUGHPUT”. CLB can be configured at Client-Side or at Server-Side. Of which, Server-Side load balancing is recommended and have better functionality over Client-Side.
When a service is created by default Connection Load Balancing is enabled and set to “LONG” and Run-Time Load Balancing is disabled. Irrespective of Connection Load Balancing Goal “SHORT” or “LONG” you can enable Runtime Load Balancing Goal to “SERVICE_TIME” or “THROUGHPUT”.
With Connection Load Balancing goal set to LONG, do not configure Run-time Load Balancing as it is only applicable to applications where next session or job starts only after the current one ends which is not practical. This is the reason you must have read that Run-time Load Balancing must be enabled with CLB goal set to SHORT.
[oracle@RAC1 ~]$ srvctl modify service -d RACDB -s LBASRV1 -B SERVICE_TIME
[oracle@RAC1 ~]$ srvctl config service -d RACDB -s LBASRV1
[oracle@RAC1 ~]$ srvctl modify service -d RACDB -s LBASRV1 -B THROUGHPUT
[oracle@RAC1 ~]$ srvctl config service -d RACDB -s
Disabling CLB on server-side is equivalent to disabling LBA (Load Balancing Advisory). You will not find an option to do it if you search in “srvctl” help.
Oracle document says that configuring GOAL to NONE will disable Load Balancing Advisory (LBA) on the service. Let us try doing it and see what exactly it is.
I will try to use DBMS_SERVICE package to modify this service and to disable LBA.
SQL> EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => ‘LBASRV1’, goal => DBMS_SERVICE.GOAL_NONE);
SQL> select GOAL,CLB_GOAL from dba_services where name=’LBASRV1′;
GOAL CLB_G
———— —–
NONE LONG
But as soon as we restart the service using srvctl GOAL has come back to THROUGHPUT.
[oracle@RAC1 ~]$ srvctl stop service -s LBASRV1 -d RACDB
[oracle@RAC1 ~]$ srvctl start service -s LBASRV1 -d RACDB
SQL> select GOAL,CLB_GOAL from dba_services where name=’LBASRV1′;
GOAL CLB_G
———— —–
THROUGHPUT LONG
select user_data from sys.sys$service_metrics_tab order by enq_time;
Manage service using DBMS_SERVICE for Non Rac
exec DBMS_SERVICE.CREATE_SERVICE('service2','service2');
exec dbms_service.start_service('rdbprod.envoy.net');
exec dbms_service.stop_service('bb', dbms_service.all_instances)
begin
dbms_service.create_service (
service_name =>'ABC' , network_name => 'src_net_name' ,
failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, -
failover_retries => 10, -
failover_delay => 1, -
) ;
dbms_service.start_service('abc',dbms_service.all_instances);
end ;
/
exec DBMS_SERVICE.MODIFY_SERVICE( -
service_name => 'o11gr1', -
goal => DBMS_SERVICE.GOAL_THROUGHPUT, -
failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC, -
failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, -
failover_retries => 10, -
failover_delay => 1, -
clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);
exec dbms_service.start_service('rdbprod.envoy.net');
exec dbms_service.stop_service('bb', dbms_service.all_instances)
dbms_service.create_service (
service_name =>'ABC' , network_name => 'src_net_name' ,
failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, -
failover_retries => 10, -
failover_delay => 1, -
) ;
dbms_service.start_service('abc',dbms_service.all_instances);
end ;
/
service_name => 'o11gr1', -
goal => DBMS_SERVICE.GOAL_THROUGHPUT, -
failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC, -
failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, -
failover_retries => 10, -
failover_delay => 1, -
clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);
BEGIN
DBMS_SCHEDULER.create_job_class(
job_class_name => 'OLTP_JOB_CLASS',
service => 'OLTP_SERVICE');
job_class_name => 'BATCH_JOB_CLASS',
service => 'BATCH_SERVICE');
END;
/
GRANT EXECUTE ON sys.oltp_job_class TO my_user;
GRANT EXECUTE ON sys.batch_job_class TO my_user;
SQL> select * from DBA_SCHEDULER_JOB_CLASSES;
SQL> exec DBMS_SCHEDULER.CREATE_JOB_CLASS (job_class_name => 'AUDITING_JOB_CLASS', service
=> 'AUDITING_SERVICE', comments => 'THIS IS AUDIT SERVICE JOB CLASS');
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'my_user.oltp_job_test',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN NULL; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;',
job_class => 'SYS.OLTP_JOB_CLASS',
end_date => NULL,
enabled => TRUE,
comments => 'Job linked to the OLTP_JOB_CLASS.');
END;
/
EXEC DBMS_SCHEDULER.set_attribute('MY_BATCH_JOB', 'JOB_CLASS', 'BATCH_JOB_CLASS');
col NAME for a35
col NETWORK_NAME for a35
col FAILOVER_METHOD for a30
select failover_type, failover_method,
failed_over from v$session where username='SCOTT';
, aq_ha_notifications => true -
, failover_method => dbms_service.failover_method_basic -
, failover_type => dbms_service.failover_type_select -
, failover_retries =>180 -
, failover_delay =>5 -
)
;
procedure disconnect_session( service_name in varchar2);
procedure create_service( service_name in varchar2, network_name in varchar2, goal in number, dtp in boolean, aq_ha_notifications in boolean, failover_method in varchar2, failover_type in varchar2, failover_retries in number, failover_delay in number, clb_goal in number);
procedure modify_service( service_name in varchar2, goal in number, dtp in boolean, aq_ha_notifications in boolean, failover_method in varchar2, failover_type in varchar2, failover_retries in number, failover_delay in number, clb_goal in number);
procedure delete_service( service_name in varchar2);
procedure start_service( service_name in varchar2, instance_name in varchar2);
procedure stop_service( service_name in varchar2, instance_name in varchar2);
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server03)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = OLTP_SERVICE)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 20)
(DELAY = 1)
)
)
)
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server03)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = BATCH_SERVICE)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 20)
(DELAY = 1)
)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.hclt.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.hclt.com)(PORT = 1521))
(LOAD_BALANCE = no)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = devdb_oltp)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.hclt.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.hclt.com)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = devdb_oltp)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
Note for DBA TEAM: application teams use application specific RAC services to connect to the database.
Following are the tnsname entries they use SS indicates Shared Server and non SS means Dedicated Server.
MARGIN-PRD.world, MARGIN-SS-PRD.world, RISKVIEW-PRD.world,RISKVIEW-SS-PRD.world,
RISKCALC-PRD.world,RISKCALC-SS-PRD.world,MULTIVAL-PRD.world,MULTIVAL-SS-PRD.world,
VARMARGIN-PRD.world,VARMARGIN-SS-PRD.world,CMA-PRD.world,CMA-SS-PRD.world.
If application team complains about connection issue to the database first thing ensure
archive area is not full. Then try connecting using above tnsname entries from global tnsnames.
If you get error like listener currently does not know service specified then follow
below step to start the services :
1. check if both the instances are up and running.
2. check all the application specific RAC services are up and running as below : (This command can be run from any node)
xstmc021001por(oracle):MFXPRDI1:misc$ $GRID_HOME/bin/srvctl status service -d MFXPRD_xstmc021 -s "MARGIN,RISKVIEW,RISKCALC,MULTIVAL,VARMARGIN,CMA"
Service MARGIN is running on instance(s) MFXPRDI2
Service RISKVIEW is running on instance(s) MFXPRDI1
Service RISKCALC is running on instance(s) MFXPRDI1
Service MULTIVAL is running on instance(s) MFXPRDI1
Service VARMARGIN is running on instance(s) MFXPRDI1
Service CMA is running on instance(s) MFXPRDI1
3. If not then bring the services on specific instance as per above list.
Please note MARGIN service runs on MFXPRDI2 and rest all run on MFXPRDI1
eg, to bring up one service as
$GRID_HOME/bin/srvctl start service -d MFXPRD_xstmc021 -s CMA -i MFXPRDI1
to bring up all the services in one command
$GRID_HOME/bin/srvctl start service -d MFXPRD_xstmc021 -s "CMA,MULTIVAL,RISKCALC,RISKVIEW,VARMARGIN" -i MFXPRDI1
$GRID_HOME/bin/srvctl start service -d MFXPRD_xstmc021 -s MARGIN -i MFXPRDI2
4. Wait for sometime and Run listener services from both the nodes and check all the services are registered to listener with shared servers (dispatcher entries in listener services output).
TNS_ADMIN=$GRID_HOME/network/admin $GRID_HOME/bin/lsnrctl services listener_mfxprd
5. If not then check service_names parameter for both the instances. And ensure above services are properly registered.
If not then use below command to add services to service_name
ALTER SYSTEM SET service_names='CMA','RISKVIEW','RISKCALC','MULTIVAL','VARMARGIN','MFXPRD' SCOPE=BOTH SID='MFXPRDI1';
ALTER SYSTEM SET service_names='MARGIN','MFXPRD' SCOPE=BOTH SID='MFXPRDI2';
Measuring Performance by Service Using the Automatic Workload Repository
Services add a new dimension for performance tuning. With services, workloads are visible and measurable, and therefore resource consumption and wait times are attributable by application. Tuning by using "service and SQL" replaces tuning by "session and SQL" in the majority of systems where all sessions are anonymous and shared.
The AWR maintains performance statistics that include information about response time, throughput, resource consumption, and wait events for all services and work that a database performs. Oracle Database also maintains metrics, statistics, wait events, wait classes, and SQL-level traces for services. You can optionally augment these statistics by defining modules within your application to monitor certain statistics. You can also define the actions within those modules that business critical transactions should execute in response to particular statistical values.
/* Enabling Gather statiscitc on Service-Module-Action combination */
-- to display currently connected services, modules and actions
select SID, USERNAME, SERVICE_NAME, MODULE, ACTION from V$SESSION
where SERVICE_NAME in ('hrserv','oeserv')
-- service name and module name are mandatory
begin
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'hrserv',
MODULE_NAME=>'PAYROLL',
ACTION_NAME => 'EXCEPTIONS PAY');
end;
/
-- gather stats for PAYROLL module and ACTION whose name is null
begin
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'hrserv',
Page 489 Oracle DBA Code Examples
MODULE_NAME=>'PAYROLL',
ACTION_NAME => NULL);
end;
/
-- gather stats for PAYROLL module and All its ACTIONs
begin
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'hrserv',
MODULE_NAME=>'PAYROLL',
ACTION_NAME => '###ALL_ACTIONS');
end;
/
-- to view enabled monitorings
-- types: SERVICE, SERVICE_MODULE, SERVICE_MODULE_ACTION
select A.AGGREGATION_TYPE, A.PRIMARY_ID , A.QUALIFIER_ID1 , A.QUALIFIER_ID2
from DBA_ENABLED_AGGREGATIONS a
-- to view gathered stats
select S.AGGREGATION_TYPE, S.SERVICE_NAME, S.MODULE, S.ACTION, N.CLASS,
decode(n.CLASS,
'1','User','2','Redo','4','Enqueue','8','Cache','16','OS','32','RAC','64','SQL
','128','Debug', N.CLASS) STAT_CLASS,
S.STAT_NAME, S.VALUE
from V$SERV_MOD_ACT_STATS s, V$STATNAME n
where S.STAT_ID = N.STAT_ID
order by N.CLASS, S.STAT_ID
-- call times and performance statistics views:
V$SERVICE_STATS
V$SERVICE_EVENTS
V$SERVICE_WAIT_CLASSES
V$SERVICEMETRIC
V$SERVICEMETRIC_HISTORY
/* To Disable Cumulative Stats */
-- stats will be removed from V$SERV_MOD_ACT_STATS
begin
DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(SERVICE_NAME => 'hrserv',
MODULE_NAME=>'PAYROLL',
ACTION_NAME => 'EXCEPTIONS PAY');
end;
/
/* Service Quality Statistics */
-- script from Oracle documentation
-- provides service quality statistics every five seconds
SET PAGESIZE 60 COLSEP '|' NUMWIDTH 8 LINESIZE 132 VERIFY OFF FEEDBACK OFF
COLUMN service_name FORMAT A20 TRUNCATED HEADING 'Service'
COLUMN begin_time HEADING 'Begin Time' FORMAT A10
COLUMN end_time HEADING 'End Time' FORMAT A10
COLUMN instance_name HEADING 'Instance' FORMAT A10
COLUMN service_time HEADING 'Service Time|mSec/Call' FORMAT 999999999
COLUMN throughput HEADING 'Calls/sec'FORMAT 99.99
BREAK ON service_name SKIP 1
SELECT
Page 490 Oracle DBA Code Examples
service_name
, TO_CHAR(begin_time, 'HH:MI:SS') begin_time
, TO_CHAR(end_time, 'HH:MI:SS') end_time
, instance_name
, elapsedpercall service_time
, callspersec throughput
FROM gv$instance i
, gv$active_services s
, gv$servicemetric m
WHERE s.inst_id = m.inst_id
AND s.name_hash = m.service_name_hash
AND i.inst_id = m.inst_id
AND m.group_id = 10
ORDER BY service_name , i.inst_id , begin_time ;
Reference :
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/racad/workload-management-with-dynamic-database-services.html#GUID-095B67FB-3E3A-44BE-84A4-321174015A08
https://docs.oracle.com/database/121/RACAD/GUID-559FB230-857B-4D97-B36A-F4F76C3A1D47.htm#RACAD7127
👍
ReplyDelete