Re: performance question

  • From: Joan Hsieh <joan.hsieh@xxxxxxxxx>
  • To: Alvaro Jose Fernandez <alvaro.fernandez@xxxxxxxxx>
  • Date: Mon, 10 Sep 2007 10:36:02 -0400

here is TRN trace with 300m more sga and 400m more temp space. It failed with pstemp out of space. 


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)' STAT #1 id=14 cnt=0 pid=13 pos=1 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=15 cnt=0 pid=14 pos=1 obj=0 op='MERGE JOIN (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=16 cnt=0 pid=15 pos=1 obj=61795 op='TABLE ACCESS BY INDEX ROWID PS_JOB (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=17 cnt=0 pid=16 pos=1 obj=61796 op='INDEX FULL SCAN PS_JOB (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=18 cnt=0 pid=17 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=19 cnt=0 pid=18 pos=1 obj=0 op='FIRST ROW (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=20 cnt=0 pid=19 pos=1 obj=61802 op='INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=21 cnt=0 pid=17 pos=2 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=22 cnt=0 pid=21 pos=1 obj=0 op='FIRST ROW (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=23 cnt=0 pid=22 pos=1 obj=61802 op='INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=24 cnt=0 pid=15 pos=2 obj=0 op='SORT JOIN (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=25 cnt=0 pid=24 pos=1 obj=106513 op='TABLE ACCESS FULL PS_TFTH_JOB (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=26 cnt=0 pid=14 pos=2 obj=58001 op='TABLE ACCESS FULL PS_EMPLOYMENT (cr=0 pr=0 pw=0 time=0 us)' STAT #1 id=27 cnt=0 pid=13 pos=2 obj=64184 op='INDEX FAST FULL SCAN PS0PERSONAL_DATA (cr=0 pr=0 pw=0 time=0 us)' 

Alvaro Jose Fernandez wrote:


Joan,

it seems the excerpt you posted is incomplete (I cannot understand that the 
last op is the hash join with id=13 on both plans)
please, ¿would you mind to post a tkprof-processed plan of both (without running the "explain= " param of tkprof), just to compare why the diff in rowcounts? 

QA
.
.
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
.
.
.
.
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





--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2007