Most of us (Oracle DBAs) query GV$ views in RAC environment to gather information from multiple or all the nodes in the cluster. We call them as Global Dynamic Performance Views. Unlike V$ views, these will collate data from all the instances in RAC. We are going to look at this process in detail with a clear study case and understand how this works internally when we fire SQL statement on GV$ views.
Our case study is on:
- 2 node RAC setup
- 3 SCAN listeners – How does SCAN listener works in Oracle RAC 11gR2?
- 1 client server
- Let us connect to the 2 node RAC environment (on Linux) from the client server (on Windows) as SYSTEM user.
- Connect to node 1 and capture the session details from GV$SESSION global dynamic performance view.
- So the user session of SYSTEM has been established on node 2 and we are able to see that from node 1 because of GV$SESSION global dynamic performance view.
- Try running the same SQL statement with V$SESSION dynamic performance view. We find no sessions.
- This is the major differentiation between V$ and GV$ views. V$ views fetch data from the instance you are connected to but GV$ views fetch data from multiple instances in the cluster and collates. But HOW?
- Answer is now ahead… Let us run the same SQL statement which we ran on GV$SESSION from the client console (on Windows) server.
- As we know the program SQLPLUS.exe was the actual session established on node 2 prior running this query. Now we see additional 2 sessions on both the nodes which are established temporarily.
- To understand, program(PZ99) that these 2 new sessions ran is the parallel query slave process which starts from PZ99 and descends to 0 for multiple parallel servers. Refer – http://docs.oracle.com/cd/E11882_01/server.112/e40402/bgprocesses.htm#REFRN104 for details on Pnnn background process.
- When you query GV$ global dynamic performance views, there will be additional sessions created in the environment.
- If 2 node RAC, then 2 sessions. If 3 node RAC, then 3 sessions and so on.
- When all the nodes in the cluster are facing heavy performance issues, running SQL statement on GV$ dynamic performance views is not advisable, as the new sessions has to be established on all the nodes in the cluster in the background.
- When you run a query GV$ session, irrespective of the filter you give, sessions are established on all the nodes in the cluster.