Thursday, May 29, 2008

"dual"-ing for resources

Oracle 9.2.0.6 on AIX
Application module running in 12 concurrent streams (12 CPU box)

Total response time (%R) = 18 minutes 55 seconds
Total executions of "SELECT ... FROM dual" = 1,087,770
Total logical I/O's = 3,263,033
Contribution to R = 32.9%

Actual code:

PROCEDURE next_delq_date
(DUE_DATE IN VARCHAR2,
DELQ_DAYS IN NUMBER,
ACTION_DATE IN OUT VARCHAR2)
IS
DAY_NUM NUMBER;
HOLIDAY_DATE VARCHAR2(11);
BEGIN
BEGIN
ACTION_DATE := DUE_DATE;
FOR DAYS_COUNT IN 1..DELQ_DAYS LOOP
SELECT TO_CHAR(TO_DATE
(ACTION_DATE,'DD-MON-YYYY')+1, 'DD-MON-YYYY')
INTO ACTION_DATE FROM DUAL;
SELECT TO_CHAR(TO_DATE
(ACTION_DATE,'DD-MON-YYYY'),'D')
INTO DAY_NUM
FROM DUAL;
IF DAY_NUM = 1 THEN
SELECT TO_CHAR(TO_DATE
(ACTION_DATE,'DD-MON-YYYY')+1, 'DD-MON-YYYY')
INTO ACTION_DATE FROM DUAL;
END IF;
IF DAY_NUM = 7 THEN
SELECT TO_CHAR(TO_DATE
(ACTION_DATE,'DD-MON-YYYY')+2, 'DD-MON-YYYY')
INTO ACTION_DATE FROM DUAL;
END IF;

LOOP
HOLIDAY_DATE := NULL;
BEGIN
SELECT TO_CHAR(UTRHLDY_DATE,'DD-MON-YYYY')
INTO HOLIDAY_DATE FROM UTRHLDY
WHERE UTRHLDY_DATE = to_date(ACTION_DATE,'DD-MON-YYYY')
AND ROWNUM < 2;
EXCEPTION WHEN NO_DATA_FOUND THEN
HOLIDAY_DATE := NULL;
END;
IF HOLIDAY_DATE IS NULL THEN
EXIT;
END IF;
IF HOLIDAY_DATE IS NOT NULL THEN
SELECT TO_CHAR(TO_DATE(ACTION_DATE,'DD-MON-YYYY')+1, 'DD-MON-YYYY')
INTO ACTION_DATE FROM DUAL;
SELECT TO_CHAR(TO_DATE(ACTION_DATE,'DD-MON-YYYY'),'D')
INTO DAY_NUM FROM DUAL;
IF DAY_NUM = 1 THEN
SELECT TO_CHAR(TO_DATE(ACTION_DATE,'DD-MON-YYYY')+1, 'DD-MON-YYYY')
INTO ACTION_DATE FROM DUAL;
END IF;
IF DAY_NUM = 7 THEN
SELECT TO_CHAR(TO_DATE(ACTION_DATE,'DD-MON-YYYY')+2, 'DD-MON-YYYY')
INTO ACTION_DATE FROM DUAL;
END IF;
END IF;
END LOOP;
END LOOP;
END;
END;


I believe this is the most inefficient way I've ever seen to determine the next business day (not counting holidays) when an account will become n days delinquent. Admittedly, this code gets better (performance wise) in v10 with the addition of FAST DUAL, but still... There's only one SELECT (on the holiday table, UTRHLDY) that appears to be needed at all. Why use DUAL to calculate and format dates? Ever hear of variable assignment?

I suppose you've gotta laugh, huh? This may be one for the Oracle WTF!

No comments: