As many of us are now aware I'm sure the upgrade process is very different in 12c than in previous releases due mainly to the introduction of the catctl.pl script for running parallel upgrades of multiple pluggable db's at once (no more just runing catupgrd.sql or catalog.sql individually). What's not maybe so immediately obvious about this script is that it even helps with the upgrade of a single pluggable database or a database in traditional installation mode (no doubt soon to be called legacy).
This is accomplished by the catctl script creating multiple connections and actually being able to run some of the sql script in parallel - so we can get the situation where script a might have to run serially, followed by script b,c,d that could run at the same time, script d has to then complete before e,f,g can then run (you get the general idea) - parts of the process have to be sequential but other could be parallel. In the old style method of running scripts in it could only ever be sequential - there was no kind of workflow process to handle anything else.
catctl adds some basic flow control to permit this pattern of serial->parallel->serial etc so we end up with a flow kind of like a christmas tree - we start at the top with a single process , go down into parallel , come back to serial, back to parallel etc - something like the below picture (analogy borrowed from Mike Dietrich :-))
This is visible in the output of commands that are run this way - something like the below where you can see it switching from serial to parallel as it goes through the script
Serial Phase #: 0 Files: 1 Time: 26s CDB$ROOT
Serial Phase #: 1 Files: 5 Time: 66s CDB$ROOT
Restart Phase #: 2 Files: 1 Time: 0s CDB$ROOT
Parallel Phase #: 3 Files: 18 Time: 17s CDB$ROOT
Restart Phase #: 4 Files: 1 Time: 1s CDB$ROOT
Serial Phase #: 5 Files: 5 Time: 24s CDB$ROOT
Serial Phase #: 6 Files: 1 Time: 15s CDB$ROOT
Serial Phase #: 7 Files: 4 Time: 13s CDB$ROOT
Restart Phase #: 8 Files: 1 Time: 0s CDB$ROOT
Parallel Phase #: 9 Files: 62 Time: 73s CDB$ROOT
Restart Phase #:10 Files: 1 Time: 0s CDB$ROOT
You can see how this would make the whole process faster as instead of one script after another some are run at the same time and the overall elapsed time is less.
I wondered how this had been implemented so i went about looking at some of the built in scripts to see how they were doing it - below is an extract from catupgrd.sql
Rem
Rem Initial checks and RDBMS upgrade scripts
Rem
@@catupstr.sql
Rem
Rem Execute upgrade and catalog session script
Rem
@@catupses.sql --CATFILE -SES
@@catalogses.sql --CATFILE -SES
Rem
Rem Run catalog with some multiprocess phases
Rem
@@catalog.sql --CATFILE -X
Rem
Rem Execute catproc session script
Rem
@@catprocses.sql --CATFILE -SES
Rem
Rem Run catproc with some multiprocess phases
Rem
@@catproc.sql --CATFILE -X
The key bits are the bits in orange - these are special comments (hints if you like......) that tell catctl something about this script
--CATFILE -SES = serial
--CATFILE -X = parallel
There are also further hints down in the child scripts which seem to flag start and end of sections
--CATCTL -M
--CATCTL -R
I create a simple sql file which would do an insert into a one row table and then sleep for 5 seconds - the content of this is shown below - i named this onerow.sql
insert into system.test values (sysdate);
exec dbms_lock.sleep(5);
commit;
I then created a script to call that 20 times named test2.sql - content as below.....
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
@@onerow.sql
I then created a top level script called test.sql that just called test2.sql ( to mimic the way top level scripts call other scripts in catupgrd.sql for example) - the content of this is then just
@@test2.sql
If i now run this as it is against just the CDB$ROOT everything runs sequentially and i get the output as shown below
perl ./catctl.pl -u system/manager -c 'CDB$ROOT' -d /tmp -l /tmp test.sql
Argument list for [./catctl.pl]
SQL Process Count n = 0
SQL PDB Process Count N = 0
Input Directory d = /tmp
Phase Logging Table t = 0
Log Dir l = /tmp
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = CDB$ROOT
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /oracle
Analyzing file /tmp/test.sql
Log files in /tmp
Line not processed: --CAT CTL -M
catcon: ALL catcon-related output will be written to /tmp/test_catcon_22228.lst
catcon: See /tmp/test*.log files for output generated by scripts
catcon: See /tmp/test_*.lst files for spool files, if any
Number of Cpus = 16
Parallel PDB Upgrades = 8
SQL PDB Process Count = 2
SQL Process Count = 0
New SQL Process Count = 8
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
HUB1
PDB Inclusion:[] Exclusion:[]
------------------------------------------------------
Phases [0-1] Start Time:[2015_12_29 21:22:17]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
Serial Phase #: 0 CDB$ROOT Files: 20 Time: 100s
Restart Phase #: 1 CDB$ROOT Files: 1 Time: 0s
------------------------------------------------------
Phases [0-1] End Time:[2015_12_29 21:23:57]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
Time: 100s For CDB$ROOT
Grand Total Time: 100s
Grand Total Upgrade Time: [0d:0h:1m:40s]
The total run time being 100 seconds - so executing one script after another with a 5 second sleep in each. You'll notice i picked out in orange it explicitly says 'SERIAL'.
If i now want to make use of the parallel functionality catctl.pl offers i have to add some hints in
In the top level script i have to change the line to now read
@@test2.sql --CATFILE -X
And in the child script i have to mark the start and end of this section which i do like this
--CATCTL -M
@@onerow.sql
etc
etc (up to 20 execs)
--CATCTL -R
Now when i re-run the process i see this
perl ./catctl.pl -u system/manager -c 'CDB$ROOT' -d /tmp -l /tmp test.sql
Argument list for [./catctl.pl]
SQL Process Count n = 0
SQL PDB Process Count N = 0
Input Directory d = /tmp
Phase Logging Table t = 0
Log Dir l = /tmp
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = CDB$ROOT
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /oracle
Analyzing file /tmp/test.sql
Log files in /tmp
catcon: ALL catcon-related output will be written to /tmp/test_catcon_25475.lst
catcon: See /tmp/test*.log files for output generated by scripts
catcon: See /tmp/test_*.lst files for spool files, if any
Number of Cpus = 16
Parallel PDB Upgrades = 8
SQL PDB Process Count = 2
SQL Process Count = 0
New SQL Process Count = 8
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
HUB1
PDB Inclusion:[] Exclusion:[]
------------------------------------------------------
Phases [0-2] Start Time:[2015_12_29 21:26:30]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
Time: 0s
Parallel Phase #: 1 CDB$ROOT Files: 20 Time: 16s
Restart Phase #: 2 CDB$ROOT Files: 1 Time: 0s
------------------------------------------------------
Phases [0-2] End Time:[2015_12_29 21:26:46]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
Time: 16s For CDB$ROOT
Grand Total Time: 16s
Grand Total Upgrade Time: [0d:0h:0m:16s]
So now it's specifically marked as PARALLEL.
You'll notice that the total runtime wasn't 100 seconds or 5 seconds but 16 seconds this is because the parallelism was limited to 8 so we needed 3 passes through the files - you can see that from this line
New SQL Process Count = 8
This seems to be a hardcoded limit somewhere as setting it to higher values using -n/-N is acknowledged but ignored.
Anyway quite interesting i thought and it's nice to know how this has been implemented - it is something you could make use of in your own schema deployment scripts i guess - though you'd need a pretty big schema and a good understanding of how things relate to really make use of this.
Comments
Post a Comment