Performance issues with APEX_UTIL.GET_SESSION_ITEM

I

I faced severe performance degradation in one of my APEX applications. After some time I identified issue with APEX_UTIL.GET_SESSION_ITEM call.

Let's create test table with 1M rows:

--DROP TABLE test_table;
CREATE TABLE test_table (
    id          NUMBER,
    group_id    NUMBER
);
--
INSERT INTO /*+ append */ test_table (id, group_id)
SELECT ROWNUM, MOD(ROWNUM, 5)
FROM DUAL
CONNECT BY LEVEL <= 1000000;
--
COMMIT;

Check table content:

SELECT group_id, COUNT(*) AS rows_
FROM test_table
GROUP BY group_id
ORDER BY 1;

Now we can simulate query with APEX_UTIL.GET_SESSION_ITEM function. For this test item existance doesn't matter. Let's pretend, we want to select rows for specific group_id based on APEX item value.

SELECT COUNT(*) AS rows_    -- 152 sec
FROM test_table t
WHERE t.group_id = NVL(APEX_UTIL.GET_SESSION_STATE('APEX_ITEM'), 0);

Any ideas why this is that slow?

It is because that function is called for every single row in that table. Here is a proof.

BEGIN
    apex_test.get_item_calls := 0;
    --
    FOR c IN (
        SELECT COUNT(*) AS rows_    -- 152 sec
        FROM test_table t
        WHERE t.group_id = NVL(apex_test.get_item('APEX_ITEM'), 0)
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(c.rows_);
    END LOOP;
    --
    DBMS_OUTPUT.PUT_LINE(apex_test.get_item_calls);
END;
/

Oh yes, you will need APEX_TEST wrapper:

CREATE OR REPLACE PACKAGE apex_test AS

    get_item_calls          NUMBER;

    FUNCTION get_item (
        in_name             VARCHAR2
    )
    RETURN VARCHAR2;

END;
/

CREATE OR REPLACE PACKAGE BODY apex_test AS

    FUNCTION get_item (
        in_name             VARCHAR2
    )
    RETURN VARCHAR2 AS
    BEGIN
        get_item_calls := get_item_calls + 1;       -- track calls
        --
        RETURN APEX_UTIL.GET_SESSION_STATE(in_name);
    END;

END;
/

Now when we know what is wrong, we can fix it. WITH and hints to the rescue.

BEGIN
    apex_test.get_item_calls := 0;
    --
    FOR c IN (
        WITH x AS (
            SELECT /*+ materialize */
            --SELECT /*+ result_cache */
                apex_test.get_item('APEX_ITEM') AS APEX_ITEM
            FROM DUAL
        )
        SELECT COUNT(*) AS rows_    -- 152 sec
        FROM test_table t
        CROSS JOIN x
        WHERE t.group_id = NVL(x.APEX_ITEM, 0)
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(c.rows_);
    END LOOP;
    --
    DBMS_OUTPUT.PUT_LINE(apex_test.get_item_calls);
END;
/

You can use either (undocumented) materialize hint, which will call the function just once for every run. Or you can use result_cache hint, which will call that function twice for the first run and then most likely zero times for subsequent calls.

Learn more about hints.

Cleanup:

DROP TABLE test_table PURGE;
DROP PACKAGE apex_test;