Re: performance question

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

Other related posts: