Home » RDBMS Server » Server Administration » Query Performance
Query Performance [message #218833] Sun, 11 February 2007 00:47 Go to next message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
Dear All

In Our Database the Query Performance is Very Poor Specially whenever we are using "Not In" Function or "Order By" or "Group By" Clause , sometimes it takes minutes to execute a query but same query if we use minus it takes fraction of seconds to execute what is the exact reason can anyone tell us

thanks

Re: Query Performance [message #218836 is a reply to message #218833] Sun, 11 February 2007 02:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
without posting the query and the statistics it will difficult for anybody tell you why it is behaving like this.

Information needed are

a) first the actual query which you have executed
b) Statistics of the table
c) Explain plan

cheers
Re: Query Performance [message #218837 is a reply to message #218836] Sun, 11 February 2007 02:59 Go to previous message
sarfraz_attari
Messages: 123
Registered: July 2005
Senior Member
Query is
SELECT A.PART1,A.PART2,A.PART3,SUM(STOCK_QTY) STK,
NVL(B.WAVG_COST,0) COST,
(SELECT MAX(INV_DT)
FROM ituser.PURCHASE_DETAILS_it
WHERE PART1 = A.PART1 AND PART2 = A.PART2
AND PART3 = A.PART3 )INV_DT,
(SELECT
DECODE(TRUNC((SYSDATE-MAX(TO_DATE(INV_DT,'DD-MON-RR')))/365),0,10,1,10,2,10,3,10
,4,20,5,20,6,30,7,30,8,30,9,30,10,30,50)
FROM ITUSER.PURCHASE_dETAILS_IT
WHERE PART1 = A.PART1 AND PART2 = A.PART2
AND PART3 = A.PART3 ) DISC
FROM WHOUSE_LOC_STOCK A,M_PART B
WHERE A.PART1 = B.PART1
AND A.PART2 = B.PART2 AND A.PART3 = B.PART3
GROUP BY A.PART1,A.PART2,A.PART3,NVL(B.WAVG_COST,0)

It is taking at least an hour to finish
Previous Topic: dblink error
Next Topic: Ora-12540 : TNS:internal limit restriction exceeded
Goto Forum:
  


Current Time: Sun Dec 04 06:33:38 CST 2016

Total time taken to generate the page: 0.10987 seconds