Query Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query Tuning

  1. #1
    Join Date
    Sep 2005
    Posts
    278

    Query Tuning

    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

    Code:
    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
    Last edited by tamilselvan; 02-20-2006 at 12:15 PM.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How many rows did you get from the in-line view?

    Tamil

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    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

    Code:
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    If you analyze extended sql trace output, you will know where to tune it.

    Tamil

  5. #5
    Join Date
    Sep 2005
    Posts
    278
    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

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Code:
    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

  7. #7
    Join Date
    Sep 2005
    Posts
    278
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width