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  |
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 #312471 is a reply to message #312462] |
Wed, 09 April 2008 00:19  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
Goto Forum:
Current Time: Tue Feb 18 22:33:52 CST 2025
|