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 Go to next message](/forum/theme/orafaq/images/down.png) |
mm_kanish05
Messages: 493 Registered: January 2007 Location: Chennai
|
Senior Member |
![mm_kanish05%40yahoo.co.in](/forum/theme/orafaq/images/yahoo.png)
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mm_kanish05
Messages: 493 Registered: January 2007 Location: Chennai
|
Senior Member |
![mm_kanish05%40yahoo.co.in](/forum/theme/orafaq/images/yahoo.png)
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Re: How tuning this query ? (merged) [message #329319 is a reply to message #329318] |
Wed, 25 June 2008 01:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: How tuning this query ? (merged) [message #329342 is a reply to message #329321] |
Wed, 25 June 2008 02:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:33:49 CDT 2024
|