Global indexes and query performance

A question came to me recently about whether a global index on a partitioned table can provide a performance benefit over a similar index on a non-partitioned table. As with many query related things, the answer is “it depends” but the simple answer is “yes it can do if your query contains the table partitioning key”. Let’s look at what I mean using a simple example…

We’ll use two tables this demo, run under Oracle 21 XE:

• NONPART_TABLE – the non-partitioned table
• PART_TABLE – the partitioned table

Each table will have the following columns:

• ID – unique value designed to represent the primary key, which we won’t actually use for this demo
• CAT_1 – column with 7 distinct values. This will be the list partitioning key for table PART_TABLE
• CAT_2 – column with 1,000 distinct values. This will be the indexed column.

A simple b-tree index will be created on the CAT_2 column. The index on the partitioned table, PART_TABLE, will be global.

Each table will be populated with 1,000,000 rows:

• ID – a unique integer
• CAT_1 – values VAL_0 through VAL_6, distributed evenly
• CAT_2 – values VAL_0 through VAL_999, distributed evenly

The values in columns CAT_1 and CAT_2 are set such that there is an even distribution between the combinations of (CAT_1, CAT_2).

The script for the above is:

create table nonpart_table
  (id     number not null
  ,cat_1  varchar2(10) not null
  ,cat_2  varchar2(10) not null)
/


create table part_table
  (id     number not null
  ,cat_1  varchar2(10) not null
  ,cat_2  varchar2(10) not null)
partition by list (cat_1) automatic
  (partition p1 values ('VAL_0'))
/

insert into nonpart_table
  (id, cat_1, cat_2)
with rowgen as (select rownum as rn from dual connect by level <= 1000)  
select rownum
,      'VAL_' || trim(mod(rownum,7)) as cat_1
,      'VAL_' || trim(mod(rownum,1000)) as cat_2
from   rowgen r1
,      rowgen r2;

insert into part_table
select *
from   nonpart_table;

commit;

create index nonpart_table_idx1
  on nonpart_table (cat_2);

create index part_table_idx1
  on part_table (cat_2);

exec dbms_stats.gather_table_stats ('','nonpart_table');
exec dbms_stats.gather_table_stats ('','part_table');

For our test, we’ll look at the following simple query, hinted to ensure we’re access the table via the index:

select /*+ index (nonpart_table nonpart_table_idx1) */ *
from   nonpart_table
where  cat_1 = 'VAL_5'
and    cat_2 = 'VAL_42';

Running this query with AUTOTRACE enabled, we get the following:

143 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3717371333

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |   143 |  2717 |  1006   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| NONPART_TABLE      |   143 |  2717 |  1006   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | NONPART_TABLE_IDX1 |  1000 |       |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   1 - filter("CAT_1"='VAL_5')
   2 - access("CAT_2"='VAL_42')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1016  consistent gets
          0  physical reads
          0  redo size
       4004  bytes sent via SQL*Net to client
        151  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        143  rows processed

So, against our non-partitioned table, we retrieved the 143 rows with 1,016 consistent gets. Now we repeat the test using the partitioned table and obtain the AUTOTRACE output of that:

143 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2417870909

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                 |   143 |  2717 |   897   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PART_TABLE      |   143 |  2717 |   897   (0)| 00:00:01 |     4 |     4 |
|*  2 |   INDEX RANGE SCAN                         | PART_TABLE_IDX1 |   143 |       |     6   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("CAT_1"='VAL_5')
   2 - access("CAT_2"='VAL_42')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        144  consistent gets
          0  physical reads
          0  redo size
       4004  bytes sent via SQL*Net to client
        151  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        143  rows processed

This time we can see the 143 rows were retrieved with just 144 consistent gets, which is about one seventh of the 1,016 consistent gets for the non-partitioned table query. Clearly the query against the partitioned table is better from a performance perspective.

If we look at the execution plan for the partitioned table query we can why it performs better. Line 1 of the plan, operation TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED, has PSTART and PSTOP set to 4, i.e. only 1 partition is being accessed. What Oracle is doing is an index range scan of the global index, satisfying the CAT_2 = ‘VAL_42’ criteria of the query. This gets the table ROWIDs of the rows that satisfies that criteria but it doesn’t need to read the rows to evaluate the CAT_1 = ‘VAL_5’ criteria for the partitioned table. Since the index is global, the table ROWIDs specify the partition of the row and this is sufficient for Oracle to determine if the referenced row will be CAT_1 = ‘VAL_5’ or not.

If we contrast that with the query against the non-partitioned table, we see that Oracle had to visit the table row to evaluate the CAT_1 = ‘VAL_5’ query criteria and in doing so incurred an extra logical IO for every row where CAT_2 = ‘VAL_42’. This is why the consistent gets is seven times what the query against the partitioned table incurred.

You might be wondering that if our query contains predicates on both column CAT_1 and CAT_2 why not make the index local? Well, in most systems there are many different queries and we might have some that do not specify CAT_1 partitioning column. Queries that contain filter predicates on just CAT_2 will benefit better from a global index, rather than a local index.

In summary, despite our index being global, we can get a performance benefit from having our table partitioned for those queries that filter on the partitioning key of the table in addition to filters that would use the index key.

Mutating table error (ORA-04091) but no trigger?!?

