Skip to main content

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.

Comments

Anonymous said…
really helpful
Richard said…
Good Input on performance

Popular posts from this blog

Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values. Note:    There is a bug  30801170  in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost . This bug has been fixed in APEX version 20.2. Client Side Only Conditions If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query. SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases. This can be done declaratively using dynamic actions (DA) DA "Enable/Disable Commission - 1" Create DA and give it a prope

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ

Interactive Grid - Process Filtered Data on Server Side

Recently one of the APEX developers has reached out to me and asked if it's possible to capture filtered rows data of the Interactive Grid on the server-side and do some processing. In APEX 20.1, there is a new API APEX_IG , using which we can achieve this. Photo by Jakub Kapusnak on Unsplash The approach is very simple and straightforward. Get the internal region id based on the Static ID given for the IG region Get the last viewed report id based on region id Open query context for the region and report using region id and report id Fetch and loop through the rows using the query context Do something with fetched rows And finally, close the query context If you have already done this for interactive reports, then you should be already aware of these steps. The only difference here is, we use APEX_IG APIs instead of APEX_IR APIs. For the demo purpose, let's Build an Interactive Grid on EMP table and let's give it a Static ID as emp Create a Textarea page item with