How to speed up Multitenant CUSTOM database creation

I usually create my container databases for testing with the CUSTOM option of DBCA. That allows me to specify exactly the components I’d like to have – or not. But I realized two things during the CUSTOM creation: It takes very long. And in the alert.log I see resize operations especially with the PDB$SEED during dictionary view creation. I see heap warnings. And recompilations. I did ask myself how to speed up Multitenant CUSTOM database creation.

Background Information

The PDB$SEED is the – I call it – “blueprint” pluggable database. It’s sole purpose is to allow you fast provisioning of a new PDB as a clone of the PDB$SEED with some additions. That means, every PDB you create as a clone of the PDB$SEED will inherit the setup and characteristics of the PDB$SEED.

A PDB$SEED gets always created with Multitenant and Single Tenant. From the workflow during a create database process, you’ll recognize that the CDB$ROOT container gets built first, and then the PDB$SEED receives its dictionary views. As a result, you’ll experience longer database creation duration as for a non-CDB.

As you can’t specify any size parameters for the PDB$SEED during the database creation process, the datafile sizes for SYSTEM and SYSAUX in the PDB$SEED get automatically calculated as 30% of the CDB$ROOT‘s files. But this leads automatically to a very small UNDO – which may not be a wise decision.

See an example CREATE DATABASE statement:

CREATE DATABASE "HUGO"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/u02/oradata/HUGO/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u02/oradata/HUGO/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/HUGO/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u02/oradata/HUGO/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u02/oradata/HUGO/redo01.log') SIZE 200M,
GROUP 2 ('/u02/oradata/HUGO/redo02.log') SIZE 200M,
GROUP 3 ('/u02/oradata/HUGO/redo03.log') SIZE 200M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
seed file_name_convert=('/u02/oradata/HUGO/system01.dbf','/u02/oradata/HUGO/pdbseed/system01.dbf','/u02/oradata/HUGO/sysaux01.dbf','/u02/oradata/HUGO/pdbseed/sysaux01.dbf','/u02/oradata/HUGO/temp01.dbf','/u02/oradata/HUGO/pdbseed/temp01.dbf','/u02/oradata/HUGO/undotbs01.dbf','/u02/oradata/HUGO/pdbseed/undotbs01.dbf') LOCAL UNDO ON;

You can’t find an option to influence the sizing of the PDB$SEED.

We are creating the tablespaces the following way (taken from the alert.log):

PDB$SEED(2):create tablespace SYSTEM datafile  '/u02/oradata/HUGO/pdbseed/system01.dbf'
SIZE 220200960 REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL online

PDB$SEED(2):CREATE TABLESPACE sysaux DATAFILE  '/u02/oradata/HUGO/pdbseed/sysaux01.dbf'
SIZE 173015040 REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT
AUTO ONLINE FORCE LOGGING

PDB$SEED(2):CREATE SMALLFILE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u02/oradata/HUGO/pdbseed/undotbs01.dbf'
SIZE 62914560 REUSE AUTOEXTEND ON NEXT 5242880 MAXSIZE UNLIMITED

PDB$SEED(2):CREATE SMALLFILE TEMPORARY TABLESPACE TEMP TEMPFILE  '/u02/oradata/HUGO/pdbseed/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

Comparison

In my example above, a comparison of datafile/tablespace sizes shows:

Start size of UNDO for the CDB$ROOT was 200MB as you can see from the above CREATE DATABASE statement. Hence, the start size of UNDO you’ll see in PDB$SEED is a third of 200MB. The PDB$SEED has inherited also the future extension of datafiles from CDB$ROOT (you can check CDB_DATA_FILES). And the increment is fairly small as you can see from the above CREATE statements already as well:

select tablespace_name, increment_by*8192/1000000 "Inc in MB" from dba_data_files order by 1;

TABLESPACE_NAME 		Inc in MB
------------------------------ ----------
SYSAUX				 10.48576
SYSTEM				 10.48576
UNDOTBS1			  5.24288

But this doesn’t seem to be an issue as the sizes for SYSTEM and SYSAUX are the same between start and end of the creation process. The UNDO is sized too small. This results any many tiny 5MB extensions of the file:

2018-11-12T16:27:04.050398+01:00
PDB$SEED(2):Resize operation completed for file# 6, old size 92160K, new size 97280K

Why does the CUSTOM “Create Container Database” take so long?

There is more than one reason for the long duration of the CREATE DATABASE statement for a Multitenant container database. Apart from the UNDO resize operations which slows down the run maybe a tiny bit, one of the main reasons is a conceptual decision: The dictionary gets created in the CDB$ROOT at first. And then it will be created in the PDB$SEED. You won’t see any parallelism. Hence, a Multitenant container database creation in CUSTOM mode where all the scripts get executed takes a least twice as long as a regular non-CDB creation.

In addition, in the alert.log, you will find a lot of warnings leading to trace files being written:

2018-11-12T11:19:23.034488+01:00
KGL object name :grant read on ku$_zm_view_pfh_view to public
2018-11-12T11:20:24.515554+01:00
Memory Notification: Library Cache Object loaded into SGA
Heap size 52749K exceeds notification threshold (51200K)
Details in trace file /u01/app/oracle/diag/rdbms/hugo/HUGO/trace/HUGO_ora_9833.trc

You may of course ignore this as you potentially don’t create a container database every day. Hence, the impact may be very low. But just in case you wonder …

What do these warnings mean? MOS Note: 330239.1 explains:

As large objects in the shared pool can potentially cause problems this warning threshold was implemented. Items/SQLs which allocate more space than this warning threshold, outputs a warning to the alert log. This warning is only to inform that a given heap object exceeds the defined threshold size and a trace file is generated so that a DBA can check potentially expensive – from shared memory point of view – objects. These are purely warning messages and have no impact on the database functionality, although they are designed to indicate possible tuning opportunities in customers’ applications.

Hence no issue. But still traces get written constantly. The default of _kgl_large_heap_warning_threshold is 52428800 (52MB). Since Oracle 12.1, _kgl_large_heap_assert_threshold got introduced and set to the same value as _kgl_large_heap_warning_threshold by default.

And finally, there are forced recompilations in the PDB$SEED as we see them during upgrade as well. which don’t happen in non-CDB databases. See:

• Why does the upgrade of PDB$SEED always take longer?

2018-11-12T11:52:49.154626+01:00
PDB$SEED(2):KGL object name :ALTER VIEW "KU$_M_ZONEMAP_PFH_VIEW" COMPILE
2018-11-12T11:53:07.903288+01:00
PDB$SEED(2):Memory Notification: Library Cache Object loaded into SGA
Heap size 52562K exceeds notification threshold (51200K)
Details in trace file /u01/app/oracle/diag/rdbms/hugo/HUGO/trace/HUGO_ora_9833.trc
2018-11-12T11:53:07.903365+01:00
PDB$SEED(2):KGL object name :ALTER VIEW "KU$_M_ZONEMAP_PIOT_VIEW" COMPILE

The CUSTOM creation of the Multitenant Container database did take almost exactly 60 minutes in my environment (VBox, laptop SSD). Just for the records: A non-CDB creation on the same system with the same characteristics takes me 28 minutes.

A potential workaround

Instead of letting the DBCA create the database, I have it create the scripts only.

Then I will change the following in $ORACLE_BASE/admin/<SID>/scripts:

• Modify the init.ora and add:
  • _kgl_large_heap_assert_threshold=1524288000
  • _kgl_large_heap_warning_threshold=1524288000
  • shared_pool_size=800M
  • _cursor_obsolete_threshold=1024
• Before Oracle 12.2.0.1:
  Modify the CREATE DATABASE statement in the file CreateDB.sql:
  • In the line:
    SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/HUGO2/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
    change:
    • NEXT 5120K to
    • NEXT 50M
  • Afterwards the line reads:
    SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/HUGO2/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
    This avoids too many resize operations. As the UNDO tablespace in PDB$SEED will still be created as a third of 200M, there will be 4 resize operations. But this is much less than 33 operations before.
    Actually I would change the NEXT values for the other tablespaces as well. But take into account that it will be inherited to the PDB$SEED – and then to all future PDBs as well.
• Since Oracle 12.2.0.1:
  Simply adjust the underscore parameter _seed_root_undo_ratio from its default 30 (i.e. 30 percent) to a higher value.
• After the database creation has completed please remove these parameters again from the init.ora/spfile:
  • _kgl_large_heap_assert_threshold
  • _kgl_large_heap_warning_threshold
  • shared_pool_size
  • But leave _cursor_obsolete_threshold=1024 – read more here.

The Result and a Summary

I wrote a lot of text for very minimal improvement. But I’d like to understand what happens – and why it happens. My CREATE DATABASE operation is now 11 minutes faster with just a few minimal changes.

In summary, I set two underscore parameters _kgl_large_heap_assert_threshold and _kgl_large_heap_warning_threshold to higher values to avoid unnecessary warnings and the trace operations, I preallocated a larger shared_pool_size – and I set a parameter we recommend anyway for every 12.2 and newer database unless you have hundreds and thousands of PDBs.

In addition I changed the setup of the UNDO tablespace to have 10x larger increments for extension. I could have done this change in the DBCA as well – but as I can’t set the underscore parameters in DBCA’s workflow, I had to adjust them on the command line anyway.

As I can’t influence the PDB$SEED‘s tablespace creation at 30% of the CDB$ROOT‘s size, I could only change the characteristics. These will inherited from the CDB$ROOT without change.

More Information

• Always create databases as CUSTOM databases
• Use your own templates in DBCA to create databases
• Create Multitenant Container databases with DBCA – Things to Know
• MOS Note: 330239.1 – Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded]
• Oracle 18c Concepts Guide: Multitenant

–Mike