I encountered an interesting scenario recently… a “mutating table” error but the reported table had no trigger on it.

The Oracle documentation has this to say about ORA-04091:

ORA-04091: table string.string is mutating, trigger/function may not see it
Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

Basically, if we have a row level trigger on a table then our trigger cannot read or write the same table that the trigger belongs to. The following illustrates this:

SQL> create table src_tab
  2    (id     number(10) not null
  3    ,val    char(10) not null
  4    ,constraint src_tab_pk primary key (id));

Table created.

SQL> insert /*+ append */ into src_tab
  2    (id, val)
  3  with row_gen as (select rownum as rn from dual connect by level <= 1000)
  4  select rownum
  5  ,      'x'
  6  from   row_gen r1
  7  ,      row_gen r2;

1000000 rows created.

SQL> commit;

SQL> create or replace trigger src_tab_aiu
  2  after insert or update
  3  on src_tab
  4  for each row
  5  declare
  6    l_temp number;
  7  begin
  8  
  9    select count(*)
 10    into   l_temp
 11    from   src_tab;
 12  
 13  end src_tab_aiu;
 14  /

Trigger created.

SQL> update src_tab
  2  set val = 'y'
  3  where id = 1234;
update src_tab
       *
ERROR at line 1:
ORA-04091: table DEVELOPER.SRC_TAB is mutating, trigger/function may not see it 
ORA-06512: at "DEVELOPER.SRC_TAB_AIU", line 5 
ORA-04088: error during execution of trigger 'DEVELOPER.SRC_TAB_AIU'

In the above we created a table, populated it with a million rows, added a row level trigger that counts the number of rows in the table and then attempted to update a row. The update caused the trigger to fire, which then failed with our ORA-04091 mutating table error.

The scenario I encountered recently reported a valid trigger in the system but the table noted from the error was not the table upon which the trigger was defined. The trigger in question did insert into the reported table and had been operating successfully for a number of years so the usual cause of the ORA-04091 exception was puzzling…

… until one of the DBAs reported that they had been doing some online table maintenance. The cause of the ORA-04091 scenario was then easy to replicate.

We’ll start with a source table and define a trigger that inserts the data changes into a log table, mimicking what might be an audit or change capture process. We’ll then run a series of updates against the source table and at the same time perform an online move of the log table (this was run on a 21c Express Edition database):

SQL> create table src_tab
  2    (id     number(10) not null
  3    ,val    char(10) not null
  4    ,constraint src_tab_pk primary key (id));

Table created.

SQL> insert /*+ append */ into src_tab
  2    (id, val)
  3  with row_gen as (select rownum as rn from dual connect by level <= 1000)
  4  select rownum
  5  ,      'x'
  6  from   row_gen r1
  7  ,      row_gen r2;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create table src_tab_log
  2    (id     number(10) not null
  3    ,val    char(10) not null
  4    ,update_timestamp timestamp not null);

Table created.

SQL> insert /*+ append */ into src_tab_log
  2    (id, val, update_timestamp)
  3  with row_gen as (select rownum as rn from dual connect by level <= 1000)
  4  select rownum
  5  ,      'x'
  6  ,      systimestamp
  7  from   row_gen r1
  8  ,      row_gen r2;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create or replace trigger src_tab_aiu
  2  after insert or update
  3  on src_tab
  4  for each row
  5  declare
  6  begin
  7  
  8    insert into src_tab_log
  9      (id
 10      ,val
 11      ,update_timestamp)
 12    values
 13      (:NEW.id
 14      ,:NEW.val
 15      ,systimestamp);
 16  
 17    dbms_session.sleep (0.01); -- just to slow down the processing
 18  
 19  end src_tab_aiu;
 20  /

Trigger created.

SQL> update src_tab -- to show that the trigger does work
  2  set val = 'y'
  3  where id = 1234;

1 row updated.

SQL> commit;

Commit complete.

SQL> begin -- run lots of updates and in another session run an online move of src_tab_log
  2    for i in 1..1000
  3    loop
  4      update src_tab
  5      set val = 'y'
  6      where id = i*100;
  7      commit;
  8    end loop;
  9  end;
 10  /
begin
*
ERROR at line 1:
ORA-04091: table DEVELOPER.SRC_TAB_LOG is mutating, trigger/function may not 
see it 
ORA-06512: at "DEVELOPER.SRC_TAB_AIU", line 4 
ORA-04088: error during execution of trigger 'DEVELOPER.SRC_TAB_AIU' 
ORA-06512: at line 4 

Whilst the last block updating the table rows was running, the following was kicked off in another session:

SQL> alter table src_tab_log move online;

which caused the reported ORA-04091.

Online partitioning of a table has the same impact. It seems versions 19c and 21c are affected.

So, our online table maintenance operation can interfere with the usual operation of the database when the table is subject to DML invoked from a trigger. I’m kinda inclined to call “bug” on this but I guess Oracle might like to describe it as an “unavoidable feature”. Regardless, it’s something to be aware of and to avoid online table reorganisations on tables subject to trigger sourced DML.

ROLLUP Function

