{"id":1984,"date":"2019-04-07T22:49:23","date_gmt":"2019-04-08T01:49:23","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1984"},"modified":"2019-11-11T18:32:19","modified_gmt":"2019-11-11T21:32:19","slug":"anabolizante-para-o-otimizador","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/anabolizante-para-o-otimizador\/","title":{"rendered":"Anabolizante para o Otimizador"},"content":{"rendered":"

O Otimizador do Oracle cria planos de execu\u00e7\u00e3o com alta performance baseado nas estat\u00edsticas dos objetos no banco de dados, com estas informa\u00e7\u00f5es ele consegue fazer estimativas de acesso para cada uma das opera\u00e7\u00f5es do plano de execu\u00e7\u00e3o e a qualidade destas estimativas \u00e9 que determina o desempenho das instru\u00e7\u00f5es SQL. O padr\u00e3o de coleta de estat\u00edsticas \u00e9 baseado em cada coluna de uma tabela, este padr\u00e3o atende bem a necessidade do Otimizador quando ele cria um plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL cujo filtro na cl\u00e1usula WHERE \u00e9 composto de apenas uma coluna da tabela, porem quando a consulta possui duas ou mais colunas da tabela no filtro da cl\u00e1usula WHERE as estat\u00edsticas individuais de colunas da tabela n\u00e3o garantem uma precis\u00e3o adequada no c\u00e1lculo da CARDINALIDADE<\/strong><\/a> da opera\u00e7\u00e3o e pode levar o Otimizador a montar uma plano de execu\u00e7\u00e3o cujo desempenho fique abaixo do esperado. O banco de dados Oracle permite a utiliza\u00e7\u00e3o de um processo anabolizante que vai melhorar a qualidade das estat\u00edsticas e ampliar a precis\u00e3o do Otimizador na estimativa da CARDINALIDADE das opera\u00e7\u00f5es no plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Neste artigo vamos mostrar um exemplo pr\u00e1tico de um processo que permite a cria\u00e7\u00e3o de ESTAT\u00cdSTICAS ESTENDIDAS<\/strong><\/a> no atacado, ou seja, o Oracle vai monitorar as instru\u00e7\u00f5es SQL executadas num determinado per\u00edodo e baseado nos filtros utilizados na cl\u00e1usula WHERE destas instru\u00e7\u00f5es ele vai criar todas as ESTAT\u00cdSTICAS ESTENDIDAS necess\u00e1rias para melhorar a estimativa de CARDINALIDADE da opera\u00e7\u00f5es realizadas pelo Otimizador.<\/span><\/p>\n

Para realizar a simula\u00e7\u00e3o do processo de coleta de estat\u00edsticas para grupos de colunas vamos executar as seguintes etapas:<\/span><\/p>\n

1) Criar as tabelas e \u00edndices para a simula\u00e7\u00e3o<\/strong><\/span>
\n2) Executar a consulta sem a estat\u00edstica de grupo de colunas<\/strong><\/span>
\n3) Ativar monitora\u00e7\u00e3o de grupo de colunas utilizadas como filtro<\/strong><\/span>
\n4) Executar consulta que utiliza grupo de colunas como filtro<\/strong><\/span>
\n5) Gerar relat\u00f3rio para verificar os grupos de colunas identificados<\/strong><\/span>
\n6) Criar as estat\u00edsticas dos grupos de colunas identificados<\/strong><\/span>
\n7) Executar a consulta novamente para verificar a melhoria no plano de execu\u00e7\u00e3o<\/strong><\/span><\/p>\n

1) Criar as tabelas e \u00edndices para a simula\u00e7\u00e3o<\/span><\/h2>\n

