Interesting fact on reading data from GV$ views in RAC

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:

Case Study:

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

blog1

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

blog2

    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.

blog3

    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.

blog4

  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.

Related Posts

About The Author

2 Comments

  1. chetangupte1@gmail.com'
    Chetan
    January 11, 2017
    • Pawan Kumar Yaddanapudi
      January 11, 2017

Add Comment