“The database seems a bit slow today…” was the comment I received upon my arrival at work recently. It wasn’t too hard to see why either with a rather large, convoluted reporting style query chugging along, much to the annoyance of the other activity in the database. Peering into the depths of the query I noticed at its core it comprised of a UNION of 3 queries. What struck me as strange was that each of the three components were nearly identical to each other:

SELECT col_1, col_2, col3, col_4
FROM <tables>
WHERE <selection criteria>
GROUP BY
      col_1, col_2, col3, col_4
UNION ALL
SELECT col_1, col_2, col3, 'TOTAL'
FROM <tables>
WHERE <selection criteria>
GROUP BY
      col_1, col_2, col3, 'TOTAL'
UNION ALL
SELECT col_1, col_2, 'TOTAL', 'TOTAL'
FROM <tables>
WHERE <selection criteria>
GROUP BY
      col_1, col_2, 'TOTAL', 'TOTAL'

The execution details of the query showed that Oracle spent 10 minutes executing each component. The tables and the WHERE clause were identical in each of the query components. This meant that the second component is nothing more than an aggregation of the first and the third component an aggregation of the second. Surely there’s a better way of obtaining the aggregation entries without rerunning the entire query again? And there is…

Lurking in the depths of the Database Data Warehousing Guide, and not the SQL Language Reference, is a chapter titled SQL for Aggregation in Data Warehouses. The particular function we want for our query is ROLLUP. The documentation has this to say about ROLLUP:

ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

By way of simple example we create a table with 4 columns: 3 of them dimension type attributes and one numeric metric, along with some test data:

CREATE TABLE rollup_test
	(col_a 		VARCHAR2(1)	NOT NULL
	,col_b		VARCHAR2(1)	NOT NULL
	,col_c		VARCHAR2(1)	NOT NULL
	,val		NUMBER(3)	NOT NULL);

INSERT INTO rollup_test
VALUES ('A','A','A',1);
INSERT INTO rollup_test
VALUES ('A','A','B',1);
INSERT INTO rollup_test
VALUES ('A','A','C',1);

INSERT INTO rollup_test
VALUES ('A','B','A',1);
INSERT INTO rollup_test
VALUES ('A','B','B',1);
INSERT INTO rollup_test
VALUES ('A','B','C',1);

INSERT INTO rollup_test
VALUES ('A','C','A',1);
INSERT INTO rollup_test
VALUES ('A','C','B',1);
INSERT INTO rollup_test
VALUES ('A','C','C',1);

INSERT INTO rollup_test
VALUES ('B','A','A',1);
INSERT INTO rollup_test
VALUES ('B','A','B',1);
INSERT INTO rollup_test
VALUES ('B','A','C',1);

INSERT INTO rollup_test
VALUES ('B','B','A',1);
INSERT INTO rollup_test
VALUES ('B','B','B',1);
INSERT INTO rollup_test
VALUES ('B','B','C',1);

INSERT INTO rollup_test
VALUES ('B','C','A',1);
INSERT INTO rollup_test
VALUES ('B','C','B',1);
INSERT INTO rollup_test
VALUES ('B','C','C',1);

COMMIT;

and we can see the impact that ROLLUP has using the following query:

SQL> SELECT col_a
  2  ,      NVL(col_b,'TOTAL') AS b
  3  ,      NVL(col_c,'TOTAL') AS c
  4  ,      SUM(val)           AS sum_val
  5  FROM   rollup_test
  6  GROUP  BY
  7         col_a
  8  ,      ROLLUP(col_b, col_c)
  9  ORDER  BY
 10         col_a
 11  ,      NVL(col_b,'TOTAL')
 12  ,      NVL(col_c,'TOTAL');

COL_A  B      C       SUM_VAL
------ ------ ------ --------
A      A      A             1
A      A      B             1
A      A      C             1
A      A      TOTAL         3
A      B      A             1
A      B      B             1
A      B      C             1
A      B      TOTAL         3
A      C      A             1
A      C      B             1
A      C      C             1
A      C      TOTAL         3
A      TOTAL  TOTAL         9
B      A      A             1
B      A      B             1
B      A      C             1
B      A      TOTAL         3
B      B      A             1
B      B      B             1
B      B      C             1
B      B      TOTAL         3
B      C      A             1
B      C      B             1
B      C      C             1
B      C      TOTAL         3
B      TOTAL  TOTAL         9

26 rows selected.

Applying it to our reporting query was as simple as replacing the UNION ALL with:

SELECT col_1, col_2, NVL(col3,'TOTAL'), NVL(col_4,'TOTAL')
FROM <tables>
WHERE <selection criteria>
GROUP BY
      col_1, col_2, ROLLUP(col3, col_4)

No more UNION ALL, no more execution of (nearly) the same query three times and an immediate saving of 20 minutes of wasted execution time… and all before my morning coffee.

Database Views: the good, the bad and the ugly

Views are good as they can be used to hide complexity.

Views are bad as they can hide complexity.

It’s when views are used for the first point above without consideration of the second point that things can turn ugly. Here’s recently encountered example…

A developer decided to modify a process so that it would determine when it was being run from within the internal database job scheduler. The following simple query was added to the code:

SELECT job_name
INTO   l_result
FROM   user_scheduler_running_jobs
where  session_id = l_sid;

