10 Top Tips to tune your
Oracle APEX Apps Performance

 

Jorge Rimblas

Twitter: @rimblas

DRW

Jorge Rimblas

APEX Tech Lead at DRW

@rimblas

rimblas.com/blog

  • Oracle DB since 1995
  • APEX since it was HTMLDB in 2004
  • Always involved in web technologies
  • ex: Independent, Sumner, Sumneva, Enkitec,  Accenture, Insum, DRW

Michelle Skamene

insum.ca

Objective

Bottlenecks

1

Monitor Activity

The universal experience of programmers who have been using measurement tools is that their intuitive guesses fail.

 

– Donald Knuth

Monitor Activity

select
    a.application_id app_id
  , a.application_name app
  , a.apex_user
  , a.apex_session_id
  , a.page_id
  , a.page_name
  , a.seconds_ago
  , (current_timestamp-numtodsinterval(a.seconds_ago,'SECOND')) as activity_time
  , to_char(round((a.seconds_ago)/3600,2),'9,999.99') as hours_ago
from apex_workspace_activity_log a
where a.application_id = :1
order by a.seconds_ago
fetch first 50 rows only

2

Debug Mode

Mining APEX Debug Data for Hidden Performance Trends

Doug Gault

Wednesday 11:45am

Texas 2

Client Side

  •  JavaScript messages
  • AJAX calls
  • Visible in the Browser Console

 

Server Side

  • Native APEX processing
  • Different Levels of Information

Debug Levels

1

4

9

Default

YES

LEVEL9

6

(Full Trace)

(Info)

Debug Levels

YES

LEVEL9

apex.oracle.com/pls/apex/f?p=30931:1:127819468132360:::::

apex.oracle.com/pls/apex/f?p=30931:1:127819468132360::YES:::

apex.oracle.com/pls/apex/f?p=30931:1:127819468132360::LEVEL9:::

apex.oracle.com/pls/apex/r/jrimblas/ocdreorder/1?session=1278194681

apex.oracle.com/pls/apex/r/jrimblas/ocdreorder/1?debug=YES&session=1278194681

Debug Levels

 rendering

or

submitting?

How do I know if I'm

Debug: Path Info

3

#TIMING#

Execution & Processing of a Classic Report

Add to the "Footer Text" of a region

4

Advisor

Perform various checks on this application, including programming errors and best practices.

5

Do you need v("")?

The V/NV/DV functions allow you to get the value of an APEX item

v('APP_USER')
v('P2_ID')
v('P15_NAME')
select *
  from shopping_carts
 where buyer = :APP_USER
create or replace view my_cart
as
select *
  from shopping_carts
 where buyer = :APP_USER
/
create or replace view my_cart
as
select *
  from shopping_carts
 where buyer = :APP_USER
/
create or replace view my_cart
as
select *
  from shopping_carts
 where buyer = v('APP_USER')
/
create or replace view my_cart
as
select *
  from shopping_carts
 where buyer = (select v('APP_USER') from dual)
/

Scalar Subquery

6

Substitution Strings

Substitution Strings

in SQL

&ITEM.

Invoice &P2_INVOICE_NO. from &P2_VENDOR_NAME. is ready for processing.

Example, on a Region or

"HTML Expression" field

Substitution Notation

APEX_UTIL.PREPARE_URL

Used for creating page links

select case when locked_flag = 'Y' then ''
       else
         apex_util.prepare_url(...)
       end edit_url
     , id
     , name
  from my_table

APEX_UTIL.PREPARE_URL

apex_util.prepare_url('f?p=&APP_ID.:PAGE:&SESSION.::&DEBUG.:')
         
apex_util.prepare_url('f?p=' || :APP_ID || ':PAGE:' || :APP_SESSION || '::' || :DEBUG || ':')

select apex_util.prepare_url('f?p=&APP_ID.:PAGE:&SESSION.::&DEBUG.:') from my_table;
         
select apex_util.prepare_url('f?p=2000:2:23432.::NO:') from my_table;
select apex_util.prepare_url('f?p=2000:2:85773.::NO:') from my_table;
select apex_util.prepare_url('f?p=2000:2:29464.::NO:') from my_table;
select apex_util.prepare_url('f?p=2000:2:65400.::NO:') from my_table;
select apex_util.prepare_url('f?p=2000:2:14276.::NO:') from my_table;

...

Each SQL becomes a unique new SQL!

select prepare_url('f?p=2000:2:23432.::NO:P2_ID:1') from my_table;
select prepare_url('f?p=2000:2:23432.::NO:P2_ID:2') from my_table;
select prepare_url('f?p=2000:2:23432.::NO:P2_ID:3') from my_table;
select prepare_url('f?p=2000:2:85773.::NO:P2_ID:1') from my_table;
select prepare_url('f?p=2000:2:85773.::NO:P2_ID:2') from my_table;
select prepare_url('f?p=2000:2:85773.::NO:P2_ID:3') from my_table;
select prepare_url('f?p=2000:2:29464.::NO:P2_ID:1') from my_table;
select prepare_url('f?p=2000:2:29464.::NO:P2_ID:2') from my_table;
select prepare_url('f?p=2000:2:29464.::NO:P2_ID:3') from my_table;
select prepare_url('f?p=2000:2:65400.::NO:P2_ID:1') from my_table;
select prepare_url('f?p=2000:2:65400.::NO:P2_ID:2') from my_table;
select prepare_url('f?p=2000:2:65400.::NO:P2_ID:3') from my_table;

