Olá a todos. Nesta segunda parte, falarei um pouco sobre como otimizar o uso das estruturas de memória do Oracle, afim de obter o máximo destas. Lembrando que essas informações serão voltadas exclusivamente para o Oracle 11gR2.

 

1 – SHARED POOL

Existem algumas queries úteis para ajudar a determinar se a Shared Pool está subdimensionado. Vale lembrar que uma shared Pool subdimensionada pode causar um elevado número de hard parses (Library Cache Miss) o que é ruim para a performance geral do banco de dados.

Abaixo é possível ver uma query simples que pode ajudar a determinar se a Shared Pool está subdimensionada. Caso a coluna “request_failures” esteja maior que 0, e estiver crescendo continuamente, isso pode indica que a Shared Pool esta subdimensionada:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFJFUVVFU1RfTUlTU0VTLApSRVFVRVNUX0ZBSUxVUkVTCkZST00gViRTSEFSRURfUE9PTF9SRVNFUlZFRDs=” hover_enabled=”0″]U0VMRUNUIFJFUVVFU1RfTUlTU0VTLApSRVFVRVNUX0ZBSUxVUkVTCkZST00gViRTSEFSRURfUE9PTF9SRVNFUlZFRDs=[/et_pb_dmb_code_snippet]

A Shared Pool possui uma estrutura interna, chamada de Shared Pool Reserved Size, que é reservado para grandes requisições contínuas. É usada para evitar degradação de performance no caso de a fragmentação na Shared Pool forçar o Oracle a buscar pedaços não usados da Shared pool para satisfazer a requisição corrente. Seu default é 5% da SHARED_POOL_SIZE. A view V$SHARED_POOL_RESERVED contém informações úteis sobre ela. Para interpretar se a Shared Pool Reserved Size precisa ser redimensionada, basta executar a query acima, a interpretando da seguinte forma:

– SHARED_POOL_RESERVED_SIZE pequena demais: Quando a coluna REQUEST_FAILURES for maior que zero e estiver crescendo constante,
aumentar a SHARED_POOL_RESERVED_SIZE e a SHARED_POOL_SIZE proporcionalmente. Isso indica que a SHARED_POOL_SIZE também está subdimensionada.

– SHARED_POOL_RESERVED_SIZE grande demais: Quando a coluna REQUEST_MISSES for 0 ou não estiver aumentando ou quando a coluna
FREE_SPACE for maior ou igual a 50% do tamanho mínimo da SHARED_POOL_RESERVED_SIZE.

A Library Cache, que faz parte da Shared Pool, também é passiva de otimizações. Essa estrutura interna armazena os SQLs que já sofreram o parse, planos de execução, blocos PL/SQL e classes Java. A query abaixo pode ser usada para monitorar a Library Cache (atentar para as colunas RATIO):

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIE5BTUVTUEFDRSwKR0VUUywKUkVMT0FEUywKUElOUywKSU5WQUxJREFUSU9OUywKUk9VTkQoR0VUSElUUkFUSU8gKiAxMDAsIDIpIEdFVEhJVFJBVElPLApST1VORChQSU5ISVRSQVRJTyAqIDEwMCwgMikgUElOSElUUkFUSU8KRlJPTSBWJExJQlJBUllDQUNIRTs=” hover_enabled=”0″]U0VMRUNUIE5BTUVTUEFDRSwKR0VUUywKUkVMT0FEUywKUElOUywKSU5WQUxJREFUSU9OUywKUk9VTkQoR0VUSElUUkFUSU8gKiAxMDAsIDIpIEdFVEhJVFJBVElPLApST1VORChQSU5ISVRSQVRJTyAqIDEwMCwgMikgUElOSElUUkFUSU8KRlJPTSBWJExJQlJBUllDQUNIRTs=[/et_pb_dmb_code_snippet]