What the developer overlooked was that user_scheduler_running_jobs is a view and even though it’s an Oracle data dictionary view the underlying table structure might not be as simple as the query would suggest. The following is the execution plan that Oracle selected:

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |       |       |     6 (100)|          |        |      |            |
|   1 |  VIEW                                | USER_SCHEDULER_RUNNING_JOBS |     3 |   198 |     6  (34)| 00:00:01 |        |      |            |
|   2 |   UNION-ALL                          |                             |       |       |            |          |        |      |            |
|   3 |    MERGE JOIN CARTESIAN              |                             |     1 |   190 |     5  (20)| 00:00:01 |        |      |            |
|*  4 |     HASH JOIN OUTER                  |                             |     1 |   190 |     5  (20)| 00:00:01 |        |      |            |
|   5 |      NESTED LOOPS                    |                             |     1 |   171 |     4   (0)| 00:00:01 |        |      |            |
|   6 |       NESTED LOOPS                   |                             |     1 |   171 |     4   (0)| 00:00:01 |        |      |            |
|*  7 |        HASH JOIN OUTER               |                             |     1 |   138 |     1   (0)| 00:00:01 |        |      |            |
|   8 |         NESTED LOOPS                 |                             |     1 |    99 |     1   (0)| 00:00:01 |        |      |            |
|   9 |          NESTED LOOPS                |                             |     1 |    99 |     1   (0)| 00:00:01 |        |      |            |
|  10 |           MERGE JOIN CARTESIAN       |                             |     1 |    73 |     0   (0)|          |        |      |            |
|* 11 |            FIXED TABLE FULL          | X$KCCDI                     |     1 |    15 |     0   (0)|          |        |      |            |
|  12 |            BUFFER SORT               |                             |     1 |    58 |     0   (0)|          |        |      |            |
|* 13 |             PX COORDINATOR           |                             |       |       |            |          |        |      |            |
|  14 |              PX SEND QC (RANDOM)     | :TQ10000                    |     1 |    26 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|* 15 |               VIEW                   | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  Q1,00 | PCWP |            |
|* 16 |                FIXED TABLE FULL      | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 17 |           INDEX UNIQUE SCAN          | SCHEDULER$_JOB_PK           |     1 |       |     0   (0)|          |        |      |            |
|* 18 |          TABLE ACCESS BY INDEX ROWID | SCHEDULER$_JOB              |     1 |    26 |     1   (0)| 00:00:01 |        |      |            |
|* 19 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  20 |          PX SEND QC (RANDOM)         | :TQ20000                    |     1 |    28 |     0   (0)|          |  Q2,00 | P->S | QC (RAND)  |
|* 21 |           VIEW                       | GV$SESSION                  |       |       |            |          |  Q2,00 | PCWP |            |
|  22 |            MERGE JOIN CARTESIAN      |                             |     1 |    28 |     0   (0)|          |  Q2,00 | PCWP |            |
|  23 |             NESTED LOOPS             |                             |     1 |    12 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 24 |              FIXED TABLE FIXED INDEX | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 25 |              FIXED TABLE FIXED INDEX | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  Q2,00 | PCWP |            |
|  26 |             BUFFER SORT              |                             |     1 |    16 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 27 |              FIXED TABLE FIXED INDEX | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 28 |        INDEX RANGE SCAN              | I_OBJ1                      |     1 |       |     2   (0)| 00:00:01 |        |      |            |
|  29 |       TABLE ACCESS BY INDEX ROWID    | OBJ$                        |     1 |    33 |     3   (0)| 00:00:01 |        |      |            |
|  30 |      PX COORDINATOR                  |                             |       |       |            |          |        |      |            |
|  31 |       PX SEND QC (RANDOM)            | :TQ30000                    |   173 |  2595 |     1 (100)| 00:00:01 |  Q3,00 | P->S | QC (RAND)  |
|  32 |        VIEW                          | GV$PROCESS                  |       |       |            |          |  Q3,00 | PCWP |            |
|* 33 |         FIXED TABLE FULL             | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  Q3,00 | PCWP |            |
|  34 |     BUFFER SORT                      |                             |     1 |       |     5  (20)| 00:00:01 |        |      |            |
|  35 |      FIXED TABLE FULL                | X$KCCDI2                    |     1 |       |     0   (0)|          |        |      |            |
|* 36 |    HASH JOIN OUTER                   |                             |     1 |   286 |     1 (100)| 00:00:01 |        |      |            |
|* 37 |     HASH JOIN OUTER                  |                             |     1 |   267 |     0   (0)|          |        |      |            |
|* 38 |      FILTER                          |                             |       |       |            |          |        |      |            |
|* 39 |       HASH JOIN OUTER                |                             |     1 |   228 |     0   (0)|          |        |      |            |
|  40 |        NESTED LOOPS                  |                             |     1 |   189 |     0   (0)|          |        |      |            |
|  41 |         NESTED LOOPS                 |                             |     1 |   189 |     0   (0)|          |        |      |            |
|  42 |          NESTED LOOPS                |                             |     1 |    97 |     0   (0)|          |        |      |            |
|* 43 |           PX COORDINATOR             |                             |       |       |            |          |        |      |            |
|  44 |            PX SEND QC (RANDOM)       | :TQ40000                    |     1 |    26 |     0   (0)|          |  Q4,00 | P->S | QC (RAND)  |
|* 45 |             VIEW                     | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  Q4,00 | PCWP |            |
|* 46 |              FIXED TABLE FULL        | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  Q4,00 | PCWP |            |
|  47 |           TABLE ACCESS BY INDEX ROWID| SCHEDULER$_LIGHTWEIGHT_JOB  |     1 |    39 |     0   (0)|          |        |      |            |
|* 48 |            INDEX UNIQUE SCAN         | SCHEDULER$_LWJOB_PK         |     1 |       |     0   (0)|          |        |      |            |
|* 49 |          INDEX RANGE SCAN            | SCHEDULER$_LOBJ_UK          |     1 |       |     0   (0)|          |        |      |            |
|* 50 |         TABLE ACCESS BY INDEX ROWID  | SCHEDULER$_LWJOB_OBJ        |     1 |    92 |     0   (0)|          |        |      |            |
|* 51 |        PX COORDINATOR                |                             |       |       |            |          |        |      |            |
|  52 |         PX SEND QC (RANDOM)          | :TQ50000                    |     1 |    39 |            |          |  Q5,00 | P->S | QC (RAND)  |
|* 53 |          VIEW                        | GV$SCHEDULER_INMEM_RTINFO   |       |       |            |          |  Q5,00 | PCWP |            |
|* 54 |           FIXED TABLE FULL           | X$JSKMIMRT                  |     1 |    39 |            |          |  Q5,00 | PCWP |            |
|* 55 |      PX COORDINATOR                  |                             |       |       |            |          |        |      |            |
|  56 |       PX SEND QC (RANDOM)            | :TQ60000                    |     1 |    28 |     0   (0)|          |  Q6,00 | P->S | QC (RAND)  |
|* 57 |        VIEW                          | GV$SESSION                  |       |       |            |          |  Q6,00 | PCWP |            |
|  58 |         MERGE JOIN CARTESIAN         |                             |     1 |    28 |     0   (0)|          |  Q6,00 | PCWP |            |
|  59 |          NESTED LOOPS                |                             |     1 |    12 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 60 |           FIXED TABLE FIXED INDEX    | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 61 |           FIXED TABLE FIXED INDEX    | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  Q6,00 | PCWP |            |
|  62 |          BUFFER SORT                 |                             |     1 |    16 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 63 |           FIXED TABLE FIXED INDEX    | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  Q6,00 | PCWP |            |
|  64 |     PX COORDINATOR                   |                             |       |       |            |          |        |      |            |
|  65 |      PX SEND QC (RANDOM)             | :TQ70000                    |   173 |  2595 |     1 (100)| 00:00:01 |  Q7,00 | P->S | QC (RAND)  |
|  66 |       VIEW                           | GV$PROCESS                  |       |       |            |          |  Q7,00 | PCWP |            |
|* 67 |        FIXED TABLE FULL              | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  Q7,00 | PCWP |            |
|* 68 |    HASH JOIN OUTER                   |                             |     1 |   202 |     1 (100)| 00:00:01 |        |      |            |
|  69 |     MERGE JOIN CARTESIAN             |                             |     1 |   183 |     0   (0)|          |        |      |            |
|* 70 |      HASH JOIN OUTER                 |                             |     1 |   179 |     0   (0)|          |        |      |            |
|* 71 |       HASH JOIN                      |                             |     1 |   140 |     0   (0)|          |        |      |            |
|* 72 |        HASH JOIN                     |                             |     1 |    82 |     0   (0)|          |        |      |            |
|* 73 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  74 |          PX SEND QC (RANDOM)         | :TQ80000                    |     1 |    69 |            |          |  Q8,00 | P->S | QC (RAND)  |
|* 75 |           VIEW                       | GV$SCHEDULER_INMEM_RTINFO   |       |       |            |          |  Q8,00 | PCWP |            |
|* 76 |            FIXED TABLE FULL          | X$JSKMIMRT                  |     1 |    69 |            |          |  Q8,00 | PCWP |            |
|  77 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  78 |          PX SEND QC (RANDOM)         | :TQ90000                    |     1 |    13 |            |          |  Q9,00 | P->S | QC (RAND)  |
|  79 |           VIEW                       | GV$SCHEDULER_INMEM_MDINFO   |       |       |            |          |  Q9,00 | PCWP |            |
|  80 |            FIXED TABLE FULL          | X$JSKMIMMD                  |     1 |    13 |            |          |  Q9,00 | PCWP |            |
|* 81 |        PX COORDINATOR                |                             |       |       |            |          |        |      |            |
|  82 |         PX SEND QC (RANDOM)          | :TQ100000                   |     1 |    26 |     0   (0)|          |  10,00 | P->S | QC (RAND)  |
|* 83 |          VIEW                        | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  10,00 | PCWP |            |
|* 84 |           FIXED TABLE FULL           | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  10,00 | PCWP |            |
|* 85 |       PX COORDINATOR                 |                             |       |       |            |          |        |      |            |
|  86 |        PX SEND QC (RANDOM)           | :TQ110000                   |     1 |    28 |     0   (0)|          |  11,00 | P->S | QC (RAND)  |
|* 87 |         VIEW                         | GV$SESSION                  |       |       |            |          |  11,00 | PCWP |            |
|  88 |          MERGE JOIN CARTESIAN        |                             |     1 |    28 |     0   (0)|          |  11,00 | PCWP |            |
|  89 |           NESTED LOOPS               |                             |     1 |    12 |     0   (0)|          |  11,00 | PCWP |            |
|* 90 |            FIXED TABLE FIXED INDEX   | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  11,00 | PCWP |            |
|* 91 |            FIXED TABLE FIXED INDEX   | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  11,00 | PCWP |            |
|  92 |           BUFFER SORT                |                             |     1 |    16 |     0   (0)|          |  11,00 | PCWP |            |
|* 93 |            FIXED TABLE FIXED INDEX   | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  11,00 | PCWP |            |
|  94 |      BUFFER SORT                     |                             |     1 |     4 |     0   (0)|          |        |      |            |
|  95 |       TABLE ACCESS CLUSTER           | USER$                       |     1 |     4 |     0   (0)|          |        |      |            |
|* 96 |        INDEX UNIQUE SCAN             | I_USER#                     |     1 |       |     0   (0)|          |        |      |            |
|  97 |     PX COORDINATOR                   |                             |       |       |            |          |        |      |            |
|  98 |      PX SEND QC (RANDOM)             | :TQ120000                   |   173 |  2595 |     1 (100)| 00:00:01 |  12,00 | P->S | QC (RAND)  |
|  99 |       VIEW                           | GV$PROCESS                  |       |       |            |          |  12,00 | PCWP |            |
|*100 |        FIXED TABLE FULL              | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  12,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------

Yep, to top it all off, that’s a parallel query execution too.

The ugly part occurred when this query got deployed and the queue-based processing that contained the modified code went from processing 500+ messages per second down to just 2. Ouch!

Thankfully the problem was trivial to diagnose and easily fixed so no damage was done.

CHECK constraint evaluation

A developer approached me with the question “Are check constraints only evaluated when the columns that they apply to are modified and not some other column on the row?”. An unusual question perhaps but as it turned out they were creating a number of check constraints and wanted to assess the overhead this might introduce.

My response was “yes, a constraint it only evaluated when the columns(s) associated with the constraint are modified”… but then I had to think about how to prove this. After a minute or two I came up with the following.

Let’s take a table with two columns and insert a single row:

SQL> CREATE TABLE chk_test
  2     (col_1     NUMBER(2) NOT NULL
  3     ,col_2     NUMBER(2) NOT NULL)
  4  /

Table created.

SQL> INSERT INTO chk_test
  2  VALUES (1, -1)
  3  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

Onto this table we’ll add a CHECK constraint such that COL_2 must be greater than 0. However, because our table already has a value in COL_2 that violates this constraint, we’ll create the constraint in an ENABLE NOVALIDATE state, meaning that existing values do not need to abide by it but any new data changes must:

SQL> ALTER TABLE chk_test
  2     ADD CONSTRAINT chk_test_ch1
  3     CHECK (col_2 > 0)
  4     ENABLE NOVALIDATE
  5  /

