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.

Environment:

Our case study is on:

• 2 node RAC setup
• 3 SCAN listeners – How does SCAN listener works in Oracle RAC 11gR2?
• 1 client server

Case Study:

1. Let us connect to the 2 node RAC environment (on Linux) from the client server (on Windows) as SYSTEM user.

1. Connect to node 1 and capture the session details from GV$SESSION global dynamic performance view.

1. 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.

2. Try running the same SQL statement with V$SESSION dynamic performance view. We find no sessions.

1. 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?

2. Answer is now ahead… Let us run the same SQL statement which we ran on GV$SESSION from the client console (on Windows) server.

1. 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.

2. 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.

Conclusion:

• 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.