Home » RDBMS Server » Performance Tuning » How tuning this query ? (merged)
How tuning this query ? (merged) [message #329141] Tue, 24 June 2008 04:30 Go to next message
mm_kanish05
Messages: 487
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
how to tuning the query? [message #329143 is a reply to message #329141] Tue, 24 June 2008 04:33 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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
Re: how to tuning the query? [message #329279 is a reply to message #329143] Tue, 24 June 2008 22:18 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: How tuning this query ? (merged) [message #329282 is a reply to message #329141] Tue, 24 June 2008 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Are statistics current for all objects involved?

Please post EXPLAIN PLAN.
Re: How tuning this query ? (merged) [message #329318 is a reply to message #329141] Wed, 25 June 2008 01:07 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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


Re: How tuning this query ? (merged) [message #329319 is a reply to message #329318] Wed, 25 June 2008 01:15 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: How tuning this query ? (merged) [message #329321 is a reply to message #329141] Wed, 25 June 2008 01:30 Go to previous messageGo to next message
mm_kanish05
Messages: 487
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
Re: How tuning this query ? (merged) [message #329342 is a reply to message #329321] Wed, 25 June 2008 02:54 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: max number of users and sessions
Next Topic: Window Sort
Goto Forum:
  


Current Time: Fri Dec 02 23:08:59 CST 2016

Total time taken to generate the page: 0.06720 seconds