Home » SQL & PL/SQL » SQL & PL/SQL » Unstable Query Execution Time (Oracle 10 G, [Unix Server])
Unstable Query Execution Time [message #312462] Tue, 08 April 2008 23:59 Go to next message
Avalanche
Messages: 1
Registered: April 2008
Junior Member
Hi,

I am running the query below on Oracle 10G (From .NET Application using Bind Variables) and each time, the response time is different. Sometimes, it runs in 5 seconds, sometimes it takes 5 minutes, sometimes hangs, etc.

What may be the problem ?

-----------------------------------------------------

/* Formatted on 2008/04/09 07:57 (Formatter Plus v4.8.6) */
SELECT TO_CHAR (TO_DATE (REPLACE (fadmission.fvalue, '-', '/'),
'YYYY/MM/DD HH24:MI:SS'
),
'YYYY'
) AS YEAR,
TO_CHAR (TO_DATE (REPLACE (fadmission.fvalue, '-', '/'),
'YYYY/MM/DD HH24:MI:SS'
),
'MM'
) AS MONTH,
TRUNC
(( (SUM (ROUND ( TO_DATE (REPLACE (fdischarge.fvalue, '-',
'/'),
'YYYY/MM/DD HH24:MI:SS'
)
- TO_DATE (REPLACE (fadmission.fvalue, '-',
'/'),
'YYYY/MM/DD HH24:MI:SS'
)
)
)
)
/ COUNT (fadmission.fvalue)
),
1
) AS alos
FROM action a,
episode e,
formvalues fadmission,
formvalues fdischarge,
mresource mh,
formvalues fw,
resourcetree rt
WHERE a.actiontype = 'YATANHASTA'
AND a.actionsubtype = 'PROSEDUR'
AND a.cancelled = 0
AND a.episodeid = e.ID
AND e.inpatient IN (1, 2)
AND TO_DATE (REPLACE (fadmission.fvalue, '-', '/'),
'YYYY/MM/DD HH24:MI:SS'
) BETWEEN TO_DATE (:start_date, 'MM.DD.YYYY HH24:MI:SS')
AND TO_DATE (:end_date, 'MM.DD.YYYY HH24:MI:SS')
AND fdischarge.recordid(+) = e.ID
AND fdischarge.fieldid(+) = 124
AND fdischarge.flevel(+) = 'E'
AND fadmission.recordid = e.ID
AND fadmission.fieldid = 123
AND fadmission.flevel = 'E'
AND fw.recordid = e.ID
AND fw.fieldid = 120
AND fw.flevel = 'E'
AND rt.childresourceid = fw.fvalue
AND mh.resourcetype = 'SUBHOSPITAL'
AND mh.ID = rt.parentresourceid
AND mh.ID = :hospital_id
GROUP BY TO_CHAR (TO_DATE (REPLACE (fadmission.fvalue, '-', '/'),
'YYYY/MM/DD HH24:MI:SS'
),
'YYYY'
),
TO_CHAR (TO_DATE (REPLACE (fadmission.fvalue, '-', '/'),
'YYYY/MM/DD HH24:MI:SS'
),
'MM'
)
ORDER BY TO_CHAR (TO_DATE (REPLACE (fadmission.fvalue, '-', '/'),
'YYYY/MM/DD HH24:MI:SS'
),
'YYYY'
),
TO_CHAR (TO_DATE (REPLACE (fadmission.fvalue, '-', '/'),
'YYYY/MM/DD HH24:MI:SS'
),
'MM'
)

------------------------

Your help is really appreciated


Regards,

Ahmed
Re: Unstable Query Execution Time [message #312470 is a reply to message #312462] Wed, 09 April 2008 00:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
read & FOLLOW posting guidelines above
Re: Unstable Query Execution Time [message #312471 is a reply to message #312462] Wed, 09 April 2008 00:19 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I could be many reasons.
Check if you always use the same plan.
Check if you have the same workoad.
Activate a trace to know where time is spent.

Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: JOIN
Next Topic: how to run reports from sql (merged)
Goto Forum:
  


Current Time: Fri Dec 09 21:07:58 CST 2016

Total time taken to generate the page: 0.08958 seconds