My requirement is to find the cluster and Which blocks are HOT
Which blocks move the fastest across interconnect
Which instance/node requests the most blocks and
Which SQL query is responsible for the highest level of blocking?
I have been asked to find out by interconnect queries and through GRD views…i search and find out GV$BH which is block header view
-
Data blocks currently in the buffer cache
-
Which object (table/index) the block belongs to
-
The status of the block (e.g., free, dirty, pinned, etc.)
-
Which instance holds the block (important in RAC)
My requirement is to the find out which sql is causing cluster clulprit ,but no direct joins with GRD view which shows sql as a culprit for clusters wait.
which all interconnect queries gives details about clusters and which sql is impacting blocks more.