OraFAQ Forum: Performance Tuning » How tuning this query ? (merged)

Home » RDBMS Server » Performance Tuning » How tuning this query ? (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How tuning this query ? (merged) [message #329141]

Tue, 24 June 2008 04:30

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Hi Im running below the query on ISQL*plus for one month detail
and im getting result at

Elapsed: 00:01:51.33

SELECT   d.glCode,d.DocumentNo,d.DocumentDate,
         d.DebitAmount,a.VatSerialNo,a.VatCreditDate,
         a.TaxinVoiceNo,a.TaxinVoicedt,a.TaxPerc,
         a.TaxableAmt,a.TaxAmount,a.grdNumber,
         a.grdDate,c.GrinNo,c.Grindt,a.GroupNo,
         a.VendCode,b.VendName,b.VendCity,
         b.TinNo VendLocalst,Nvl(d.DebitAmount,0) - Nvl(a.TaxAmount,0)
FROM     grdData a,VendMast b,
         GrinMain c,Ledger d
WHERE    c.Orgnl_grnNo IS NULL 
         AND c.UnitId = :P_UNITID
         AND d.UnitId = c.UnitId
         AND c.grdref_No = a.grdNumber
         AND c.PeriodId = a.PeriodId
         AND c.cn_refNo = d.DocumentNo
         AND a.UnitId = c.UnitId
         AND a.VatCreditDate BETWEEN :P_FROMDATE
                                     AND :P_TODATE
         AND a.VendCode = b.VendCode
         AND d.glCode IN ('55695','90731','90732','90733',
                          '90734','90735','90736','90737',
                          '90740','90741','90742','90743','90739')
ORDER BY a.VatSerialNo,
         a.VatCreditDate,
         d.DocumentNo,
         d.DocumentDate,
         a.VendCode

Can I have detail idea. What type of tuning is necessary and what should i want to check to correct this issue.

please guide me

Kanish

Report message to a moderator

how to tuning the query? [message #329143 is a reply to message #329141]

Tue, 24 June 2008 04:33

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Hi
Im running the following query on ISQL*PLUS for to get the one month detail
with below elapsed time

SELECT   d.glCode,d.DocumentNo,d.DocumentDate,
         d.DebitAmount,a.VatSerialNo,a.VatCreditDate,
         a.TaxinVoiceNo,a.TaxinVoicedt,a.TaxPerc,
         a.TaxableAmt,a.TaxAmount,a.grdNumber,
         a.grdDate,c.GrinNo,c.Grindt,a.GroupNo,
         a.VendCode,b.VendName,b.VendCity,
         b.TinNo VendLocalst,Nvl(d.DebitAmount,0) - Nvl(a.TaxAmount,0)
FROM     grdData a,VendMast b,
         GrinMain c,Ledger d
WHERE    c.Orgnl_grnNo IS NULL 
         AND c.UnitId = :P_UNITID
         AND d.UnitId = c.UnitId
         AND c.grdref_No = a.grdNumber
         AND c.PeriodId = a.PeriodId
         AND c.cn_refNo = d.DocumentNo
         AND a.UnitId = c.UnitId
         AND a.VatCreditDate BETWEEN :P_FROMDATE
                                     AND :P_TODATE
         AND a.VendCode = b.VendCode
         AND d.glCode IN ('55695','90731','90732','90733',
                          '90734','90735','90736','90737',
                          '90740','90741','90742','90743','90739')
ORDER BY a.VatSerialNo,
         a.VatCreditDate,
         d.DocumentNo,
         d.DocumentDate,
         a.VendCode

Elapsed: 00:01:51.33

To reduce the time what type of corrective action should i want to take

please guide me.

kanish

Report message to a moderator

Re: how to tuning the query? [message #329279 is a reply to message #329143]

Tue, 24 June 2008 22:18

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Take a look at the Performance Tuning Sticky - the first thread in this forum. It will tell you the sort of information we need to know to help.

It also contains links to some tuning resources that could help you tune it yourself if you are inclined.

Ross Leishman

Report message to a moderator

Re: How tuning this query ? (merged) [message #329282 is a reply to message #329141]

Tue, 24 June 2008 22:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Are statistics current for all objects involved?

Please post EXPLAIN PLAN.

Report message to a moderator

Re: How tuning this query ? (merged) [message #329318 is a reply to message #329141]

Wed, 25 June 2008 01:07

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

explain plan

Execution Plan 
---------------------------------------------------------- 
0     SELECT STATEMENT Optimizer=CHOOSE (Cost=1990 Card=1 Bytes=15 4)     
1  0      SORT (ORDER BY) (Cost=1990 Card=1 Bytes=154)     
2  1          NESTED LOOPS (Cost=1978 Card=1 B ytes=154)     
3  2              NESTED LOOPS (Cost =1977 Card=1 Bytes=114)     
4  3                  HASH JOIN (Cost=537 Card=1 Bytes=84)     
5  4                  &nbs p;   TABLE ACCESS (FULL) OF 'GRDDATA' (Cost=224 Card=17 Bytes=714)     
6  4                  &nbs p;   TABLE ACCESS (FULL) OF 'GRINMAIN' (Cost=312 Card=1 2183 Bytes=511686)     
7  3                  TABL E ACCESS (BY INDEX ROWID) OF 'LEDGER' (Cost=1440 Card=1 Byte s=30)     
8  7                  &nbs p;   INDEX (RANGE SCAN) OF 'PK_LEDA' (UNIQUE) (Cost=143 9 Card=1)     
9  2              TABLE ACCESS (BY I NDEX ROWID) OF 'VENDMAST' (Cost=1 Card=1 Bytes=40)     
10  9                  INDE X (UNIQUE SCAN) OF 'PK_VEND' (UNIQUE)

Statistics 
---------------------------------------------------------- 

0  recursive calls  
0  db block gets  
2143604  consistent gets  
1389  physical reads  
0  redo size  
49180  bytes sent via SQL*Net to client  
800  bytes received via SQL*Net from client  
29  SQL*Net roundtrips to/from client  
1  sorts (memory)  
0  sorts (disk)

412 rows processed

Report message to a moderator

Re: How tuning this query ? (merged) [message #329319 is a reply to message #329318]

Wed, 25 June 2008 01:15

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Got an index on a.VatCreditDate?
What about c.UnitId?

These tables are performing Full table scans? Is your SQL using more than 10% of the rows in these tables? If less, then indexes will help eliminate unnecessary IO.

Ross Leishman

Report message to a moderator

Re: How tuning this query ? (merged) [message #329321 is a reply to message #329141]

Wed, 25 June 2008 01:30

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Quote:

Got an index on a.VatCreditDate

?

No index

so i created index now

  create index vatcr on grddata(vatcreditdate);

it is over.

Quote:

What about c.UnitId?

unitid,periodid,grinno,grindate is primary on that table.

so i feel i dont want to index for unitid.

after finish all this things im getting this output.

Elapsed: 00:01:46.40

Execution Plan 
---------------------------------------------------------- 
0     SELECT STATEMENT Optimizer=CHOOSE (Cost=1777 Card=1 Bytes=15 4)     
1  0      SORT (ORDER BY) (Cost=1777 Card=1 Bytes=154)     
2  1          NESTED LOOPS (Cost=1765 Card=1 B ytes=154)     
3  2              NESTED LOOPS (Cost =1764 Card=1 Bytes=114)     
4  3                  HASH JOIN (Cost=324 Card=1 Bytes=84)     
5  4                  &nbs p;   TABLE ACCESS (BY INDEX ROWID) OF 'GRDDATA' (Cost=1 1 Card=17 Bytes=714)     
6  5                  &nbs p;       INDEX (RANGE SCAN) OF 'VATCRDDATE' ( NON-UNIQUE) (Cost=2 Card=1019)     
7  4                  &nbs p;   TABLE ACCESS (FULL) OF 'GRINMAIN' (Cost=312 Card=1 2183 Bytes=511686)     
8  3                  TABL E ACCESS (BY INDEX ROWID) OF 'LEDGER' (Cost=1440 Card=1 Byte s=30)     
9  8                  &nbs p;   INDEX (RANGE SCAN) OF 'PK_LEDA' (UNIQUE) (Cost=143 9 Card=1)     
10  2              TABLE ACCESS (BY I NDEX ROWID) OF 'VENDMAST' (Cost=1 Card=1 Bytes=40)     
11  10                  INDE X (UNIQUE SCAN) OF 'PK_VEND' (UNIQUE)

Statistics 
---------------------------------------------------------- 

0  recursive calls  
0  db block gets  
2142377  consistent gets  
112  physical reads  
0  redo size  
49180  bytes sent via SQL*Net to client  
800  bytes received via SQL*Net from client  
29  SQL*Net roundtrips to/from client  
1  sorts (memory)  
0  sorts (disk)

412 rows processed

please any updation rleishman

kanish

Report message to a moderator

Re: How tuning this query ? (merged) [message #329342 is a reply to message #329321]

Wed, 25 June 2008 02:54

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

The query is reading 2142377 blocks from the buffer-cache. We don't know haw many rows are returned, so it's hard to say if that's good or bad.

Since you think it should be a lot faster, I'm guessing it doesn't return millions of rows. Assuming it returns not much data, one of the steps in your plan is reading a lot of unneccessary rows and then discarding them.

I cannot tell which step, because I don't know the strucutre of your tables or indexes. The easiest way to find out is to run a trace and generate the TKPROF output; it will show a row-count against each step of the plan. That will tell us where the problem is.

See the Oracle Performance Tuning manual for details of running SQL Trace and TKPROF.

Ross Leishman

Report message to a moderator

Previous Topic:	max number of users and sessions
Next Topic:	Window Sort

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 20:42:51 CDT 2024