Anabolizante para o Otimizador

Extended statistics

O Otimizador do Oracle cria planos de execução com alta performance baseado nas estatísticas dos objetos no banco de dados, com estas informações ele consegue fazer estimativas de acesso para cada uma das operações do plano de execução e a qualidade destas estimativas é que determina o desempenho das instruções SQL. O padrão de coleta de estatísticas é baseado em cada coluna de uma tabela, este padrão atende bem a necessidade do Otimizador quando ele cria um plano de execução de uma instrução SQL cujo filtro na cláusula WHERE é composto de apenas uma coluna da tabela, porem quando a consulta possui duas ou mais colunas da tabela no filtro da cláusula WHERE as estatísticas individuais de colunas da tabela não garantem uma precisão adequada no cálculo da CARDINALIDADE da operação e pode levar o Otimizador a montar uma plano de execução cujo desempenho fique abaixo do esperado. O banco de dados Oracle permite a utilização de um processo anabolizante que vai melhorar a qualidade das estatísticas e ampliar a precisão do Otimizador na estimativa da CARDINALIDADE das operações no plano de execução.

Neste artigo vamos mostrar um exemplo prático de um processo que permite a criação de ESTATÍSTICAS ESTENDIDAS no atacado, ou seja, o Oracle vai monitorar as instruções SQL executadas num determinado período e baseado nos filtros utilizados na cláusula WHERE destas instruções ele vai criar todas as ESTATÍSTICAS ESTENDIDAS necessárias para melhorar a estimativa de CARDINALIDADE da operações realizadas pelo Otimizador.

Para realizar a simulação do processo de coleta de estatísticas para grupos de colunas vamos executar as seguintes etapas:

1) Criar as tabelas e índices para a simulação
2) Executar a consulta sem a estatística de grupo de colunas
3) Ativar monitoração de grupo de colunas utilizadas como filtro
4) Executar consulta que utiliza grupo de colunas como filtro
5) Gerar relatório para verificar os grupos de colunas identificados
6) Criar as estatísticas dos grupos de colunas identificados
7) Executar a consulta novamente para verificar a melhoria no plano de execução

1) Criar as tabelas e índices para a simulação

Vamos criar os objetos necessários para realizar a nossa simulação e coletar as estatísticas destes objetos.

 

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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

1 linha selecionada.

SQL >
SQL > DROP TABLE tab01 purge;

Tabela eliminada.

SQL > DROP TABLE tab02 purge;

Tabela eliminada.

SQL > DROP TABLE tab03 purge;

Tabela eliminada.

SQL >
SQL > CREATE TABLE tab01
  2  AS
  3  SELECT rownum col01,
  4      MOD(level,  20) col02,
  5      MOD(level, 200) col03,
  6      MOD(level,1000) col04,
  7      lpad('x',40,'x') col05
  8  FROM dual
  9  CONNECT BY level <= 10000
 10  /

Tabela criada.

SQL >
SQL > CREATE TABLE tab02
  2  AS
  3  SELECT rownum col01,
  4      MOD(level,  20) col02,
  5      MOD(level, 200) col03,
  6      MOD(level,1000) col04,
  7      lpad('y',40,'y') col05
  8  FROM dual
  9  CONNECT BY level <= 10
 10  /

Tabela criada.

SQL >
SQL > CREATE TABLE tab03
  2  AS
  3  SELECT rownum col01, lpad('z',40,'z') col02
  4  FROM dual
  5  CONNECT BY level <= 10000
  6  /

Tabela criada.

SQL >
SQL > EXEC dbms_stats.gather_table_stats(USER,'tab01',method_opt=>'for all columns size auto');

Procedimento PL/SQL concluído com sucesso.

SQL > EXEC dbms_stats.gather_table_stats(USER,'tab02',method_opt=>'for all columns size auto');

Procedimento PL/SQL concluído com sucesso.

SQL > EXEC dbms_stats.gather_table_stats(USER,'tab03',method_opt=>'for all columns size auto');

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > ALTER TABLE tab01 ADD CONSTRAINT tab01col01 PRIMARY KEY(col01);

