{"id":2112,"date":"2019-11-17T19:56:55","date_gmt":"2019-11-17T22:56:55","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=2112"},"modified":"2019-11-17T19:56:55","modified_gmt":"2019-11-17T22:56:55","slug":"quer-mais-performance-faca-upgrade-para-o-19c","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/quer-mais-performance-faca-upgrade-para-o-19c\/","title":{"rendered":"Quer mais performance? Fa\u00e7a upgrade para o 19c"},"content":{"rendered":"

O Otimizador do Oracle vem sendo aprimorado a cada nova vers\u00e3o, a partir da 12c foram introduzidas muitas melhorias que tornaram o Otimizador muito mais eficente na escolha dos planos de execu\u00e7\u00e3o, nas vers\u00f5es anteriores o Otimizador tinha algumas limita\u00e7\u00f5es que o impedia de escolher planos de execu\u00e7\u00e3o melhores. Na vers\u00e3o 19c o Otimizador n\u00e3o alcan\u00e7ou a perfei\u00e7\u00e3o, mas houve um avan\u00e7o gigantesco comparado com as vers\u00f5es 11g e anteriores. <\/span>
\n 
\n 
\nNeste artigo vamos fazer uma demonstra\u00e7\u00e3o pr\u00e1tica de uma dessas melhorias, mostrando uma limita\u00e7\u00e3o do Otimizador existente na vers\u00e3o 11g e como ele superou esta limita\u00e7\u00e3o produzindo planos de execu\u00e7\u00e3o com melhor desempenho na vers\u00e3o 18c.<\/span><\/p>\n

 
\n <\/p>\n

Na primeira simula\u00e7\u00e3o pr\u00e1tica vamos verificar a inefici\u00eancia do Otimizador na escolha do plano de execu\u00e7\u00e3o na vers\u00e3o 11gR2:<\/span><\/p>\n

1.1) Criar os objetos para simular a consulta <\/span><\/strong>
\n1.2) Executar a consulta com plano de execu\u00e7\u00e3o ineficiente<\/span><\/strong>
\n1.3) Executar a mesma consulta com HINTs para melhorar o seu desempenho<\/span><\/strong><\/p>\n

Na segunda simula\u00e7\u00e3o pr\u00e1tica vamos verificar como o Otimizador resolve esta limita\u00e7\u00e3o da vers\u00e3o 11gR2 produzindo um plano de execu\u00e7\u00e3o com melhor desempenho na vers\u00e3o 18c:<\/span><\/p>\n

2.1) Criar os objetos para simular a consulta <\/span><\/strong>
\n2.2) Executar a consulta com plano de execu\u00e7\u00e3o eficiente<\/span><\/strong><\/p>\n

PRIMEIRA SIMULA\u00c7\u00c3O (11gR2)<\/span><\/h1>\n

1.1) Criar os objetos para simular a consulta<\/span><\/h2>\n

