10 Top Tips to tune your
Oracle APEX Apps Performance
Jorge Rimblas
Twitter: @rimblas
DRW
Jorge Rimblas
APEX Tech Lead at DRW
- 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
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 only2
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_USERcreate 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 <= 10000select * 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_rowsPseudo Hints
APEX$USE_ROWNUM_PAGINATION
APEX$USE_NO_PAGINATION
APEX$USE_ROWNUM_PAGINATIONselect --+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_PAGINATIONselect 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 lastselect 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 lastWatch 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/imgMove 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}/imgGlobal
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
10 Top Tips to tune your
Oracle APEX Performance
Jorge Rimblas
Twitter: @rimblas
DRW
10 Top Tips to tune your Oracle APEX Performance
By Jorge Rimblas