Tabela alterada.

SQL > ALTER TABLE tab02 ADD CONSTRAINT tab02col01 PRIMARY KEY(col01);

Tabela alterada.

SQL > ALTER TABLE tab03 ADD CONSTRAINT tab03col01 PRIMARY KEY(col01);

Tabela alterada.

SQL >

 

2) Executar a consulta sem a estatística de grupo de colunas

 

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
74
75
76
77
78
79
80
81
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > WITH visao AS (
  2  SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3
  3    FROM tab01 t1,
  4         tab02 t2,
  5         tab03 t3
  6   WHERE t1.col02 = t2.col02
  7     AND t1.col03 = t2.col03
  8     AND t1.col01 = t3.col01)
  9  select /* DBTW059.1 */  * from visao where rownum < 51;

V1                                       V2                                       V3
---------------------------------------- ---------------------------------------- ----------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
  .
  .
  .
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

50 linhas selecionadas.

SQL >
SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW059.1%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
2kvfq9y3nw9hf            0

1 linha selecionada.

SQL >
SQL > -- Gera o relatório do plano de execução da consulta
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2kvfq9y3nw9hf, child number 0
-------------------------------------
WITH visao AS ( SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3   FROM tab01
t1,        tab02 t2,        tab03 t3  WHERE t1.col02 = t2.col02    AND
t1.col03 = t2.col03    AND t1.col01 = t3.col01) select /* DBTW059.1 */
* from visao where rownum < :"SYS_B_0"

Plan hash value: 328153508

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |       |    11 (100)|          |     50 |00:00:00.01 |     101 |
|*  1 |  COUNT STOPKEY       |       |      1 |        |       |            |          |     50 |00:00:00.01 |     101 |
|*  2 |   HASH JOIN          |       |      1 |     25 |  3600 |    11  (10)| 00:00:01 |     50 |00:00:00.01 |     101 |
|*  3 |    HASH JOIN         |       |      1 |     25 |  2475 |     6   (0)| 00:00:01 |    500 |00:00:00.01 |      89 |
|   4 |     TABLE ACCESS FULL| TAB02 |      1 |     10 |   470 |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       2 |
|   5 |     TABLE ACCESS FULL| TAB01 |      1 |  10000 |   507K|     4   (0)| 00:00:01 |  10000 |00:00:00.01 |      87 |
|   6 |    TABLE ACCESS FULL | TAB03 |      1 |  10000 |   439K|     4   (0)| 00:00:01 |    810 |00:00:00.01 |      12 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<:SYS_B_0)
   2 - access("T1"."COL01"="T3"."COL01")
   3 - access("T1"."COL02"="T2"."COL02" AND "T1"."COL03"="T2"."COL03")


28 linhas selecionadas.

SQL >

 

Observe no plano de execução acima que na operação de ID=3 (HASH JOIN) o Otimizador estimou que seriam selecionadas 25 linhas na tabela quando na verdade foram lidas 500 linhas, uma diferença muito significativa que pode levar o Otimizador a escolher uma plano de execução inadequado.

3) Ativar monitoração de grupo de colunas utilizadas como filtro

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL > conn sys/password@lab01 as sysdba;
Connected.
SQL > grant ANALYZE ANY to curso03;

Concessão bem-sucedida.

SQL > grant ANALYZE ANY DICTIONARY to curso03;

Concessão bem-sucedida.

SQL > conn curso03/curso03@lab01;
SQL > EXEC DBMS_STATS.seed_col_usage(NULL, NULL, 180);

Procedimento PL/SQL concluído com sucesso.

SQL >

 

Para executar a procedure SEED_COL_USAGE o usuário precisa ter os privilégios ANALYZE ANY e ANALYZE ANY DICTIONARY, os dois primeiros parâmetros passados para a procedure são utilizados quando criamos um SQLSET, na nossa simulação vamos deixar estes parâmetros como NULL e informar somente o terceiro parâmetro que representa o numero de segundos durante os quais o banco vai monitorar a utilização de grupos de colunas das instruções SQL.

