The two databases are cloned from prod by different date, QA was two month earlier than TRN. TRN was cloned last week. I just did a tkprof on both databases and rerun the sql statement, the execution plans are identical on both databases, just return rows are so big different, hence the result is one run while the other failed. My question is if the execution plans are same, why the return rows are so different against same tables, same steps? The source tables are not big difference in terms of rows, extents..etc.
Joan Hemant K Chitale wrote:
When you say " Why the returned rows are so different ..."can you tell us the number of rows returned ? That isn't clear in the STAT output.I would start looking at tables PS_EMPLOYMENT, PS_TFTH_JOB and PS0PERSONAL_DATA. The number of Blocks Gets for a Full Scan or Index Full Scan is significant in TRN but not in QA. Have the tables been rebuilt using Export/Import ? Are the two databases not cloned from the samesource , but built seperately ? At 09:21 PM Monday, Joan Hsieh wrote:Dear list,Our peoplesoft HR system recently upgraded from 9i to 10.2.0.3. We did some statistics adjustment to solve one major sql statement. However, we had another performace issue last week for a newly sqr statement. I am confused after I had 10046 trace. Here is the story.QA and TRN two database, same release on same server.Hemant K Chitale http://hemantoracledba.blogspot.com"First they ignore you, then they laugh at you, then they fight you, then you win" !" Mohandas Gandhi Quotes : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
-- //www.freelists.org/webpage/oracle-l