Table altered.

We can verify the constraint is working by trying to update COL_2:

SQL> UPDATE chk_test
  2  SET col_2 = 0
  3  /
UPDATE chk_test
*
ERROR at line 1:
ORA-02290: check constraint (DEVELOPER.CHK_TEST_CH1) violated

However, we are allowed to modify COL_1 even though the existing value of COL_2 violates the constraint:

SQL> UPDATE chk_test
  2  SET col_1 = 0
  3  /

1 row updated.

So, a change to the row that did not modify COL_2 did not trigger the evaluation of the CHECK constraint… proof that CHECK constraints are only evaluated when the column(s) they relate to are manipulated.

It’s obvious…

Whilst attempting to apply a CHECK constraint to a table, Oracle threw back the following:

SQL Error: ORA-02293: cannot validate (xxxx.xxxxxxxxx) - check constraint violated
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

As a database person, to me the obvious action would be to correct the data and re-apply the constraint. To a non-database developer the obvious action may be to discard the constraint entirely.

It’s a matter of perspective… obviously.

Image Attributes

I’ve worked on a number of systems where images were stored in the tables of the database, rather than some sort of reference to a file system location. This approach has quite a few merits; adding and modifying images will be transactional with whatever business process is updating the database and the images will be backed up with the rest of the data.

However, quite often I see the images stored in simple BLOB data type columns. This approach works just fine until you want to know details image. An example that springs to mind was the need to know the format and dimensions of the images so they could make decisions about how the images should be displayed on a screen.

Oracle has an object type for images where this type of metadata is exposed: ORDImage. You’ll find the documentation of this object type in the Multimedia User’s Guide and Multimedia Reference. One solution to the problem of determining attributes of an image stored in a BLOB is to query the BLOB, casting it to a ORDIMage type and reading the attributes that way. Let’s take a quick look at how this is done…

For the image I’m going to use for this exercise I’m going to use the banner image from this blog; picture I took just outside of Queenstown in New Zealand. This image is a 1000 x 288 jpg image.

First we need to load the image into the database in a BLOB column of a table:

CREATE TABLE img_tab
   (id      NUMBER (6) NOT NULL
   ,img     BLOB)
/

CREATE DIRECTORY img_dir AS 'C:\oracle\images'
/

