The datasets appear to be vastly different because the row source numbers are quite different. If you look at the id=11 & id=3 you will see that the QA trace shows cnt=0 while TRN shows cnt=12229423. A row source that returns over 12 million rows vs. 0 rows has a large effect on the performance of the plans as shown by the execution times (time=). If the TRN query finishes in a reasonable time, I would suggest running it with a /*+ gather_plan_statistics */ hint and then using dbms_xplan.display_cursor with 'ALLSTATS LAST' as shown here: http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/ If the E-rows (estimated) and A-rows (actual) are dramatically off, you may have to refine your stats to be more representative. On 9/10/07, Joan Hsieh <joan.hsieh@xxxxxxxxx> 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. All the parameters > are same, except QA has 200m sga target and TRN has 500m sqa target > size. Same sql statement ran on QA without any problem, but failed on > HRN with run out of pstemp temporary tablepspace error. > > I took 10046 trace on both databases, the excuecution plan are identical > which puzzled me. Why the returned rows are so different from each other > with the same plan. QA database has less rows than TRN, but not in a big > gap. I hope someone can shed some light on this. Thanks,----Joan > > QA > XCTEND rlbk=0, rd_only=1 > STAT #2 id=1 cnt=8538 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=238458 > pr=40823 pw=4941 time=57337179 us)' > STAT #2 id=2 cnt=8538 pid=1 pos=1 obj=0 op='CONCATENATION (cr=238458 > pr=40823 pw=4941 time=57349483 us)' > STAT #2 id=3 cnt=0 pid=2 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=21201 > pr=17745 pw=0 time=15426348 us)' > STAT #2 id=4 cnt=0 pid=3 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=21201 > pr=17745 pw=0 time=15426332 us)' > STAT #2 id=5 cnt=0 pid=4 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=21201 > pr=17745 pw=0 time=15426321 us)' > STAT #2 id=6 cnt=0 pid=5 pos=1 obj=61795 op='TABLE ACCESS FULL PS_JOB > (cr=21201 pr=17745 pw=0 time=15426306 us)' > STAT #2 id=7 cnt=0 pid=5 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 > time=0 us)' > STAT #2 id=8 cnt=0 pid=7 pos=1 obj=58001 op='TABLE ACCESS FULL > PS_EMPLOYMENT (cr=0 pr=0 pw=0 time=0 us)' > STAT #2 id=9 cnt=0 pid=4 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 > time=0 us)' > STAT #2 id=10 cnt=0 pid=9 pos=1 obj=106102 op='TABLE ACCESS FULL > PS_TFTH_JOB (cr=0 pr=0 pw=0 time=0 us)' > STAT #2 id=11 cnt=0 pid=3 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 > time=0 us)' > STAT #2 id=12 cnt=0 pid=11 pos=1 obj=64184 op='INDEX FAST FULL SCAN > PS0PERSONAL_DATA (cr=0 pr=0 pw=0 time=0 us)' > STAT #2 id=13 cnt=8538 pid=2 pos=2 obj=0 op='HASH JOIN (cr=217257 > pr=23078 pw=4941 time=41906027 us)' > > TRN > XCTEND rlbk=0, rd_only=1 > STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 > time=59 us)' > STAT #1 id=2 cnt=12229423 pid=1 pos=1 obj=0 op='CONCATENATION (cr=23627 > pr=21331 pw=1321 time=134505041 us)' > STAT #1 id=3 cnt=12229423 pid=2 pos=1 obj=0 op='MERGE JOIN CARTESIAN > (cr=23627 pr=21331 pw=1321 time=110046190 us)' > STAT #1 id=4 cnt=381 pid=3 pos=1 obj=0 op='MERGE JOIN CARTESIAN > (cr=23466 pr=21331 pw=1321 time=72639302 us)' > STAT #1 id=5 cnt=1 pid=4 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=21322 > pr=21296 pw=0 time=64436968 us)' > STAT #1 id=6 cnt=1 pid=5 pos=1 obj=61795 op='TABLE ACCESS FULL PS_JOB > (cr=20187 pr=20165 pw=0 time=60311025 us)' > STAT #1 id=7 cnt=1 pid=5 pos=2 obj=0 op='BUFFER SORT (cr=1135 pr=1131 > pw=0 time=4125923 us)' > STAT #1 id=8 cnt=43121 pid=7 pos=1 obj=58001 op='TABLE ACCESS FULL > PS_EMPLOYMENT (cr=1135 pr=1131 pw=0 time=6067916 us)' > STAT #1 id=9 cnt=381 pid=4 pos=2 obj=0 op='BUFFER SORT (cr=2144 pr=35 > pw=1321 time=8200036 us)' > STAT #1 id=10 cnt=266950 pid=9 pos=1 obj=106513 op='TABLE ACCESS FULL > PS_TFTH_JOB (cr=2144 pr=21 pw=0 time=9623843 us)' > STAT #1 id=11 cnt=12229423 pid=3 pos=2 obj=0 op='BUFFER SORT (cr=161 > pr=0 pw=0 time=25192169 us)' > STAT #1 id=12 cnt=32141 pid=11 pos=1 obj=64184 op='INDEX FAST FULL SCAN > PS0PERSONAL_DATA (cr=161 pr=0 pw=0 time=3164790 us)' > STAT #1 id=13 cnt=0 pid=2 pos=2 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 > time=0 us)' > > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Regards, Greg Rahn http://structureddata.org -- //www.freelists.org/webpage/oracle-l