In case of an oracle RAC setup, A single transaction having 100 DMLs, will all the DML go to the same RAC node?
If yes, could you please point me to the official documentation?
If no, in what cases does this happen?
Basically my end goal is to understand in a RAC setup for any given transaction is it sufficient to read a particular node's redo log file or will it be distributed across multiple Node in any of the following cases? I do understand it would have node affinity and other configuration to make it work in a single node. But below are few of the scenarios that I can think of where it might have to move transaction to another node? I was thinking of below scenarios.
Yes, a single transaction will be isolated to only one node. No documentation is needed on this point: a transaction is isolated to a single session, and a session exists on only one node. No other session can see into that transaction - not even PX slaves in a PDML situation (they each have their own transactions).
Say I start a transaction and perform 10 DMLs for the next 10 days (100 DMLs)and commit the 11th day
Yikes! You do not want to hold a transaction open for 10 days! If you are trying to do that, there's a design problem here. Transactions need to be kept short-lived. If you try to keep one open this long, you are very likely to run out of undo and throw an ORA-1555. You will also be tying up some resource and blocking others for a very long time.
Say I start a transaction on a Node and perform 50 DMLs, then Node becomes highly loaded with other ingress request. Will it perform load balancing move next DMLs of same transaction to another node?
No, there is no migration of a transaction between nodes, because there is no migration of sessions between instances. Once a session is created on an instance, it will stay there until it logs out. Only new sessions can be directed to other nodes for load balancing. That would be new connections, or spinning up PX slaves for a parallel query or DML.
In case of failure of a Node after say 50 DMLs, will all the DMLs be rolled back on this node and a new Node gets all the 100 DMLs(will the redo log file of the new instance have all the DMLs pertaining to this transaction or it get divided between the 2 nodes redo log files)
In case of a failure mid-transaction, all the work in that transaction will be rolled back. That's the whole point of the ACID model: either the entire unit of work is successfully completed, or none of it is. Never only a part of it.
Also, it isn't clear why reference is being made to the redo logs. Unless you are working with an advanced log-mining product, nobody reads redo logs. They are used internally for roll-forward in case of abnormal instance termination before blocks are written to disk. They really shouldn't be part of your thinking process about how to work with RAC.
To get load balancing, you need lots of separate sessions doing work. If you are using the SCAN listener and the service requested is configured for all instances, your workload should be fairly evenly load balanced. But a single session is by definition in one place only, for the life of the session. If you are trying to do everything with one connection, you will be doing it all (except for PX slaves, as mentioned above) on a single node with no load balancing or other RAC benefit.