Query Tuning

[tabreaz]

Hi friends,

can someone help me in tuning the following query
both trans_tab and tax_tab have more than 180 million rows
and partition by hash, but the partition column is not present
is the query

insert into summ
select /*+ full(b) parallel (b,4)*/
a.srce_code, a.dest_code,
a.file_seq_no, a.filename, a.rec_type,
count(*) as rec_no, sum(a.charge) as charge,
sum(a.chargeable_units) as chargeable_units,
sum(b.tax) as tax
from (Select /*+ full(t) parallel(t, 4)*/
              srce_code, dest_code, file_seq_no, filename, 
               rec_type, rec_no, sum(charge) as charge,
               sum(chargeable_units) as chargeable_units
        from trans_tab t
Where ((t.rec_type != 75 or (t.rec_type = 75 and 
t.recentity_listno = 1))
and (t.charge_type = '00' or 
      t.charge_type is null or t.charge_type = ' '))
group by srce_code,dest_code, file_seq_no,
filename, rec_type, rec_no)a, tax_tab b
where a.filename = b.filename and
      a.rec_type = b.rec_type and
      a.rec_no = b.rec_no
group by a.srce_code, a.dest_code,
 a.file_seq_no, a.filename, a.rec_type

The above query executes as follows

1)Fetches all the data from trans_tab parallelely
2)SORT/MERGE join for grouping
3)Fetches all the data from tax_tab parallelely
4)SORT the result set
5)Hash join the on both result set

I watched above execution through OEM

[tamilselvan]

How many rows did you get from the in-line view?

Tamil

[tabreaz]

I tune the above query as below
but the problem is now with distinct, but it saves time when comparing to older version but still, can we tune it more?

oracle db is equped with 4 cpus

insert into summ
select /*+ full(a) full(b) parallel(a,4) parallel (b,4)*/
a.srce_code, a.dest_code,
a.file_seq_no, a.filename, a.rec_type,
count(distinct rec_no) as rec_no, sum(a.charge) as charge,
sum(a.chargeable_units) as chargeable_units,
sum(b.tax) as tax
from trans_tab t, tax_tab b
where a.filename = b.filename and
      a.rec_type = b.rec_type and
      a.rec_no = b.rec_no and
      a.sub_rec_type = b.sub_rec_type and
      a.sub_rec_no = b.sub_rec_no and
	((t.rec_type != 75 or (t.rec_type = 75 and t.recentity_listno = 1))
	and (t.charge_type = '00' or t.charge_type is null or t.charge_type = ' '))
group by a.srce_code, a.dest_code, a.file_seq_no, a.filename, a.rec_type

[tamilselvan]

If you analyze extended sql trace output, you will know where to tune it.

Tamil

[tabreaz]

The query is processed like folowing way
1. Fetch data from both tables(this is ok, and the time taking for fetching is considered to be good)
2. Hash join (ok)
3. Sort Merge
4. Sort Output

the third and fourth steps are coz of distinct keyword in the query, can someone help to avoid that distinct keyword in query and gaining in query performance

[tamilselvan]

select /*+ full(b) parallel (b,4)*/
a.srce_code, 
a.dest_code,
a.file_seq_no, 
a.filename, 
a.rec_type,
count(*) as rec_no, 
sum(a.charge) as charge,
sum(a.chargeable_units) as chargeable_units,
sum(b.tax) as tax
from (Select /*+ full(t) parallel(t, 4)*/
               srce_code, dest_code, file_seq_no, filename, 
               rec_type, rec_no, 
               sum(charge) as charge,
               sum(chargeable_units) as chargeable_units
        from trans_tab t
        Where ((t.rec_type != 75 or 
               (t.rec_type = 75 and t.recentity_listno = 1))
          and (t.charge_type = '00' or 
               t.charge_type is null or 
               t.charge_type = ' '))
         group by srce_code,dest_code, file_seq_no,
              filename, rec_type, rec_no) a, 
      tax_tab b
where a.filename = b.filename and
      a.rec_type = b.rec_type and
      a.rec_no = b.rec_no
group by a.srce_code, 
         a.dest_code,
         a.file_seq_no, 
         a.filename, 
         a.rec_type

I think in-line view is not necessary here.
Summing a column two times is a waste.
Write simple join and group by clause.

Tamil

Tamil

[tabreaz]

I use In-line view coz,
rec_no is divided into 'n' number of sub_rec_no and
each sub_rec_no can have multiple tax amounts.

thats why i was using in-line view, so that the two tables can be joined based on rec_no.