noncdb_to_pdb.sql – why does it take so long?

What is noncdb_to_pdb.sql?

noncdb_to_pdb.sql - Why does it take so long?The script noncdb_to_pdb.sql gets run only once in the life span of your database. It gets executed when a regular Oracle database (non-CDB) gets move to become part of a Single-/Multitenant deployment as a pluggable database (PDB). But why does it take so long?

You will never run this script again for this database. The script is a sanity script to clean up and change things in a non-CDB, and unite it finally and irreversible with the CDB.

How long does it take to complete?

Runtime varies a lot. It depends mainly on the number of objects it has to adjust and map to the CDB’s dictionary. And on the time it takes for recompilation as it does forced recompilations.

16:21:48 SQL> DECLARE
16:21:48   2     threads pls_integer := &&1;
16:21:48   3  BEGIN
16:21:48   4     utl_recomp.recomp_parallel(threads);
16:21:48   5  END;
16:21:48   6  /

PL/SQL procedure successfully completed.
Elapsed: 00:10:12.67

16:32:01 SQL>
16:32:01 SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2017-06-08 16:32:01

1 row selected.

Can you speed it up? Can it be restarted?

Based on my experience you can’t speed it up except for adding CPU power for the recompilation. Therefore test the runtime upfront if you depend on certain downtime restrictions and rules.

In Oracle Database 12.1.0.2 the script can’t be restarted or resumed. Once it failed the entire setup had to be restored and the process need to be restarted from the beginning. That’s why a backup is absolutely essential.

Since Oracle Database 12.2.0.1 the script is supposed to be resumable – and I did check back with my Support colleagues in Romania who tested this already with positive results. Bu the overall run time hasn’t been improved.

Known issue

One known issue I came across (Thanks to Franky Weber Faust!):
noncdb_to_pdb.sql will fail when Concurrent Statistics Gathering is on.

Error pattern:

15:35:52 SQL> DECLARE
15:35:52   2     threads pls_integer := &&1;
15:35:52   3  BEGIN
15:35:52   4     utl_recomp.recomp_parallel(threads);
15:35:52   5  END;
15:35:52   6  /
DECLARE
*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: Resource manager plan is
not active or is not managing CPU usage
ORA-06512: at "SYS.UTL_RECOMP", line 899
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 4321
ORA-06512: at "SYS.DBMS_STATS", line 36652
ORA-06512: at "SYS.UTL_RECOMP", line 260
ORA-06512: at "SYS.UTL_RECOMP", line 803
ORA-06512: at line 4

This can be solved simply:

 SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');

Furthermore the issue gets tracked and solved under bug 26434999.

More information?

Please find a complete upgrade followed by a conversion into a PDB here:

Upgrade to Oracle Database 12.2 and Converting it into an 12.2 PDB (plugin)

–Mike

Share this:

2 thoughts on “noncdb_to_pdb.sql – why does it take so long?

  1. Hi Mike!

    We are trying to convert our 12.2.0.1 non CDB database to PDB and during the execution of noncdb_to_pdb.sql we saw that almost all the time session spent on recompilation. Tracing of that session showed that actually it was doing d1s02myktu19h begin dbms_utility.validate(:1,:2,:3,:4); end; and it was waiting for the “single-task message” event.

    18:56:41 DOC>#
    18:56:41 SQL>
    18:56:41 SQL> DECLARE
    18:56:41 2 /* threads pls_integer := &&1;*/
    18:56:41 3 threads pls_integer := 32;
    18:56:41 4 BEGIN
    18:56:41 5 utl_recomp.recomp_parallel(threads);
    18:56:41 6 END;
    18:56:41 7 /

    PL/SQL procedure successfully completed.

    Elapsed: 00:39:12.86
    19:35:54 SQL>
    19:35:54 SQL> SELECT dbms_registry_sys.time_stamp(‘utlrp_end’) as timestamp from dual;

    TIMESTAMP
    ——————————————————————————–
    COMP_TIMESTAMP UTLRP_END 2018-12-10 19:35:54

    Seems to me there is some bug here, because 39 minutes for recompilation of objects it is too much!

    Could you please help us to find out the cause of long dbms_utility.validate/single-task message waits ?

    Thank you in advance!

    • Sergei,

      I can’t do this via the blog. And I know how painful SRs are – but PLEASE PLEASE PLEASE open an SR for this and send me the number (ideally via email – firstname.lastname@oracle.com because than I can follow up with you much easier). I know that problem from several cases – but so far it is treated as “it does what it does”.

      I’m willing to help you – but we need all information collected in an SR first.

      Thanks,
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.