Algumas vezes nos deparamos com o seguinte tipo de problema em nosso trabalho:

“Vou fazer uma alteração no servidor de banco de dados. Qual será o impacto? Vai melhorar ou vai piorar? E quanto?”

Se você também se deparou com esta situação, o Oracle 11g oferece um ótimo recurso que pode te reportar a diferença do “antes e depois” da alteração. Seja ela uma alteração simples, como a criação de um índice em uma tabela ou uma nova coleta de estatísticas; até grandes alterações como upgrades do SGBD, do sistema operacional, ou até mesmo do hardware do servidor.

Conheçam o SQL Performance Analyzer. Com ele você pode ter métricas exatas da diferença de performance depois que a alteração desejada seja efetuada. Vou mostrar isto na prática, usando como base um exemplo que peguei no site Oracle-Base.

Primeiro, conecte-se à sua base de dados, com um usuário que tenha grants CONNECT e CREATE TABLE.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”UDo+c3FscGx1cyBtaWx0b24vbWlsdG9uCgpTUUwqUGx1czogUmVsZWFzZSAxMS4yLjAuMS4wIFByb2R1Y3Rpb24gb24gUXVpIEFnbyA0IDEwOjI3OjAzIDIwMTEKCkNvcHlyaWdodCAoYykgMTk4MiwgMjAxMCwgT3JhY2xlLiAgQWxsIHJpZ2h0cyByZXNlcnZlZC4KCkNvbmVjdGFkbyBhOgpPcmFjbGUgRGF0YWJhc2UgMTFnIEVudGVycHJpc2UgRWRpdGlvbiBSZWxlYXNlIDExLjIuMC4xLjAgLSBQcm9kdWN0aW9uCldpdGggdGhlIFBhcnRpdGlvbmluZywgT0xBUCwgRGF0YSBNaW5pbmcgYW5kIFJlYWwgQXBwbGljYXRpb24gVGVzdGluZwpvcHRpb25zCgpTUUw+IENSRUFURSBUQUJMRSBvYmpldG9zIEFTCiAgMiAgU0VMRUNUICogRlJPTSBhbGxfb2JqZWN0czsKClRhYmVsYSBjcmlhZGEu” hover_enabled=”0″]UDo+c3FscGx1cyBtaWx0b24vbWlsdG9uCgpTUUwqUGx1czogUmVsZWFzZSAxMS4yLjAuMS4wIFByb2R1Y3Rpb24gb24gUXVpIEFnbyA0IDEwOjI3OjAzIDIwMTEKCkNvcHlyaWdodCAoYykgMTk4MiwgMjAxMCwgT3JhY2xlLiAgQWxsIHJpZ2h0cyByZXNlcnZlZC4KCkNvbmVjdGFkbyBhOgpPcmFjbGUgRGF0YWJhc2UgMTFnIEVudGVycHJpc2UgRWRpdGlvbiBSZWxlYXNlIDExLjIuMC4xLjAgLSBQcm9kdWN0aW9uCldpdGggdGhlIFBhcnRpdGlvbmluZywgT0xBUCwgRGF0YSBNaW5pbmcgYW5kIFJlYWwgQXBwbGljYXRpb24gVGVzdGluZwpvcHRpb25zCgpTUUw+IENSRUFURSBUQUJMRSBvYmpldG9zIEFTCiAgMiAgU0VMRUNUICogRlJPTSBhbGxfb2JqZWN0czsKClRhYmVsYSBjcmlhZGEu[/et_pb_dmb_code_snippet]

Criei uma tabela acima, com uma quantidade razoável de registros, para usá-la no nossos testes. Com a tabela recém-criada, vamos fazer uma coleta de estatísticas:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IFNRTD4gRVhFQyBEQk1TX1NUQVRTLmdhdGhlcl90YWJsZV9zdGF0cyhVU0VSLCAnT0JKRVRPUycsCmNhc2NhZGUgPT4gdHJ1ZSk7″ hover_enabled=”0″]IFNRTD4gRVhFQyBEQk1TX1NUQVRTLmdhdGhlcl90YWJsZV9zdGF0cyhVU0VSLCAnT0JKRVRPUycsCmNhc2NhZGUgPT4gdHJ1ZSk7[/et_pb_dmb_code_snippet] 
Procedimento PL/SQL concluÝdo com sucesso.

