I'm seeing some really messed up behavior from a stored procedure. Here is the DDL:

CREATE PROCEDURE AIM.GetUserIDRetrievals (IN strUserID VARCHAR(64),
IN decAIMConnectionID DECIMAL(13,0))
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
SPECIFIC AIM.GetUserIDRetrieval
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
DECLARE SELECT_CURSOR CURSOR WITH RETURN FOR
SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE, AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT, AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
(SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT - COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM AIM.AIMRETRIEVEDITEM WHERE
(AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
AS NUMBERIMAGESAVAILABLE
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE,
AIM.CHANNEL
WHERE
AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
AND (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE >= DATE(CURRENT TIMESTAMP) OR (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE IS NULL AND AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME >= CURRENT TIMESTAMP))
AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'D'
AND AIM.AIMCONNECTION.USERID = strUserID
AND AIM.CHANNEL.CHANNEL =
(SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESSION, AIM.AIMCONNECTION WHERE
AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.CHANNELSESSION.CHANNELID = AIM.CHANNEL.CHANNELID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = decAIMConnectionID)
GROUP BY AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE, AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT, AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT
ORDER BY AIMRETRIEVALID;
OPEN SELECT_CURSOR;
END P1

For some reason this procedure is running absolutely dog slow!!!! If I run the SQL in the procedure ad-hoc (command line, etc.), the query completes sub-second; the procedure takes about three minutes to return the result set...

Can anyone shed any light on what the issue may be?!? Do stored procedures run in a different memory pool or something? Does the query optimizer need some help?

Any help would be greatly appreciated!!!

AMR

Have you tried Explaiming the statements dynamically and the procedure's package...
Are they the same ?

If you have done a RUNSTATS recently, rebind the procedure's package ...

To know exactly where the problem is in your appl, switch on the statement and transactio event monitors and check how long this query actually takes ... It may be that other bits of your appl are taking longer and not this proc

HTH

Sathyaram