4) Executar consulta que utiliza grupo de colunas como filtro

Ativada a monitoração, nos próximos 180 segundos todas as instruções que forem executadas no banco serão examinadas quanto a utilização de grupos de colunas de uma mesma tabela sendo utilizadas no filtro da cláusula WHERE. Na nossa simulação vamos executar a mesma consulta da etapa 2.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL > WITH visao AS (
  2  SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3
  3    FROM tab01 t1,
  4         tab02 t2,
  5         tab03 t3
  6   WHERE t1.col02 = t2.col02
  7     AND t1.col03 = t2.col03
  8     AND t1.col01 = t3.col01)
  9  select /* DBTW059.2 */  * from visao where rownum < 51;

V1                                       V2                                       V3
---------------------------------------- ---------------------------------------- ----------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
   .
   .
   .
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

50 linhas selecionadas.

SQL >

 

5) Gerar relatório para verificar os grupos de colunas identificados

A procedure REPORT_COL_USAGE do pacote DBMS_STATS gera um relatório contendo todos os grupos de colunas utilizados no filtro da cláusula WHERE das instruções SQL executadas durante o período de monitoração que iniciamos na etapa anterior, esta procedure possui dois parâmetros, o primeiro é o SCHEMA da tabela e o segundo o nome da tabela, deixando o segundo parâmetro como NULL será gerado um relatório para todas as tabelas do SCHEMA.

 

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 > SET LONG 100000
SQL > SET LINES 120
SQL > SET PAGES 50
SQL >
SQL > SELECT DBMS_STATS.report_col_usage(USER, NULL) FROM   dual;

DBMS_STATS.REPORT_COL_USAGE(USER,NULL)
--------------------------------------------------------------------------------
LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR CURSO03.TAB01
.....................................

1. COL01                               : EQ_JOIN
2. COL02                               : EQ_JOIN
3. COL03                               : EQ_JOIN
4. (COL02, COL03)                      : JOIN
###############################################################################

###############################################################################

COLUMN USAGE REPORT FOR CURSO03.TAB02
.....................................

1. COL02                               : EQ_JOIN
2. COL03                               : EQ_JOIN
3. (COL02, COL03)                      : JOIN
###############################################################################

###############################################################################

COLUMN USAGE REPORT FOR CURSO03.TAB03
.....................................

1. COL01                               : EQ_JOIN
###############################################################################



SQL >

 

6) Criar as estatísticas dos grupos de colunas identificados

A procedure CREATE_EXTENDED_STATS do pacote DBMS_STATS cria as estatísticas estendidas para os grupos de colunas identificados na etapa 4, esta procedure possui dois parâmetros, o primeiro é o SCHEMA da tabela e o segundo o nome da tabela, deixando o segundo parâmetro como NULL serão criadas todas as estatísticas estendidas para todas as tabelas do SCHEMA. Alem da criação das estatísticas estendidas é necessário coleta-las manualmente pois o processo de coleta de estatísticas não é dinâmico, ele ocorre normalmente de forma programada no período da noite.

 

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
SQL > SELECT DBMS_STATS.create_extended_stats(USER, NULL) FROM   dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,NULL)
--------------------------------------------------------------------------------
###############################################################################

EXTENSIONS FOR CURSO03.TAB01
............................

1. (COL02, COL03)                      : SYS_STU#0G3WGNECOTHXW8K0TKS02G created
###############################################################################

###############################################################################

EXTENSIONS FOR CURSO03.TAB02
............................

1. (COL02, COL03)                      : SYS_STU#0G3WGNECOTHXW8K0TKS02G created
###############################################################################



