SQL Tuning

**waitecj** · 04-29-2005, 09:59 AM

8.1.7.4
4CPU 2GB RAM

Code:

select uf.case_number, uf.error_fg, uf.case_amt, uf.case_balance, 
	uf.aba, uf.credit_ref_id, uf.sequence_nbr, uf.proc_date, 
        imc.case_number, imc.error_fg, imc.case_amt, imc.case_balance, 
	imc.aba, imc.credit_ref_id, imc.sequence_nbr, imc.proc_date
from ( select c.case_number, c.error_fg, c.case_amt, c.case_balance, 
		r.aba, r.credit_ref_id, r.sequence_nbr, r.proc_date
	from case_t c, case_research_t r
	where c.case_number=r.case_number and
	c.proc_date between '20040510' and '20050228' and
	c.error_fg = 'UF' and
	c.status_fg in('O','P') and
	r.credit_ref_id <> '                  ' and
	r.credit_ref_id <> '000000' ) uf,
     ( select c.case_number, c.error_fg, c.case_amt, c.case_balance, 
		r.aba, r.credit_ref_id, r.sequence_nbr, r.proc_date
	from case_t c, case_research_t r
	where c.case_number=r.case_number and
	c.proc_date between '20040510' and '20040517' and
	c.error_fg = 'IMC' and
	c.status_fg ='C' and
	r.credit_ref_id <> '                  ' and
	r.credit_ref_id <> '000000' ) imc
where uf.credit_ref_id=imc.credit_ref_id and
uf.case_amt=imc.case_amt;

This query is taking an age to run and i'm a bit stuck with tuning it. THe two tables involved (CASE_T and CASE_RESEARCH_T) have about 3.5 million rows in each, one per CASE_NUMBER, which is the primary key in both. CASE_T is the parent table. The explain plan is:

Code:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1109 Card=1 Bytes=16
          0)

   1    0   NESTED LOOPS (Cost=1109 Card=1 Bytes=160)
   2    1     NESTED LOOPS (Cost=907 Card=101 Bytes=11009)
   3    2       NESTED LOOPS (Cost=649 Card=43 Bytes=3440)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'CASE_T' (Cost=563 Card=43 Bytes=1247)

   5    4           INDEX (RANGE SCAN) OF 'CASE_SEARCH_IDX' (NON-UNIQUE) (Cost=43 Card=43)

   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'CASE_RESEARCH_T' (Cost=2 Card=3263103 Bytes=166418253)

   7    6           INDEX (UNIQUE SCAN) OF 'CASE_RESEARCH_KEY' (UNIQUE) (Cost=1 Card=3263103)

   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'CASE_T' (Cost=6 Card=3649 Bytes=105821)

   9    8         INDEX (RANGE SCAN) OF 'CASE_AMT_KEY' (NON-UNIQUE) (Cost=2 Card=3649)

  10    1     TABLE ACCESS (BY INDEX ROWID) OF 'CASE_RESEARCH_T' (Cost=2 Card=3263103 Bytes=166418253)

  11   10       INDEX (UNIQUE SCAN) OF 'CASE_RESEARCH_KEY' (UNIQUE) (Cost=1 Card=3263103)

I am tracing my session whilst i'm running it as we speak but its been going for over an hour now so I thought in the mean time I might ask for some advise with tuning the SQL. I'm fairly sure that the top wait events for this will almost certainly be for reading blocks from disk (as its retrieving nearly all the rows in CASE_RESEARCH_T) and DIRECT PATH reads\writes (because I am comparing two result sets). SQL aside I was thinking I could increase SORT_AREA_SIZE for the session? Anything else I could try? I'll add the SQL trace to the thread once it has finanlly finished...

**waitecj** · 04-29-2005, 10:00 AM

Oops, should probably be in the Developer forum. Can you move it please Mr Moderator?

**tamilselvan** · 04-29-2005, 08:33 PM

Try this:

PHP Code:


select uf.case_number, uf.error_fg, uf.case_amt, uf.case_balance, 

    uf.aba, uf.credit_ref_id, uf.sequence_nbr, uf.proc_date, 

        imc.case_number, imc.error_fg, imc.case_amt,  

        imc.case_balance, 

    imc.aba, imc.credit_ref_id, imc.sequence_nbr, imc.proc_date

from ( select rownum as RN,

              c.case_number, c.error_fg, 

              c.case_amt, c.case_balance, 

          r.aba, r.credit_ref_id, r.sequence_nbr, 

              r.proc_date

    from  case_t c, 

              case_research_t r

    where c.case_number=r.case_number and

    c.proc_date between '20040510' and '20050228' and

    c.error_fg = 'UF' and

    c.status_fg in('O','P') and

    r.credit_ref_id <> '                  ' and

    r.credit_ref_id <> '000000' ) uf,

     ( select rownum as RN,

              c.case_number, c.error_fg, c.case_amt, 

              c.case_balance, 

              r.aba, r.credit_ref_id, 

              r.sequence_nbr, r.proc_date

    from  case_t c, 

              case_research_t r

    where c.case_number=r.case_number and

    c.proc_date between '20040510' and '20040517' and

    c.error_fg = 'IMC' and

    c.status_fg ='C' and

    r.credit_ref_id <> '                  ' and

    r.credit_ref_id <> '000000' ) imc

where uf.credit_ref_id=imc.credit_ref_id and

uf.case_amt=imc.case_amt;

By adding ROWNUM in the sub-query, the CBO may choose different JOIN operation for imc and uf in-line views.

Tamil

**waitecj** · 05-03-2005, 04:51 AM

Thanks for your suggestion Tamil. I added rownum in as you suggested and ended up with the following explain plan:

