# 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…

```plaintext
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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/UTL_RECOMP.html) 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:

```plaintext
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:

```plaintext
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:

```plaintext
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:

```plaintext
  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`:

```plaintext
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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/UTL_RECOMP.html)
    
* [DBMS\_STATS](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html)