Measuring
Performance Before Optimization in Oracle APEX
Introduction
When
an Oracle APEX page becomes slow, it is tempting to immediately change the SQL
or add an index. But before doing that, we should first find out what is
actually taking time.
Let's
understand this with a simple example.
Suppose
we have a hotel transaction table:
Our
APEX page contains an Interactive Report showing transaction details.
The
report query is:
The
report works fine when there are 5,000 records. But after the table grows to
several million records, the page becomes slow.
Instead
of immediately changing the query, let's measure the problem first.
1.
First Measure the Existing Performance
Suppose
we check the report and find:
Rows
returned : 2,000,000
SQL
execution time : 4.5 seconds
Page
rendering : 2 seconds
Total
response : 6.5 seconds
Now
we know that the report is actually doing a lot of work.
The
problem is not simply:
"APEX
is slow."
We
have measurable information showing that a large amount of data is being
retrieved.
2.
Check the SQL Execution Plan
Before
changing the SQL, check how Oracle is executing it.
For
example:
The
execution plan might show something like:
TABLE
ACCESS FULL
HOTEL_TRANSACTIONS
Rows:
2,000,000
This
tells us that Oracle is scanning a very large amount of data.
However,
a full table scan is not automatically bad. We need to understand what the
report actually needs.
3.
Ask the Important Question: Do Users Need 2 Million Rows?
Usually,
the answer is no.
A
user looking at a transaction report probably wants to search by:
- Property
- Date
- Status
So
instead of loading everything, we can add filters.
For
example, create APEX items:
P10_PROPERTY_ID
P10_FROM_DATE
P10_TO_DATE
P10_STATUS
Then
change the query:
Now
the database can return only the data the user actually needs.
4.
Compare the Performance Again
Before
optimization:
Rows
returned : 2,000,000
SQL
time : 4.5 seconds
Page
rendering : 2 seconds
Total : 6.5 seconds
After
filtering:
Rows
returned : 8,500
SQL
time : 0.8 seconds
Page
rendering : 0.7 seconds
Total : 1.5 seconds
Now
we have evidence that reducing the amount of data made a significant
difference.
This
is much better than randomly adding indexes and hoping the page becomes faster.
5.
Now Look at Indexes
Once
the query is properly filtered, we can check whether the filtering columns need
indexes.
For
example:
For
date-based searches, we might also consider:
But
don't create indexes blindly.
After
creating an index, check the execution plan again and measure the query.
The
important point is:
An
index should solve an identified access problem, not be added just because the
table is large.
6.
Measure the APEX Region
Even
if the SQL becomes fast, the APEX page can still be slow.
Suppose
the SQL now takes:
SQL
execution = 0.5 seconds
But
the page still takes:
Total
page time = 3 seconds
Now
we need to investigate the APEX side.
Check:
- Region rendering
- Dynamic Actions
- AJAX requests
- PL/SQL processes
- Other regions on the page
7.
Example of a Row-by-Row PL/SQL Problem
Suppose
we have this type of logic:
If
there are millions of rows, this can become expensive.
Instead,
whenever possible, use a set-based SQL operation.
For
example:
One
SQL statement can process the required set of rows instead of handling them one
by one in PL/SQL.
8.
Measure Dynamic Actions
Suppose
the user changes:
P10_PROPERTY_ID
and
you have three Dynamic Actions:
Property
Changed
↓
Refresh
Report
↓
Refresh
Chart
↓
Refresh
Summary
If
all three regions execute expensive SQL, one user action may generate several
database requests.
Instead,
ask:
Does
every region really need to refresh?
If
only the report depends on the property selection, refresh only that region.
This can reduce unnecessary processing.
9.
A Simple Real-World Approach
When
an APEX page is slow, don't immediately rewrite everything.
This
approach helps you solve the actual problem instead of making unnecessary
changes.
Final Takeaway 🚀
Performance optimization in Oracle APEX should be based on evidence, not assumptions.
When a report is slow, don't immediately rewrite the SQL or add indexes. First find out:
🔹 How much data is being loaded?
🔹 How long does the SQL query take?
🔹 How is Oracle accessing the data?
🔹 Which region is taking the most time to render?
🔹 Are Dynamic Actions generating unnecessary requests?
🔹 Is PL/SQL adding extra processing time?
Once you identify the real bottleneck, make one improvement at a time and measure the result.

Comments
Post a Comment