Aula 6 - Monitoração e Gerencimento de Processo no Oracel

Prévia do material em texto

/
Administração de Banco de Dados III
Aula 6: Monitoração e Gerenciamento de Processos no Oracle
Apresentação
Esta sexta aula da disciplina Administração de Bancos de Dados III aborda o gerenciamento de processos como tema
central, incluindo a monitoração da atividade do banco de dados, manutenção das sessões dos usuários, visualização de
registros da atividade dos processos e tratamento de eventos de espera.
Ao �nal desta aula você será capaz de utilizar ferramentas do Oracle para construir monitorações e�cientes dos recursos
do banco de dados e das cargas de trabalho submetidas a ele. Será capaz de compreender também a diferença entre
processos e sessões, e realizar simulação e testes de carga em suas instâncias Oracle.
Objetivo
Aplicar ferramentas e técnicas para monitoração da instância Oracle e da atividade dos processos em execução;
Discutir os aspectos correspondentes a processos e sessões, e utilizar ferramentas para visualizar suas informações;
Usar ferramentas para simular a execução de cargas de trabalho e monitorar os comandos SQL submetidos, bem
como a atividade das sessões correspondentes.
Introdução
Ao receber solicitações de consultas e atualizações, o Oracle deve realizar duas tarefas:
Processar as instruções SQL
submetidas.
Enviar os resultados aos clientes
correspondentes.
Para tratar essa carga de trabalho, processos são criados na instância e atravessam uma série de estados, até que seu
trabalho esteja concluído e o cliente tenha recebido o resultado.
/
Enquanto processos são executados, os recursos do banco de dados
são consumidos. Recursos como CPU e memória são distribuídos
entre os processos e cabe ao administrador do banco de dados
monitorar esse consumo e atuar em caso de incidentes, garantindo
que todos os processos sejam executados.
Para isso, o Oracle oferece ferramentas de trabalho para os administradores. Entre elas, estão a captura automática de
informações sobre os eventos, processos e sessões de usuários. Essas informações capturadas formam a base da
monitoração de uma instância Oracle e o principal repositório de investigação de incidentes e problemas para os DBAs Oracle.
Alert Log
A atividade dos processos executados em uma instância Oracle gera uma série de mensagens durante a execução, incluindo o
log das execuções, mensagens de erro e mensagens de aviso. Por meio do Alert Log, o administrador pode ler essas
mensagens e identi�car as várias etapas na execução dos processos, constituindo-se na principal fonte de informações da
inicialização e do shutdown do banco de dados, monitoração da instância do banco de dados e identi�cação de problemas
durante a execução de um processo de sistema ou de usuário.
O log de alertas pode ser visualizado diretamente nos arquivos gerados pelo Oracle, pelo Enterprise Manager, ou ainda pelo
utilitário adrci.
Saiba mais
De fato, o Alert Log é um subsistema do ADR (Automatic Diagnostic Repository), um repositório completo de toda a atividade da
instância, que inclui a carga de trabalho submetida ao banco de dados, as consultas e as atualizações, além de informações
sobre desempenho que utilizaremos na Aula de Tuning.
Para veri�car onde está o seu log de alertas, consulte a visão v$diag_info, conforme a imagem a seguir.
/
 Fonte: Oracle (2020).
Figura 1 - Log de Alertas
O resultado da consulta mostra os componentes do ADR, além dos contadores de problems e incidents. Veja a pasta
correspondente a Diag Trace e navegue até lá. Você pode ver os arquivos de trace .trc e .trm, além do arquivo .log.
 Fonte: O autor.
Figura 2 - Pasta do arquivo de log
Cada par de arquivos .trc e .trm representa a coleta de um processo, sendo que o formato do nome do arquivo é a composição
da identi�cação da instância (SID), do nome do processo e do número do processo no sistema operacional. Abra o arquivo .log
para visualizar todas as mensagens.
/
 Fonte: O autor.
