performance question

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Mon, 10 Sep 2007 09:21:21 -0400
Message-ID: <46E544D1.7070501@tufts.edu>

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

Received on Mon Sep 10 2007 - 08:21:21 CDT