You solved your problem by updating the statistics. Why on the earth do you think that CPU count and physical memory size were the reasons? ================================ Dion Cho - Oracle Performance Storyteller http://dioncho.wordpress.com (english) http://ukja.tistory.com (korean) ================================ On Mon, Mar 16, 2009 at 3:43 PM, Yechiel Adar <adar666@xxxxxxxxxxxx> wrote: > Problem solved. > We did compute statistics with histograms on the table and now it works > fine on both machines. > > It turned out that the second node is NOT the same. It has 4 CPU and 4 GB > of memory instead of 8 CPU and 8 GB of memory in node 1. > This probably had effect on the explain plan and used less efficient path > to the data. > > Adar Yechiel > Rechovot, Israel > > > > Riyaj Shamsudeen wrote: > > Adar > You might want to look at the plan for that SQL on node 2. May be the > execution plan is different in node 2 compared to node 1. This scenario is > possible due to difference in instance parameters. > Can you also print tkprof output wait lines from node 2? I am > specifically interested in seeing Interconnect performance numbers. What is > the average time taken for 'gc current block 2 way'? > What is your average block receive time and average send time from AWR > reports during this time frame ? That might provide more insights too. > > -- > Cheers > > Riyaj Shamsudeen > Principal DBA, > Ora!nternals - http://www.orainternals.com > Specialists in Performance, Recovery and EBS11i > Blog: http://orainternals.wordpress.com > > On Sun, Mar 15, 2009 at 4:55 AM, Yechiel Adar <adar666@xxxxxxxxxxxx>wrote: > >> Update: >> This happens only when the user connects to node 2. >> I went a little crazy as I run the same sql from my station and it worked >> fine. >> After I took 10046 trace I saw that their trace was on node 2 and mine was >> on node 1. >> When I removed the load_balance parameter from the tnsnames, so they will >> always connect to node 1, the problem went away. >> >> So the problem is not so urgent but I would like to work with load >> balance. >> >> Adar Yechiel >> Rechovot, Israel >> >> >> >> Yechiel Adar wrote: >> >>> We moved from one node rac to 2 node rac. >>> A simple query takes 9 seconds instead of 0.3 second. >>> >>> The user activated 10046 trace and I see a lot of "gc current block >>> 2-way". >>> >>> Help urgently required. >>> >>> Adar Yechiel >>> Rechovot, Israel >>> >>> -- >>> //www.freelists.org/webpage/oracle-l >>> >>> >>> >>> -- >> //www.freelists.org/webpage/oracle-l >> >> >> > > >