Invalidações ocorrem quando o objeto referenciado pela query dentro da Library Cache sofre alguma alteração (adição de colunas, grants, criação de indices e etc). Portanto, recomenda-se evitar, o quanto possível, alterações estruturais em objetos frequentemente acessados, pois isso invalida a query que já sofreu o parse, sendo que na próxima execução desta query, um hard parse será necessário.

Outra métrica relativamente útil, relacionada a Library Cache, é seu hit ratio. Eu mencionei “relativamente” porque o hit ratio não é uma indicação fiel de que algo está errado ou 100% otimizado. Já vi casos de bases com um hit ratio baixo, mas sem problemas aparentes, portanto, usem essa métrica juntamente com outras análises. Para mensurar o hit ratio do Library Cache:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFNVTShQSU5TKSAiRVhFQ1VUSU9OUyIsClNVTShSRUxPQURTKSAiQ0FDSEUgTUlTU0VTIFdISUxFIEVYRUNVVElORyIsClNVTShQSU5TIC0gUkVMT0FEUykqMTAwL1NVTShQSU5TKSBhcyBoaXRfcmF0aW8KRlJPTSBWJExJQlJBUllDQUNIRTs=” hover_enabled=”0″]U0VMRUNUIFNVTShQSU5TKSAiRVhFQ1VUSU9OUyIsClNVTShSRUxPQURTKSAiQ0FDSEUgTUlTU0VTIFdISUxFIEVYRUNVVElORyIsClNVTShQSU5TIC0gUkVMT0FEUykqMTAwL1NVTShQSU5TKSBhcyBoaXRfcmF0aW8KRlJPTSBWJExJQlJBUllDQUNIRTs=[/et_pb_dmb_code_snippet]

Por fim, a Shared Pool possui um Advisor, como qualquer outra estrutura.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFNIQVJFRF9QT09MX1NJWkVfRk9SX0VTVElNQVRFIEFTIEVTVElNQVRFX1NJWkUsClNIQVJFRF9QT09MX1NJWkVfRkFDVE9SIEFTIEZBQ1RPUiwKRVNURF9MQ19USU1FX1NBVkVEIEFTIFRJTUVfU0FWRUQsCkVTVERfTENfTE9BRF9USU1FIEFTIExPQURfVElNRQpGUk9NIFYkU0hBUkVEX1BPT0xfQURWSUNFOw==” hover_enabled=”0″]U0VMRUNUIFNIQVJFRF9QT09MX1NJWkVfRk9SX0VTVElNQVRFIEFTIEVTVElNQVRFX1NJWkUsClNIQVJFRF9QT09MX1NJWkVfRkFDVE9SIEFTIEZBQ1RPUiwKRVNURF9MQ19USU1FX1NBVkVEIEFTIFRJTUVfU0FWRUQsCkVTVERfTENfTE9BRF9USU1FIEFTIExPQURfVElNRQpGUk9NIFYkU0hBUkVEX1BPT0xfQURWSUNFOw==[/et_pb_dmb_code_snippet]

2 – LOG BUFFER

O Log Buffer geralmente possui um tamanho bem menor em relação as outras estruturas de memória do banco, mas também possui um papel importante no assunto performance. Algumas recomendações básicas para otimizar o uso dessa estrutura de meória são:

– Realizar COMMITs em lote

– Usar o NOLOGGING para operações regulares, evitando a geração de REDO. Porém isso torna as transações irrecuperáveis na eventualidade de um crash da instância. Usar essa opção com cautela.

Para monitorar o Log Buffer, afim de identificar se é necessário o seu aumento, executar a query abaixo. Caso o valor do “redo buffer allocation retries” esteja aumentando gradativamente, isso pode indica que o Log Buffer está subdimensionado, e como consequência disso, processos estão esperando para escrever os
redo log entries no Log Buffer.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIE5BTUUsClZBTFVFCkZST00gViRTWVNTVEFUCldIRVJFIE5BTUUgPSAncmVkbyBidWZmZXIgYWxsb2NhdGlvbiByZXRyaWVzJzs=” hover_enabled=”0″]U0VMRUNUIE5BTUUsClZBTFVFCkZST00gViRTWVNTVEFUCldIRVJFIE5BTUUgPSAncmVkbyBidWZmZXIgYWxsb2NhdGlvbiByZXRyaWVzJzs=[/et_pb_dmb_code_snippet]

3 – BUFFER CACHE

Para melhorar o uso do Buffer Cache, não basta apenas aumentar seu tamanho. A principal atitude a ser tomada é a otimização das queries do banco de dados, principalmente aquelas que são executadas com muita frequência.

É possível mensurar o ratio do Buffer cache. Esse ratio mede a taxa em que o Oracle encontra os datablocks no buffer cache. Caso esteja muito baixo, isso pode indicar um subdimensionamento do Buffer Cache. Mara obter esse ratio, além de outras informações:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFBSLlZBTFVFIEFTICJwaHkuIHJlYWRzIiwKUFJELlZBTFVFIEFTICJwaHkuIHJlYWRzIGRpcmVjdCIsClBSREwuVkFMVUUgQVMgInBoeS4gcmVhZHMgZGlyZWN0IChsb2IpIiwKU0xSLlZBTFVFIEFTICJzZXNzaW9uIGxvZ2ljYWwgcmVhZHMiLAooMSAtIChQUi5WQUxVRSAtIFBSRC5WQUxVRSAtIFBSREwuVkFMVUUpIC8gU0xSLlZBTFVFKSoxMDAgQVMgImhpdCByYXRpbyIKRlJPTSBWJFNZU1NUQVQgUFIsIFYkU1lTU1RBVCBQUkQsIFYkU1lTU1RBVCBQUkRMLCBWJFNZU1NUQVQgU0xSCldIRVJFIFBSLk5BTUUgPSAncGh5c2ljYWwgcmVhZHMnCkFORCBQUkQuTkFNRSA9ICdwaHlzaWNhbCByZWFkcyBkaXJlY3QnCkFORCBQUkRMLk5BTUUgPSAncGh5c2ljYWwgcmVhZHMgZGlyZWN0IChsb2IpJwpBTkQgU0xSLk5BTUUgPSAnc2Vzc2lvbiBsb2dpY2FsIHJlYWRzJzs=” hover_enabled=”0″]U0VMRUNUIFBSLlZBTFVFIEFTICJwaHkuIHJlYWRzIiwKUFJELlZBTFVFIEFTICJwaHkuIHJlYWRzIGRpcmVjdCIsClBSREwuVkFMVUUgQVMgInBoeS4gcmVhZHMgZGlyZWN0IChsb2IpIiwKU0xSLlZBTFVFIEFTICJzZXNzaW9uIGxvZ2ljYWwgcmVhZHMiLAooMSAtIChQUi5WQUxVRSAtIFBSRC5WQUxVRSAtIFBSREwuVkFMVUUpIC8gU0xSLlZBTFVFKSoxMDAgQVMgImhpdCByYXRpbyIKRlJPTSBWJFNZU1NUQVQgUFIsIFYkU1lTU1RBVCBQUkQsIFYkU1lTU1RBVCBQUkRMLCBWJFNZU1NUQVQgU0xSCldIRVJFIFBSLk5BTUUgPSAncGh5c2ljYWwgcmVhZHMnCkFORCBQUkQuTkFNRSA9ICdwaHlzaWNhbCByZWFkcyBkaXJlY3QnCkFORCBQUkRMLk5BTUUgPSAncGh5c2ljYWwgcmVhZHMgZGlyZWN0IChsb2IpJwpBTkQgU0xSLk5BTUUgPSAnc2Vzc2lvbiBsb2dpY2FsIHJlYWRzJzs=[/et_pb_dmb_code_snippet]

Essa estrutura de memória também possui um advisor, com possíveis recomendações de redimensionamento:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIHNpemVfZm9yX2VzdGltYXRlLApzaXplX2ZhY3RvciwKYnVmZmVyc19mb3JfZXN0aW1hdGUsCmFicygoKGVzdGRfcGh5c2ljYWxfcmVhZF9mYWN0b3IpKjEwMCktMTAwKSBhcyBwY3RfZ2FuaG9fcGh5c19yZWFkcywKZXN0ZF9waHlzaWNhbF9yZWFkcwpGUk9NIFYkREJfQ0FDSEVfQURWSUNFCldIRVJFIG5hbWUgICAgICAgICAgPSAnREVGQVVMVCcKQU5EIGJsb2NrX3NpemUgICAgPSAoU0VMRUNUIHZhbHVlIEZST00gViRQQVJBTUVURVIgV0hFUkUgbmFtZSA9ICdkYl9ibG9ja19zaXplJykKQU5EIGFkdmljZV9zdGF0dXMgPSAnT04nOw==” hover_enabled=”0″]U0VMRUNUIHNpemVfZm9yX2VzdGltYXRlLApzaXplX2ZhY3RvciwKYnVmZmVyc19mb3JfZXN0aW1hdGUsCmFicygoKGVzdGRfcGh5c2ljYWxfcmVhZF9mYWN0b3IpKjEwMCktMTAwKSBhcyBwY3RfZ2FuaG9fcGh5c19yZWFkcywKZXN0ZF9waHlzaWNhbF9yZWFkcwpGUk9NIFYkREJfQ0FDSEVfQURWSUNFCldIRVJFIG5hbWUgICAgICAgICAgPSAnREVGQVVMVCcKQU5EIGJsb2NrX3NpemUgICAgPSAoU0VMRUNUIHZhbHVlIEZST00gViRQQVJBTUVURVIgV0hFUkUgbmFtZSA9ICdkYl9ibG9ja19zaXplJykKQU5EIGFkdmljZV9zdGF0dXMgPSAnT04nOw==[/et_pb_dmb_code_snippet]

4 – PGA

A PGA tem papel importante na performance de queries do banco de dados, principalmente aquelas queries que realizam muitos sorts e hashing. Uma query que pode fornecer uma visão geral de como a PGA está, segue a seguir:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIE5BTUUsClJPVU5EKFZBTFVFLzEwMjQvMTAyNCwgMikgfHwnIE1CcycgQVMgVkFMT1IKRlJPTSBWJFBHQVNUQVQKV0hFUkUgTkFNRSBJTiAoJ2FnZ3JlZ2F0ZSBQR0EgdGFyZ2V0IHBhcmFtZXRlcicsICd0b3RhbCBQR0EgYWxsb2NhdGVkJywgJ3RvdGFsIFBHQSBpbnVzZScpClVOSU9OClNFTEVDdCAnUEdBIGNhY2hlIGhpdCBwZXJjZW50YWdlIChPYmo6IDEwMCUpJywgVkFMVUUgfHwgJyUnCkZST00gViRQR0FTVEFUIFdIRVJFIE5BTUUgPSAnY2FjaGUgaGl0IHBlcmNlbnRhZ2UnClVOSU9OClNFTEVDVCAnT3ZlcmFsbG9jYXRpb25zJywgVkFMVUUgfHwgJycKRlJPTSBWJFBHQVNUQVQgV0hFUkUgTkFNRSA9ICdvdmVyIGFsbG9jYXRpb24gY291bnQn” hover_enabled=”0″]U0VMRUNUIE5BTUUsClJPVU5EKFZBTFVFLzEwMjQvMTAyNCwgMikgfHwnIE1CcycgQVMgVkFMT1IKRlJPTSBWJFBHQVNUQVQKV0hFUkUgTkFNRSBJTiAoJ2FnZ3JlZ2F0ZSBQR0EgdGFyZ2V0IHBhcmFtZXRlcicsICd0b3RhbCBQR0EgYWxsb2NhdGVkJywgJ3RvdGFsIFBHQSBpbnVzZScpClVOSU9OClNFTEVDdCAnUEdBIGNhY2hlIGhpdCBwZXJjZW50YWdlIChPYmo6IDEwMCUpJywgVkFMVUUgfHwgJyUnCkZST00gViRQR0FTVEFUIFdIRVJFIE5BTUUgPSAnY2FjaGUgaGl0IHBlcmNlbnRhZ2UnClVOSU9OClNFTEVDVCAnT3ZlcmFsbG9jYXRpb25zJywgVkFMVUUgfHwgJycKRlJPTSBWJFBHQVNUQVQgV0hFUkUgTkFNRSA9ICdvdmVyIGFsbG9jYXRpb24gY291bnQn[/et_pb_dmb_code_snippet]

