Make Oracle Skill to know What is the Load Balancing Advisory?

After trip Samui, I wanna write about Load Balancing Advisory. Anyway I should talk about "Workload Management" before.

What is the Workload Management?

Workloads in Oracle Real Application Clusters (Oracle RAC) to provide high availability and scalability for your applications.

Workload management enables you to manage workload distributions to provide optimal performance for users and applications.

Workload management comprises the following:

- Services — Oracle Database 10g introduces a powerful automatic workload management facility, called services, to enable the enterprise grid vision. Services are entities that you can define in Oracle RAC databases that enable you to group database workloads and route work to the optimal instances that are assigned to offer the service.

- Connection Load Balancing — A feature of Oracle Net Services that balances incoming connections across all of the instances that provide the requested database service.

- High Availability Framework — An Oracle RAC component that enables the Oracle Database to maintain components in a running state at all times.

- Fast Application Notification (FAN) — The notification mechanism that Oracle RAC uses to quickly alert applications about configuration and workload service level changes.

- Load Balancing Advisory — Provides information to applications about the current service levels that the database and its instances are providing. The load balancing advisory makes recommendations to applications about where to direct application requests to obtain the best service based on the policy that you have defined for that service.

- Fast Connection Failover — This is the ability of Oracle Clients to provide rapid failover of connections by subscribing to FAN events.

- Runtime Connection Load Balancing — This is the ability of Oracle Clients to provide intelligent allocations of connections in the connection pool based on the current service level provided by the database instances when applications request a connection to complete some work.

Ok, come back to the point...

Load Balancing Advisory

Load balancing distributes work across all of the available Oracle RAC database instances. Oracle recommends that applications use persistent connections that span the instances that offer a particular service. 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 THROUGHPUT or SERVICE_TIME 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.

Configuring Your Environment to Use the Load Balancing Advisory

You can configure your environment to use the load balancing advisory by defining service-level goals for each service for which you want to enable load balancing. This enables the load balancing advisory for that service and FAN load balancing events are published.

There are two types of service-level goals (when use service-level goal, should set "clb_goal" be short)for runtime:

- SERVICE TIME — Attempts to direct work requests to instances according to response time. Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. An example for the use of SERVICE TIME is for workloads such as internet shopping where the rate of demand changes:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'OE' , goal => DBMS_SERVICE.GOAL_SERVICE_TIME -, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

- THROUGHPUT — Attempts to direct work requests according to throughput. The load balancing advisory is based on the rate that work is completed in the service plus available bandwidth to the service. An example for the use of THROUGHPUT is for workloads such as batch processes, where the next job starts when the last job completes:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'sjob' - , goal => DBMS_SERVICE.GOAL_SERVICE_TIME - , clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

Setting the goal to NONE disables load balancing for the service. You can see the goal settings for a service in the data dictionary and in the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views.

Example: I have "RADIUS" service name.

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RADIUS', aq_ha_notifications => TRUE, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT, goal=>dbms_service.goal_service_time);

Load Balancing Advisory FAN Events

"sys.sys$service_metrics_tab" Object.

Example:

SET PAGES 60 COLSEP '' LINES 132 NUM 8 VERIFY OFF FEEDBACK OFF

COLUMN user_data HEADING "AQ Service Metrics" FORMAT A60 WRAP

BREAK ON service_name SKIP 1

SELECT TO_CHAR(enq_time, 'HH:MI:SS') Enq_time , user_dataFROM sys.sys$service_metrics_tabORDER BY 1 ;

/
ENQ_TIME AQ Service Metrics

-------- ------------------------------------------------------------

10:33:07 SYS$RLBTYP('RADIUS', 'VERSION=1.0 database=DB service=RADI US { {instance=DB4 percent=25 flag=GOOD}{instance=DB3 pe rcent=29 flag=GOOD}{instance=DB2 percent=21 flag=GOOD}{ins tance=DB1 percent=25 flag=GOOD} } timestamp=2008-02-20 10: 33:07')

10:33:38 SYS$RLBTYP('RADIUS', 'VERSION=1.0 database=DB service=RADI US { {instance=DB4 percent=25 flag=GOOD}{instance=DB3 pe rcent=29 flag=GOOD}{instance=DB2 percent=22 flag=GOOD}{ins tance=DB1 percent=24 flag=GOOD} } timestamp=2008-02-20 10: 33:38')

10:34:08 SYS$RLBTYP('RADIUS', 'VERSION=1.0 database=DB service=RADI US { {instance=DB4 percent=25 flag=GOOD}{instance=DB3 pe rcent=30 flag=GOOD}{instance=DB2 percent=22 flag=GOOD}{ins tance=DB1 percent=23 flag=GOOD} } timestamp=2008-02-20 10: 34:08')

10:34:37 SYS$RLBTYP('RADIUS', 'VERSION=1.0 database=DB service=RADI US { {instance=DB4 percent=24 flag=GOOD}{instance=DB3 pe rcent=30 flag=GOOD}{instance=DB2 percent=23 flag=GOOD}{ins tance=DB1 percent=23 flag=GOOD} } timestamp=2008-02-20 10: 34:37')

10:35:07 SYS$RLBTYP('RADIUS', 'VERSION=1.0 database=DB service=RADI US { {instance=DB4 percent=25 flag=GOOD}{instance=DB3 pe rcent=26 flag=GOOD}{instance=DB2 percent=25 flag=GOOD}{ins tance=DB1 percent=24 flag=GOOD} } timestamp=2008-02-20 10: 35:07')

10:35:37 SYS$RLBTYP('RADIUS', 'VERSION=1.0 database=DB service=RADI US { {instance=DB4 percent=26 flag=GOOD}{instance=DB3 pe rcent=22 flag=GOOD}{instance=DB2 percent=27 flag=GOOD}{ins tance=DB1 percent=25 flag=GOOD} } timestamp=2008-02-20 10: 35:37')

......................................................................