Vamos criar os objetos necess\u00e1rios para realizar a nossa simula\u00e7\u00e3o e coletar as estat\u00edsticas destes objetos.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> DROP TABLE tab01 purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span> DROP TABLE tab02 purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span> DROP TABLE tab03 purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE tab01
\n  2<\/span>  AS<\/span>
\n  3<\/span>  SELECT rownum col01,<\/span>
\n  4<\/span>      MOD(<\/span>level,<\/span>  20<\/span>)<\/span> col02,<\/span>
\n  5<\/span>      MOD(<\/span>level,<\/span> 200<\/span>)<\/span> col03,<\/span>
\n  6<\/span>      MOD(<\/span>level,<\/span>1000<\/span>)<\/span> col04,<\/span>
\n  7<\/span>      lpad(<\/span>'x'<\/span>,<\/span>40<\/span>,<\/span>'x'<\/span>)<\/span> col05
\n  8<\/span>  FROM dual
\n  9<\/span>  CONNECT BY level <=<\/span> 10000<\/span>
\n 10<\/span>  \/<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE tab02
\n  2<\/span>  AS<\/span>
\n  3<\/span>  SELECT rownum col01,<\/span>
\n  4<\/span>      MOD(<\/span>level,<\/span>  20<\/span>)<\/span> col02,<\/span>
\n  5<\/span>      MOD(<\/span>level,<\/span> 200<\/span>)<\/span> col03,<\/span>
\n  6<\/span>      MOD(<\/span>level,<\/span>1000<\/span>)<\/span> col04,<\/span>
\n  7<\/span>      lpad(<\/span>'y'<\/span>,<\/span>40<\/span>,<\/span>'y'<\/span>)<\/span> col05
\n  8<\/span>  FROM dual
\n  9<\/span>  CONNECT BY level <=<\/span> 10<\/span>
\n 10<\/span>  \/<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE tab03
\n  2<\/span>  AS<\/span>
\n  3<\/span>  SELECT rownum col01,<\/span> lpad(<\/span>'z'<\/span>,<\/span>40<\/span>,<\/span>'z'<\/span>)<\/span> col02
\n  4<\/span>  FROM dual
\n  5<\/span>  CONNECT BY level <=<\/span> 10000<\/span>
\n  6<\/span>  \/<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>USER,<\/span>'tab01'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> EXEC<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>USER,<\/span>'tab02'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> EXEC<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>USER,<\/span>'tab03'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER TABLE tab01 ADD CONSTRAINT tab01col01 PRIMARY KEY<\/span><\/a>(<\/span>col01)<\/span>;<\/span>
\n
\nTabela alterada.<\/span>
\n
\nSQL ><\/span> ALTER TABLE tab02 ADD CONSTRAINT tab02col01 PRIMARY KEY<\/span><\/a>(<\/span>col01)<\/span>;<\/span>
\n
\nTabela alterada.<\/span>
\n
\nSQL ><\/span> ALTER TABLE tab03 ADD CONSTRAINT tab03col01 PRIMARY KEY<\/span><\/a>(<\/span>col01)<\/span>;<\/span>
\n
\nTabela alterada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Executar a consulta sem a estat\u00edstica de grupo de colunas<\/span><\/h2>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> WITH visao AS<\/span> (<\/span>
\n  2<\/span>  SELECT t1.<\/span>col05 v1,<\/span>t2.<\/span>col05 v2,<\/span>t3.<\/span>col02 v3
\n  3<\/span>    FROM tab01 t1,<\/span>
\n  4<\/span>         tab02 t2,<\/span>
\n  5<\/span>         tab03 t3
\n  6<\/span>   WHERE t1.<\/span>col02 =<\/span> t2.<\/span>col02
\n  7<\/span>     AND t1.<\/span>col03 =<\/span> t2.<\/span>col03
\n  8<\/span>     AND t1.<\/span>col01 =<\/span> t3.<\/span>col01)<\/span>
\n  9<\/span>  select \/* DBTW059.1 *\/<\/span>  *<\/span> from visao where rownum <<\/span> 51<\/span>;<\/span>
\n
\nV1                                       V2                                       V3
\n----------------------------------------<\/span> ----------------------------------------<\/span> ----------------------------------------<\/span>
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\n  .<\/span>
\n  .<\/span>
\n  .<\/span>
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\n
\n50<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW059.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n2kvfq9y3nw9hf            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2kvfq9y3nw9hf,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nWITH visao AS<\/span> (<\/span> SELECT t1.<\/span>col05 v1,<\/span>t2.<\/span>col05 v2,<\/span>t3.<\/span>col02 v3   FROM tab01
\nt1,<\/span>        tab02 t2,<\/span>        tab03 t3  WHERE t1.<\/span>col02 =<\/span> t2.<\/span>col02    AND
\nt1.<\/span>col03 =<\/span> t2.<\/span>col03    AND t1.<\/span>col01 =<\/span> t3.<\/span>col01)<\/span> select \/* DBTW059.1 *\/<\/span>
\n*<\/span> from visao where rownum <<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 328153508<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation            |<\/span> Name  |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT     |<\/span>       |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>    11<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     50<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     101<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  COUNT<\/span><\/a> STOPKEY       |<\/span>       |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>     50<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     101<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   HASH<\/span><\/a> JOIN<\/span><\/a>          |<\/span>       |<\/span>      1<\/span> |<\/span>     25<\/span> |<\/span>  3600<\/span> |<\/span>    11<\/span>  (<\/span>10<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     50<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     101<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    HASH<\/span><\/a> JOIN<\/span><\/a>         |<\/span>       |<\/span>      1<\/span> |<\/span>     25<\/span> |<\/span>  2475<\/span> |<\/span>     6<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      89<\/span> |<\/span>
\n|<\/span>   4<\/span> |<\/span>     TABLE ACCESS FULL|<\/span> TAB02 |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>   470<\/span> |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|<\/span>   5<\/span> |<\/span>     TABLE ACCESS FULL|<\/span> TAB01 |<\/span>      1<\/span> |<\/span>  10000<\/span> |<\/span>   507K|<\/span>     4<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>  10000<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      87<\/span> |<\/span>
\n|<\/span>   6<\/span> |<\/span>    TABLE ACCESS FULL |<\/span> TAB03 |<\/span>      1<\/span> |<\/span>  10000<\/span> |<\/span>   439K|<\/span>     4<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    810<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>ROWNUM<:<\/span>SYS_B_0)<\/span>
\n   2<\/span> -<\/span> access(<\/span>"T1"<\/span>.<\/span>"COL01"<\/span>=<\/span>"T3"<\/span>.<\/span>"COL01"<\/span>)<\/span>
\n   3<\/span> -<\/span> access(<\/span>"T1"<\/span>.<\/span>"COL02"<\/span>=<\/span>"T2"<\/span>.<\/span>"COL02"<\/span> AND "T1"<\/span>.<\/span>"COL03"<\/span>=<\/span>"T2"<\/span>.<\/span>"COL03"<\/span>)<\/span>
\n
\n
\n28<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o acima que na opera\u00e7\u00e3o de ID=3 (HASH JOIN) o Otimizador estimou que seriam selecionadas 25 linhas na tabela quando na verdade foram lidas 500 linhas, uma diferen\u00e7a muito significativa que pode levar o Otimizador a escolher uma plano de execu\u00e7\u00e3o inadequado.<\/span><\/p>\n

3) Ativar monitora\u00e7\u00e3o de grupo de colunas utilizadas como filtro<\/span><\/h2>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<\/div><\/td>
SQL ><\/span> conn sys\/<\/span>password@<\/span>lab01 as<\/span> sysdba;<\/span>
\nConnected.<\/span>
\nSQL ><\/span> grant ANALYZE ANY to curso03;<\/span>
\n
\nConcess\u00e3o bem-<\/span>sucedida.<\/span>
\n
\nSQL ><\/span> grant ANALYZE ANY DICTIONARY to curso03;<\/span>
\n
\nConcess\u00e3o bem-<\/span>sucedida.<\/span>
\n
\nSQL ><\/span> conn curso03\/<\/span>curso03@<\/span>lab01;<\/span>
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>seed_col_usage(<\/span>NULL<\/span>,<\/span> NULL<\/span>,<\/span> 180<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Para executar a procedure SEED_COL_USAGE o usu\u00e1rio precisa ter os privil\u00e9gios ANALYZE ANY e ANALYZE ANY DICTIONARY, os dois primeiros par\u00e2metros passados para a procedure s\u00e3o utilizados quando criamos um SQLSET, na nossa simula\u00e7\u00e3o vamos deixar estes par\u00e2metros como NULL e informar somente o terceiro par\u00e2metro que representa o numero de segundos durante os quais o banco vai monitorar a utiliza\u00e7\u00e3o de grupos de colunas das instru\u00e7\u00f5es SQL.<\/span><\/p>\n

4) Executar consulta que utiliza grupo de colunas como filtro<\/span><\/h2>\n

Ativada a monitora\u00e7\u00e3o, nos pr\u00f3ximos 180 segundos todas as instru\u00e7\u00f5es que forem executadas no banco ser\u00e3o examinadas quanto a utiliza\u00e7\u00e3o de grupos de colunas de uma mesma tabela sendo utilizadas no filtro da cl\u00e1usula WHERE. Na nossa simula\u00e7\u00e3o vamos executar a mesma consulta da etapa 2.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<\/div><\/td>
SQL ><\/span> WITH visao AS<\/span> (<\/span>
\n  2<\/span>  SELECT t1.<\/span>col05 v1,<\/span>t2.<\/span>col05 v2,<\/span>t3.<\/span>col02 v3
\n  3<\/span>    FROM tab01 t1,<\/span>
\n  4<\/span>         tab02 t2,<\/span>
\n  5<\/span>         tab03 t3
\n  6<\/span>   WHERE t1.<\/span>col02 =<\/span> t2.<\/span>col02
\n  7<\/span>     AND t1.<\/span>col03 =<\/span> t2.<\/span>col03
\n  8<\/span>     AND t1.<\/span>col01 =<\/span> t3.<\/span>col01)<\/span>
\n  9<\/span>  select \/* DBTW059.2 *\/<\/span>  *<\/span> from visao where rownum <<\/span> 51<\/span>;<\/span>
\n
\nV1                                       V2                                       V3
\n----------------------------------------<\/span> ----------------------------------------<\/span> ----------------------------------------<\/span>
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\n   .<\/span>
\n   .<\/span>
\n   .<\/span>
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\n
\n50<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

5) Gerar relat\u00f3rio para verificar os grupos de colunas identificados<\/span><\/h2>\n

A procedure REPORT_COL_USAGE do pacote DBMS_STATS gera um relat\u00f3rio contendo todos os grupos de colunas utilizados no filtro da cl\u00e1usula WHERE das instru\u00e7\u00f5es SQL executadas durante o per\u00edodo de monitora\u00e7\u00e3o que iniciamos na etapa anterior, esta procedure possui dois par\u00e2metros, o primeiro \u00e9 o SCHEMA da tabela e o segundo o nome da tabela, deixando o segundo par\u00e2metro como NULL ser\u00e1 gerado um relat\u00f3rio para todas as tabelas do SCHEMA.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SET LONG 100000<\/span>
\nSQL ><\/span> SET LINES 120<\/span>
\nSQL ><\/span> SET PAGES 50<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT DBMS_STATS.<\/span>report_col_usage(<\/span>USER,<\/span> NULL<\/span>)<\/span> FROM   dual;<\/span>
\n
\nDBMS_STATS.<\/span>REPORT_COL_USAGE(<\/span>USER,<\/span>NULL<\/span>)<\/span>
\n--------------------------------------------------------------------------------<\/span>
\nLEGEND:<\/span>
\n.......<\/span>
\n
\nEQ         :<\/span> Used in single table EQuality predicate
\nRANGE<\/span><\/a>      :<\/span> Used in single table RANGE<\/span><\/a> predicate
\nLIKE       :<\/span> Used in single table LIKE predicate
\nNULL<\/span>       :<\/span> Used in single table is (<\/span>not)<\/span> NULL<\/span> predicate
\nEQ_JOIN    :<\/span> Used in EQuality JOIN<\/span><\/a> predicate
\nNONEQ_JOIN :<\/span> Used in NON EQuality JOIN<\/span><\/a> predicate
\nFILTER     :<\/span> Used in single table FILTER predicate
\nJOIN<\/span><\/a>       :<\/span> Used in JOIN<\/span><\/a> predicate
\nGROUP_BY   :<\/span> Used in GROUP BY expression
\n...............................................................................<\/span>
\n
\n###############################################################################
\n<\/span>
\nCOLUMN USAGE REPORT FOR<\/span> CURSO03.<\/span>TAB01
\n.....................................<\/span>
\n
\n1<\/span>.<\/span> COL01                               :<\/span> EQ_JOIN
\n2<\/span>.<\/span> COL02                               :<\/span> EQ_JOIN
\n3<\/span>.<\/span> COL03                               :<\/span> EQ_JOIN
\n4<\/span>.<\/span> (<\/span>COL02,<\/span> COL03)<\/span>                      :<\/span> JOIN<\/span><\/a>
\n###############################################################################
\n<\/span>
\n###############################################################################
\n<\/span>
\nCOLUMN USAGE REPORT FOR<\/span> CURSO03.<\/span>TAB02
\n.....................................<\/span>
\n
\n1<\/span>.<\/span> COL02                               :<\/span> EQ_JOIN
\n2<\/span>.<\/span> COL03                               :<\/span> EQ_JOIN
\n3<\/span>.<\/span> (<\/span>COL02,<\/span> COL03)<\/span>                      :<\/span> JOIN<\/span><\/a>
\n###############################################################################
\n<\/span>
\n###############################################################################
\n<\/span>
\nCOLUMN USAGE REPORT FOR<\/span> CURSO03.<\/span>TAB03
\n.....................................<\/span>
\n
\n1<\/span>.<\/span> COL01                               :<\/span> EQ_JOIN
\n###############################################################################
\n<\/span>
\n
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

6) Criar as estat\u00edsticas dos grupos de colunas identificados<\/span><\/h2>\n

A procedure CREATE_EXTENDED_STATS do pacote DBMS_STATS cria as estat\u00edsticas estendidas para os grupos de colunas identificados na etapa 4, esta procedure possui dois par\u00e2metros, o primeiro \u00e9 o SCHEMA da tabela e o segundo o nome da tabela, deixando o segundo par\u00e2metro como NULL ser\u00e3o criadas todas as estat\u00edsticas estendidas para todas as tabelas do SCHEMA. Alem da cria\u00e7\u00e3o das estat\u00edsticas estendidas \u00e9 necess\u00e1rio coleta-las manualmente pois o processo de coleta de estat\u00edsticas n\u00e3o \u00e9 din\u00e2mico, ele ocorre normalmente de forma programada no per\u00edodo da noite.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT DBMS_STATS.<\/span>create_extended_stats(<\/span>USER,<\/span> NULL<\/span>)<\/span> FROM   dual;<\/span>
\n
\nDBMS_STATS.<\/span>CREATE_EXTENDED_STATS(<\/span>USER,<\/span>NULL<\/span>)<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n###############################################################################
\n<\/span>
\nEXTENSIONS FOR<\/span> CURSO03.<\/span>TAB01
\n............................<\/span>
\n
\n1<\/span>.<\/span> (<\/span>COL02,<\/span> COL03)<\/span>                      :<\/span> SYS_STU#0G3WGNECOTHXW8K0TKS02G created
\n<\/span>###############################################################################
\n<\/span>
\n###############################################################################
\n<\/span>
\nEXTENSIONS FOR<\/span> CURSO03.<\/span>TAB02
\n............................<\/span>
\n
\n1<\/span>.<\/span> (<\/span>COL02,<\/span> COL03)<\/span>                      :<\/span> SYS_STU#0G3WGNECOTHXW8K0TKS02G created
\n<\/span>###############################################################################
\n<\/span>
\n
\n
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>gather_schema_stats(<\/span>USER,<\/span> method_opt =><\/span> 'for all columns size auto'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT table_name,<\/span> column_name,<\/span> num_distinct,<\/span> density,<\/span> num_nulls,<\/span> histogram,<\/span> num_buckets
\n  2<\/span>    FROM user_tab_col_statistics
\n  3<\/span>   WHERE table_name in (<\/span>'TAB01'<\/span>,<\/span>'TAB02'<\/span>)<\/span>;<\/span>
\n
\nTABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS HISTOGRAM       NUM_BUCKETS
\n------------------------------<\/span> ------------------------------<\/span> ------------<\/span> ----------<\/span> ----------<\/span> ---------------<\/span> -----------<\/span>
\nTAB01                          COL01                                 10000<\/span>      ,<\/span>0001<\/span>          0<\/span> NONE                      1<\/span>
\nTAB01                          COL02                                    20<\/span>     ,<\/span>00005<\/span>          0<\/span> FREQUENCY                20<\/span>
\nTAB01                          COL03                                   200<\/span>     ,<\/span>00005<\/span>          0<\/span> FREQUENCY               200<\/span>
\nTAB01                          COL04                                  1000<\/span>       ,<\/span>001<\/span>          0<\/span> NONE                      1<\/span>
\nTAB01                          COL05                                     1<\/span>          1<\/span>          0<\/span> NONE                      1<\/span>
\nTAB01                          SYS_STU#0G3WGNECOTHXW8K0TKS02G          200       ,005          0 NONE                      1
\n<\/span>TAB02                          COL01                                    10<\/span>         ,<\/span>1<\/span>          0<\/span> NONE                      1<\/span>
\nTAB02                          COL02                                    10<\/span>         ,<\/span>1<\/span>          0<\/span> NONE                      1<\/span>
\nTAB02                          COL03                                    10<\/span>         ,<\/span>1<\/span>          0<\/span> NONE                      1<\/span>
\nTAB02                          COL04                                    10<\/span>         ,<\/span>1<\/span>          0<\/span> NONE                      1<\/span>
\nTAB02                          COL05                                     1<\/span>          1<\/span>          0<\/span> NONE                      1<\/span>
\nTAB02                          SYS_STU#0G3WGNECOTHXW8K0TKS02G           10         ,1          0 NONE                      1
\n<\/span>
\n12<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

7) Executar a consulta novamente para verificar a melhoria no plano de execu\u00e7\u00e3o<\/span><\/h2>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> WITH visao AS<\/span> (<\/span>
\n  2<\/span>  SELECT t1.<\/span>col05 v1,<\/span>t2.<\/span>col05 v2,<\/span>t3.<\/span>col02 v3
\n  3<\/span>    FROM tab01 t1,<\/span>
\n  4<\/span>         tab02 t2,<\/span>
\n  5<\/span>         tab03 t3
\n  6<\/span>   WHERE t1.<\/span>col02 =<\/span> t2.<\/span>col02
\n  7<\/span>     AND t1.<\/span>col03 =<\/span> t2.<\/span>col03
\n  8<\/span>     AND t1.<\/span>col01 =<\/span> t3.<\/span>col01)<\/span>
\n  9<\/span>  select \/* DBTW059.3 *\/<\/span>  *<\/span> from visao where rownum <<\/span> 51<\/span>;<\/span>
\n
\nV1                                       V2                                       V3
\n----------------------------------------<\/span> ----------------------------------------<\/span> ----------------------------------------<\/span>
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\n   .<\/span>
\n   .<\/span>
\n   .<\/span>
\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
\n
\n50<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW059.3%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n0d2k76dcn6sf8            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  0d2k76dcn6sf8,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nWITH visao AS<\/span> (<\/span> SELECT t1.<\/span>col05 v1,<\/span>t2.<\/span>col05 v2,<\/span>t3.<\/span>col02 v3   FROM tab01
\nt1,<\/span>        tab02 t2,<\/span>        tab03 t3  WHERE t1.<\/span>col02 =<\/span> t2.<\/span>col02    AND
\nt1.<\/span>col03 =<\/span> t2.<\/span>col03    AND t1.<\/span>col01 =<\/span> t3.<\/span>col01)<\/span> select \/* DBTW059.3 *\/<\/span>
\n*<\/span> from visao where rownum <<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 732749324<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation            |<\/span> Name  |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT     |<\/span>       |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>     8<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     50<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      87<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  COUNT<\/span><\/a> STOPKEY       |<\/span>       |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>     50<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      87<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   HASH<\/span><\/a> JOIN<\/span><\/a>          |<\/span>       |<\/span>      1<\/span> |<\/span>     50<\/span> |<\/span> 14650<\/span> |<\/span>     8<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     50<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      87<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS FULL |<\/span> TAB02 |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>   470<\/span> |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>    HASH<\/span><\/a> JOIN<\/span><\/a>         |<\/span>       |<\/span>      1<\/span> |<\/span>   1000<\/span> |<\/span>   145K|<\/span>     6<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    810<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      85<\/span> |<\/span>
\n|<\/span>   5<\/span> |<\/span>     TABLE ACCESS FULL|<\/span> TAB03 |<\/span>      1<\/span> |<\/span>  10000<\/span> |<\/span>   439K|<\/span>     4<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>  10000<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      72<\/span> |<\/span>
\n|<\/span>   6<\/span> |<\/span>     TABLE ACCESS FULL|<\/span> TAB01 |<\/span>      1<\/span> |<\/span>  10000<\/span> |<\/span>   507K|<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    810<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      13<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>ROWNUM<:<\/span>SYS_B_0)<\/span>
\n   2<\/span> -<\/span> access(<\/span>"T1"<\/span>.<\/span>"COL02"<\/span>=<\/span>"T2"<\/span>.<\/span>"COL02"<\/span> AND "T1"<\/span>.<\/span>"COL03"<\/span>=<\/span>"T2"<\/span>.<\/span>"COL03"<\/span>)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"T1"<\/span>.<\/span>"COL01"<\/span>=<\/span>"T3"<\/span>.<\/span>"COL01"<\/span>)<\/span>
\n
\n
\n28<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o que a estimativa de cardinalidade das opera\u00e7\u00f5es est\u00e3o muito pr\u00f3ximas do numero real de linhas selecionadas, como a estimativa de cardinalidade melhorou o Otimizador gerou um plano de execu\u00e7\u00e3o mais eficiente, neste plano de execu\u00e7\u00e3o foram lidos 87 Buffers enquanto no plano de execu\u00e7\u00e3o inicial foram 101 Buffers. Esta diferen\u00e7a de desempenho \u00e9 pequena mas em consultas mais complexas estes erros de estimativas de cardinalidade podem gerar planos de execu\u00e7\u00e3o com desempenho insatisfat\u00f3rio para os usu\u00e1rios.<\/span><\/p>\n

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

 <\/p>\n

https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/12.2\/<\/a><\/span><\/strong><\/p>\n

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

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

O Otimizador do Oracle cria planos de execu\u00e7\u00e3o com alta performance baseado nas estat\u00edsticas dos objetos no banco de dados, com estas informa\u00e7\u00f5es ele consegue fazer estimativas de acesso para cada uma das opera\u00e7\u00f5es do plano de execu\u00e7\u00e3o e a qualidade destas estimativas \u00e9 que determina o desempenho das instru\u00e7\u00f5es SQL. O padr\u00e3o de coleta de estat\u00edsticas \u00e9 baseado em<\/p>\n","protected":false},"author":2,"featured_media":1985,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[204,181,163,39,182,127,82,10,215,214,119],"class_list":["post-1984","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-column-group-statistics","tag-create_extended_stats","tag-dbms_stats","tag-estatisticas","tag-estatisticas-estendidas","tag-extended-statistics","tag-optimizer","tag-otimizador","tag-report_col_usage","tag-seed_col_usage","tag-statistics"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1984","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=1984"}],"version-history":[{"count":12,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1984\/revisions"}],"predecessor-version":[{"id":2069,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1984\/revisions\/2069"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1985"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1984"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1984"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1984"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}