Figura 3 - Log de alertas
Você pode ver todas as mensagens desde a inicialização do Oracle, a ativação dos processos em background, a referência aos
data�les em uso pela instância e eventuais mensagens de erro. Quando uma instância falha na inicialização, por exemplo, o
administrador abre esse arquivo para procurar pelas mensagens de erro. Veja a seguir os registros correspondentes à alteração
do parâmetro db_create_�le_dest e à criação do tablespace CRM que �zemos na aula passada.
 Fonte: O autor.
Figura 4 - Log do Create Tablespace
ADR – Automatic Diagnostic Repository
Uma outra forma de visualizar os registros é a partir do utilitário adrci. Acesse o utilitário pela linha de comando e visualize suas
opções pelo comando help.
/
 Fonte: O autor.
Figura 5 - Log do Create Tablespace
Show Alert é um dos comandos do utilitário. Ao executá-lo, você pode escolher entre as alternativas de diagnóstico. A que
corresponde ao listener mostra a atividade de processo de conexão de clientes da instância. Veja a seguir um trecho deste log.
 Fonte: O autor.
Figura 6 - Log do Create Tablespace
O administrador pode encontrar nesse log todas as conexões realizadas a partir das strings de conexão correspondentes. Veja
a conexão do SQL Developer a XEPDB1. Pode ver também as conexões encaminhadas (handoff) pelo listener e realizadas pelo
dispatcher. Um código de retorno 0 representa sucesso na conexão; caso contrário, um código de erro é mostrado.
Enterprise Monitoring
/
Outra ferramenta de trabalho do administrador é a monitoração oferecida pelo Enterprise Manager, sendo que a manutenção
do consumo de recursos é uma tarefa frequente do DBA Oracle. Veja a seguir um comportamento comum da monitoração
quando ocorre uma carga de dados na instância. Inicialmente, o consumo dos recursos está baixo e, tão logo seja iniciada a
execução da carga, é possível veri�car o incremento no consumo dos recursos. Veja na Figura 7 o momento em que a carga
iniciou sua execução.
 Fonte: Oracle (2020).
Figura 7 - Consumo de recursos
As informações mostradas são relevantes para o administrador na medida
em que elas podem sinalizar variações na disponibilidade dos recursos e/ou
no desempenho da instância.
Nos exemplos a seguir é possível observar um aumento no consumo de CPU e um aumento no tempo de espera das sessões
ativas. A conjunção dos dois indicadores mostra que o processamento que iniciou a execução gerou uma contenção na
instância, indicada pelo aumento no tempo de espera.
 Fonte: Oracle (2020).
Figura 8 - Aumento no consumo de CPU. 
/
 Fonte: Oracle (2020).
Figura 9 - Aumento no tempo de espera
Ainda no Enterprise Manager, o administrador tem a funcionalidade de Marcas D’agua, através da qual podem ser visualizados
os valores máximos registrados para determinados recursos e os últimos valores encontrados.
 Fonte: Oracle (2020).
Figura 10 - Marcas D’agua
Na primeira linha temos o número máximo de sessões ativas na instância. Veja que o maior valor é 24 e o último valor também,
sinalizando para o administrador que, naquele momento, a instância está recebendo um número maior de conexões do que já
tinha sido visto. Essa informação combinada com outras pode requerer maior atenção da equipe de monitoração e dos DBAs.
Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online
Processos
Agora que já sabemos como monitorar os processos e rastrear sua atividade por meio dos logs, vamos compreender os
estados pelos quais ele pode passar durante a execução.
/
 Fonte: Oracle (2020).