Agora vamos rodar alguns select’s – que serão os select’s comparados no “antes e depois”. Em um sistema do “mundo real”, troque pelos select’s mais críticos do seu sistema, ou seja, aqueles que você pretende fazer a comparação.

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

Ok, já criamos uma tabela com uma boa quantidade de dados, coletamos estatísticas da tabela e rodamos alguns select’s sobre essa tabela. Reparem que todos os select’s usaram a coluna object_id na cláusula WHERE. Esta coluna não tem índice, e é exatamente essa a alteração que faremos. Vamos criar um índice nesta coluna e depois comparar a performance destes select’s sem índice e com índice.

Agora vamos conectar como SYS em um outro terminal, para criar um “SQL Tuning Set”:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”UDo+c3FscGx1cyAvIGFzIHN5c2RiYQoKU1FMPiBFWEVDIERCTVNfU1FMVFVORS5jcmVhdGVfc3Fsc2V0KHNxbHNldF9uYW1lID0+ICd0ZXN0ZV9taWx0b24nKTs=” hover_enabled=”0″]UDo+c3FscGx1cyAvIGFzIHN5c2RiYQoKU1FMPiBFWEVDIERCTVNfU1FMVFVORS5jcmVhdGVfc3Fsc2V0KHNxbHNldF9uYW1lID0+ICd0ZXN0ZV9taWx0b24nKTs=[/et_pb_dmb_code_snippet]