Code:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21355 Card=1 Bytes=166)

   1    0   HASH JOIN (Cost=21355 Card=1 Bytes=166)
   2    1     VIEW (Cost=650 Card=43 Bytes=3569)
   3    2       COUNT
   4    3         NESTED LOOPS (Cost=650 Card=43 Bytes=3440)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'CASE_T' (Cost=564 Card=43 Bytes=1247)

   6    5             INDEX (RANGE SCAN) OF 'CASE_SEARCH_IDX' (NON-UNIQUE) (Cost=43 Card=43)

   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'CASE_RESEARCH_T' (Cost=2 Card=3267947 Bytes=166665297)

   8    7             INDEX (UNIQUE SCAN) OF 'CASE_RESEARCH_KEY' (UNIQUE) (Cost=1 Card=3267947)

   9    1     VIEW (Cost=20704 Card=3654 Bytes=303282)
  10    9       COUNT
  11   10         NESTED LOOPS (Cost=20704 Card=3654 Bytes=292320)
  12   11           TABLE ACCESS (BY INDEX ROWID) OF 'CASE_T' (Cost=13396 Card=3654 Bytes=105966)

  13   12             INDEX (RANGE SCAN) OF 'CASE_ERROR' (NON-UNIQUE) (Cost=204 Card=3654)

  14   11           TABLE ACCESS (BY INDEX ROWID) OF 'CASE_RESEARCH_T' (Cost=2 Card=3267947 Bytes=166665297)

  15   14             INDEX (UNIQUE SCAN) OF 'CASE_RESEARCH_KEY' (UNIQUE) (Cost=1 Card=3267947)

The major wait event seems to be reading from disk, presumably because all the rows in table CASE_RESEARCH_T in the subquery meets the condition. Any other suggestions?

**abhaysk** · 05-03-2005, 05:04 AM

Code:

select uf.case_number, uf.error_fg, uf.case_amt, uf.case_balance, 
	uf.aba, uf.credit_ref_id, uf.sequence_nbr, uf.proc_date, 
        imc.case_number, imc.error_fg, imc.case_amt, imc.case_balance, 
	imc.aba, imc.credit_ref_id, imc.sequence_nbr, imc.proc_date
from ( select /*+ full(c) full(r) parallel(c, 4) parallel(r, 4) */ c.case_number, c.error_fg, c.case_amt, c.case_balance, 
		r.aba, r.credit_ref_id, r.sequence_nbr, r.proc_date
	from case_t c, case_research_t r
	where c.case_number=r.case_number and
	c.proc_date between '20040510' and '20050228' and
	c.error_fg = 'UF' and
	c.status_fg in('O','P') and
	r.credit_ref_id <> '                  ' and
	r.credit_ref_id <> '000000'  and rownum > 0) uf,
     ( select /*+ full(c) full(r) parallel(c, 4) parallel(r, 4) */ c.case_number, c.error_fg, c.case_amt, c.case_balance, 
		r.aba, r.credit_ref_id, r.sequence_nbr, r.proc_date
	from case_t c, case_research_t r
	where c.case_number=r.case_number and
	c.proc_date between '20040510' and '20040517' and
	c.error_fg = 'IMC' and
	c.status_fg ='C' and
	r.credit_ref_id <> '                  ' and
	r.credit_ref_id <> '000000' ) imc
where uf.credit_ref_id=imc.credit_ref_id and
uf.case_amt=imc.case_amt;

Abhay.

**waitecj** · 05-03-2005, 06:19 AM

Hmmm, not sure if I want to force FTS on both tables and hash the resultset together... have a look at the plan.

Code:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=146367 Card=1 Bytes=163)

   1    0   HASH JOIN (Cost=146367 Card=1 Bytes=163)
   2    1     HASH JOIN (Cost=72085 Card=43 Bytes=3440)
   3    2       TABLE ACCESS (FULL) OF 'CASE_T' (Cost=40561 Card=43 Bytes=1247)

   4    2       TABLE ACCESS (FULL) OF 'CASE_RESEARCH_T' (Cost=31401 Card=3267947 Bytes=166665297)

   5    1     VIEW (Cost=74281 Card=3654 Bytes=303282)
   6    5       COUNT
   7    6         FILTER
   8    7           HASH JOIN (Cost=74281 Card=3654 Bytes=292320)
   9    8             TABLE ACCESS (FULL) OF 'CASE_T' (Cost=40561 Card=3654 Bytes=105966)

  10    8             TABLE ACCESS (FULL) OF 'CASE_RESEARCH_T' (Cost=31401 Card=3267947 Bytes=166665297)

I am trying to get a trace file for this but have blown my MAX_DUMP_FILE_SIZE twice!! Can anyone think of a way of completely re-structuring the SQL to achieve the same result? I have tried making my criteria more selective (using my knowledge of the app) to try to prevent the high number of blocks that are being read, but that didn't seem to have much of an effect.

**abhaysk** · 05-03-2005, 06:46 AM

FTS seems better option.. Did u run the query?

**waitecj** · 05-03-2005, 06:55 AM

I'm still waiting for the results...

**waitecj** · 05-03-2005, 07:20 AM

abhaysk - It ran in 27 minutes... thats quicker than the un-hinted version of the sql but still seems an awful long time to me. Is there anything else I can experiment with?

**abhaysk** · 05-03-2005, 07:24 AM

I dont think you have defined ur parallel_max_servers.. but any ways.. how many records will your subqueries imc & uh fetch..

If any one returns very less number of recs.. then u can force other subquery for index scans..

Abhay.

Thread: SQL Tuning

Thread Tools

Display

SQL Tuning

Posting Permissions