For Oracle RAC configurations that rely on spreading the load equally among instances, ensuring that inbound connections are balanced is quite important.
Since I was not able to find a suitable metric in the Oracle Enterprise Manager, I had to create one myself.
As I started thinking about it, some fundamental questions started popping up. How much difference in connection count should trigger an alert? Should I measure the percentage difference, the actual difference (number of sessions) , or something else?
Resisting the temptation to start coding convoluted logic, I reviewed what applied statistics has to offer and this is what I found – the Chi Square test !
The way I understand it, you can use that test to see if the connections are independently (uniformly) distributed across DB instances, or if there are some DB instances tend to get more or fewer connections than “expected”.
Another great thing about Chi Square test is that it is already implemented in the Oracle RDBMS.
The build-in Oracle function (STATS_CROSSTAB) can give us the value of Chi-squared (CHISQ_OBS) , the degree of freedom (CHISQ_DF), and the statistical significance (CHISQ_SIG). What we are interested in is the statistical significance. A number less that 0.05 indicates that the data is likely not distributed uniformly.
Here is the query that can detect if a DB user prefers/avoids a DB instance:
SELECT COUNT(*) cnt FROM (SELECT STATS_CROSSTAB(inst_id, username, 'CHISQ_SIG') p_value FROM gv$session ) WHERE p_value < 0.05
Detecting connection imbalance at client machine level is bit more tricky because each instance received a few connections from the server it is on.
That can be easily accounted for my excluding the servers that run the DBs:
SELECT COUNT (*) cnt FROM (SELECT STATS_CROSSTAB(inst_id, machine, 'CHISQ_SIG') p_value FROM gv$session WHERE machine NOT LIKE '%dbp%.newsamerica.com' ) WHERE p_value < 0.05
These monitoring queries work without modification for any size RAC cluster. Adding or removing nodes is handled without issues, apart from a temporary imbalance that may come with adding nodes.