DECLARE
   l_blob        BLOB;
   l_bfile       BFILE := BFILENAME('IMG_DIR', 'cropped-queenstown_sml.jpg');
   l_dest_offset INTEGER := 1;
   l_src_offset  INTEGER := 1;
BEGIN

   dbms_lob.createtemporary
      (lob_loc => l_blob
      ,cache   => TRUE);
      
   dbms_lob.open (file_loc => l_bfile);
                           
   dbms_lob.loadblobfromfile
      (dest_lob    => l_blob
      ,src_bfile   => l_bfile
      ,amount      => dbms_lob.lobmaxsize
      ,dest_offset => l_dest_offset 
      ,src_offset  => l_src_offset);   
   
   INSERT INTO img_tab
      (id
      ,img)
   VALUES
      (1
      ,l_blob);

   COMMIT;
   
   dbms_lob.close (file_loc => l_bfile);
END;
/

Running the above gets us our image into a column named IMG in the table IMG_TAB. Now to see what Oracle ORDImage object type can tell us about our image. We can do this directly in SQL, without having to resort to PL/SQL (note, the second parameter of the ORDImage constructor, the “1”, instructs Oracle to process the image and obtain the metadata):

WITH imgs AS
   (SELECT ORDImage(img, 1) AS ord_img
    FROM   img_tab)
SELECT i.ord_img.getFileFormat() AS file_format
,      i.ord_img.getContentLength() AS content_length
,      i.ord_img.getHeight() AS height
,      i.ord_img.getWidth() AS width
FROM   imgs i

which gives us an output of:

FILE_FORMAT     CONTENT_LENGTH     HEIGHT      WIDTH
--------------- -------------- ---------- ----------
JFIF                     51960        288       1000

Exactly what we wanted to know.

A word of caution though. Running a query like this against a large number of stored BLOB images is likely to be processor and IO intensive. Pick a time of day when the system has capacity.

Slow DELETEs

My lunch was interrupted yet again by a developer with a support question. “The users are complaining that it takes too long to clear out some data. There’s a delete that’s taking ages. Can you look into it please?” he asked.

“A long running delete?” I mused. “That’s interesting…”. Like so many systems these days we don’t do much in the way of deletes. Oracle could deprecate the DELETE statement and it would only be a minor inconvenience to us.:-)

Luckily for me the developer had already done an excellent job of narrowing down the problem to a specific packaged SQL statement. The statement itself was quite trivial; delete a single row from a table, identifying the row via its single column primary key. It doesn’t get much simpler than that. I didn’t bother checking for triggers on the table as the system doesn’t use them.

Since the problem occurred in the past hour I turned to my favourite diagnostic tool; Active Session History. A quick query of ASH using the SQL_ID of the delete statement revealed the extent of the problem; 120 samples. Yep, the users were quite right to complain as a single row delete should not take 2 minutes to complete. Looking at the EVENT details I saw that the session had been waiting on IO for they were mostly “db file scattered read” with a handful of “db file sequential read”. This told me what the problem was likely to be…

Foreign keys are constraints in the database to ensure that data values used in child table entry are present in a parent, reference table. This helps enforce the integrity we desire, and generally assume to be, in the data. The presence of foreign keys also has an often overlooked impact to deletions made against the parent table. When a row is deleted from the parent table, Oracle needs to do a check against the child table to ensure that the row being deleted is not referenced by the child table. When the foreign key column(s) in the child table are indexed then this check against the child table is quick and generally not noticable. If the foreign key column(s) are not indexed then Oracle has no choice but to resort to a full table scan of the child table… and if the child table is fairly large then that check may take a while to complete, holding up the delete.

So, knowing about the impact of unindexed foreign keys when deleting from a parent table and observing a lot of physical IO the next step for me was to identify the object that the IO was being performed on to confirm my suspicion. Once again the ASH data contained the necessary details. The CURRENT_OBJ# attribute contains the reference to the object that the IO related to. A quick query against DBA_OBJECTS using the object ID from ASH revealed it to be a table and, yep, it contained an unindexed foreign key to the table that the delete was being performed on. The child table contained tens of millions of rows so a table scan would certainly have explained the delay in deleting from the parent table.

Diagnosis complete. Time to resolve the problem. The solution: drop the foreign key on the child table. No, no, just kidding! The solution was to index the foreign key column in the child table, making sure to specify the ONLINE clause of the CREATE INDEX statement so that the index could be added without blocking acivity on the child the table.

Maximum NUMBER Precision

What’s the maximum precision for the NUMBER data type? For many years I believed it to be 38. Afterall, it’s in the documentation under the Datatype Limits of the Reference manual:

38 significant digits

We can easily test this out (the following on a 12.1.0.2 database):

SQL>CREATE TABLE table_a
  2     (val    NUMBER(38,0))
  3  /

Table created.

SQL>CREATE TABLE table_b
  2     (val    NUMBER(39,0))
  3  /
   (val    NUMBER(39,0))
                  *
ERROR at line 2:
ORA-01727: numeric precision specifier is out of range (1 to 38)

and see that we cannot create a NUMBER (39,0) column. However, what happens if we leave the precision off the data type and insert some really big numbers into it (38 to 42 digits):

SQL>CREATE TABLE table_c
  2     (val    NUMBER)
  3  /

