Re: performance question

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_at_tufts.edu> 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)'
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l