...

Each SQL becomes a unique new SQL!

select apex_util.prepare_url(
     'f?p='
  || :APP_ID || ':'
  || '2'     || ':'
  || :APP_SESSION
 from my_table;
APP_ID
2000
APP_SESSION
23432
85773
29464
65400
14276

With Bind Variables we re-use the SQL

One single hard parse

select apex_page.get_url(
       p_page => '2'
     , p_items => 'P2_ID'
     , p_values => t.id
 from my_table t;

Use the APEX_PAGE.GET_URL call

FUNCTION GET_URL (
    p_application        IN VARCHAR2 DEFAULT NULL,
    p_page               IN VARCHAR2 DEFAULT NULL,
    p_session            IN NUMBER   DEFAULT APEX.G_INSTANCE,
    p_request            IN VARCHAR2 DEFAULT NULL,
    p_debug              IN VARCHAR2 DEFAULT NULL,
    p_clear_cache        IN VARCHAR2 DEFAULT NULL,
    p_items              IN VARCHAR2 DEFAULT NULL,
    p_values             IN VARCHAR2 DEFAULT NULL,
    p_printer_friendly   IN VARCHAR2 DEFAULT NULL,
    p_trace              IN VARCHAR2 DEFAULT NULL,       
    p_triggering_element IN VARCHAR2 DEFAULT 'this',
    p_plain_url          IN BOOLEAN DEFAULT FALSE )
    RETURN VARCHAR2;

7

Declarative Options
For Conditions

Most APEX elements allow for a Server-side Condition

Use the built-in options first then the "Expression" or "Function Body"

8

Report Pagination

IG "Infinite Scroll"

Report Pagination

Report SQL Wrapping

APEX uses the ROW_NUMBER analytic function

select level rnum, 'Text ' || level as txt
    from dual
 connect by level <= 10000
select * from(select a.*,row_number() over (order by null) apx$rownum from(
 select i.*
   from (select "RNUM","TXT"
   from ((select /*+ qb_name(apex$inner) */d."RNUM",d."TXT" from (
     select level rnum, 'Text ' || level as txt
       from dual
    connect by level <= 10000
) d
 )) i 
) i where 1=1 
order by "RNUM" asc nulls last
)a
)where apx$rownum<=:p$_max_rows

Pseudo Hints

APEX$USE_ROWNUM_PAGINATION
APEX$USE_NO_PAGINATION
APEX$USE_ROWNUM_PAGINATION
select --+APEX$USE_ROWNUM_PAGINATION
* from (select a.* from(select i.*
 from (select "RNUM","TXT"
from ((select /*+ qb_name(apex$inner) */d."RNUM",d."TXT" from (
  select level rnum, 'Text ' || level as txt
    from dual
  connect by level <= 10000
) d
 )) i 
) i where 1=1 
order by "RNUM" asc nulls last
)a
where ROWNUM<=:p$_max_rows
)
APEX$USE_NO_PAGINATION
select i.*
 from (select "RNUM","TXT"
from ((select /*+ qb_name(apex$inner) */d."RNUM",d."TXT" from (
  select level rnum, 'Text ' || level as txt
    from dual
  connect by level <= 10000
) d
 )) i 
) i where 1=1 
order by "RNUM" asc nulls last
select i.*
 from (select "RNUM","TXT"
from ((select /*+ qb_name(apex$inner) */d."RNUM",d."TXT" from (
  select level rnum, 'Text ' || level as txt
    from dual
  connect by level <= 10000
  order by 1
) d
 )) i 
) i where 1=1 
order by "RNUM" asc nulls last

Watch for "ORDER BY" with IR

9

Many Report Tabs?

Slow on Rarely used Reports

Default Report

Slow Reports

Defer Load on Tab Focus

plugin

Demo

10

Static Files

Move your

JavaScript and CSS to files

File order matters

File order matters

CSS is non-blocking

JavaScript is blocking

This is a standard APEX feature

Extra credit

Move Static Files

to Webserver

Move Static Files

to Webserver

/c
/c/js
/c/lib
/c/css
/c/img

Move Static Files

to Webserver

/c
/c/js
/c/lib
/c/css
/c/img
/c/{app}
/c/{app}/js
/c/{app}/lib
/c/{app}/css
/c/{app}/img

Global

App Specific

11

Bonus!

Move PL/SQL to Packages

Compiled Minimal Code

11b

Move Plugin PL/SQL to Packages

Plugin PL/SQL Code can be extensive

Remove code from the

"PL/SQL Code" and move it into

a package

What about plugin upgrades?

  • The upgrade plugin will have the code or
  • May even have the code in a package
  • Just move the code to the package again

12

Lazy Loading

hint: lazy is good

Q&A

References

laureston.ca/2019/12/05/15-top-tips-to-tune-your-oracle-apex-performance/

rimblas.com/blog/2016/09/video-006-substitution-strings-bind-variables-and-apex-links/

rimblas.com/blog/2020/05/plugin-defer-report-load-on-tab-focus/

rimblas.com/blog/2018/02/how-to-hide-show-a-regions-timing-during-development/

blogs.oracle.com/apex/post/application-express-18-and-report-pagination

danischnider.wordpress.com/2022/05/15/performance-tips-pl-sql-functions-in-sql-queries/

10 Top Tips to tune your
Oracle APEX Performance

 

Jorge Rimblas

Twitter: @rimblas

DRW