Slow running procedures

        I have an overloaded procedure that runs quite well when called with one argument (performs an equijoin). However, when it is called with two arguments (performs a bounded range search) the procedure takes 30-40 times as long to run as the same exact SQL code entered by hand through SQL*DBA!

Code for the two argument version of the procedure: PROCEDURE rxclm_lev1 (lev1_first NUMBER, lev1_last NUMBER) IS BEGIN
INSERT INTO RXCLM_WANT
SELECT DISTINCT RXCLM.ID

	FROM RXCLM, RXTRADE
	WHERE RXCLM.CHAMPUS = RXTRADE.CHAMPUS AND
		RXTRADE.GENERIC >= lev1_first AND
		RXTRADE.GENERIC <= lev1_last;

        There are indexes available on RXCLM.CHAMPUS, RXTRADE.CHAMPUS (primary key) and RXTRADE.GENERIC. Table RXCLM has 4million tuples so using the RXCLM.CHAMPUS index is very selective. EXPLAIN_PLAN says the RXCLM.CHAMPUS is used for both an equijoin and for a range between lev1_first and lev1_last (where lev1_first and lev1_last select a small percentage of the GENERIC values). I know the index is being used when procedure is called with one argument because it runs in seconds. When entering the above SQL code in by hand from SQL*DBA runs in about a minute where constants 12 and 13 are used. However, executing RXCLM_LEV1(12,13) takes 35 minutes to run (returning the same result)!

        I'm running Oracle 7.0 on an Intel/NT server. Any assistance would be appreciated.
Randy Fought Received on Fri Mar 24 1995 - 21:44:55 CET