OGB Appreciation Day: parallel ops #ThanksOGB

Time is money so let’s get things done faster with parallel operations like:

  • Expdp/Impdp:
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=impdpSCOTT.log

Must read note here.

  • RMAN
CONFIGURE DEVICE TYPE disk PARALLELISM 2;
CONFIGURE DEVICE TYPE sbt PARALLELISM 2;


RMAN always allocates the number of channels specified in PARALLELISM, using specifically configured channels if you have configured them and generic channels if you have not. Note that if you configure specific channels with numbers higher than the parallelism setting, RMAN will not use these channels.
  • Parallel Execution of SQL statments

Nice Oracle Paper here and of course Oracle docs.

  • Parallel Upgrade Utility (catctl.pl/dbupgrade)
$ORACLE_HOME/bin/dbupgrade -n 4

-n options specifies the number of processes to use for parallel operations.

Non-CDBs: The -n parameter specifies the number of SQL processes to use when upgrading the database.

Multitenant architecture databases (CDBs): The number of PDBs upgraded concurrently is controlled by the value of the -n parameter. Multiple PDB upgrades are processed together. Starting in Oracle Database 12c, the default value for multitenant architecture databases is the number of CPUs on your system. A cpu_count equal to 24 equates to a default value of 24 for -n.

Values for the -n parameter:

Non-CDBs: The maximum value for -n is 8. The minimum value is 1. The default value is 4.

Multitenant architecture databases (CDBs): The maximum value for -n is unlimited. The minimum value is 4. The maximum PDB upgrades running concurrently is the value of -n divided by the value of -N.

-N option specifies the number of SQL processors to use when upgrading PDBs.

For non-CDBs, this parameter is ignored.
For CDBs, the maximum value is 8. The minimum value is 1. The default value is 2.

So without specifying values for the parameters -n and -N (that is, accept the default value of -N, which is 2, and accept the default value of -n as the CPU_COUNT parameter value, which is 24). 

The following parallel processing occurs:

12 PDBs are upgraded in parallel (CPU_COUNT divided by 2)
2 parallel processes run for each PDB

That is what came from the top of my head so it should have more parallel options 🙂

Thanks Tim to get the community together every year ! #ThanksOGB

DBA Brasil 3.0

Pessoal,

Minha palestra Free Oracle Performance Tools foi aprovada na conferência DBA Brasil 3.0, que ocorrerá dia 05 de maio de 2018 no Centro Universitário UNIBTA e é totalmente gratuita.

Não perca tempo, inscreva-se já em http://db3.dbabr.com.br/inscrever/

Nos vemos lá !

Atualização: Slides da palestra em https://www.slideshare.net/rbeguchi/free-oracle-performance-tools