Faster ASM Rebalance

Yes, it is possible to accomplish it but only if you have a flash storage.

Why ?

Because in this case you can skip the Compact Phase of ASM rebalance operation.

For 12c onwards, you can use the command below to skip the compact phase:


But before running this command, please read the following articles so you can better understand what you are doing.

Rebalancing act –

MOS Note 1902001.1 – What is ASM rebalance compact Phase and how it can be disabled

ASM Rebalance Too Slow? 3 Tips To Improve Rebalance Times –

Statspack batch install

If you don’t have Oracle Diagnostics pack or is running Oracle Standard Edition, just go for statspack !

In this blog post I will show you how to install it in batch mode.

It is actually very simple, you just need to define three variables:

connect / as sysdba

define default_tablespace='perfstat'
define temporary_tablespace='temp01'
define perfstat_password='YourComplexStatspackPassword'

then run


Simple, isn’t ?

I would also recommend you to take a look at Franck’s way of improving statspack here.

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

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 (
$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


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

Nos vemos lá !

Atualização: Slides da palestra em