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

Popular posts from this blog

Implementing Authentication and Authorization in Oracle APEX

Mastering Shared Components in Oracle APEX

Designing a Scalable Database Architecture for Oracle APEX Applications