Figura 11 - Estados dos processos
Clique nos botões para ver as informações.
Quando um processo está pronto para execução em memória, mas ainda não está associado a uma CPU, dizemos que
ele está runnable in memory. Nesse estado, ele aguarda a sua vez, quando então receberá um tempo de CPU (time slice)
para executar. Nesse momento, ele passa para o estado running e permanecerá assim até que utilize todo o seu tempo ou
termine a execução. No caso de ser consumido o seu tempo de execução, ele volta para a �la runnable in memory.
A preempção ocorre quando o processoé interrompido para dar lugar a outro, seja porque terminou seu time slice ou
porque um processo com uma prioridade mais alta chegou à �la runnable in memory.
Runnable in memory e Running 
Um processo também sai do estado running quando precisa aguardar por um evento externo, como uma leitura de disco.
Nesse caso, ele passa para o estado sleeping e permanece nele até que o evento externo seja �nalizado, quando então
acorda (wake up) e volta para o estado running in memory, aguardando novamente sua vez para entrar em execução.
Sleeping in memory 
Existem ainda as situações nas quais o espaço de endereços do processo na memória sofre swap, isto é, é transferido
para memória secundária (área de swap). Quando o swap ocorre e o processo está na �la running in memory, dizemos
que ele passa para o estado runnable swapped. Se o swap ocorre quando o processo está aguardando algum evento
externo, então ele passa para o estado sleeping swapped. Nesse caso, ao acordar, ele passa para o estado runnable
swapped e aguarda o swap in, evento no qual o espaço de endereços do processo retorna para memória e ele volta ao
estado running in memory.
Runnable swapped e runnable swapped 
Para visualizar os processos em execução na sua instância, você pode consultar a visão v$process. Veja na Figura 12. Cada
processo possui um PID e um SPID:
/
1
PID
A Identi�cador do processo do Oracle.
2
SPID
Identi�cador do processo no sistema operacional. Você vai ver
também o username, nome do usuário que iniciou a execução
do processo, e o serial# (número serial do processo).
Agora que já sabemos como monitorar os processos e rastrear sua atividade por meio dos logs, vamos compreender os
estados pelos quais ele pode passar durante a execução.
 Fonte: Oracle (2020).
Figura 12 - Lista dos processos
Os processos listados na v$process incluem os processos em background, isto é, processos da instância Oracle, como os que
vimos na Aula de Arquitetura, como PMON e SMON. Os processos com a coluna background = NULL são aqueles associados a
sessões de usuários. No Oracle, um processo pode estar associado a várias sessões de usuários.
Sessões
Quando você se conecta ao Oracle, uma sessão é criada. A partir de uma sessão você pode executar códigos SQL e, enquanto
esses comandos estão sendo executados, sua sessão está ativa e associada a um processo do Oracle. Para visualizar as
sessões existentes na sua instância, você pode usar a visão v$session. Veja na imagem a seguir.
/
 Fonte: Oracle (2020).
Figura 13 - Lista das sessões
Observe que cada sessão possui um SID, identi�cador da sessão, e um serial#, número serial da sessão. O status da sessão
pode ser ativa ou inativa:
Ativa
Quando um código SQL está
sendo executado.
Inativa
Quando não há uma execução
em andamento.
Comentário
Em algumas situações o administrador pode escolher terminar uma sessão, seja porque ela está gerando alguma contenção não
desejável ou porque está executando há muito tempo, ou, ainda, porque está consumindo recursos em demasia. Nesse caso, o
status passa a ser killed.
O comando a seguir termina a sessão com identi�cador SID e número serial#.
 
ALTER SYSTEM KILL SESSION ‘SID, serial#’;
Observe que cada sessão possui um SID, identi�cador da sessão, e um serial#, número serial da sessão. O status da O
comportamento padrão de uma sessão que é terminada com o comando anterior é aguardar as operações necessárias para o
término, como o rollback de uma transação. Ao adicionar a cláusula IMMEDIATE ao comando, o controle é retornado
imediatamente para a sessão, sem esperar o término do Kill.
/
 
ALTER SYSTEM KILL SESSION ‘SID, serial#’ IMMEDIATE;
Existem outras opções para gerenciamento de sessões como DISCONNECT SESSION ou CANCEL SQL. O administrador decide
pela melhor abordagem a partir da análise de informações sobre a sessão como seu estado, tipo de espera e seus tempos,
entre outros. Para visualizar essas informações você pode fazer um join da v$session com a v$session_wait, conforme a seguir.
 