Em seguida, a função SELECT_CURSOR_CACHE é usado para recuperar um cursor que contém todas as instruções SQL que foram analisados ​​pelo esquema MILTON e que contém a palavra “objetos”. O cursor resultante é carregado para o SQLSET utilizando o procedimento LOAD_SQLSET.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”REVDTEFSRQogIGxfY3Vyc29yICBEQk1TX1NRTFRVTkUuc3Fsc2V0X2N1cnNvcjsKQkVHSU4KICBPUEVOIGxfY3Vyc29yIEZPUgogICAgIFNFTEVDVCBWQUxVRShhKQogICAgIEZST00gICBUQUJMRSgKICAgICAgICAgICAgICBEQk1TX1NRTFRVTkUuc2VsZWN0X2N1cnNvcl9jYWNoZSgKICAgICAgICAgICAgICAgIGJhc2ljX2ZpbHRlciAgID0+ICdzcWxfdGV4dCBMSUtFICcnJW9iamV0b3MlJycKICAgICAgICAgICAgICAgICAgICAgYW5kIHBhcnNpbmdfc2NoZW1hX25hbWUgPSAnJ01JTFRPTicnJywKICAgICAgICAgICAgICAgIGF0dHJpYnV0ZV9saXN0ID0+ICdBTEwnKQogICAgICAgICAgICApIGE7CgogIERCTVNfU1FMVFVORS5sb2FkX3NxbHNldChzcWxzZXRfbmFtZSAgICAgPT4gJ3Rlc3RlX21pbHRvbicsCiAgICAgICAgICAgICAgICAgICAgICAgICAgIHBvcHVsYXRlX2N1cnNvciA9PiBsX2N1cnNvcik7CkVORDsKLwpTUUw+IFNFTEVDVCBzcWxfdGV4dAogIDIgIEZST00gICBkYmFfc3Fsc2V0X3N0YXRlbWVudHMKICAzICBXSEVSRSAgc3Fsc2V0X25hbWUgPSAndGVzdGVfbWlsdG9uJzsKClNRTF9URVhUCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KU0VMRUNUIG9iamVjdF9uYW1lIEZST00gb2JqZXRvcyBXSEVSRSBvYmplY3RfaWQgPSAxMDAKU0VMRUNUIENPVU5UKCopIEZST00gb2JqZXRvcyBXSEVSRSBvYmplY3RfaWQgQkVUV0VFTiAxMDAgYW5kIDEwMDAKU0VMRUNUIENPVU5UKCopIEZST00gb2JqZXRvcyBXSEVSRSBvYmplY3RfaWQgPD0gMTAwMApTRUxFQ1QgQ09VTlQoKikgRlJPTSBvYmpldG9zIFdIRVJFIG9iamVjdF9pZCA8PSAxMDAKQ1JFQVRFIFRBQkxFIG9iamV0b3MgQVMKU0VMRUNUICogRlJPTSBhbGxfb2JqZWN0cwoKU0VMRUNUIG9iamVjdF9uYW1lIEZST00gb2JqZXRvcyBXSEVSRSBvYmplY3RfaWQgPSAxMDAwCgo2IGxpbmhhcyBzZWxlY2lvbmFkYXMu” hover_enabled=”0″]REVDTEFSRQogIGxfY3Vyc29yICBEQk1TX1NRTFRVTkUuc3Fsc2V0X2N1cnNvcjsKQkVHSU4KICBPUEVOIGxfY3Vyc29yIEZPUgogICAgIFNFTEVDVCBWQUxVRShhKQogICAgIEZST00gICBUQUJMRSgKICAgICAgICAgICAgICBEQk1TX1NRTFRVTkUuc2VsZWN0X2N1cnNvcl9jYWNoZSgKICAgICAgICAgICAgICAgIGJhc2ljX2ZpbHRlciAgID0+ICdzcWxfdGV4dCBMSUtFICcnJW9iamV0b3MlJycKICAgICAgICAgICAgICAgICAgICAgYW5kIHBhcnNpbmdfc2NoZW1hX25hbWUgPSAnJ01JTFRPTicnJywKICAgICAgICAgICAgICAgIGF0dHJpYnV0ZV9saXN0ID0+ICdBTEwnKQogICAgICAgICAgICApIGE7CgogIERCTVNfU1FMVFVORS5sb2FkX3NxbHNldChzcWxzZXRfbmFtZSAgICAgPT4gJ3Rlc3RlX21pbHRvbicsCiAgICAgICAgICAgICAgICAgICAgICAgICAgIHBvcHVsYXRlX2N1cnNvciA9PiBsX2N1cnNvcik7CkVORDsKLwpTUUw+IFNFTEVDVCBzcWxfdGV4dAogIDIgIEZST00gICBkYmFfc3Fsc2V0X3N0YXRlbWVudHMKICAzICBXSEVSRSAgc3Fsc2V0X25hbWUgPSAndGVzdGVfbWlsdG9uJzsKClNRTF9URVhUCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KU0VMRUNUIG9iamVjdF9uYW1lIEZST00gb2JqZXRvcyBXSEVSRSBvYmplY3RfaWQgPSAxMDAKU0VMRUNUIENPVU5UKCopIEZST00gb2JqZXRvcyBXSEVSRSBvYmplY3RfaWQgQkVUV0VFTiAxMDAgYW5kIDEwMDAKU0VMRUNUIENPVU5UKCopIEZST00gb2JqZXRvcyBXSEVSRSBvYmplY3RfaWQgPD0gMTAwMApTRUxFQ1QgQ09VTlQoKikgRlJPTSBvYmpldG9zIFdIRVJFIG9iamVjdF9pZCA8PSAxMDAKQ1JFQVRFIFRBQkxFIG9iamV0b3MgQVMKU0VMRUNUICogRlJPTSBhbGxfb2JqZWN0cwoKU0VMRUNUIG9iamVjdF9uYW1lIEZST00gb2JqZXRvcyBXSEVSRSBvYmplY3RfaWQgPSAxMDAwCgo2IGxpbmhhcyBzZWxlY2lvbmFkYXMu[/et_pb_dmb_code_snippet]