Vamos criar os objetos necess\u00e1rios para realizar a nossa simula\u00e7\u00e3o.<\/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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
<\/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
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw_grupo (<\/span>key_id number,<\/span> id_grupo number,<\/span> nome_grupo varchar(<\/span>32<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> Declare<\/span>
\n  2<\/span>
\n  3<\/span>     num_lo01  INTEGER :=<\/span> 0<\/span>;<\/span>
\n  4<\/span>     num_lo02  INTEGER :=<\/span> 0<\/span>;<\/span>
\n  5<\/span>     num_ocor  INTEGER :=<\/span> 167<\/span>;<\/span>
\n  6<\/span>     row_count INTEGER :=<\/span> 1<\/span>;<\/span>
\n  7<\/span>
\n  8<\/span>  begin
\n  9<\/span>
\n 10<\/span>     FOR<\/span> c1 in (<\/span>select trunc(<\/span>dbms_random.<\/span>value(<\/span>0<\/span>,<\/span>1000<\/span>)<\/span>)<\/span> num_dist from dual connect by level <=<\/span> 1000<\/span>)<\/span>
\n 11<\/span>     LOOP
\n 12<\/span>        FOR<\/span> num_lo02 in 0<\/span> ..<\/span> num_ocor
\n 13<\/span>        LOOP
\n 14<\/span>          INSERT INTO dbtw_grupo values(<\/span>row_count,<\/span>c1.<\/span>num_dist,<\/span>'GRUPO_NUMERO-'<\/span>||<\/span>c1.<\/span>num_dist)<\/span>;<\/span>
\n 15<\/span>          row_count :=<\/span> row_count +<\/span> 1<\/span>;<\/span>
\n 16<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 17<\/span>        num_ocor :=<\/span> num_ocor -<\/span> 1<\/span>;<\/span>
\n 18<\/span>        if<\/span> num_ocor =<\/span> 0<\/span> then
\n 19<\/span>           num_ocor :=<\/span> 1<\/span>;<\/span>
\n 20<\/span>        end<\/span><\/a> if<\/span>;<\/span>
\n 21<\/span>     END<\/span><\/a> LOOP;<\/span>
\n 22<\/span>     commit;<\/span>
\n 23<\/span>
\n 24<\/span>  end<\/span><\/a>;<\/span>
\n 25<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw_id_grupo_idx on dbtw_grupo(<\/span>id_grupo)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_GRUPO'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/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>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_GRUPO'<\/span>,<\/span>method_opt=><\/span>'FOR COLUMNS ID_GRUPO SIZE 254'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw_usuario (<\/span>login varchar(<\/span>10<\/span>)<\/span>,<\/span> id_grupo number,<\/span> status varchar(<\/span>1<\/span>)<\/span>,<\/span> codigo number,<\/span> note varchar(<\/span>256<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> Declare<\/span>
\n  2<\/span>
\n  3<\/span>     num_lo01  INTEGER :=<\/span> 0<\/span>;<\/span>
\n  4<\/span>     num_ocor  INTEGER :=<\/span> 167<\/span>;<\/span>
\n  5<\/span>     l_id_grupo INTEGER;<\/span>
\n  6<\/span>     log_id INTEGER :=<\/span> 1<\/span>;<\/span>
\n  7<\/span>     l_key_id INTEGER;<\/span>
\n  8<\/span>
\n  9<\/span>  begin
\n 10<\/span>
\n 11<\/span>     FOR<\/span> num_lo01 in 1<\/span> ..<\/span> 350000<\/span>
\n 12<\/span>     LOOP
\n 13<\/span>       l_key_id :=<\/span> trunc(<\/span>dbms_random.<\/span>value(<\/span>1<\/span>,<\/span>14999<\/span>)<\/span>)<\/span>;<\/span>
\n 14<\/span>       select id_grupo into l_id_grupo from DBTW_GRUPO where key_id =<\/span> l_key_id;<\/span>
\n 15<\/span>       INSERT INTO dbtw_usuario values(<\/span>'LOG_'<\/span>||<\/span>LPAD(<\/span>log_id,<\/span>6<\/span>,<\/span>0<\/span>)<\/span>,<\/span>l_id_grupo,<\/span>'I'<\/span>,<\/span>LPAD(<\/span>log_id,<\/span>6<\/span>,<\/span>0<\/span>)<\/span>,<\/span>LPAD(<\/span>'X'<\/span>,<\/span>256<\/span>,<\/span>'X'<\/span>)<\/span>)<\/span>;<\/span>
\n 16<\/span>       log_id :=<\/span> log_id +<\/span> 1<\/span>;<\/span>
\n 17<\/span>     END<\/span><\/a> LOOP;<\/span>
\n 18<\/span>     commit;<\/span>
\n 19<\/span>
\n 20<\/span>  end<\/span><\/a>;<\/span>
\n 21<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw_usuario_id_grupo_idx on dbtw_usuario(<\/span>id_grupo)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> create index dbtw_usuario_status_idx on dbtw_usuario(<\/span>status)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> create index dbtw_usuario_login_idx on dbtw_usuario(<\/span>login)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> update dbtw_usuario set status =<\/span> 'N'<\/span> where codigo between  60000<\/span> and 120000<\/span>;<\/span>
\n
\n60001<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span> update dbtw_usuario set status =<\/span> 'F'<\/span> where codigo between 150000<\/span> and 153000<\/span>;<\/span>
\n
\n3001<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span> update dbtw_usuario set status =<\/span> 'A'<\/span> where codigo between 210000<\/span> and 212000<\/span>;<\/span>
\n
\n2001<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span> update dbtw_usuario set status =<\/span> 'R'<\/span> where codigo between 280000<\/span> and 280500<\/span>;<\/span>
\n
\n501<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> commit;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_USUARIO'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/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>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_USUARIO'<\/span>,<\/span>method_opt=><\/span>'FOR COLUMNS ID_GRUPO SIZE 254'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/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>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_USUARIO'<\/span>,<\/span>method_opt=><\/span>'FOR COLUMNS STATUS SIZE 254'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/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

1.2) Executar a consulta com plano de execu\u00e7\u00e3o ineficiente<\/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
82
83
84
85
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> variable LOGIN   VARCHAR2(<\/span>6<\/span>)<\/span>;<\/span>
\nSQL ><\/span> variable SYS_B_3 VARCHAR2(<\/span>32<\/span>)<\/span>;<\/span>
\nSQL ><\/span> variable SYS_B_4 VARCHAR2(<\/span>32<\/span>)<\/span>;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>SYS_B_3 :=<\/span> 'I'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>SYS_B_4 :=<\/span> 'R'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>LOGIN :=<\/span> '076955'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* dbtw-062.1 *\/<\/span> u.<\/span>codigo,<\/span> u.<\/span>login
\n  2<\/span>   FROM dbtw_usuario u
\n  3<\/span>  INNER JOIN<\/span><\/a> dbtw_grupo g
\n  4<\/span>     ON u.<\/span>id_grupo =<\/span> g.<\/span>id_grupo
\n  5<\/span>  WHERE u.<\/span>status NOT IN (<\/span>:<\/span>"SYS_B_3"<\/span>,<\/span> :<\/span>"SYS_B_4"<\/span>)<\/span>
\n  6<\/span>    AND u.<\/span>login like '%'<\/span>||:<\/span>LOGIN;<\/span>
\n
\n    CODIGO LOGIN
\n----------<\/span> ----------<\/span>
\n     76955<\/span> LOG_076955
\n     76955<\/span> LOG_076955
\n     76955<\/span> LOG_076955
\n      ..<\/span>   ..<\/span>
\n     76955<\/span> LOG_076955
\n
\n157<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span>
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number FROM v$sql<\/span> WHERE sql_text LIKE '%dbtw-062.1%'<\/span> AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ndrc438b9c12ty            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'BASIC ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'BASIC ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'drc438b9c12ty'<\/span>,<\/span>         0<\/span>,<\/span>'BASIC ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* dbtw-062.1 *\/<\/span> u.<\/span>codigo,<\/span> u.<\/span>login  FROM dbtw_usuario u INNER
\nJOIN<\/span><\/a> dbtw_grupo g    ON u.<\/span>id_grupo =<\/span> g.<\/span>id_grupo WHERE u.<\/span>status NOT IN
\n(<\/span>:<\/span>"SYS_B_3"<\/span>,<\/span> :<\/span>"SYS_B_4"<\/span>)<\/span>   AND u.<\/span>login like :<\/span>"B0"<\/span>||:<\/span>LOGIN
\n
\nPlan hash<\/span><\/a> value:<\/span> 2044621122<\/span>
\n
\n-----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation             |<\/span> Name              |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT      |<\/span>                   |<\/span>      1<\/span> |<\/span>        |<\/span>    157<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.70<\/span> |<\/span>   14230<\/span> |<\/span>  14211<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  HASH<\/span><\/a> JOIN<\/span><\/a>            |<\/span>                   |<\/span>      1<\/span> |<\/span>    345K|<\/span>    157<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.70<\/span> |<\/span>   14230<\/span> |<\/span>  14211<\/span> |<\/span>  1321K|<\/span>  1321K|<\/span>  721K (<\/span>0<\/span>)<\/span>|<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL   |<\/span> DBTW_USUARIO      |<\/span>      1<\/span> |<\/span>   3242<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.66<\/span> |<\/span>   14181<\/span> |<\/span>  14177<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   3<\/span> |<\/span>   INDEX FAST FULL SCAN|<\/span> DBTW_ID_GRUPO_IDX |<\/span>      1<\/span> |<\/span>  15861<\/span> |<\/span>  15861<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/span> |<\/span>      49<\/span> |<\/span>     34<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> access(<\/span>"U"<\/span>.<\/span>"ID_GRUPO"<\/span>=<\/span>"G"<\/span>.<\/span>"ID_GRUPO"<\/span>)<\/span>
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"U"<\/span>.<\/span>"STATUS"<\/span><>:<\/span>SYS_B_3 AND "U"<\/span>.<\/span>"LOGIN"<\/span> LIKE :<\/span>B0||:<\/span>LOGIN AND "U"<\/span>.<\/span>"STATUS"<\/span><>:<\/span>SYS_B_4)<\/span>)<\/span>
\n
\n
\n23<\/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 o Otimizador utilizou a opera\u00e7\u00e3o HASH JOIN para juntar as duas tabelas quando a melhor op\u00e7\u00e3o seria uma opera\u00e7\u00e3o NESTED LOOPS, uma vez que foi selecionada somente 1 linha na tabela DBTW_USUARIO (A-Rows = 1). O Otimizador escolheu a opera\u00e7\u00e3o HASH JOIN pois a estimativa de linhas que seriam lidas da tabela DBTW_USUARIO seriam 3242 linhas (E-Rows = 3242), este erro de estimativa foi causado pela complexidade dos filtros da cl\u00e1usula WHERE.<\/span><\/p>\n

1.3) Executar a mesma consulta com HINTs para melhorar o seu desempenho<\/span><\/h2>\n

