Same optimizer setting in all RAC nodes
Rajeshwaran, Jeyabal, December 03, 2014 - 2:49 pm UTC
Tom:
lets say all nodes in RAC have same optimizer settings.
and the node#1 Hard-parses this query
select * from t where primary_key = ?
then this will be available in library cache of node#1
when the same query is executed from node#2 - will that get shared from node#1 or does it hardparsed again in node#2 ?
December 03, 2014 - 3:38 pm UTC
it hard parses it, each instance will always hard parse it the first time at least...
and each could get a different plan, probably not for that query - but it could!
A reader, December 03, 2014 - 6:16 pm UTC
considering same hardware (i.e. CPU/memory) for each node and same optimizer setting for each node even than we could have different plan for same query?
if yes than what will be the reason. can you please explain?
Execute Gather Stats on each node
A reader, January 16, 2017 - 3:58 pm UTC
Hi,
So does this mean if i am running exec gather_stats to improve the performance of my database after some of the tables have crossed 50 million rows, i need to run it on every node in the RAC separately. Will it not be sufficient to run it on just one node.
Thanks in advance,
Cheers,
Vipin.
January 16, 2017 - 5:52 pm UTC
The stats are the same across all the nodes in the cluster. So no.
Krishnaprasad Yadav, May 01, 2018 - 12:50 pm UTC
Hi Team,
I have faced this problem recently in 2 node rac system , in both node for one of sql id plan hash value was different ,
i purged bad plan from one of node but unfortunately it still picked up old plan .
How to get rid of this bad plan . additionally how bind peeking can cause change of different plan in in RAC ?
May 02, 2018 - 1:56 am UTC
Do we need to pin the plan all nodes?
Saad Ahmad, October 14, 2018 - 10:52 pm UTC
do we need to run the PL/SQl to pin the plan explicitly on all nodes or running on ANY node with an input of sql_id and plan_id enough?
October 16, 2018 - 2:15 am UTC
Single node should suffice because the sql plan management details are stored in the data dictionary, which is common to all nodes.