I think I found the problem. This is really a sql statement issue, not a performance puzzle. Although I didn't confirm it yet.
QA was cloned on early Aug, TRN was cloned on last week. The sql statement is asking the data for the data range from 8/15 to 8/31. On QA, there is no data to return in the merge join/hash join steps. So no wonder it works on QA and not on TRN.
joan Alvaro Jose Fernandez wrote:
ah, I understand now. Joan, one last question. Have you tried then to equal the PGA size on both?... (sorry this seems so stupid, but nopw I really cannot think of other explanation aside different temp area capacity for the hash) regards.The id=27, on TRN it retuns 0 while it returns 31,967 rows on QA is_understandable, cause on TRN it failed and it never reached that far.
-- //www.freelists.org/webpage/oracle-l