Agora sim vamos efetivamente usar o pacote do Performance Analyzer, o DBMS_SQLPA. Primeiro, criar uma “analysis task”:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q09OTiAvIEFTIFNZU0RCQQoKVkFSSUFCTEUgdl90YXNrIFZBUkNIQVIyKDY0KTsKRVhFQyA6dl90YXNrIDo9ICBEQk1TX1NRTFBBLmNyZWF0ZV9hbmFseXNpc190YXNrKHNxbHNldF9uYW1lID0+Cid0ZXN0ZV9taWx0b24nKTsKClBML1NRTCBwcm9jZWR1cmUgc3VjY2Vzc2Z1bGx5IGNvbXBsZXRlZC4KClNRTD4gUFJJTlQgOnZfdGFzawoKVl9UQVNLCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQpUQVJFRkFfNjQzCkFnb3JhIHZhbW9zIGNhcHR1cmFyIGUgYXJtYXplbmFyIGEgYW7DoWxpc2UgZG8g4oCcYW50ZXPigJ0sIG91IHNlamEsIGEgcGVyZm9ybWFuY2UgYXR1YWwsIGFudGVzIGRhIGNyaWHDp8OjbyBkbyDDrW5kaWNlOgoKQkVHSU4KICBEQk1TX1NRTFBBLmV4ZWN1dGVfYW5hbHlzaXNfdGFzaygKICAgIHRhc2tfbmFtZSAgICAgICA9PiA6dl90YXNrLAogICAgZXhlY3V0aW9uX3R5cGUgID0+ICd0ZXN0IGV4ZWN1dGUnLAogICAgZXhlY3V0aW9uX25hbWUgID0+ICdiZWZvcmVfY2hhbmdlJyk7CkVORDsKLw==” hover_enabled=”0″]Q09OTiAvIEFTIFNZU0RCQQoKVkFSSUFCTEUgdl90YXNrIFZBUkNIQVIyKDY0KTsKRVhFQyA6dl90YXNrIDo9ICBEQk1TX1NRTFBBLmNyZWF0ZV9hbmFseXNpc190YXNrKHNxbHNldF9uYW1lID0+Cid0ZXN0ZV9taWx0b24nKTsKClBML1NRTCBwcm9jZWR1cmUgc3VjY2Vzc2Z1bGx5IGNvbXBsZXRlZC4KClNRTD4gUFJJTlQgOnZfdGFzawoKVl9UQVNLCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQpUQVJFRkFfNjQzCkFnb3JhIHZhbW9zIGNhcHR1cmFyIGUgYXJtYXplbmFyIGEgYW7DoWxpc2UgZG8g4oCcYW50ZXPigJ0sIG91IHNlamEsIGEgcGVyZm9ybWFuY2UgYXR1YWwsIGFudGVzIGRhIGNyaWHDp8OjbyBkbyDDrW5kaWNlOgoKQkVHSU4KICBEQk1TX1NRTFBBLmV4ZWN1dGVfYW5hbHlzaXNfdGFzaygKICAgIHRhc2tfbmFtZSAgICAgICA9PiA6dl90YXNrLAogICAgZXhlY3V0aW9uX3R5cGUgID0+ICd0ZXN0IGV4ZWN1dGUnLAogICAgZXhlY3V0aW9uX25hbWUgID0+ICdiZWZvcmVfY2hhbmdlJyk7CkVORDsKLw==[/et_pb_dmb_code_snippet]
[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IENPTk4gbWlsdG9uL21pbHRvbgoKQ1JFQVRFIElOREVYIG9iamV0b3NfaW5kZXhfMDEgT04gb2JqZXRvcyhvYmplY3RfaWQpOwoKRVhFQyBEQk1TX1NUQVRTLmdhdGhlcl90YWJsZV9zdGF0cyhVU0VSLCAnT0JKRVRPUycsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBjYXNjYWRlID0+IFRSVUUpOw==” hover_enabled=”0″]IENPTk4gbWlsdG9uL21pbHRvbgoKQ1JFQVRFIElOREVYIG9iamV0b3NfaW5kZXhfMDEgT04gb2JqZXRvcyhvYmplY3RfaWQpOwoKRVhFQyBEQk1TX1NUQVRTLmdhdGhlcl90YWJsZV9zdGF0cyhVU0VSLCAnT0JKRVRPUycsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBjYXNjYWRlID0+IFRSVUUpOw==[/et_pb_dmb_code_snippet]

Novamente como usuário SYS, capturo a análise de performance da nova situação, ou seja, após a crianção do índice:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”QkVHSU4KICBEQk1TX1NRTFBBLmV4ZWN1dGVfYW5hbHlzaXNfdGFzaygKICAgIHRhc2tfbmFtZSAgICAgICA9PiA6dl90YXNrLAogICAgZXhlY3V0aW9uX3R5cGUgID0+ICd0ZXN0IGV4ZWN1dGUnLAogICAgZXhlY3V0aW9uX25hbWUgID0+ICdhZnRlcl9jaGFuZ2UnKTsKRU5EOwov” hover_enabled=”0″]QkVHSU4KICBEQk1TX1NRTFBBLmV4ZWN1dGVfYW5hbHlzaXNfdGFzaygKICAgIHRhc2tfbmFtZSAgICAgICA9PiA6dl90YXNrLAogICAgZXhlY3V0aW9uX3R5cGUgID0+ICd0ZXN0IGV4ZWN1dGUnLAogICAgZXhlY3V0aW9uX25hbWUgID0+ICdhZnRlcl9jaGFuZ2UnKTsKRU5EOwov[/et_pb_dmb_code_snippet]

