Mixing and matching

September 2, 2025

3–5 minutes

I’ve written some complex software in my years as an Oracle professional, but I’m happy to concede that nothing comes remotely close to the complexity that must be present to run the Oracle database.

Such an incredible array of features, so many capabilities and 40+ years of backward compatibility is surely an amazing achievement in software engineering.

But if you have “n” features in a product, then (without thinking too closely about the maths) there is probably something like n! (Factorial) ways that each of those features might interact with each other.

Even though Oracle has millions of unit tests for the database, testing every one of those possible feature interactions is impossible, especially for the various environments that customers run their database estate on. This means it is important to take care when you are “cross-pollenating” features, because whilst hopefully hitting actual bugs is rare, you are more likely to hit upon niche conditions that might not be what you were expecting.

Here’s a quick example I stumbled upon recently. Let’s start with

Large strings and CLOBs
Parallel query

This is two features that I’m “mix and matching”. I’ll create a table, populate it with data, and the run a parallel query on it. First the CLOB



SQL> create table sptab(n number not null, v clob not null);

Table created.

SQL> insert into sptab select deptno, ename from emp;

15 rows created.

SQL> select /*+ PARALLEL(2) */ n, dbms_lob.getlength(v) from sptab;

         N DBMS_LOB.GETLENGTH(V)
---------- ---------------------
        20                     5
        30                     5
        30                     4
        20                     5
        30                     6
        30                     5
        10                     5
        20                     5
        10                     4
        30                     6
        20                     5
        30                     5
        20                     4
        10                     6
        10                     6

15 rows selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  0r9nuxz9cmbm8, child number 1
-------------------------------------
select /*+ PARALLEL(2) */ n, dbms_lob.getlength(v) from sptab

Plan hash value: 2065969052

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     2 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    15 | 30225 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    15 | 30225 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| SPTAB    |    15 | 30225 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 2 because of hint

and now the large VARCHAR2



SQL> create table sptab(n number not null, v varchar2(32767) not null);

Table created.

SQL> insert into sptab select deptno, ename from emp;

15 rows created.

SQL> select /*+ PARALLEL(2) */ n, dbms_lob.getlength(v) from sptab;

         N DBMS_LOB.GETLENGTH(V)
---------- ---------------------
        20                     5
        30                     5
        30                     4
        20                     5
        30                     6
        30                     5
        10                     5
        20                     5
        10                     4
        30                     6
        20                     5
        30                     5
        20                     4
        10                     6
        10                     6

15 rows selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  0r9nuxz9cmbm8, child number 1
-------------------------------------
select /*+ PARALLEL(2) */ n, dbms_lob.getlength(v) from sptab

Plan hash value: 2065969052

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     2 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    15 |   240K|     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    15 |   240K|     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| SPTAB    |    15 |   240K|     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(INTERNAL_FUNCTION("V" /*+ LOB_BY_VALUE */ ))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 2 because of hint


27 rows selected.

Everything looks fine. Now let me bring another feature into the mix, so now we have

Large strings and CLOBs
Parallel query
Global temporary tables

Let me repeat the same setup and query. First the large VARCHAR2



SQL> create global temporary table spgtt(n number not null, v varchar2(32767) not null);

Table created.

SQL> insert into spgtt select deptno, ename from emp;

15 rows created.

SQL> select /*+ PARALLEL(2) */ n, dbms_lob.getlength(v) from spgtt;

         N DBMS_LOB.GETLENGTH(V)
---------- ---------------------
        20                     5
        30                     5
        30                     4
        20                     5
        30                     6
        30                     5
        10                     5
        20                     5
        10                     4
        30                     6
        20                     5
        30                     5
        20                     4
        10                     6
        10                     6

15 rows selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
SQL_ID  0r9nuxz9cmbm8, child number 1
-------------------------------------
select /*+ PARALLEL(2) */ n, dbms_lob.getlength(v) from spgtt

Plan hash value: 2065969052

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     2 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    15 |   240K|     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    15 |   240K|     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| SPGTT    |    15 |   240K|     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 2 because of hint


26 rows selected.

and now the CLOB.



SQL> create global temporary table spgtt(n number not null, v clob not null);

Table created.

SQL> insert into spgtt select deptno, ename from emp;

15 rows created.

SQL> select /*+ PARALLEL(2) */ n, dbms_lob.getlength(v) from spgtt;

         N DBMS_LOB.GETLENGTH(V)
---------- ---------------------
        20                     5
        30                     5
        30                     4
        20                     5
        30                     6
        30                     5
        10                     5
        20                     5
        10                     4
        30                     6
        20                     5
        30                     5
        20                     4
        10                     6
        10                     6

15 rows selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  0r9nuxz9cmbm8, child number 1
-------------------------------------
select /*+ PARALLEL(2) */ n, dbms_lob.getlength(v) from spgtt

Plan hash value: 3973291228

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| SPGTT |    15 | 30225 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   0 -  STATEMENT
         U -  PARALLEL(2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Notice that now we have a difference between our ability to run parallel queries. It might be a bug, but is most likely to be an intended implementation restriction. The basic functionality is unchanged, but if you were absolutely depending on parallel query for this particular set of circumstances, then this might be a critical issue for you. This also shows that whilst a VARCHAR2(32767) is conceptually very similar to a CLOB, there are indeed some functional differences in the implementation.

As always, the moment you are mixing and matching Oracle features, it is critical that you test thoroughly to ensure that for your functional use case, the database is meeting your requirements.

2 responses to “Mixing and matching”

iudithd5bf8e4d8d

September 2, 2025 at 11:35 pm

Hi Connor,
I think that, for the last CLOB case, it would be nice if Oracle had supplied an explanation for why it ignored parallelism,
just like in the other cases where we see a Note saying: “Degree of Parallelism is 2 because of hint”.

So, maybe something like “Parallelism ignored due to a CLOB used with a GTT” could do this.

Or, maybe the 10053 trace file does already contain an explanation, for those who decide to check deeper.

Cheers & Best Regards,
Iudith Mentzel

Reply
Jared

September 3, 2025 at 12:01 am

Nice example Connor.
This reminds me of testing memory in a computer.
That is also something that is impossible to fully test.
While there are memory tests that can spot egregious errors, the number of bit patterns that can be created in memory are rather large.
It is simply not possible to test every bit pattern.

Why would that even be desired?

Because a particular bit pattern may cause an error that does not happen any other way.

I have ‘fixed’ reproducible memory issues in computers by simple swapping the chips to different sockets or slots.

As you explained, complex software suffers from the same limitation.

Reply

Learning is not a spectator sport