SELECT s.sid, s.serial#, sw.state, sw.event, sw.wait_class, sw.wait_time, sw.seconds_in_wait
FROM v$session s INNER JOIN v$session_wait sw ON (sw.sid = s.sid)
WHERE username IS NOT NULL
ORDER BY sw.seconds_in_wait DESC;
Vamos ver um exemplo. Considere o loop a seguir, que insere linhas nas tabelas Usuarios e Usuarios_Historico.
 Fonte: Oracle (2020).
Figura 14 - Loop para Inserção de linhas
Durante a operação do banco de dados, você pode checar os comandos SQL em execução na instância e os recursos do
sistema que estão sendo utilizados.
Como são, normalmente, centenas ou milhares de sessões ativas
simultaneamente, listar todas as instruções SQL em execução não faz muito
sentido. Você deve �ltrar essa listagem pelas colunas disponíveis, como
buffer_gets.
No exemplo a seguir queremos visualizar as instruções em execução que mais utilizam os buffers em memória.
/
 Fonte: Oracle (2020).
Figura 15 - Uso de v$sqlarea
Na Figura 16 estamos �ltrando as linhas de v$sqlarea por disk_reads. Então, as instruções SQL mostradas são aquelas que
mais realizam leituras de disco, uma das referências mais importantes para administradores de SGBDs.
Saiba mais
Veja em https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2129.htm todas as colunas de v$sqlarea.
javascript:void(0);
/
 Fonte: Oracle (2020).
Figura 16 - Uso de v$sqlarea
Os números que vimos nas �guras 15 e 16 estão altos ou baixos?
Clique nos botões para ver as informações.
O DBA precisa manter registros regulares desses valores. É preciso monitorar e guardar as medições. Somente com
essas informações você vai poder dizer se os números observados em determinado momento estão mais altos do que o
padrão normal de comportamento das suas instâncias.
Ver a resposta 
Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online
Simulação e testes de carga
Cargas de trabalho apresentam variações ao longo do tempo. Seja por sazonalidades associadas à atividade das aplicações
que utilizam os bancos de dados ou por novas tendências, administradores precisam estar preparados. Uma das técnicas
frequentemente utilizadas é a realização de simulações que produzam algum tipo de comportamento esperado. Para realizá-
las, vamos contar com os artefatos a seguir.
executa_simulacao.cmd
executa_atualizacao_usuarios.cmd
atualiza_usuarios.sql
Você pode ver os códigos dos scripts adiante.
/
O primeiro script é o que inicia a
simulação. No código, você pode
ver que ele tem um loop para
iniciar 50 execuções do segundo
script.
O segundo script utiliza o sqlplus
para conectar-se à instância e
executar o script SQL.
E o script SQL executa os
comandos SQL da simulação.
No exemplo a seguir temos um loop para executar atualizações na tabela Usuarios.
 Fonte: O autor.
Figura 17 - Simulação
Ao disparar a execução do primeiro script, 50 janelas são abertas e, em cada uma delas, é disparada a execução do código
SQL. Na instância, o que podemos ver são 50 sessões de usuários, todas elas competindo pela escrita na tabela.
/
 Fonte: O autor.