SQL > EXEC DBMS_STATS.gather_schema_stats(USER, method_opt => 'for all columns size auto');

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > SELECT table_name, column_name, num_distinct, density, num_nulls, histogram, num_buckets
  2    FROM user_tab_col_statistics
  3   WHERE table_name in ('TAB01','TAB02');

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS HISTOGRAM       NUM_BUCKETS
------------------------------ ------------------------------ ------------ ---------- ---------- --------------- -----------
TAB01                          COL01                                 10000      ,0001          0 NONE                      1
TAB01                          COL02                                    20     ,00005          0 FREQUENCY                20
TAB01                          COL03                                   200     ,00005          0 FREQUENCY               200
TAB01                          COL04                                  1000       ,001          0 NONE                      1
TAB01                          COL05                                     1          1          0 NONE                      1
TAB01                          SYS_STU#0G3WGNECOTHXW8K0TKS02G          200       ,005          0 NONE                      1
TAB02                          COL01                                    10         ,1          0 NONE                      1
TAB02                          COL02                                    10         ,1          0 NONE                      1
TAB02                          COL03                                    10         ,1          0 NONE                      1
TAB02                          COL04                                    10         ,1          0 NONE                      1
TAB02                          COL05                                     1          1          0 NONE                      1
TAB02                          SYS_STU#0G3WGNECOTHXW8K0TKS02G           10         ,1          0 NONE                      1

12 linhas selecionadas.

SQL >

 

7) Executar a consulta novamente para verificar a melhoria no plano de execução

 

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
74
75
76
77
78
79
80
81
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > WITH visao AS (
  2  SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3
  3    FROM tab01 t1,
  4         tab02 t2,
  5         tab03 t3
  6   WHERE t1.col02 = t2.col02
  7     AND t1.col03 = t2.col03
  8     AND t1.col01 = t3.col01)
  9  select /* DBTW059.3 */  * from visao where rownum < 51;

V1                                       V2                                       V3
---------------------------------------- ---------------------------------------- ----------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
   .
   .
   .
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

50 linhas selecionadas.

SQL >
SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW059.3%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
0d2k76dcn6sf8            0

1 linha selecionada.

SQL >
SQL > -- Gera o relatório do plano de execução da consulta
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0d2k76dcn6sf8, child number 0
-------------------------------------
WITH visao AS ( SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3   FROM tab01
t1,        tab02 t2,        tab03 t3  WHERE t1.col02 = t2.col02    AND
t1.col03 = t2.col03    AND t1.col01 = t3.col01) select /* DBTW059.3 */
* from visao where rownum < :"SYS_B_0"

Plan hash value: 732749324

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |       |     8 (100)|          |     50 |00:00:00.01 |      87 |
|*  1 |  COUNT STOPKEY       |       |      1 |        |       |            |          |     50 |00:00:00.01 |      87 |
|*  2 |   HASH JOIN          |       |      1 |     50 | 14650 |     8   (0)| 00:00:01 |     50 |00:00:00.01 |      87 |
|   3 |    TABLE ACCESS FULL | TAB02 |      1 |     10 |   470 |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       2 |
|*  4 |    HASH JOIN         |       |      1 |   1000 |   145K|     6   (0)| 00:00:01 |    810 |00:00:00.01 |      85 |
|   5 |     TABLE ACCESS FULL| TAB03 |      1 |  10000 |   439K|     4   (0)| 00:00:01 |  10000 |00:00:00.01 |      72 |
|   6 |     TABLE ACCESS FULL| TAB01 |      1 |  10000 |   507K|     2   (0)| 00:00:01 |    810 |00:00:00.01 |      13 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<:SYS_B_0)
   2 - access("T1"."COL02"="T2"."COL02" AND "T1"."COL03"="T2"."COL03")
   4 - access("T1"."COL01"="T3"."COL01")


28 linhas selecionadas.

SQL >

 

Observe no plano de execução que a estimativa de cardinalidade das operações estão muito próximas do numero real de linhas selecionadas, como a estimativa de cardinalidade melhorou o Otimizador gerou um plano de execução mais eficiente, neste plano de execução foram lidos 87 Buffers enquanto no plano de execução inicial foram 101 Buffers. Esta diferença de desempenho é pequena mas em consultas mais complexas estes erros de estimativas de cardinalidade podem gerar planos de execução com desempenho insatisfatório para os usuários.

Referências:

 

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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