Re: performance question

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Mon, 10 Sep 2007 10:34:53 -0400
Message-ID: <46E5560D.8080101@tufts.edu>

I will sent seperate trace
here is QA one, it ran successfully and very fast with less sga and less   temp space.

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)'
STAT #2 id=14 cnt=8538 pid=13 pos=1 obj=0 op='HASH JOIN (cr=217096 pr=22620 pw=4641 time=38286698 us)'
STAT #2 id=15 cnt=8538 pid=14 pos=1 obj=0 op='MERGE JOIN (cr=215961 pr=21487 pw=4641 time=13832156 us)'
STAT #2 id=16 cnt=8538 pid=15 pos=1 obj=61795 op='TABLE ACCESS BY INDEX ROWID PS_JOB (cr=213843 pr=14857 pw=0 time=10097022 us)'

STAT #2 id=17 cnt=42849 pid=16 pos=1 obj=61796 op='INDEX FULL SCAN 
PS_JOB (cr=172642 pr=1268 pw=0 time=9680847 us)'
STAT #2 id=18 cnt=42893 pid=17 pos=1 obj=0 op='SORT AGGREGATE (cr=85790 

pr=510 pw=0 time=9982612 us)'
STAT #2 id=19 cnt=42849 pid=18 pos=1 obj=0 op='FIRST ROW (cr=85790 pr=510 pw=0 time=9563473 us)'
STAT #2 id=20 cnt=42849 pid=19 pos=1 obj=61802 op='INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=85790 pr=510 pw=0 time=9357800 us)' STAT #2 id=21 cnt=42849 pid=17 pos=2 obj=0 op='SORT AGGREGATE (cr=85700 pr=0 pw=0 time=1716753 us)'
STAT #2 id=22 cnt=42849 pid=21 pos=1 obj=0 op='FIRST ROW (cr=85700 pr=0 pw=0 time=1357415 us)'
STAT #2 id=23 cnt=42849 pid=22 pos=1 obj=61802 op='INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=85700 pr=0 pw=0 time=1159298 us)' STAT #2 id=24 cnt=8538 pid=15 pos=2 obj=0 op='SORT JOIN (cr=2118 pr=6630 pw=4641 time=3863356 us)'
STAT #2 id=25 cnt=264836 pid=24 pos=1 obj=106102 op='TABLE ACCESS FULL PS_TFTH_JOB (cr=2118 pr=1989 pw=0 time=2704710 us)'

STAT #2 id=26 cnt=42893 pid=14 pos=2 obj=58001 op='TABLE ACCESS FULL 
PS_EMPLOYMENT (cr=1135 pr=1133 pw=0 time=1449750 us)'
STAT #2 id=27 cnt=31967 pid=13 pos=2 obj=64184 op='INDEX FAST FULL SCAN 

PS0PERSONAL_DATA (cr=161 pr=158 pw=0 time=56034 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:34:53 CDT