Figura 18 - Uso de v$sqlarea
Enquanto estão em execução, cada update deve aguardar sua vez para executar. É nesse momento que o administrador faz as
observações sobre o consumo dos recursos e o impacto da carga na instância. Conforme os updates vão terminando, as
sessões no Oracle vão sendo desconectadas por conta do echo exit no pipe do sqlplus. Ao término de todos os updates, já não
há mais sessões ativas e a simulação está completa.
Vamos praticar.
Atividade
Execute a simulação proposta em sua instância. Você pode fazer alguns experimentos interessantes, como aumentar o
número de execuções disparadas ou modi�car o código SQL, incluindo a criação de novos códigos para execução em
paralelo. Enquanto a carga de trabalho estiver em execução, monitore a atividade na instância por meio das ferramentas
que vimos até aqui, recuperando as seguintes informações:
Painel de consumo de recursos no Enterprise Manager.
Listagem dassessões ativas e dos processos associados.
Instruções SQL mais executadas na instância.
Consultas com maior número de acessos a buffers.
Consultas com maior número de leituras em disco.
Consultas com maior tempo de espera.
Após a execução da carga de trabalho, procure os logs do Oracle que evidenciam a execução da carga de trabalho. Você
pode ver as mensagens relacionadas a essa execução?
/
Conclusão
Monitorar e medir são duas das atividades mais importantes para um administrador Oracle. Compreender o comportamento
de suas instâncias e saber de antemão quando algo não vai bem é uma vantagem que você, como DBA, precisa conquistar. E
não é uma tarefa difícil porque o próprio Oracle fornece todas as ferramentas que você precisa para registrar as informações
mais importantes para essas medições.
Nesta aula vimos como utilizar o Alert Log, o
utilitário adrci e o Enterprise Manager para
monitorar e visualizar as informações sobre o
consumo de recursos da instância Oracle e
sobre a atividade das sessões dos usuários.
Ao consultar os logs do SGBD, o
administrador pode encontrar as evidências
de incidentes e problemas na instância. As
mensagens encontradas nos logs são as
dicas para as suas investigações.
Além dos logs, o Oracle possui ferramentas
de trabalho que permitem ao administrador
observar a execução dos processos em
tempo real. Entre as ferramentas disponíveis
estão a visualização dos comandos SQL em
execução e os tempos de espera associados
aos processos da instância. Com essas
informações, o administrador pode monitorar
a atividade das sessões e manobrar os
recursos e processos de forma a garantir que
todos os usuários recebam os resultados
esperados.
Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online