Com o conhecimento mais aprofundado dos dados da tabela DBTW_USUARIO sabemos que apesar da complexidade do filtro da cl\u00e1usula WHERE, o numero de linhas selecionadas ser\u00e1 sempre pr\u00f3ximo de 1, logo podemos dizer para o Otimizador, atrav\u00e9s de HINTs, que estime 1 linha para cardinalidade da opera\u00e7\u00e3o de leitura da tabela DBTW_USUARIO e utilize o indice DBTW_USUARIO_LOGIN_IDX.<\/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
88
89
90
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> variable LOGIN   VARCHAR2(<\/span>6<\/span>)<\/span>;<\/span>
\nSQL ><\/span> variable SYS_B_3 VARCHAR2(<\/span>32<\/span>)<\/span>;<\/span>
\nSQL ><\/span> variable SYS_B_4 VARCHAR2(<\/span>32<\/span>)<\/span>;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>SYS_B_3 :=<\/span> 'I'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>SYS_B_4 :=<\/span> 'R'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>LOGIN :=<\/span> '076955'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/*+  OPT_ESTIMATE(TABLE U ROWS=1) INDEX(U DBTW_USUARIO_LOGIN_IDX) *\/<\/span> \/* dbtw-062.1 *\/<\/span> u.<\/span>codigo,<\/span> u.<\/span>login
\n  2<\/span>   FROM dbtw_usuario u
\n  3<\/span>  INNER JOIN<\/span><\/a> dbtw_grupo g
\n  4<\/span>     ON u.<\/span>id_grupo =<\/span> g.<\/span>id_grupo
\n  5<\/span>  WHERE u.<\/span>status NOT IN (<\/span>:<\/span>"SYS_B_3"<\/span>,<\/span> :<\/span>"SYS_B_4"<\/span>)<\/span>
\n  6<\/span>    AND u.<\/span>login like '%'<\/span>||:<\/span>LOGIN;<\/span>
\n
\n    CODIGO LOGIN
\n----------<\/span> ----------<\/span>
\n     76955<\/span> LOG_076955
\n     76955<\/span> LOG_076955
\n     76955<\/span> LOG_076955
\n     ..<\/span>    ..<\/span>
\n     76955<\/span> LOG_076955
\n
\n157<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span>
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number FROM v$sql<\/span> WHERE sql_text LIKE '%dbtw-062.1%'<\/span> AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\na2dkyxr0qdc08            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'BASIC ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'BASIC ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'a2dkyxr0qdc08'<\/span>,<\/span>         0<\/span>,<\/span>'BASIC ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/*+  OPT_ESTIMATE(TABLE U ROWS=1) INDEX(U
\nDBTW_USUARIO_LOGIN_IDX) *\/<\/span> \/* dbtw-062.1 *\/<\/span> u.<\/span>codigo,<\/span> u.<\/span>login  FROM
\ndbtw_usuario u INNER JOIN<\/span><\/a> dbtw_grupo g    ON u.<\/span>id_grupo =<\/span> g.<\/span>id_grupo
\nWHERE u.<\/span>status NOT IN (<\/span>:<\/span>"SYS_B_3"<\/span>,<\/span> :<\/span>"SYS_B_4"<\/span>)<\/span>   AND u.<\/span>login like
\n:<\/span>"B0"<\/span>||:<\/span>LOGIN
\n
\nPlan hash<\/span><\/a> value:<\/span> 3055727543<\/span>
\n
\n--------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name                   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>                        |<\/span>      1<\/span> |<\/span>        |<\/span>    157<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.62<\/span> |<\/span>    1091<\/span> |<\/span>     19<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  NESTED LOOPS                |<\/span>                        |<\/span>      1<\/span> |<\/span>    107<\/span> |<\/span>    157<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.62<\/span> |<\/span>    1091<\/span> |<\/span>     19<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW_USUARIO           |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.62<\/span> |<\/span>    1078<\/span> |<\/span>     19<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW_USUARIO_LOGIN_IDX |<\/span>      1<\/span> |<\/span>  17500<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.61<\/span> |<\/span>    1077<\/span> |<\/span>     18<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN           |<\/span> DBTW_ID_GRUPO_IDX      |<\/span>      1<\/span> |<\/span>    107<\/span> |<\/span>    157<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      13<\/span> |<\/span>      0<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"U"<\/span>.<\/span>"STATUS"<\/span><>:<\/span>SYS_B_3 AND "U"<\/span>.<\/span>"STATUS"<\/span><>:<\/span>SYS_B_4)<\/span>)<\/span>
\n   3<\/span> -<\/span> access(<\/span>"U"<\/span>.<\/span>"LOGIN"<\/span> LIKE :<\/span>B0||:<\/span>LOGIN)<\/span>
\n       filter(<\/span>"U"<\/span>.<\/span>"LOGIN"<\/span> LIKE :<\/span>B0||:<\/span>LOGIN)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"U"<\/span>.<\/span>"ID_GRUPO"<\/span>=<\/span>"G"<\/span>.<\/span>"ID_GRUPO"<\/span>)<\/span>
\n
\n
\n28<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Ap\u00f3s a execu\u00e7\u00e3o da consulta podemos verificar no plano de execu\u00e7\u00e3o acima que o desempenho melhorou significativamente, enquanto na primeira execu\u00e7\u00e3o foram acessados 14.230 Buffers, nesta execu\u00e7\u00e3o a quantidade Buffers acessados caiu para 1.091.<\/span><\/p>\n

SEGUNDA SIMULA\u00c7\u00c3O (18C)<\/span><\/h1>\n

Agora vamos utilizar esta mesma consulta numa base 18c e verificar como o Otimizador lida com esta quest\u00e3o de predicados ou filtros da cl\u00e1usula WHERE complexos.<\/span><\/p>\n

2.1) Criar os objetos para simular a consulta<\/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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
<\/div><\/td>
SQL ><\/span> SELECT BANNER_FULL FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER_FULL
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nOracle Database 18c Express Edition Release 18.<\/span>0.<\/span>0.<\/span>0.<\/span>0 -<\/span> Production
\nVersion 18.<\/span>4.<\/span>0.<\/span>0.<\/span>0
\n
\n
\nSQL ><\/span> create table dbtw_grupo (<\/span>key_id number,<\/span> id_grupo number,<\/span> nome_grupo varchar(<\/span>32<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> Declare<\/span>
\n  2<\/span>
\n  3<\/span>     num_lo01  INTEGER :=<\/span> 0<\/span>;<\/span>
\n  4<\/span>     num_lo02  INTEGER :=<\/span> 0<\/span>;<\/span>
\n  5<\/span>     num_ocor  INTEGER :=<\/span> 167<\/span>;<\/span>
\n  6<\/span>     row_count INTEGER :=<\/span> 1<\/span>;<\/span>
\n  7<\/span>
\n  8<\/span>  begin
\n  9<\/span>
\n 10<\/span>     FOR<\/span> c1 in (<\/span>select trunc(<\/span>dbms_random.<\/span>value(<\/span>0<\/span>,<\/span>1000<\/span>)<\/span>)<\/span> num_dist from dual connect by level <=<\/span> 1000<\/span>)<\/span>
\n 11<\/span>     LOOP
\n 12<\/span>        FOR<\/span> num_lo02 in 0<\/span> ..<\/span> num_ocor
\n 13<\/span>        LOOP
\n 14<\/span>          INSERT INTO dbtw_grupo values(<\/span>row_count,<\/span>c1.<\/span>num_dist,<\/span>'GRUPO_NUMERO-'<\/span>||<\/span>c1.<\/span>num_dist)<\/span>;<\/span>
\n 15<\/span>          row_count :=<\/span> row_count +<\/span> 1<\/span>;<\/span>
\n 16<\/span>        END<\/span><\/a> LOOP;<\/span>
\n 17<\/span>        num_ocor :=<\/span> num_ocor -<\/span> 1<\/span>;<\/span>
\n 18<\/span>        if<\/span> num_ocor =<\/span> 0<\/span> then
\n 19<\/span>           num_ocor :=<\/span> 1<\/span>;<\/span>
\n 20<\/span>        end<\/span><\/a> if<\/span>;<\/span>
\n 21<\/span>     END<\/span><\/a> LOOP;<\/span>
\n 22<\/span>     commit;<\/span>
\n 23<\/span>
\n 24<\/span>  end<\/span><\/a>;<\/span>
\n 25<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw_id_grupo_idx on dbtw_grupo(<\/span>id_grupo)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_GRUPO'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/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>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_GRUPO'<\/span>,<\/span>method_opt=><\/span>'FOR COLUMNS ID_GRUPO SIZE 254'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw_usuario (<\/span>login varchar(<\/span>10<\/span>)<\/span>,<\/span> id_grupo number,<\/span> status varchar(<\/span>1<\/span>)<\/span>,<\/span> codigo number,<\/span> note varchar(<\/span>256<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> Declare<\/span>
\n  2<\/span>
\n  3<\/span>     num_lo01  INTEGER :=<\/span> 0<\/span>;<\/span>
\n  4<\/span>     num_ocor  INTEGER :=<\/span> 167<\/span>;<\/span>
\n  5<\/span>     l_id_grupo INTEGER;<\/span>
\n  6<\/span>     log_id INTEGER :=<\/span> 1<\/span>;<\/span>
\n  7<\/span>     l_key_id INTEGER;<\/span>
\n  8<\/span>
\n  9<\/span>  begin
\n 10<\/span>
\n 11<\/span>     FOR<\/span> num_lo01 in 1<\/span> ..<\/span> 350000<\/span>
\n 12<\/span>     LOOP
\n 13<\/span>       l_key_id :=<\/span> trunc(<\/span>dbms_random.<\/span>value(<\/span>1<\/span>,<\/span>14999<\/span>)<\/span>)<\/span>;<\/span>
\n 14<\/span>       select id_grupo into l_id_grupo from DBTW_GRUPO where key_id =<\/span> l_key_id;<\/span>
\n 15<\/span>       INSERT INTO dbtw_usuario values(<\/span>'LOG_'<\/span>||<\/span>LPAD(<\/span>log_id,<\/span>6<\/span>,<\/span>0<\/span>)<\/span>,<\/span>l_id_grupo,<\/span>'I'<\/span>,<\/span>LPAD(<\/span>log_id,<\/span>6<\/span>,<\/span>0<\/span>)<\/span>,<\/span>LPAD(<\/span>'X'<\/span>,<\/span>256<\/span>,<\/span>'X'<\/span>)<\/span>)<\/span>;<\/span>
\n 16<\/span>       log_id :=<\/span> log_id +<\/span> 1<\/span>;<\/span>
\n 17<\/span>     END<\/span><\/a> LOOP;<\/span>
\n 18<\/span>     commit;<\/span>
\n 19<\/span>
\n 20<\/span>  end<\/span><\/a>;<\/span>
\n 21<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw_usuario_id_grupo_idx on dbtw_usuario(<\/span>id_grupo)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> create index dbtw_usuario_status_idx on dbtw_usuario(<\/span>status)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> create index dbtw_usuario_login_idx on dbtw_usuario(<\/span>login)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> update dbtw_usuario set status =<\/span> 'N'<\/span> where codigo between  60000<\/span> and 120000<\/span>;<\/span>
\n
\n60001<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span> update dbtw_usuario set status =<\/span> 'F'<\/span> where codigo between 150000<\/span> and 153000<\/span>;<\/span>
\n
\n3001<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span> update dbtw_usuario set status =<\/span> 'A'<\/span> where codigo between 210000<\/span> and 212000<\/span>;<\/span>
\n
\n2001<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span> update dbtw_usuario set status =<\/span> 'R'<\/span> where codigo between 280000<\/span> and 280500<\/span>;<\/span>
\n
\n501<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> commit;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_USUARIO'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/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>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_USUARIO'<\/span>,<\/span>method_opt=><\/span>'FOR COLUMNS ID_GRUPO SIZE 254'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/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>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_USUARIO'<\/span>,<\/span>method_opt=><\/span>'FOR COLUMNS STATUS SIZE 254'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/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

2.2) Executar a consulta com plano de execu\u00e7\u00e3o eficiente<\/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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> variable LOGIN   VARCHAR2(<\/span>6<\/span>)<\/span>;<\/span>
\nSQL ><\/span> variable SYS_B_3 VARCHAR2(<\/span>32<\/span>)<\/span>;<\/span>
\nSQL ><\/span> variable SYS_B_4 VARCHAR2(<\/span>32<\/span>)<\/span>;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>SYS_B_3 :=<\/span> 'I'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>SYS_B_4 :=<\/span> 'R'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a>  :<\/span>LOGIN :=<\/span> '076955'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* dbtw-062.3 *\/<\/span> u.<\/span>codigo,<\/span> u.<\/span>login
\n  2<\/span>   FROM dbtw_usuario u
\n  3<\/span>  INNER JOIN<\/span><\/a> dbtw_grupo g
\n  4<\/span>     ON u.<\/span>id_grupo =<\/span> g.<\/span>id_grupo
\n  5<\/span>  WHERE u.<\/span>status NOT IN (<\/span>:<\/span>"SYS_B_3"<\/span>,<\/span> :<\/span>"SYS_B_4"<\/span>)<\/span>
\n  6<\/span>    AND u.<\/span>login like '%'<\/span>||:<\/span>LOGIN;<\/span>
\n
\n    CODIGO LOGIN
\n----------<\/span> ----------<\/span>
\n     76955<\/span> LOG_076955
\n     76955<\/span> LOG_076955
\n     ..<\/span>    ..<\/span>
\n     76955<\/span> LOG_076955
\n
\n72<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span>
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number FROM v$sql<\/span> WHERE sql_text LIKE '%dbtw-062.3%'<\/span> AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n6v7qnmxnyf0ru            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'ADAPTIVE ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'ADAPTIVE ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'6v7qnmxnyf0ru'<\/span>,<\/span>         0<\/span>,<\/span>'ADAPTIVE ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  6v7qnmxnyf0ru,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw-062.3 *\/<\/span> u.<\/span>codigo,<\/span> u.<\/span>login  FROM dbtw_usuario u INNER
\nJOIN<\/span><\/a> dbtw_grupo g    ON u.<\/span>id_grupo =<\/span> g.<\/span>id_grupo WHERE u.<\/span>status NOT IN
\n(<\/span>:<\/span>"SYS_B_3"<\/span>,<\/span> :<\/span>"SYS_B_4"<\/span>)<\/span>   AND u.<\/span>login like '%'<\/span>||:<\/span>LOGIN
\n
\nPlan hash<\/span><\/a> value:<\/span> 1119022536<\/span>
\n
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   Id  |<\/span> Operation                              |<\/span> Name                   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/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>     72<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    1086<\/span> |<\/span>
\n|-<\/span> *<\/span>  1<\/span> |<\/span>  HASH<\/span><\/a> JOIN<\/span><\/a>                             |<\/span>                        |<\/span>      1<\/span> |<\/span>    369K|<\/span>     72<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    1086<\/span> |<\/span>
\n|<\/span>     2<\/span> |<\/span>   NESTED LOOPS                         |<\/span>                        |<\/span>      1<\/span> |<\/span>    369K|<\/span>     72<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    1086<\/span> |<\/span>
\n|-<\/span>    3<\/span> |<\/span>    STATISTICS COLLECTOR                |<\/span>                        |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    1078<\/span> |<\/span>
\n|<\/span>  *<\/span>  4<\/span> |<\/span>     TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> DBTW_USUARIO           |<\/span>      1<\/span> |<\/span>   3271<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    1078<\/span> |<\/span>
\n|<\/span>  *<\/span>  5<\/span> |<\/span>      INDEX RANGE<\/span><\/a> SCAN                  |<\/span> DBTW_USUARIO_LOGIN_IDX |<\/span>      1<\/span> |<\/span>  17500<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    1077<\/span> |<\/span>
\n|<\/span>  *<\/span>  6<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN                    |<\/span> DBTW_ID_GRUPO_IDX      |<\/span>      1<\/span> |<\/span>    113<\/span> |<\/span>     72<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|-<\/span>    7<\/span> |<\/span>   INDEX FAST FULL SCAN                 |<\/span> DBTW_ID_GRUPO_IDX      |<\/span>      0<\/span> |<\/span>  15861<\/span> |<\/span>      0<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       0<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> access(<\/span>"U"<\/span>.<\/span>"ID_GRUPO"<\/span>=<\/span>"G"<\/span>.<\/span>"ID_GRUPO"<\/span>)<\/span>
\n   4<\/span> -<\/span> filter(<\/span>(<\/span>"U"<\/span>.<\/span>"STATUS"<\/span><>:<\/span>SYS_B_3 AND "U"<\/span>.<\/span>"STATUS"<\/span><>:<\/span>SYS_B_4)<\/span>)<\/span>
\n   5<\/span> -<\/span> access(<\/span>"U"<\/span>.<\/span>"LOGIN"<\/span> LIKE '%'<\/span>||:<\/span>LOGIN)<\/span>
\n       filter(<\/span>"U"<\/span>.<\/span>"LOGIN"<\/span> LIKE '%'<\/span>||:<\/span>LOGIN)<\/span>
\n   6<\/span> -<\/span> access(<\/span>"U"<\/span>.<\/span>"ID_GRUPO"<\/span>=<\/span>"G"<\/span>.<\/span>"ID_GRUPO"<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> this is an adaptive plan (<\/span>rows marked '-'<\/span> are inactive)<\/span>
\n
\n
\n34<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o acima, a se\u00e7\u00e3o NOTE apresenta a mensagem “this is an adaptive plan”, isto significa que o Otimizador utilizou um novo recurso chamado ADAPTIVE QUERY OPTIMIZATION que permite ao Otimizador alterar o plano de execu\u00e7\u00e3o durante a execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL. Se voc\u00ea analisar o plano de execu\u00e7\u00e3o com mais cuidado vai verificar que existem algumas opera\u00e7\u00f5es que foram assinaladas com o caracter “-” na coluna ID, isto significa que estas opera\u00e7\u00f5es foram desativadas ou desconsideradas. Como resultado podemos observar que o desempenho desta consulta foi igual ao desempenho da consulta executada na base 11g com os HINTs para melhoria de performance.<\/span><\/p>\n

CONCLUS\u00c3O<\/span><\/h1>\n

Esta \u00e9 uma pequena amostra de como o Otimizador do Oracle Database evoluiu nas ultimas vers\u00f5es, se voc\u00ea ainda esta em d\u00favida se vale a pena fazer o Upgrade para a vers\u00e3o 19c, saiba que alem da manuten\u00e7\u00e3o do suporte e das melhorias de seguran\u00e7a, voc\u00ea esta perdendo a oportunidade de melhorar a performance de suas aplica\u00e7\u00f5es.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/database\/121\/<\/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 vem sendo aprimorado a cada nova vers\u00e3o, a partir da 12c foram introduzidas muitas melhorias que tornaram o Otimizador muito mais eficente na escolha dos planos de execu\u00e7\u00e3o, nas vers\u00f5es anteriores o Otimizador tinha algumas limita\u00e7\u00f5es que o impedia de escolher planos de execu\u00e7\u00e3o melhores. Na vers\u00e3o 19c o Otimizador n\u00e3o alcan\u00e7ou a perfei\u00e7\u00e3o, mas houve<\/p>\n","protected":false},"author":2,"featured_media":2113,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[223,222,129,224,42,82,10],"class_list":["post-2112","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cost-base-optimizer","tag-adaptive-plan","tag-adaptive-query-optimization","tag-cardinalidade","tag-complex-predicate","tag-cost-base-optimizer","tag-optimizer","tag-otimizador"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2112","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=2112"}],"version-history":[{"count":12,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2112\/revisions"}],"predecessor-version":[{"id":2128,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2112\/revisions\/2128"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/2113"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=2112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=2112"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=2112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}