Executar a comparação de performance:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IEJFR0lOCiAgREJNU19TUUxQQS5leGVjdXRlX2FuYWx5c2lzX3Rhc2soCiAgICB0YXNrX25hbWUgICAgICAgID0+IDp2X3Rhc2ssCiAgICBleGVjdXRpb25fdHlwZSAgID0+ICdjb21wYXJlIHBlcmZvcm1hbmNlJywKICAgIGV4ZWN1dGlvbl9wYXJhbXMgPT4gZGJtc19hZHZpc29yLmFyZ2xpc3QoCiAgICAgICAgICAgICAgICAgICAgICAgICAgJ2V4ZWN1dGlvbl9uYW1lMScsCiAgICAgICAgICAgICAgICAgICAgICAgICAgJ2JlZm9yZV9jaGFuZ2UnLAogICAgICAgICAgICAgICAgICAgICAgICAgICdleGVjdXRpb25fbmFtZTInLAogICAgICAgICAgICAgICAgICAgICAgICAgICdhZnRlcl9jaGFuZ2UnKQogICAgKTsKRU5EOwov” hover_enabled=”0″]IEJFR0lOCiAgREJNU19TUUxQQS5leGVjdXRlX2FuYWx5c2lzX3Rhc2soCiAgICB0YXNrX25hbWUgICAgICAgID0+IDp2X3Rhc2ssCiAgICBleGVjdXRpb25fdHlwZSAgID0+ICdjb21wYXJlIHBlcmZvcm1hbmNlJywKICAgIGV4ZWN1dGlvbl9wYXJhbXMgPT4gZGJtc19hZHZpc29yLmFyZ2xpc3QoCiAgICAgICAgICAgICAgICAgICAgICAgICAgJ2V4ZWN1dGlvbl9uYW1lMScsCiAgICAgICAgICAgICAgICAgICAgICAgICAgJ2JlZm9yZV9jaGFuZ2UnLAogICAgICAgICAgICAgICAgICAgICAgICAgICdleGVjdXRpb25fbmFtZTInLAogICAgICAgICAgICAgICAgICAgICAgICAgICdhZnRlcl9jaGFuZ2UnKQogICAgKTsKRU5EOwov[/et_pb_dmb_code_snippet]

E finalmente, vamos gerar uma página HTML contendo o relatório. Algo lindo pra mostrar pro seu gerente, é uma evidência quantitativa de melhoria de performance!

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBTRVQgUEFHRVNJWkUgMApTUUw+IFNFVCBMSU5FU0laRSAxMDAwClNRTD4gU0VUIExPTkcgMTAwMDAwMApTUUw+IFNFVCBMT05HQ0hVTktTSVpFIDEwMDAwMDAKU1FMPiBTRVQgVFJJTVNQT09MIE9OClNRTD4gU0VUIFRSSU0gT04KClNRTD4gU1BPT0wgRDpXb3JrdGVzdGVzc3FsX3BlcmZfYW5hbHl6ZXIuaHRtCgpTUUw+IFNFTEVDVCBEQk1TX1NRTFBBLnJlcG9ydF9hbmFseXNpc190YXNrKDp2X3Rhc2ssICdIVE1MJywgJ0FMTCcpCiAgICAgRlJPTSAgIGR1YWw7CgpTUUw+IFNQT09MIE9GRg==” hover_enabled=”0″]U1FMPiBTRVQgUEFHRVNJWkUgMApTUUw+IFNFVCBMSU5FU0laRSAxMDAwClNRTD4gU0VUIExPTkcgMTAwMDAwMApTUUw+IFNFVCBMT05HQ0hVTktTSVpFIDEwMDAwMDAKU1FMPiBTRVQgVFJJTVNQT09MIE9OClNRTD4gU0VUIFRSSU0gT04KClNRTD4gU1BPT0wgRDpXb3JrdGVzdGVzc3FsX3BlcmZfYW5hbHl6ZXIuaHRtCgpTUUw+IFNFTEVDVCBEQk1TX1NRTFBBLnJlcG9ydF9hbmFseXNpc190YXNrKDp2X3Rhc2ssICdIVE1MJywgJ0FMTCcpCiAgICAgRlJPTSAgIGR1YWw7CgpTUUw+IFNQT09MIE9GRg==[/et_pb_dmb_code_snippet]

Feito! Agora é só abrir o arquivo “D:Worktestessql_perf_analyzer.htm” no seu browser e analisar as informações.