/
Atividade
1. Aplicar ferramentas e técnicas para monitoração da instância Oracle e da atividade dos processos em execução.
Assinale a alternativa incorreta a respeito da monitoração de instâncias Oracle.
a) A partir do utilitário adrci é possível listar incidentes e problemas encontrados na instância e visualizar a relação entre eles.
b) Podemos encontrar os comandos DDL executados na instância nos traces do Alert Log.
c) É possível observar o aumento do consumo de CPU de uma instância a partir do painel de monitoração do Enterprise Manager.
d) Pelo Enterprise Manager o administrador pode saber quando há aumento geral nos tempos de espera das sessões dos usuários.
e) A partir do ADR o administrador pode gerenciar todas as políticas de segurança da instância Oracle, incluindo a criação de profiles e
roles.
2. Discutir os aspectos correspondentes a processos e sessões e utilizar ferramentas para visualizar suas informações.
Entre as consultas a seguir, que buscam recuperar informações sobre processos e sessões no Oracle, assinale a que está
incorreta.
a) SELECT s.sid, s.serial#, p.spid, s.program 
FROM v$process p 
INNER JOIN v$session s ON (s.paddr = p.addr) 
WHERE s.status = 'ACTIVE' 
ORDER BY s.sid; 
b) SELECT p.spid, s.sid, s.serial#, 
SUBSTR(sa.sql_text, 1, 600) current_sql 
FROM v$process p 
INNER JOIN v$session s ON (s.paddr = p.addr) 
INNER JOIN v$sqlarea sa ON (s.sql_address = sa.address 
AND s.sql_hash_value = sa.hash_value) 
WHERE s.status = 'ACTIVE' 
ORDER BY s.sid; 
c) SELECT * FROM 
(SELECT substr(sql_text,1,40) sql, 
disk_reads, executions, disk_reads/executions "Reads/Exec" 
FROM v$sqlarea 
WHERE disk_reads > 1000 
ORDER BY disk_reads DESC) 
WHERE rownum <=10; 
d) SELECT p.spid, s.sid, s.serial#, 
s.sqltext 
FROM v$process p 
INNER JOIN v$session s ON (s.paddr = p.addr) 
WHERE s.status = 'ACTIVE' 
ORDER BY s.sid; 
e) SELECT s.sid, s.serial#, to_char(s.logon_time,'dd/mm/yyyy hh24:mi:ss') slogon_time, 
p.spid, s.program, SUBSTR(sa.sql_text, 1, 600) current_sql
FROM v$process p 
INNER JOIN v$session s ON (s.paddr = p.addr) 
INNER JOIN v$sqlarea sa ON (s.sql_address = sa.address 
AND s.sql_hash_value = sa.hash_value) 
WHERE s.status = 'ACTIVE' 
ORDER BY s.sid; 
/
3. Usar ferramentas para simular a execução de cargas de trabalho e monitorar os comandos SQL submetidos, bem como a
atividade das sessões correspondentes.
Ao processar cargas de trabalho simultâneas, o Oracle monitora a atividade das sessões e gera registros da atividade dos
processos no ____. O administrador Oracle, por sua vez, deve monitorar essas sessões através das visões ____ e ____. Se for
necessário, pode consultar a visão ____ para veri�car o código SQL que está sendo executado e estatísticas do uso de recursos
por essas sessões, como ____ e ____.
a) Alert Log – v$process – v$monitor – v$sqlarea – buffer gets – disk writes
b) Data Monitor – v$process – v$session – v$sql – buffer gets – disk reads
c) Alert Log – v$process – v$session – v$sqlarea – buffer gets – disk reads
d) Data Monitor – v$monitor – v$session – v$sql – buffer gets – disk writes
e) Alert Log – v$monitor – v$session – v$sql – buffer gets – disk reads
Notas
Referências
ADRCI - Automatic Diagnostic Repository Command Interpreter. Recursos Técnicos da Oracle. Disponível em:
https://www.oracle.com/br/technical-resources/articles/database-performance/automatic-diagnostic-repository.html. Acesso
em: 10 set. 2020.
ENTERPRISE Monitoring. Oracle. Disponível em: https://www.oracle.com/manageability/enterprise-
manager/technologies/enterprise-monitoring.html. Acesso em: 8 set. 2020.
KILLING Oracle Sessions (ALTER SYSTEM KILL/DISCONNECT SESSION). Oracle-Base. Disponível em: https://oracle-
base.com/articles/misc/killing-oracle-sessions. Acesso em: 5 set. 2020.
PROBLEM Management. Oracle Help Center. Disponível em:
https://docs.oracle.com/cd/E18440_01/doc.111/e18415/chapter_problems.htm. Acesso em: 10 set. 2020.
PUGA, S.; FRANÇA, E.; GOYA, M., 2013. Banco de Dados: implementação em SQL, PL/SQL e Oracle 11g. São Paulo: Pearson.
332 p.
RAMAKHRISHNAN, R.; GEHRKE, J., 2002. Database Management Systems. 3rd. edition. New York: McGraw-Hill. 1098p.
USING Incident Management. Oracle Help Center. Disponível em:
https://docs.oracle.com/cd/E24628_01/doc.121/e24473/incident_mgmt.htm. Acesso em: 10 set. 2020.
VIEWING the Alert Log. Oracle Help Center. Disponível em:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/diag005.htm. Acesso em: 18 set. 2020.
V$PROCESS. Oracle Help Center. Disponível em:
https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2022.htm. Acesso em: 5 set. 2020.
V$SESSION. Oracle Help Center. Disponível em:
https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2088.htm. Acesso em: 5 set. 2020.
Próxima aula
Transações;
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
/
Níveis de isolamento de transações;
Bloqueios.
Explore mais
LOWI (Oracle Wait Interface) é um conjunto de visões do Oracle que trazem informações sobre os eventos e os tempos de
espera registrados na instância do banco de dados. Nós utilizamos a v$session_wait durante a aula, mas existem muitas
outras.
No endereço a seguir você vai encontrar um walkthrough bem construído para utilizar o OWI. Explore os resultados que essas
visões trazem e incorpore-as à sua caixa de ferramentas de gerenciamento de processos.
https://www.oracle.com/br/technical-resources/articles/database-performance/oracle-wait-interface-owi.html
javascript:void(0);