Table created.

SQL>INSERT INTO table_c
  2  VALUES (99999999999999999999999999999999999999)
  3  /

1 row created.

SQL>INSERT INTO table_c
  2  VALUES (999999999999999999999999999999999999999)
  3  /

1 row created.

SQL>INSERT INTO table_c
  2  VALUES (9999999999999999999999999999999999999999)
  3  /

1 row created.

SQL>INSERT INTO table_c
  2  VALUES (99999999999999999999999999999999999999999)
  3  /

1 row created.

SQL>INSERT INTO table_c
  2  VALUES (999999999999999999999999999999999999999999)
  3  /

1 row created.

SQL>COMMIT
  2  /

Commit complete.

All values were accepted by the INSERT statements so let’s now see what we’ve ended up with in our table:

SQL>COLUMN VAL FORMAT 9999999999999999999999999999999999999999999

SQL>SELECT val
  2  ,      LENGTH(TO_CHAR(val)) AS length_val
  3  FROM   table_c
  4  /

                                         VAL LENGTH_VAL
-------------------------------------------- ----------
      99999999999999999999999999999999999999         38
     999999999999999999999999999999999999999         39
    9999999999999999999999999999999999999999         40
  100000000000000000000000000000000000000000         40
 1000000000000000000000000000000000000000000         40

It would appear that we can actually get 40 digits of precision into a NUMBER data type even though we cannot define it to be NUMBER(40,0). After 40 digits Oracle approximates the number, using 40 significant digits.

A careful read of the Oracle SQL Reference documentation confirms this behaviour:

NUMBER(p,s)

where:

• p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.
• s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.
  • Positive scale is the number of significant digits to the right of the decimal point to and including the least significant digit.
  • Negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For negative scale the least significant digit is on the left side of the decimal point, because the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.

Interesting to know. However, NUMBER(38) is going to be satisfactory for virtually all practical purposes… except if you need to store an IPv6 address in decimal form.

Multi-versioning read consistency… again

Following on from some recent questions this post goes back to basics with regard to what Oracle terms “multi-versioning read consistency”. I have posted on this subject before but as it’s so critical to how applications using Oracle will behave it’s worth another post, with emphasis on a very important point at the end…

“Multi-versioning read consistency” might appear to be just a rather grand sounding name (or another bit of techno-jargon) so here’s a brief outline of what it means:

• The data reurned by a query is based on what the data in the underlying tables contained when the query commenced
• By extension of the above point, the query is not impacted by changes to the table data over the time taken to execute and retrieve the query data

It’s time for an example to illustrate this. First we’ll set up a simple table, TAB, and insert a single row:

CREATE TABLE tab
   (id  NUMBER(6) NOT NULL)
/

INSERT INTO tab
VALUES (1);

COMMIT;

Next we’ll open a cursor that returns the data in TAB but we won’t actually retrieve the data yet.

VARIABLE rc1 REFCURSOR

BEGIN
   OPEN :rc1
   FOR
   SELECT *
   FROM   tab
   ORDER  BY id;
END;
/

Let’s head back to our table and do some data changes. We’ll do these changes as autonomous transactions so as to simulate an external process coming in and making these changes, removed from the session with the open cursor:

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE tab
   SET id = 2;

   INSERT INTO tab
   VALUES (2);

   COMMIT;
END;
/

… and once again open a new cursor onto our table:

VARIABLE rc2 REFCURSOR

BEGIN
   OPEN :rc2
   FOR
   SELECT *
   FROM   tab
   ORDER  BY id;
END;
/

Lastly, just to reinforce things, we’ll do the whole thing yet again followed by a last update:

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE tab
   SET id = 3;

   INSERT INTO tab
   VALUES (3);

   COMMIT;
END;
/

VARIABLE rc3 REFCURSOR

BEGIN
   OPEN :rc3
   FOR
   SELECT *
   FROM   tab
   ORDER  BY id;
END;
/

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE tab
   SET id = 4;

   INSERT INTO tab
   VALUES (4);

   COMMIT;
END;
/

At the end of all this we have performed 4 inserts and 3 updates to our data. If we display the contents of our table as it exists at the end of the changes we get:

SQL> SELECT *
  2  FROM   tab
  3  ORDER BY id;

        ID
----------
         4
         4
         4
         4

Now let’s retrieve the data from our cursors and see what they contain:

SQL> PRINT rc1

        ID
----------
         1

SQL> PRINT rc2

        ID
----------
         2
         2

SQL> PRINT rc3

        ID
----------
         3
         3
         3

Even though the data in the table had changed and was committed after we opened the cursors Oracle still returned the data as it was at the point in time we opened the cursor, not what the table contained when we read from the cursor. This is Oracle’s multi-versioning read consistency in action.

Now for that very important point that I mentioned at the start…

The above exercise also highlights another aspect of Oracle:

Readers and writers don’t block each other.

If you look closely at the example, whilst we were holding open cursors to the table we were still able to update existing rows as well as insert new ones. No locks on the table data were being held by the cursors to prevent this… yet the data returned by the cursors were still as the table looked when the cursor was opened. This is a fundamental of how Oracle works. If you want to build a system that is performant under high levels of concurrent activity this is exactly the behaviour you will require.