DB - Instance caging

English Version

Neste post partilho uma forma de identificar a utilização de CPU por utilizador (usuario como dizem no Brasil ) em ambientes Linux com múltiplas instâncias de base de dados Oracle.Estes comandos são útils para  configurar Database Instance Caging.

$ echo "User : CPU Usage " ; 

$ cat /etc/redhat-release
  Red Hat Enterprise Linux Server release 6.9 (Santiago)

$ ps -ef | grep -i ora_pmon | grep -v "grep" | cut -f1 -d" " | while read user 
do
echo $user ":"  `top -b -n 1 -u $user | awk 'NR>7 { sum += $9; } END { print sum; }'`
done;

orarms1 : 1.1
orarms2 : 7.7
orarms3 : 102.3
orarms4 : 1.1

Pelo resultado acima - conclui se que a user orarms3 usa muito mais cpu que os restantes, acabando por ter impacto nos restantes users / dbs.

Isto acontece porque Oracle alloca os CPUs todos, a forma de evitar que as base de dados tenham impacto no cpu das  "visinhas" e configurar instance caging, para fazer isso basta activar :

• Activar Resource Manager
• Ajustar o parametero  CPU_COUNT

Abaixo mostro como se faz

SQL> !grep processor /proc/cpuinfo | wc -l
160

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     160

SQL> alter system set resource_manager_plan = DEFAULT_PLAN scope=both;
System altered.

SQL> alter system set cpu_count = 100 scope=both;
System altered

Ref: http://www.oracle.com/technetwork/database/performance/instance-caging-wp-166854.pdf

Obrigado pela leitura.
Espero que este post o tenha ajudado.

Versao Portuguesa 

I'm sharing here a way to check the CPU usage per user in Linux environments, the bellow commands are usefull to identify workloads and to help to setup Database Instance Caging.

$ echo "User : CPU Usage " ; 

$ cat /etc/redhat-release

  Red Hat Enterprise Linux Server release 6.9 (Santiago)

$ ps -ef | grep -i ora_pmon | grep -v "grep" | cut -f1 -d" " | while read user 

do

echo $user ":"  `top -b -n 1 -u $user | awk 'NR>7 { sum += $9; } END { print sum; }'`

done;

orarms1 : 1.1
orarms2 : 7.7
orarms3 : 102.3
orarms4 : 1.1

Reading the above output -  we can concluded the user orarms3 is using more cpu than the remains, that will cause impact in the neighbors.

Oracle by default allocates all CPUs for itself, the way to avoid a database has impact in the neighbours is to setup instance caging, easy to do:

• Activate Resource Manager
• Adjust the  CPU_COUNT for the needs

SQL> !grep processor /proc/cpuinfo | wc -l
160

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     160

SQL> alter system set resource_manager_plan = DEFAULT_PLAN scope=both;
System altered.

SQL> alter system set cpu_count = 100 scope=both;
System altered

Ref: http://www.oracle.com/technetwork/database/performance/instance-caging-wp-166854.pdf

Thank you for reading.
hope this post was helpful.