Re: performance question

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Mon, 10 Sep 2007 10:36:02 -0400
Message-ID: <46E55652.3000805@tufts.edu>

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

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

Received on Mon Sep 10 2007 - 09:36:02 CDT