Disable RAC server (and client) side load balance

Here is how to disable RAC server (and client) side load balance, and the test:

* Client side load balance:

SALES_DB =
(DESCRIPTION_LIST=
(LOAD_BALANCE=off)
(FAILOVER=on)
(DESCRIPTION=
(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac01_vip)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=rac02_vip)(PORT=1521))

)

(CONNECT_DATA=(SERVICE_NAME=oltp_workload))))

* server side load balance:

srvctl modify service -d <dbname> -s <servicename> -B NONE

* Test

$>

#!/bin/bash

echo "">t.txt

for i in {1..70}

do

sqlplus -s system/ABC@sales_db >> t.txt <<+++

set head off

set feedback off

set pagesize 0

select instance_name from v\$instance;

begin

  for lc in (select * from all_objects where rownum < 500)

  loop

    Null;

  end loop;

end;

/

exit;

+++

sleep 0.02

done

cat t.txt|uniq

rac01

If load balance is enabled, then the output will be 2 lines or more，for example, 

rac01,

rac02.

Otherwise, it'll be always one line, e.g. : rac01.

* Reference

srvctl modify service -d db_unique_name -s service_name
     [-g server_pool] [-c {UNIFORM|SINGLETON}] [-P {BASIC|PRECONNECT|NONE}] 
     [-l {[PRIMARY] | [PHYSICAL_STANDBY] | [LOGICAL_STANDBY] | [SNAPSHOT_STANDBY]}
     [-q {TRUE|FALSE}] [-x {TRUE|FALSE}] [-j {SHORT|LONG}] 
     [-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}] 
     [-m {NONE|BASIC}] [-z failover_retries] [-w failover_delay]
     [-y {AUTOMATIC | MANUAL}]

Table A-79 srvctl modify service Options

Option	Description
-d db_unique_name	Unique name for the database
-s service_name	Service name
-g server_pool	The name of a server pool used when the database is policy managed. Note: This option is available only with Oracle RAC and only for policy-managed databases.
-c {UNIFORM | SINGLETON}	The cardinality of the service, either UNIFORM (offered on all instances in the server pool) orSINGLETON (runs on only one instance at a time) Note: This option is available only with Oracle Clusterware.
-P {BASIC|PRECONNECT|NONE}	TAF failover policy Note: The PRECONNECT option applies only to administrator-managed databases.
-l {[PRIMARY] | [PHYSICAL _STANDBY] | [LOGICAL _STANDBY] | [SNAPSHOT _STANDBY]}	The database modes for which the service should be started automatically.
-q {TRUE | FALSE}	Indicates whether AQ HA notifications should be enabled (TRUE) for this service
-x {TRUE | FALSE}	Indicates whether or not Distributed Transaction Processing should be enabled for this service Note: This option is available only with Oracle Clusterware.
-j {SHORT | LONG}	Connection Load Balancing Goal
-B {NONE | SERVICE_TIME | THROUGHPUT}	Runtime Load Balancing Goal
-e {NONE | SESSION | SELECT}	Failover type
-m {NONE | BASIC}	Failover method
-z failover_retries	The number of failover retry attempts
-w failover_delay	The time delay between failover attempts
-y {AUTOMATIC | MANUAL}	Service management policy

• http://docs.oracle.com/cd/E11882_01/rac.112/e41960/hafeats.htm#RACAD076
• http://oracleinaction.com/rac-load-balancing
• http://www.slideshare.net/MarkusMichalewicz/oracle-rac-11g-release-2-client-connections

Thanks for the help from Ricky Zhu

Application code instrumentation

When developing software application, why code instrumentation/tracing/logging must be done?

Easy to maintain

Tells where the problem is when something goes wrong, where to look for. Helps troubleshooting and fix bugs.

It's often difficult to track down the source of an error without some form of debugging instrumentation. This is particularly true in multi-tier, stateless architectures, running on production system.

There will be lot’s of restrictions to reproduce the bugs and errors on production system, most time it’ll corrupt the production data.

To make application traceable.

Traceability, auditing and logging are crucial.

http://97things.oreilly.com/wiki/index.php/Focus_on_Application_Support_and_Maintenance

http://en.wikipedia.org/wiki/Traceability#Software_development

“In transaction processing software, traceability implies use of a unique piece of data (e.g., order date/time or a serialized sequence number) which can be traced through the entire software flow of all relevant application programs. Messages and files at any point in the system can then be audited for correctness and completeness, using the traceability key to find the particular transaction.”

To get runtime status and stats.

To know what’s going on in real time on production system.

To measure performance. Profile.

Tells the application transaction response time and transaction volume (throughput).

“End users want to know, is the system going slower over time, if so, by how much.  Management wants to know, what are my transaction response times, how many transactions do we do, when is the busiest time, and so on.  People responsible to administering the system need to be able to identify where bottlenecks are, who needs to be brought in to look at something, who is responsible.”

To make the code runs faster.

The overhead of instrumentation is approximately -27%

Yes, NEGATIVE 27%.

Evidence that is true:

a) oracle is heavily instrumented (v$ tables, sql_trace, AWR, ASH, statspack, etc etc etc)

b) without that instrumentation - you would not know where to begin to start to tune

c) therefore, we run faster with it than without it - much much faster...

Good engineer practice.

The logging should be able to be turned ON and OFF easily - just like sql_trace.

That’s why we migrated to logger from debugf. It's easy to "turn-off" logger globally with virtually no performance impact, it's easy to get in the habit of leaving debug calls in production code.

The instrumentation should always be there, it should always be infinitely detailed.

but most of the time, it shouldn't be executed. Common sense must prevail.

Reference

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:54174245781686

http://tkyte.blogspot.ca/2005/06/instrumentation.html

http://tkyte.blogspot.ca/2006/08/do-i-ever-agree-with-this.html

Logger is used by Oracle developers to instrument their PL/SQL code

https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility