O plano de execução é uma sequência de operações que acessam os dados nas tabelas indicadas na instrução SQL, considerando os filtros especificados na cláusula WHERE, a sequência em que as tabelas são acessadas e o tipo de operação que será utilizado no acesso são determinados pela estimativa de quantidade de…
Funções na cláusula WHERE podem confundir o Otimizador?
No artigo “Por que o Otimizador criou um plano de execução ineficiente?” elencamos algumas condições que levam o Otimizador a gerar um plano de execução ineficiente, neste artigo vamos mostrar com mais detalhes como a utilização de função nas colunas utilizadas na cláusula WHERE podem influenciar de maneira negativa o Otimizador.
Colunas com função na cláusula WHERE
Para calcular a cardinalidade de uma tabela na instrução SQL o Otimizador utiliza os filtros informados na cláusula WHERE, as operações apresentadas na cláusula WHERE permitem que o otimizador faça consultas nas estatísticas para determinar essa cardinalidade. Quando utilizamos uma função numa coluna da cláusula WHERE o Otimizador perde essa habilidade de consultar as estatísticas para determinar a cardinalidade, pois ele não sabe qual valor a coluna vai assumir após a aplicação da função no seu valor original. Para suprir a ausência dessa informação estatística o Otimizador estima que a cardinalidade será 1% da quantidade total dos registros da tabela, esse algoritmo não garante que o Otimizador faça uma estimativa de cardinalidade precisa.
Para que o Otimizador consiga calcular a cardinalidade de forma mais precisa na situação apresentada acima é necessário que sejam criadas estatísticas adicionais na coluna com a função utilizada na cláusula WHERE. Para geração dessas estatísticas adicionais devemos utilizar o pacote DBMS_STATS.
Para ver na prática essa situação vamos realizar as seguintes operações:
1) Executar uma consulta utilizando uma coluna com função na cláusula WHERE e verificar a cardinalidade estimada pelo Otimizador
3) Coletar as estistísticas adicionais da coluna com a função
4) Executar a mesma consulta novamente e verificar a cardinalidade estimada pelo Otimizador
Executar a consulta
Vamos executar uma consulta utilizando uma coluna com função na cláusula WHERE e verificar a cardinalidade estimada pelo Otimizador:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SELECT /* dbtw101 */ count(1) Qtde 2 FROM sh.customers 3 WHERE UPPER(CUST_CITY) = UPPER('Joinville'); QTDE ---------- 98 SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw101%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 775j44q9u23ak 0 SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 775j44q9u23ak, child number 0 ------------------------------------- SELECT /* dbtw101 */ count(:"SYS_B_0") Qtde FROM sh.customers WHERE UPPER(CUST_CITY) = UPPER(:"SYS_B_1") Plan hash value: 296924608 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1456 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1456 | |* 2 | TABLE ACCESS FULL| CUSTOMERS | 1 | 555 | 98 |00:00:00.01 | 1456 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(UPPER("CUST_CITY")=UPPER(:SYS_B_1)) 20 linhas selecionadas. SQL > column "Num. linhas na tabela" new_value num_lin_tot SQL > select count(1) "Num. linhas na tabela" from sh.customers; Num. linhas na tabela --------------------- 55500 SQL > select round(&num_lin_tot*0.01) CARDINALIDADE from dual; antigo 1: select round(&num_lin_tot*0.01) CARDINALIDADE from dual novo 1: select round( 55500*0.01) CARDINALIDADE from dual CARDINALIDADE ------------- 555 SQL > |
Ajudo DBAs e analistas de sistema a se destacarem em suas empresas
e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:
No plano de execução verificamos que o Otimizador estimou que a cardinalidade da tabela “CUSTOMERS” seriam 555 registros (E-ROWS), quando na verdade foram 98 registros (A-ROWS).
O Otimizador calculou a cardinalidade como 555 pois ele usou o algoritimo de 1% da quantidade total de registros da tabela (55500).
Coletar das estistísticas adicionais
Para ajudar o Otimizador vamos criar as estatísticas para a coluna utilizando a mesma função presente na cláusula WHERE da consulta, para isso é necessário utilizar a claúsula “method_opt=> ‘FOR ALL COLUMNS SIZE SKEWONLY for columns (UPPER(CUST_CITY))'” na coleta de estatísticas com o pacote DBMS_STATS.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | SQL > SELECT column_name, num_distinct, histogram 2 FROM dba_tab_col_statistics 3 WHERE table_name = 'CUSTOMERS' 4 AND owner = 'SH'; COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- CUST_ID 55500 HEIGHT BALANCED CUST_FIRST_NAME 1300 HEIGHT BALANCED CUST_LAST_NAME 908 HEIGHT BALANCED CUST_GENDER 2 FREQUENCY CUST_YEAR_OF_BIRTH 75 FREQUENCY CUST_MARITAL_STATUS 11 FREQUENCY CUST_STREET_ADDRESS 49900 HEIGHT BALANCED CUST_POSTAL_CODE 623 HEIGHT BALANCED CUST_CITY 620 HEIGHT BALANCED CUST_CITY_ID 620 HEIGHT BALANCED CUST_STATE_PROVINCE 145 FREQUENCY CUST_STATE_PROVINCE_ID 145 FREQUENCY COUNTRY_ID 19 FREQUENCY CUST_MAIN_PHONE_NUMBER 51344 HEIGHT BALANCED CUST_INCOME_LEVEL 12 FREQUENCY CUST_CREDIT_LIMIT 8 FREQUENCY CUST_EMAIL 1699 HEIGHT BALANCED CUST_TOTAL 1 FREQUENCY CUST_TOTAL_ID 1 FREQUENCY CUST_SRC_ID 0 NONE CUST_EFF_FROM 1 FREQUENCY CUST_EFF_TO 0 NONE CUST_VALID 2 FREQUENCY 23 linhas selecionadas. SQL > exec dbms_stats.gather_table_stats(ownname=> 'SH', tabname=> 'CUSTOMERS', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE SKEWONLY for columns (UPPER(CUST_CITY))'); Procedimento PL/SQL concluído com sucesso. SQL > SELECT column_name, num_distinct, histogram 2 FROM dba_tab_col_statistics 3 WHERE table_name = 'CUSTOMERS' 4 AND owner = 'SH'; COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- SYS_STUH1VS5X9F1_EEZ1S2XSM741O 620 HEIGHT BALANCED CUST_ID 55500 HEIGHT BALANCED CUST_FIRST_NAME 1300 HEIGHT BALANCED CUST_LAST_NAME 908 HEIGHT BALANCED CUST_GENDER 2 FREQUENCY CUST_YEAR_OF_BIRTH 75 FREQUENCY CUST_MARITAL_STATUS 11 FREQUENCY CUST_STREET_ADDRESS 49900 HEIGHT BALANCED CUST_POSTAL_CODE 623 HEIGHT BALANCED CUST_CITY 620 HEIGHT BALANCED CUST_CITY_ID 620 HEIGHT BALANCED CUST_STATE_PROVINCE 145 FREQUENCY CUST_STATE_PROVINCE_ID 145 FREQUENCY COUNTRY_ID 19 FREQUENCY CUST_MAIN_PHONE_NUMBER 51344 HEIGHT BALANCED CUST_INCOME_LEVEL 12 FREQUENCY CUST_CREDIT_LIMIT 8 FREQUENCY CUST_EMAIL 1699 HEIGHT BALANCED CUST_TOTAL 1 FREQUENCY CUST_TOTAL_ID 1 FREQUENCY CUST_SRC_ID 0 NONE CUST_EFF_FROM 1 FREQUENCY CUST_EFF_TO 0 NONE CUST_VALID 2 FREQUENCY 24 linhas selecionadas. SQL > |
Após a execução do pacote DBMS_STATS, podemos observar que existe uma coluna (linha 45) com nome gerado pelo sistema (SYS_STUH1VS5X9F1_EEZ1S2XSM741O) que representa as estatísticas geradas para a coluna com a função UPPER().
Executar a consulta novamente
Finalmente vamos executar a consulta novamente e verificar a qualidade da cardinalidade estimada pelo Otimizador.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SELECT /* dbtw102 */ count(1) Qtde 2 FROM sh.customers 3 WHERE UPPER(CUST_CITY) = UPPER('Joinville'); QTDE ---------- 98 SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw102%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ cdhk4zdy1qa2b 0 SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('cdhk4zdy1qa2b', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cdhk4zdy1qa2b, child number 0 ------------------------------------- SELECT /* dbtw102 */ count(:"SYS_B_0") Qtde FROM sh.customers WHERE UPPER(CUST_CITY) = UPPER(:"SYS_B_1") Plan hash value: 296924608 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1456 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1456 | |* 2 | TABLE ACCESS FULL| CUSTOMERS | 1 | 68 | 98 |00:00:00.01 | 1456 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(UPPER("CUST_CITY")=UPPER(:SYS_B_1)) 20 linhas selecionadas. SQL > |
Analisando o plano de execução acima verificamos que a cardinalidade estimada pelo Otimizador ficou muito próxima da realidade (E-ROWS=68 registros), para uma quantidade real de 98 registros (A-ROWS).
Conclusão
Nesse artigo verificamos na prática que as estatísticas, na sua configuração padrão, não conseguem suprir todas as informações que o Otimizador necessita para realizar estimativas de cardinalidade com precisão, o gerenciamento da coleta de estatísticas é fundamental para garantir a criação de estatísticas adicionais de colunas de tabelas que utilizem função nos filtros da cláusula WHERE das instruções SQL. O pacote DBMS_STATS pode e deve ser utilizado para fazer os ajustes necessários no processo de coleta de estatísticas de acordo com as características dos dados e como eles são acessados.
Referências
http://gavinsoorma.com/wp-content/uploads/2011/03/top_tips_for_optimal_sql_execution.pdf
https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68491
