Funções na cláusula WHERE podem confundir o Otimizador?

estatísticas adicionais

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 >

 

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

 

Mídia social

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *