Automatic Work Load Managment
sales1-scan.example.com IN A 133.22.67.194
IN A 133.22.67.193
IN A 133.22.67.192
NAME TYPE VALUE
-------------------------- ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=133.
22.67.111)(PORT=1521))))
remote_listener string sales1-scan.example.com:1521
Automatic workload management enables you to manage workload distributions to provide optimal performance for users and applications
We can configure multiple listener on multiple nodes to handle client connection requests for the same database service.
Types of workLoad Distribution
Connection balancing
Client side connect time load balancing ( LOAD_BALANCE=ON) - Using SCAN IP address list
Client side connect time failover (FAILOVER=ON) - If listener connect fails in first attempt.
Server side connect time load balancing
Runtime connection load balancing
Work requests automatically balanced across the pool of connections.
Native feature of JDBC Implicit connection cache and ODP.Net connection Pool.
1) Client side connect time load balancing - enables clients to randomize connection requests among
a list of available listeners.
This feature enabled by LOAD_BALANCE=ON
Randomly select an address list in the address list
|
|
V
Connects to Node Listener - This balances client connections across the available SCAN listeners in the cluster.
Scan Listener redirects the connection request to the local listener of the instance that is least loaded and
provides the requested service.
When Using SCAN, Oracle Net automatically load balances the client connection requests across three IP addresses your
defined for the SCAN.
LISTENERS_db_unique_name =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP1)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP2)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP3)(PORT = scan_port_number))
(ADDRESS = (PROTOCOL = TCP)(HOST = node_VIP_name1-vip)(PORT = listener_port_number))
(ADDRESS = (PROTOCOL = TCP)(HOST = node_VIP_name2-vip)(PORT = listener_port_number))
)
SQL> ALTER SYSTEM SET remote_listener = '
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP1)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP2)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP3)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=node_VIP_name1-vip)(PORT = listener_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=node_VIP_name2-vip)(PORT = listener_port_number)))'
SCOPE=BOTH SID=*
SQL> SYSTEM SET REMOTE_LISTENER = 'LISTENERS_db_unique_name' SCOPE=BOTH SID=*
Client Side Connect Time Failover
________________________________
Enables clients to connect another listener if the intial connection to the first listener fails.
failover=on
loadbalance=on
Server Side Load Balancing
__________________________
Enables and configure using DBCA while creating Database with setting REMOTE_LISTENER parameter to SCAN listener.
The listener directs a connection request to the best instance currently providing the service by using information from
the load balancing advisory.
Runtime Connection Load Balancing and Connection Pools
______________________________________________________
It is afeature of Oracle connection pools that can distribute client work requests across the instances in an oracle RAC
database based on the load.