Home » RDBMS Server » Performance Tuning » Cost is high
Cost is high [message #208738] Tue, 12 December 2006 00:39 Go to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
Hi all,

I wrote a quary in a VB application like,
1.Here A,B,C are three tables.
2.A,B,C are properly indexed
3.Size of A,B is high but C is low

SELECT DISTINCT RR.GNO GNO,R.VGNO VGNO,R.PDE
FROM A R,B RR
Where TO_DATE(TO_CHAR(R.DATELOD,'DD-MON-YYYY'),'DD-MON-YYYY') ='12-DEC-2005'
AND R.PDE IS NOT NULL
AND R.PDE <> 'NEW'
AND R.STATE IS NULL
AND R.VGNO
NOT IN (SELECT VGNO FROM C
where TO_DATE(TO_CHAR(DATELOD,'DD-MON-YYYY'),'DD-MON-YYYY') ='12-DEC-2005')
AND RR.RNO = R.CH
AND RR.RNO= R.VGNO
ORDER BY VGNO


but the cost of this quary is around 1000 and the process taking 15 min to complete.

Can anybody help me to reduce cost of this and also reduce time.I am using ORACLE 9i

[Updated on: Tue, 12 December 2006 12:04]

Report message to a moderator

Re: Cost is high [message #208760 is a reply to message #208738] Tue, 12 December 2006 02:55 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Please read this forum's sticky and post the required info - explain plan, indexed columns, optimizer mode, statistics, etc.
Re: Cost is high [message #208931 is a reply to message #208738] Tue, 12 December 2006 11:24 Go to previous messageGo to next message
dba_blr
Messages: 43
Registered: December 2006
Member
Please do not simply post the queries and ask to tune. To tune queries one need a lot of other information as well, like indexes, size of objects, statisicts collection details etc etc.
Re: Cost is high [message #209040 is a reply to message #208738] Wed, 13 December 2006 01:00 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Quote:

"2.A,B,C are properly indexed" - Actually it means "I think the indexes are OK" - it's your opinion without any proof for it.

Please explain following condition:
... AND RR.RNO= R.CH AND RR.RNO = R.VGNO
( 2 columns from table A reference the same column of B)


Anyway the query can NOT use indexes because of TO_DATE(TO_CHAR(..)).
Try following:

SELECT DISTINCT RR.GNO GNO,R.VGNO VGNO,R.PDE
FROM A R,B RR
Where
R.DATELOD BETWEEN TO_DATE('12-DEC-2005 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('12-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
AND R.PDE IS NOT NULL
AND R.PDE <> 'NEW'
AND R.STATE IS NULL
AND NOT EXISTS
( SELECT 1 FROM C
WHERE C.VGNO = R.VGNO AND
C.DATELOD BETWEEN TO_DATE('12-DEC-2005 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('12-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
AND RR.RNO = R.CH
AND RR.RNO= R.VGNO
ORDER BY VGNO

Verify that you have following indexes:
1. Index on C with following 2 leading columns ( VGNO, DATELOD )
2. Index on A with DATELOD as first index column.
3. Index on B with ( RNO, (CH???) )

HTH.
Michael



Re: Cost is high [message #209815 is a reply to message #209040] Mon, 18 December 2006 00:03 Go to previous message
subhajit
Messages: 14
Registered: December 2006
Junior Member
Thanks michael_bialik for ur kind help. Your quary working fine..
Previous Topic: how to increase the speed of a MView Refresh
Next Topic: Post fast/complete refresh COMPILE_STATE=NEEDS_COMPILE
Goto Forum:
  


Current Time: Sun Dec 04 02:12:56 CST 2016

Total time taken to generate the page: 0.12070 seconds