Monday, September 13, 2010

Sessions per Instance using GV$SESSION

I've been doing some testing around Oracle 11.2 RAC and decided to share a few findings so I'll be trying to document them in my blog, which I've been too busy to update. I have to give my sincerest thanks and appreciation to all the bloggers who regularly update, I've no idea how they find the time but appreciate their efforts as I've found gems of information which have really helped me.

One of the first things I've seen with 11.2 RAC is that the information, blogs included, is a bit wanting... there simply are not a lot of good, useful or factual information, even the Oracle documentation is light or inaccurate. Yes, I have given feedback to the inaccuracies and they should be corrected so hopefully you wont even notice or see any evidence. This contrasts with 11.1 and previous version which have lots of blogs, and good, useful information. To me this indicates adoption is still a work in progress for this version, which makes sense since it does bring a lot to the table which needs to be thoroughly tested.

A little trinket of, hopefully, useful information which I found on the OTN forums (credit to Sebastian), which I had not previously realized, but was puzzled during my 11.2 RAC testing:

Say you have a query to get the number of sessions of a particular user across all the nodes (for example to see how load balancing is working):

SELECT inst_id ,username ,count(*) FROM GV$SESSION
FROM username = 'ABC' GROUP BY inst_id ,username


This results in "strange" behavior if you query the GV$SESSION table as the same user ABC, as 2 parallel servers are spawned to query the V$SESSION table on each node. This happens as the same user, i.e. ABC. Hence you will see 3 (1 real and 2 parallel) slaves querying the V$SESSION on each instance. The reason you will see 1 on one node and 3 on the other is the order in which the parallel processes query the V$SESSION.

If you use another user to query the sessions for ABC you will not see this problem and get an accurate session per instance count.

Hope that explains it.