We improved the query by 40% and the report was still slow

Raoul Mangoensentono

— 5 min read
Share
We improved the query by 40% and the report was still slow

A real client performance problem, a personal benchmark setup and numbers that prove why it worked.

TL;DR: ORDS is Oracle's tool for exposing database data as REST services. If your ORDS handler builds its JSON response using a PL/SQL loop with JSON_OBJECT_T, replacing it with a single SQL statement using JSON_OBJECT and JSON_ARRAYAGG can make it 3-4x faster. The reason is context switching between Oracle's two runtime engines. I measured it and the numbers are in this article.

We had already been through the report once. Rewrote the query, restructured the data access, and got a 40% performance improvement. That felt good. But the report was still not where it needed to be, and I kept looking.

I do this kind of work at Qualogy, building Oracle APEX applications and REST services for enterprise clients. Performance problems are not unusual. What was unusual here is that the query was no longer the problem.

The culprit was the ORDS REST handler that fed the report. Specifically, how it was building its JSON response. The handler was using a PL/SQL loop with Oracle's JSON_OBJECT_T and JSON_ARRAY_T types. It is a pattern I have seen in many Oracle codebases and have written myself more than once.

I could not show you the real service or the real data. But I wanted to show you something real, because the gap between the two approaches turned out to be larger than I expected. So I rebuilt the problem from scratch in a personal benchmark setup, with a dataset I could share openly.

The test setup: an anime database

I am a big anime fan. When I needed a realistic dataset to test with, I did not reach for the usual HR employees or sales orders. I built an anime schema and loaded it with real data from MyAnimeList via the Jikan API, 500 titles including Fullmetal Alchemist: Brotherhood, Frieren, and Attack on Titan.

The schema:

ANI_ANIME        -- core table: title, score, episodes, status, synopsis
ANI_STUDIO       -- animation studio (Madhouse, MAPPA, Ufotable, ...)
ANI_SEASON       -- broadcast season (year + WINTER/SPRING/SUMMER/FALL)
ANI_GENRE        -- genre lookup (Action, Drama, Sci-Fi, ...)
ANI_ANIME_GENRE  -- many-to-many bridge

The ORDS service returns a JSON list of anime with nested studio, season, and genre data. That is a realistic structure that mirrors what most REST services actually need to produce: a parent record with nested child objects. The same structure as the client service, just different data.

If you are going to spend time writing benchmark scripts, you might as well enjoy the data you are looking at.

The two approaches

Both produce identical JSON output. The only difference is how that output gets built.

The loop approach: PL/SQL with JSON_OBJECT_T

This is the pattern from the client. For each row, a JSON_OBJECT_T is created, fields are added one by one with .put(), and the object is appended to a JSON_ARRAY_T. A nested cursor fetches the genres for each anime separately.

FOR r IN c_anime LOOP
    l_anime_obj := JSON_OBJECT_T();

    l_anime_obj.put('animeId', r.anime_id);
    l_anime_obj.put('title',   r.title);
    l_anime_obj.put('score',   r.score);

    -- Nested loop for genres
    l_genre_arr := JSON_ARRAY_T();
    FOR g IN c_genres(r.anime_id) LOOP
        l_genre_arr.append(g.genre_name);
    END LOOP;
    l_anime_obj.put('genres', l_genre_arr);

    l_anime_arr.append(l_anime_obj);
END LOOP;

l_root.put('status', 'Ok');
l_root.put('anime',  l_anime_arr);
RETURN l_root.to_clob;

Readable, familiar and slower than it needs to be.

The SQL approach: JSON_OBJECT and JSON_ARRAYAGG

The same result, expressed as a single SQL statement:

SELECT JSON_OBJECT(
           'status' VALUE 'Ok',
           'count'  VALUE COUNT(*),
           'anime'  VALUE JSON_ARRAYAGG(
               JSON_OBJECT(
                   'animeId' VALUE a.anime_id,
                   'title'   VALUE a.title,
                   'score'   VALUE a.score,
                   'studio'  VALUE JSON_OBJECT(
                                 'id'   VALUE s.studio_id,
                                 'name' VALUE s.studio_name
                                 ABSENT ON NULL
                             ),
                   'genres'  VALUE g.genre_list
                   ABSENT ON NULL
               )
               ORDER BY a.score DESC
               RETURNING CLOB
           )
           RETURNING CLOB
       )
INTO l_result
FROM (SELECT * FROM ani_anime WHERE ROWNUM <= p_limit) a
LEFT JOIN ani_studio s  ON s.studio_id = a.studio_id
LEFT JOIN ani_season se ON se.season_id = a.season_id
LEFT JOIN (
    SELECT ag.anime_id,
           LISTAGG(g2.genre_name, ',')
               WITHIN GROUP (ORDER BY g2.genre_name) AS genre_list
    FROM   ani_anime_genre ag
    JOIN   ani_genre g2 ON g2.genre_id = ag.genre_id
    GROUP  BY ag.anime_id
) g ON g.anime_id = a.anime_id;

No loop and no context switches. One round trip to the SQL engine.

Why the loop is slower

Oracle has two separate runtime engines: one for PL/SQL, one for SQL. Every time your PL/SQL code executes a SQL statement, including the cursor fetch inside a loop, control is handed off from the PL/SQL engine to the SQL engine and back again. This is called a context switch.

For a loop processing 500 rows, that is at minimum 500 context switches for the main cursor, plus up to 500 more for the nested genres cursor. The PL/SQL engine is excellent at procedural logic. The SQL engine is a highly optimised set-based processor. When you build JSON row by row in a loop, you are asking the wrong engine to do the heavy lifting.

The SQL approach hands the entire operation, fetching, joining, and JSON construction, to the SQL engine in one pass. No switching, no row-by-row overhead.

The benchmark results

I measured both approaches directly inside Oracle using SYSTIMESTAMP, which removes all network overhead and measures pure database execution time. Five warm runs at each row count, results averaged:

The loop is nearly 4 times slower. At 500 rows you are paying an extra 100 milliseconds on every single request. On a service that feeds a report and gets called frequently, those milliseconds add up quickly.

Both approaches returned exactly the same JSON. The only difference was execution time.

When the loop still makes sense

The loop approach is not always wrong. It is still the right choice when you need complex conditional logic per row that is difficult to express in SQL, when building deeply nested structures that require procedural control or when the result set is small enough that readability matters more than performance. But for the common case, a GET endpoint returning a list of records with nested objects, the SQL approach wins clearly.

What this means for your ORDS handlers

Oracle's SQL engine is built to process sets of rows in a single operation. Every FOR loop you write in PL/SQL to build JSON row by row is working against that strength.

JSON_OBJECT, JSON_ARRAYAGG, and JSON_OBJECT_T all produce valid JSON. But the SQL-based approach lets the database do what it does best, and the benchmark numbers prove why the fix on the client service worked.

The next time you write an ORDS handler that returns a list as JSON, ask yourself: can I express this as a single SQL statement? For most services, the answer is yes.

If you are dealing with ORDS performance questions like this in a real project, I work on these problems at Qualogy.

All scripts from this article, including the schema DDL, Jikan data loader, the ani_api package, and the benchmark procedure, are available on GitHub.

What anime are you currently enjoying?