Simple Select Statement in Two Node RAC Environment
1. User issues a select statement - select * from emp;
2. User process sends the query to the server process.
3. Server process communicates with instance and attempts to create an sql id, hash code, parse code and explain plan.
4. Metadata in Library Cache
Case one : The required metadata information (sql id, hash code, parse code and explain plan) is on the connected node
If the metadata information is present in the connected nodes Library Cache then the server process will look if the data blocks required to process the sql statement are in Database Buffer Cache.
Case two: The required metadata information is on the another node
The RAC background process LCK transfers the Metadata information ( Non Cache Fusion Request ) from the other node to the connected node. Now the metadata information is present in the connected nodes Library Cache, so the server process will look if the data blocks required to process the sql statement are in Database Buffer Cache.
Case three: The required metadata is not on any node
If the metadata information is not present in any node then this information will be created in the connected nodes Library Cache. Now the server process will look if the data blocks required to process the sql statement are in Database Buffer Cache.
5. Data blocks in Database Buffer Cache
Case one: Data blocks are present in connected node's Database Buffer Cache
Then server process processes the SQL statement.
Case two: Data blocks are present in another node's Database Buffer Cache
If data blocks are not present in connected node's buffer cache then the server process queries Global Resource Directory that is present in a memory area in SGA to check if those blocks are present in any nodes Database Buffer Cache in the cluster.
If those blocks are present in another node then LMS ( Global Cache Services Process ) process transfers the required blocks ( Cache Fusion Request ) to the connected node.
Then server process processes the SQL statement.
Case three: Data blocks are not present in any nodes Database Buffer Cache
Before understanding how this case will be dealt, it is good to know about "Resource Mastering"
In RAC environment every instance owns a subset of database blocks i.e each node keeps track of the state of the blocks owned by them and the control of those blocks are handled by the respective nodes.
If one node wants blocks mastered by another node, then the node sends a request to the resource master to send those blocks.
So, what if an instance uses certain blocks more frequently than its master. Oracle dynamically remasters the resources based on resource affinity. The feature in Oracle Lingo termed "Dynamic Remastering"
Page Under Construction................................................................................................................................