Consumo elevado de CPU

O principal foco deste artigo é obter informações sobre uma sessão no Oracle que esta consumindo muito recurso de CPU no sistema operacional em ambiente UNIX.

O DBA que tiver conhecimentos em UNIX/Linux estará mais à-vontade devido aos termos técnicos e as ferramentas utilizadas. É fundamental deixar claro que este artigo é somente uma das ações que podem ser tomadas para o processo de tuning de um Banco de Dados e SO. Tuning de sistema operacional é um assunto muito complexo e ao longo do tempo colocarei mais artigos sobre outras matérias.

Quando você tem um banco de dados Oracle em ambiente Windows, existe a restrição quando é necessário fazer o tuning relacionado ao sistema operacional porque somente é possível visualizar um processo oracle.exe e não os threads que ele inicia. Com as ferramentas padrão do Windows não é possível ir muito a fundo no que esta acontecendo com um processo. Isso não acontece em ambientes UNIX/Linux devido às diversas ferramentas já disponíveis por padrão. Algumas delas em: Solaris – sar (sar 2 100), prstat –T, vmstat 2 100, iostat 2 100 Linux – top AIX – topas, top Os principais para CPU são sar, prstat, top e topas. Para determinar se as CPU’s em um sistema esta carregada, pode-se utilizar no Solaris o sar 2 100. O resultado é o seguinte:

SunOS 5.9 Generic_118558-28 sun4u 01/12/2007
11:21:11 %usr %sys %wio %idle
11:21:13 64 24 6 5
11:21:15 55 17 25 3

A coluna IDLE significa que a maquina esta somente 3% ociosa, logo esta bem carregada. Em ambientes com top, é possível visualizar este campo IDLE na barra superior de detalhes. A interpretação é a mesma do sar. Quanto maior o IDLE mais ociosa a máquina esta. Agora que sabemos que a maquina esta muito carregada, devemos identificar quais processos estão consumindo um maior numero de CPU. Para isso podemos utilizar ou o prstat, top ou topas, dependendo do SO. No caso do Solaris, temos como exemplo o prstat –T:

PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
11238 oracle 58M 48M sleep 0 0 0:36:49 42% oracle
13459 oracle 76M 1031M sleep 29 0 0:01:45 6.7% oracle
111239 oracle 690M 660M sleep 35 0 0:11:39 4.0% oracle
116328 oracle 1076M 1029M sleep 59 0 0:00:00 0.4% oracle
12052 oracle 687M 650M sleep 59 0 0:06:15 0.3% oracle

Neste caso temos um processo consumindo 42%. Em alguns casos é comum a carga da máquina esta bem alta, mas nenhum processo com esta quantidade de uso de CPU. Pode acontecer vários processo de 6 ou 7% que acabam impactando na performance.Neste caso, utilizaremos como principal culpado pelo alto consumo de CPU o processo 11238, dado pela coluna PID. Note que a coluna TIME também é de fundamental importância. Pode haver processos como no exemplo abaixo:
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
11238 oracle 62M 52M cpu2 0 0 0:39:10 9.8% oracle
11239 oracle 690M 660M sleep 49 0 1:11:52 8.9% oracle
12689 oracle 685M 658M sleep 59 0 0:44:51 0.5% oracle
2052 oracle 687M 650M sleep 59 0 0:06:17 0.5% oracle

Neste caso é melhor investigar primeiramente o processo 11239 porque seu tempo de processamento esta muito alto. Agora que já sabemos identificar se a maquina esta com processamento elevado e o processo conhecido como “Top CPU Consumer”, podemos investigá-los no banco.Vamos trabalhar com o PID 11238 para os exemplos a seguir.Utilizando o SQL*Plus, vamos primeiramente descobrir mais sobre esta sessão no banco de dados com a seguinte query:

SQL> SELECT SID, serial#, username, program, sql_hash_value, osuser,machine, taddr,lockwait, statusFROM v$sessionWHERE paddr = (SELECT addrFROM v$processWHERE spid = 11238);

Principais campos:
Sid = Utilizado para join com outras tabelas.
Sql_hash_value = Id da query que esta sendo executada Osuser,
machine = Ajuda na identificação de qual usuário esta conectado e consumindo recursos

.Note que a coluna spid da tabela v$process é o PID do SO. Agora que sabemos qual é a sessão no banco que esta consumindo mais recursos, é possível rastrear todo o restante da sessão. Para verificar a query que esta sendo executada pela sessão que mais esta consumindo CPU, execute a seguinte query:

SQL> select sql_text from v$sql Where hash_value = HASH_VALUE

Este parâmetro HASH_VALUE vem da query anterior executada para obter as informações sobre a sessão do usuário. É possível também obter os dados sobre a execução da query com a seguinte query:

SQL> select EXECUTIONS, LOADS, INVALIDATIONS, PARSE_CALLS, DISK_READS, BUFFER_GETS, ROWS_PROCESSED
From v$sql Where hash_value = HASH_VALUE;

Fique preocupado se algum destes campos vier com valor alto. Como já foi obtido a query, você agora pode fazer um explain para verificar se algo precisa ser reescrito ou se é preciso criar algum índice. Uma das views mais importantes do banco para realizar tuning é a v$session_wait.

Eu não vou entrar em muitos detalhes de como utiliza de forma efetiva neste artigo, mas como foi obtido o SID na primeira query sobre os detalhes sessão, verifique qual “EVENT” esta ocorrendo para esta sessão. Pode ser bem eficiente. Segue exemplo:

SQL> select sid,event,p1,p2,p3 from v$session_wait where sid = SID;

Nem sempre a query/sessão que esta consumindo mais recursos de CPU é a responsável por problemas de desempenho no banco. Lembre-se de que um servidor de banco tem fatores muito importantes como acesso a memória e principalmente disco. Vamos ver isso nas próximas postagens