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.
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 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
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
We are creating the tablespaces the following way (taken from the
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
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
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:
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
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
- Modify the
- Before Oracle 220.127.116.11:
CREATE DATABASEstatement in the file
- In the line:
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/HUGO2/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
- 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
PDB$SEEDwill 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.
- In the line:
- Since Oracle 18.104.22.168:
Simply adjust the underscore parameter
_seed_root_undo_ratiofrom 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:
- 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_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.
- 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