Oracle APEX SYS_CONTEXT vs V Function

With APEX Version 5.0, new Application Context APEX$SESSION is introduced. User Name, Session ID and Work-space ID are populated into this context. I often read, using APEX$SESSION Context is faster (gives better performance) than using V function, however, how fast is it? I have never measured it. It's been on my TODO list for long time and finally here is short test I did to measure the performance using both the approaches.

DB Setup:

I have created two views in APEX Parsing schema as follows.

View demo_sys_context_v uses APEX$SESSION Context to get current APEX user name.

CREATE OR REPLACE VIEW demo_sys_context_v
 AS
    SELECT
        object_name
    FROM
        user_objects
    WHERE
        object_name = sys_context(
            'APEX$SESSION'
            ,'APP_USER'
        );

View demo_vfunction_v uses V function to get current APEX user name.

CREATE OR REPLACE VIEW demo_vfunction_v
 AS
    SELECT
        object_name
    FROM
        user_objects
    WHERE
        object_name = v(
            'APP_USER'
        );

These views might not make much sense, because I am comparing object_name with APEX user name. However, they are good enough for our testing. 

APEX Setup: 

In APEX, I have created one region with title as "With SYS_CONTEXT" and type as "PL/SQL Dynamic Content". I have written below PL/SQL code as "Source".

DECLARE

    l_obj_count   PLS_INTEGER;

BEGIN

    HTP.P('Start: '||SYSTIMESTAMP);

    FOR i IN 1..:P8_RUN_COUNT LOOP

        SELECT

            COUNT(1)

        INTO l_obj_count

        FROM

            demo_sys_context_v;

    END LOOP;

    HTP.P('<br/>End: '||SYSTIMESTAMP);    

END;

In this code, I am running loop based on page item P8_RUN_COUNT value. And for each iteration, I am accessing demo_sys_context_v view to get row count from the view. I am also emitting system timestamp before and after the for loop. 

I have also added below HTML code as "Footer Text"

<br/>Time taken to render <span style="font-weight:bold;">#TIMING# seconds</span>

Substitution string #TIMING# gives time taken by APEX engine to render the specific region.

I have created another region "With V Function" as title and type as "PL/SQL Dynamic Content". Only difference in this region source is, instead of using demo_sys_context_v, I have used demo_vfunction_v view.

DECLARE
    l_obj_count   PLS_INTEGER;
BEGIN
    HTP.P('Start: '||SYSTIMESTAMP);
    FOR i IN 1..:P8_RUN_COUNT LOOP
        SELECT
            COUNT(1)
        INTO l_obj_count
        FROM
            demo_vfunction_v;

    END LOOP;
    HTP.P('<br/>End: '||SYSTIMESTAMP);    
END;

Now we are ready to perform the test. 

I have tested rendering time for both the regions using different run counts and here is the outcome.

Region / Run Count	10	100	500	1000
With SYS_CONTEXT	~ 0.01 Sec.	~ 0.01 Sec.	~ 0.02 Sec.	~ 0.03 Sec.
With V Function	~ 0.12 Sec.	~ 1.00 Sec.	~ 5.00 Sec.	~ 10.00 Sec.

Conclusion:

As we can see, region which is using APEX$SESSION Context is consistently taking less time to render, where as region which is using V function is taking more time and rendering time is increasing linearly with number of runs.

Here is the link to demo page.

Thank you.