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:
CDB$ROOT | PDB$SEED “at start” |
PDB$SEED “at end” |
|
SYSTEM | 734MB | 220MB | 220MB |
SYSAUX | 577MB | 173MB | 173MB |
UNDO | 236MB | 63MB | 230MB |
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:
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 theCREATE DATABASE
statement in the fileCreateDB.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
toNEXT 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 theUNDO
tablespace inPDB$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.
- In the line:
- 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