{"id":926,"date":"2016-12-06T23:04:17","date_gmt":"2016-12-07T01:04:17","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=926"},"modified":"2019-11-11T18:53:13","modified_gmt":"2019-11-11T21:53:13","slug":"por-que-o-otimizador-criou-um-plano-de-execucao-ineficiente","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/por-que-o-otimizador-criou-um-plano-de-execucao-ineficiente\/","title":{"rendered":"Por que o Otimizador criou um plano de execu\u00e7\u00e3o ineficiente?"},"content":{"rendered":"

O plano de execu\u00e7\u00e3o \u00e9 uma sequ\u00eancia de opera\u00e7\u00f5es que acessam os dados nas tabelas indicadas na instru\u00e7\u00e3o SQL, considerando os filtros especificados na cl\u00e1usula WHERE, a sequ\u00eancia em que as tabelas s\u00e3o acessadas e o tipo de opera\u00e7\u00e3o que ser\u00e1 utilizado no acesso s\u00e3o\u00a0determinados pela estimativa de quantidade de linhas que cada opera\u00e7\u00e3o vai acessar, esta estimativa \u00e9 chamada de “CARDINALIDADE” (Cardinality). Como base nisso podemos afirmar que a Cardinalidade \u00e9 um fator decisivo na qualidade do plano de execu\u00e7\u00e3o e consequentemente no tempo de reposta da instru\u00e7\u00e3o SQL.<\/span><\/p>\n

Respondendo a quest\u00e3o proposta no titulo deste artigo, o Otimizador vai entregar um plano ineficiente quando a cardinalidade das opera\u00e7\u00f5es do plano de execu\u00e7\u00e3o for muito diferente da quantidade de registros real que a opera\u00e7\u00e3o vai acessar. Como o erro de cardinalidade \u00e9 uma das principais causas dos planos de execu\u00e7\u00e3o com tempo de resposta insatisfat\u00f3rio, uma t\u00e9cnica de tuning muito utilizada consiste em identificar a causa desse erro e prover a solu\u00e7\u00e3o para o mesmo.<\/span><\/p>\n

Neste artigo vamos abordar de forma sucinta as principais causas que levam o Otimizador ao erro de cardinalidade das opera\u00e7\u00f5es e quais t\u00e9cnicas podem ser utilizadas para contornar esse problema. Nos pr\u00f3ximos artigos vamos abordar de forma mais detalhada cada uma dessas causas, mostrar exemplos pr\u00e1ticos de como elas ocorrem e como corrigi-las.<\/span><\/p>\n

Aus\u00eancia de estat\u00edsticas ou falta de atualiza\u00e7\u00e3o<\/span><\/h2>\n

Quando o Otimizador precisa calcular a cardinalidade de uma opera\u00e7\u00e3o ele utiliza as estat\u00edsticas de tabelas, colunas e \u00edndices. Essas estat\u00edsticas s\u00e3o fundamentais para o calculo da cardinalidade, se elas n\u00e3o existem ou n\u00e3o est\u00e3o atualizadas o Otimizador utiliza alguns valores padr\u00f5es o que acaba comprometendo a precis\u00e3o do calculo e muitas vezes leva o Otimizador a escolher uma opera\u00e7\u00e3o ineficiente para o plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Para coletar ou atualizar as estat\u00edsticas de tabelas, colunas e \u00edndices devemos utilizar o pacote DBMS_STATS.GATHER_TABLE_STATS<\/span>.<\/span><\/p>\n

 <\/p>\n

No artigo “Estat\u00edsticas: a mat\u00e9ria prima do otimizador<\/a><\/strong>” abordamos com mais detalhes os efeitos da aus\u00eancia ou a falta de atualiza\u00e7\u00e3o das estat\u00edsticas na gera\u00e7\u00e3o do plano de execu\u00e7\u00e3o pelo Otimizador.<\/span><\/p>\n

Repeti\u00e7\u00e3o de dados n\u00e3o uniforme<\/span><\/h2>\n

Quando utilizamos na cl\u00e1usula WHERE uma coluna de uma tabela que possui poucos valores distintos e a quantidade de registros para cada um desses valores n\u00e3o \u00e9 uniforme, chamamos os dados dessa coluna de “Data Skew”. Por exemplo se consultarmos o campo cargo da tabela de empregados de uma empresa, provavelmente o numero de operadores vai ser muito superior que o numero de gerentes. Para realizar um calculo de cardinalidade com precis\u00e3o satisfat\u00f3ria para instru\u00e7\u00f5es SQL que utilizam esse tipo de coluna na cl\u00e1usula WHERE, o Otimizador precisa utilizar histogramas e se eles n\u00e3o existem para esse tipo de coluna o calculo da cardinalidade ser\u00e1 impreciso.<\/span><\/p>\n

Para criar Histogramas\u00a0devemos utilizar o pacote<\/span> DBMS_STATS.GATHER_TABLE_STATS<\/span>.<\/p>\n

 <\/p>\n

No artigo “Histogramas: Qual import\u00e2ncia deles?<\/a><\/strong>” abordamos com mais detalhes como a aus\u00eancia de histogramas em colunas com repeti\u00e7\u00e3o de dados n\u00e3o uniformes podem influenciar na qualidade do plano de execu\u00e7\u00e3o gerado pelo Otimizador.<\/span><\/p>\n

M\u00faltiplas colunas de uma tabela na cl\u00e1usula WHERE<\/span><\/h2>\n

Quando utilizamos duas ou mais colunas para uma tabela na cl\u00e1usula WHERE, o Otimizador tende a subestimar a cardinalidade de linhas que ser\u00e3o acessadas nesta tabela, isto ocorre pois ele desconhece qualquer rela\u00e7\u00e3o que existe entre os dados dessas colunas, nesse caso para ajudar o Otimizador a estimar a cardinalidade com maior precis\u00e3o precisamos criar uma estatistica de grupo de colunas utilizando o pacote (DBMS_STATS.CREATE_EXTENDED_STATS) e coletar as estat\u00edsticas da tabela novamente.<\/span><\/p>\n

\n

No artigo “O Otimizador do Oracle precisa da nossa ajuda?<\/a><\/strong>” abordamos com mais detalhes como a presen\u00e7a de m\u00faltiplas colunas de uma tabela na cl\u00e1usula WHERE podem influenciar na qualidade do plano de execu\u00e7\u00e3o gerado pelo Otimizador.<\/span><\/p>\n

Colunas com fun\u00e7\u00e3o na cl\u00e1usula WHERE<\/span><\/h2>\n

A exist\u00eancia de fun\u00e7\u00e3o para uma coluna na cl\u00e1usula WHERE faz com que o Otimizador despreze a exist\u00eancia de um Histograma para essa coluna pois o Otimizador n\u00e3o sabe como essa fun\u00e7\u00e3o vai afetar os dados, como consequ\u00eancia o Otimizador estima a cardinalidade da opera\u00e7\u00e3o como 1% do total de registros da tabela. Para melhorar a precis\u00e3o da cardinalidade nestes casos podemos criar estat\u00edsticas da coluna com a fun\u00e7\u00e3o utilizando o pacote (DBMS_STATS).<\/span><\/p>\n

\n

No artigo “Fun\u00e7\u00f5es na cl\u00e1usula WHERE podem confundir o Otimizador?<\/a><\/strong>” abordamos com mais detalhes como as colunas com fun\u00e7\u00e3o na cl\u00e1usula WHERE podem influenciar na qualidade do plano de execu\u00e7\u00e3o gerado pelo Otimizador.<\/span><\/p>\n

M\u00faltiplas colunas utilizadas na jun\u00e7\u00e3o de tabelas<\/span><\/h2>\n

Quando utilizamos duas ou mais colunas para jun\u00e7\u00e3o de duas tabelas na cl\u00e1usula WHERE, o Otimizador utiliza um algor\u00edtimo que em algumas situa\u00e7\u00f5es estima a cardinalidade da opera\u00e7\u00e3o de forma imprecisa, neste caso para ajudar o Otimizador a estimar a cardinalidade com maior precis\u00e3o precisamos criar estat\u00edsticas para as colunas utilizadas na jun\u00e7\u00e3o das duas tabela utilizando o pacote (DBMS_STATS.CREATE_EXTENDED_STATS) e coletar as estat\u00edsticas das tabelas novamente.<\/span><\/p>\n

Express\u00f5es complexas<\/span><\/h2>\n

Express\u00f5es complexas na cl\u00e1usula WHERE envolvendo operadores como “>” ou “<” impossibilitam a utiliza\u00e7\u00e3o de estat\u00edsticas de grupo de colunas pelo otimizador, neste caso podemos utilizar “Dynamic sampling” para melhorar a estimativa de cardinalidade da opera\u00e7\u00e3o no plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

Utilizando as t\u00e9cnicas descritas acima conseguimos alimentar\u00a0o Otimizador com informa\u00e7\u00f5es suficientes para que ele consiga calcular a\u00a0cardinalidade das opera\u00e7\u00f5es com maior precis\u00e3o, desta forma\u00a0ele consegue escolher o m\u00e9todo de acesso mais eficiente e a melhor sequ\u00eancia para as opera\u00e7\u00f5es visando retornar o resultado da instru\u00e7\u00e3o SQL no menor intervalo de tempo poss\u00edvel.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

http:\/\/gavinsoorma.com\/wp-content\/uploads\/2011\/03\/top_tips_for_optimal_sql_execution.pdf<\/a><\/span><\/strong><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

O plano de execu\u00e7\u00e3o \u00e9 uma sequ\u00eancia de opera\u00e7\u00f5es que acessam os dados nas tabelas indicadas na instru\u00e7\u00e3o SQL, considerando os filtros especificados na cl\u00e1usula WHERE, a sequ\u00eancia em que as tabelas s\u00e3o acessadas e o tipo de opera\u00e7\u00e3o que ser\u00e1 utilizado no acesso s\u00e3o\u00a0determinados pela estimativa de quantidade de linhas que cada opera\u00e7\u00e3o vai acessar, esta estimativa \u00e9 chamada<\/p>\n","protected":false},"author":2,"featured_media":927,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,71],"tags":[129,128,6,42,127,162,161,10],"class_list":["post-926","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cost-base-optimizer","category-estatisticas","tag-cardinalidade","tag-cardinality","tag-cbo","tag-cost-base-optimizer","tag-extended-statistics","tag-histogramas","tag-histograms","tag-otimizador"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/926","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=926"}],"version-history":[{"count":39,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/926\/revisions"}],"predecessor-version":[{"id":2088,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/926\/revisions\/2088"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/927"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=926"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}