Buenas! 🙂

Uma das atividades que mais demandam tempo, tato, expertise, xícaras de café, lágrimas, diálogo, consultas com o terapeuta, enfim… é o Tuning.

Com várias solicitações, e a obrigação de aprender o dito, resolvi documentar essa cruzada.

Quem sabe esse manual não é útil para algum de vocês que nos acompanham…

Não sou o dono da verdade, e o conteúdo aqui é baseado no meu entendimento de tuning e em materiais que estão disponíveis na internet. Todo material utilizado aqui será creditado.

Traduzindo Tuning…

O que é tuning?
– Basicamente é eliminar CUSTO desnecessário;
– Pode ser alcançado de diversos modos: criação de index, alteração de parâmetro, remodelagem, estatística, reescrita do SQL;

Planos de execução
– Atividades de manipulação ou consulta a dados previamente criam um plano de execução, o caminho mais fácil que o otimizador acredita percorrer para alcançar o resultado;
– O oracle nem sempre escolhe o melhor plano;
– Força-se um plano diferente utilizando o ‘HINT‘;

Gerando um plano de execução: (utilizando o ‘schema sample’ SCOTT que pode ser instalado junto com o banco)

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”ZGVsZXRlIHBsYW5fdGFibGU7CmNvbW1pdDsKCkVYUExBSU4gUExBTiBGT1IKU0VMRUNUICoKRlJPTSAgIHNjb3R0LmVtcCBlLCBzY290dC5kZXB0IGQKV0hFUkUgIGUuZGVwdG5vID0gZC5kZXB0bm8KQU5EICAgIGUuZW5hbWUgID0gJ1NNSVRIJzsKCkVYUExBSU4gUExBTiBzdWNjZWVkZWQuCgpzZWxlY3QgcGxhbl90YWJsZV9vdXRwdXQgZnJvbSB0YWJsZShkYm1zX3hwbGFuLmRpc3BsYXkoJ3BsYW5fdGFibGUnLG51bGwsJ3NlcmlhbCcpKTsKUExBTl9UQUJMRV9PVVRQVVQgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIApQbGFuIGhhc2ggdmFsdWU6IDM2MjU5NjIwOTIgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gIAp8IElkICB8IE9wZXJhdGlvbiAgICAgICAgICAgICAgICAgICAgfCBOYW1lICAgIHwgUm93cyAgfCBCeXRlcyB8IENvc3QgKCVDUFUpfCBUaW1lICAgICB8ICAKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAgCnwgICAwIHwgU0VMRUNUIFNUQVRFTUVOVCAgICAgICAgICAgICB8ICAgICAgICAgfCAgICAgMSB8ICAgIDU4IHwgICAgIDQgICAoMCl8IDAwOjAwOjAxIHwgLT4gVGlwbyBkZSBvcGVyYcOnw6NvIAp8ICAgMSB8ICBORVNURUQgTE9PUFMgICAgICAgICAgICAgICAgfCAgICAgICAgIHwgICAgICAgfCAgICAgICB8ICAgICAgICAgICAgfCAgICAgICAgICB8IC0+IE3DqXRvZG8gZGUgbGlnYcOnw6NvIAp8ICAgMiB8ICAgTkVTVEVEIExPT1BTICAgICAgICAgICAgICAgfCAgICAgICAgIHwgICAgIDEgfCAgICA1OCB8ICAgICA0ICAgKDApfCAwMDowMDowMSB8ICAKfCogIDMgfCAgICBUQUJMRSBBQ0NFU1MgRlVMTCAgICAgICAgIHwgRU1QICAgICB8ICAgICAxIHwgICAgMzggfCAgICAgMyAgICgwKXwgMDA6MDA6MDEgfCAtPiBNw6l0b2RvIGRlIGFjZXNzbyBhIHRhYmVsYSAKfCogIDQgfCAgICBJTkRFWCBVTklRVUUgU0NBTiAgICAgICAgIHwgUEtfREVQVCB8ICAgICAxIHwgICAgICAgfCAgICAgMCAgICgwKXwgMDA6MDA6MDEgfCAtPiBEZXRhbGhhbWVudG8gZG8gYWNlc3NvIGEgc2VndW5kYSB0YWJlbGEKfCAgIDUgfCAgIFRBQkxFIEFDQ0VTUyBCWSBJTkRFWCBST1dJRHwgREVQVCAgICB8ICAgICAxIHwgICAgMjAgfCAgICAgMSAgICgwKXwgMDA6MDA6MDEgfCAtPiBNw6l0b2RvIGRlIGFjZXNzbyBhIHNlZ3VuZGEgdGFiZWxhIAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tICAKClByZWRpY2F0ZSBJbmZvcm1hdGlvbiAoaWRlbnRpZmllZCBieSBvcGVyYXRpb24gaWQpOiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKCiAgIDMgLSBmaWx0ZXIoIkUiLiJFTkFNRSI9J1NNSVRIJykgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIAogICA0IC0gYWNjZXNzKCJFIi4iREVQVE5PIj0iRCIuIkRFUFROTyIpICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKCjE4IHJvd3Mgc2VsZWN0ZWQKClJvd3M6ICBMaW5oYXMgc2VsZWNpb25hZGFzOwpCeXRlczogVG90YWwgZGUgYnl0ZXMgc2VsZWNpb25hZG9zOwpDb3N0OiAgQ3VzdG8gZGUgZXhlY3XDp8Ojbzs=” hover_enabled=”0″]ZGVsZXRlIHBsYW5fdGFibGU7CmNvbW1pdDsKCkVYUExBSU4gUExBTiBGT1IKU0VMRUNUICoKRlJPTSAgIHNjb3R0LmVtcCBlLCBzY290dC5kZXB0IGQKV0hFUkUgIGUuZGVwdG5vID0gZC5kZXB0bm8KQU5EICAgIGUuZW5hbWUgID0gJ1NNSVRIJzsKCkVYUExBSU4gUExBTiBzdWNjZWVkZWQuCgpzZWxlY3QgcGxhbl90YWJsZV9vdXRwdXQgZnJvbSB0YWJsZShkYm1zX3hwbGFuLmRpc3BsYXkoJ3BsYW5fdGFibGUnLG51bGwsJ3NlcmlhbCcpKTsKUExBTl9UQUJMRV9PVVRQVVQgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIApQbGFuIGhhc2ggdmFsdWU6IDM2MjU5NjIwOTIgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gIAp8IElkICB8IE9wZXJhdGlvbiAgICAgICAgICAgICAgICAgICAgfCBOYW1lICAgIHwgUm93cyAgfCBCeXRlcyB8IENvc3QgKCVDUFUpfCBUaW1lICAgICB8ICAKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAgCnwgICAwIHwgU0VMRUNUIFNUQVRFTUVOVCAgICAgICAgICAgICB8ICAgICAgICAgfCAgICAgMSB8ICAgIDU4IHwgICAgIDQgICAoMCl8IDAwOjAwOjAxIHwgLT4gVGlwbyBkZSBvcGVyYcOnw6NvIAp8ICAgMSB8ICBORVNURUQgTE9PUFMgICAgICAgICAgICAgICAgfCAgICAgICAgIHwgICAgICAgfCAgICAgICB8ICAgICAgICAgICAgfCAgICAgICAgICB8IC0+IE3DqXRvZG8gZGUgbGlnYcOnw6NvIAp8ICAgMiB8ICAgTkVTVEVEIExPT1BTICAgICAgICAgICAgICAgfCAgICAgICAgIHwgICAgIDEgfCAgICA1OCB8ICAgICA0ICAgKDApfCAwMDowMDowMSB8ICAKfCogIDMgfCAgICBUQUJMRSBBQ0NFU1MgRlVMTCAgICAgICAgIHwgRU1QICAgICB8ICAgICAxIHwgICAgMzggfCAgICAgMyAgICgwKXwgMDA6MDA6MDEgfCAtPiBNw6l0b2RvIGRlIGFjZXNzbyBhIHRhYmVsYSAKfCogIDQgfCAgICBJTkRFWCBVTklRVUUgU0NBTiAgICAgICAgIHwgUEtfREVQVCB8ICAgICAxIHwgICAgICAgfCAgICAgMCAgICgwKXwgMDA6MDA6MDEgfCAtPiBEZXRhbGhhbWVudG8gZG8gYWNlc3NvIGEgc2VndW5kYSB0YWJlbGEKfCAgIDUgfCAgIFRBQkxFIEFDQ0VTUyBCWSBJTkRFWCBST1dJRHwgREVQVCAgICB8ICAgICAxIHwgICAgMjAgfCAgICAgMSAgICgwKXwgMDA6MDA6MDEgfCAtPiBNw6l0b2RvIGRlIGFjZXNzbyBhIHNlZ3VuZGEgdGFiZWxhIAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tICAKClByZWRpY2F0ZSBJbmZvcm1hdGlvbiAoaWRlbnRpZmllZCBieSBvcGVyYXRpb24gaWQpOiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKCiAgIDMgLSBmaWx0ZXIoIkUiLiJFTkFNRSI9J1NNSVRIJykgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIAogICA0IC0gYWNjZXNzKCJFIi4iREVQVE5PIj0iRCIuIkRFUFROTyIpICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKCjE4IHJvd3Mgc2VsZWN0ZWQKClJvd3M6ICBMaW5oYXMgc2VsZWNpb25hZGFzOwpCeXRlczogVG90YWwgZGUgYnl0ZXMgc2VsZWNpb25hZG9zOwpDb3N0OiAgQ3VzdG8gZGUgZXhlY3XDp8Ojbzs=[/et_pb_dmb_code_snippet]

No próximo post iremos falar um pouco sobre os métodos de junção (join types)

Créditos:
http://www.devmedia.com.br/tuning-no-oracle-parte-01/8438
http://www.dba-oracle.com/art_sql_tune.htm
http://docs.oracle.com/cd/E11882_01/server.112/e16638/sql_overview.htm#PFGRF94798

o/
Att
Vitor Jr.