Parallel cats (an overview of catctl.pl)



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 set about doing my own test to see if this can be used for any kind of user defined scripts - here is what i set up

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