OBS1: Atentar para a coluna “overallocations”, que indica se estão ocorrendo escritas na tablespace temporária, gerando disk I/O o que não é desejável. Recomenda-se que esse valor seja 0. Caso esteja muito alto, verificar a possibilidade de redimensionar a PGA.

O advisor da PGA pode ser obtido a partir da seguinte query:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIFBHQV9UQVJHRVRfRkFDVE9SLApyb3VuZChQR0FfVEFSR0VUX0ZPUl9FU1RJTUFURS8xMDI0LzEwMjQpIHRhcmdldF9tYiwKRVNURF9QR0FfQ0FDSEVfSElUX1BFUkNFTlRBR0UgY2FjaGVfaGl0X3BlcmMsCkVTVERfT1ZFUkFMTE9DX0NPVU5UCkZST00gdiRwZ2FfdGFyZ2V0X2FkdmljZTs=” hover_enabled=”0″]U0VMRUNUIFBHQV9UQVJHRVRfRkFDVE9SLApyb3VuZChQR0FfVEFSR0VUX0ZPUl9FU1RJTUFURS8xMDI0LzEwMjQpIHRhcmdldF9tYiwKRVNURF9QR0FfQ0FDSEVfSElUX1BFUkNFTlRBR0UgY2FjaGVfaGl0X3BlcmMsCkVTVERfT1ZFUkFMTE9DX0NPVU5UCkZST00gdiRwZ2FfdGFyZ2V0X2FkdmljZTs=[/et_pb_dmb_code_snippet]

5 – AMM – AUTOMATIC MEMORY MANAGEMENT

Caso se esteja usando a feature de AMM (MEMORY_TARGET <> 0), é possível otimiza-la como um todo. Ao usar essa feature, todas as estruturas de memória acima mencionadas, entre outras, serão automaticamente ajustadas pelo Oracle, conforme o ambiente demandar. Essa estrutura também possui um advisor, cujas informações podem ser obtidas a partir da seguinte view:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”ViRNRU1PUllfVEFSR0VUX0FEVklDRQ==” hover_enabled=”0″]ViRNRU1PUllfVEFSR0VUX0FEVklDRQ==[/et_pb_dmb_code_snippet]

6 – CONSIDERAÇÕES FINAIS

Na minha humilde opinião, as estruturas de memória que mais impactam o desempenho do banco de dados são o Buffer Cache e a Shared Pool e ambas são diretamente influenciadas em como os sqls são escritos e como a aplicação é escrita. SQLs mal-escritos irão realizar muitos gets desnecessários, usando assim o Buffer Cache de forma inadequada, e como consequência, gerando disk I/O, o que para ambientes críticos, deve ser evitado ao máximo. Aplicações que não usam bind variables irão influenciar negativamente a Shared Pool, realizando hard parses desnecessários e como consequência onerando o CPU do ambiente. Portanto, foquem em otimizações dos SQLs mais usados no ambiente e na otimização do código da aplicação que acessa o banco de dados.

Juntamente com as queries acima apresentadas, eu recomendo que, ao analisar as estruturas de memória da instância, você também analisa um relatório AWR/Statspack em conjunto. Ele possui infirmações valiosas que podem auxiliar a determinar se uma estrutura de memória precisa ser alterada.

Por enquanto é só pessoal. Keep tuning.