utl_recomp and Automatic Statistics Gathering

When recompiling invalid objects—often after patches, upgrades, or invalid dependency chains—you might call utl_recomp.recomp_serial. Since it's a serial procedure, one could reasonably expect it to avoid any parallel or background job interaction. But Oracle can be persuaded to behave a bit differently…

SQL> exec utl_recomp.recomp_serial('HR');
BEGIN utl_recomp.recomp_serial('HR'); END;

*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: the job_queue_processes parameter is less than 4

How does this make sense—serial recompilation wants to do concurrent statistics gathering? Also, the UTL_RECOMP documentation does not mention anything regarding statistics (I tried searching for the 'stat' keyword in this doc). So, what and why is it being gathered?

A quick spoiler, this happens if you set both of these:

alter system set job_queue_processes=0;
exec dbms_stats.set_global_prefs('concurrent', 'true');

Changing either one will make utl_recomp.recomp_serial() succeed.

Also, perhaps worth mentioning, this test case can be reproduced even if the example HR schema is empty.

Stats Gathering

This is the full stack trace:

SQL> exec utl_recomp.recomp_serial('HR');
BEGIN utl_recomp.recomp_serial('HR'); END;

*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: the job_queue_processes
parameter is less than 4
ORA-06512: at "SYS.UTL_RECOMP", line 927
ORA-06512: at "SYS.DBMS_STATS", line 40799
ORA-06512: at "SYS.DBMS_STATS", line 5065
ORA-06512: at "SYS.DBMS_STATS", line 40725
ORA-06512: at "SYS.UTL_RECOMP", line 260
ORA-06512: at "SYS.UTL_RECOMP", line 830
ORA-06512: at "SYS.UTL_RECOMP", line 940
ORA-06512: at line 1

According to the unwrapped utl_recomp source, those lines refer to the following functions:

recomp_serial() 
-> recomp_parallel()                 line 940
-> select_invalid_parallel_objs()    line 830
-> dbms_stats.gather_table_stats()   line 260

Where, at line 260, there is:

  260      DBMS_STATS.GATHER_TABLE_STATS('SYS', 'UTL_RECOMP_COMPILED',
  261         ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
  262         NO_INVALIDATE => FALSE);

So, utl_recomp.recomp_serial truly invokes dbms_stats.gather_table_stats with the intention of gathering statistics for a table called SYS.UTL_RECOMP_COMPILED:

SQL> desc SYS.UTL_RECOMP_COMPILED;
 Name              Null?    Type
 ----------------- -------- ------------
 OBJ#              NOT NULL NUMBER
 BATCH#                     NUMBER
 COMPILED_AT                TIMESTAMP(6)
 COMPLETED_AT               TIMESTAMP(6)
 COMPILED_BY                VARCHAR2(64)

which contains the list of compiled objects; it tracks which objects were compiled and in which batch.

Anyway, the dbms_stats.gather_table_stats must honor its global preferences, which can be set to use concurrency.

References

This blog post was tested on 19.29.0-EE.

Links to the official Oracle documentation:

utl_recomp and Automatic Statistics Gathering

Comments

More from this blog

How direct-path inserts and one extra row per loop bloated 18 MB into 8 GB

Migrating LOBs on Standard Edition with Materialized View Logs

The Hidden Cost of Login (Redo)

Reading Oracle ASM Files Directly Using x$kffxp

Stats Gathering

References

Command Palette

Comments

More from this blog

Stats Gathering

References