{"id":241,"date":"2016-01-10T22:48:24","date_gmt":"2016-01-11T00:48:24","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=241"},"modified":"2019-10-10T23:07:21","modified_gmt":"2019-10-11T02:07:21","slug":"qual-a-melhor-opcao-para-o-parametro-optimizer_mode","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/qual-a-melhor-opcao-para-o-parametro-optimizer_mode\/","title":{"rendered":"Qual a melhor op\u00e7\u00e3o para o par\u00e2metro optimizer_mode?"},"content":{"rendered":"

O Otimizador da Oracle foi desenvolvido para executar as instru\u00e7\u00f5es SQL de forma r\u00e1pida, as primeiras vers\u00f5es do Otimizador eram baseadas em regras, porem esse modelo demonstrou ser ineficaz pois o banco de dados e muito vol\u00e1til e os dados sofrem modifica\u00e7\u00f5es constantemente. Atualmente o Otimizador baseado em regra n\u00e3o \u00e9 mais suportado, em seu lugar temos o\u00a0Otimizador baseado em CUSTO, esse novo modelo \u00e9 mais vers\u00e1til pois esta constantemente registrando a modifica\u00e7\u00f5es dos dados e tem a capacidade de adapta\u00e7\u00e3o a essas modifica\u00e7\u00f5es.<\/span><\/p>\n

Par\u00e2metro OPTIMIZER_MODE<\/span><\/h2>\n

Na configura\u00e7\u00e3o do banco de dados Oracle temos que configurar o\u00a0par\u00e2metro (OPTIMIZER_MODE) que vai ajudar o Otimizador a tomar as melhores decis\u00f5es baseado no perfil definido para um determinado banco de dados. As op\u00e7\u00f5es poss\u00edveis para este par\u00e2metro s\u00e3o:<\/span><\/p>\n

ALL_ROWS<\/strong> – Esta op\u00e7\u00e3o diz ao Otimizador para obter todas as linhas rapidamente e as aplica\u00e7\u00f5es que mais se beneficiam dessa caracter\u00edsticas s\u00e3o aplica\u00e7\u00f5es BATCH, aplica\u00e7\u00f5es que geram relat\u00f3rios, aplica\u00e7\u00f5es do tipo OLAP. Esta \u00e9 a op\u00e7\u00e3o padr\u00e3o de instala\u00e7\u00e3o da vers\u00e3o Oracle Database 11gR2.<\/span><\/p>\n

FIRST_ROWS<\/strong> – Esta op\u00e7\u00e3o diz ao Otimizador para obter a primeira linha rapidamente e as aplica\u00e7\u00f5es que mais se beneficiam dessa caracter\u00edsticas s\u00e3o aplica\u00e7\u00f5es OLTP, ou aplica\u00e7\u00f5es que realizam muitas transa\u00e7\u00f5es ONLINE.<\/span><\/p>\n

FIRST_ROWS_n<\/strong> (1|10|100|1000) – Esta op\u00e7\u00e3o diz ao Otimizador para obter as primeiras “n” linhas rapidamente, indicado para aplica\u00e7\u00f5es que precisam retornar um grupo de linhas rapidamente como por exemplo, carregar um p\u00e1gina WEB.<\/span><\/p>\n

CHOOSE<\/strong> – Esta op\u00e7\u00e3o n\u00e3o \u00e9 mais suportada mas continua dispon\u00edvel na vers\u00e3o Oracle Database 11gR2.<\/span><\/p>\n

RULE<\/strong> – Esta op\u00e7\u00e3o n\u00e3o \u00e9 mais suportada mas continua dispon\u00edvel na vers\u00e3o Oracle Database 11gR2.<\/span><\/p>\n

Demonstra\u00e7\u00e3o do par\u00e2metro OPTIMIZER_MODE<\/span><\/h2>\n

Para ilustrar na pr\u00e1tica a teoria acima, vamos fazer uma simula\u00e7\u00e3o simples com uma consulta utilizando o “SAMPLE SCHEMA SH<\/a><\/strong>” que faz parte da instala\u00e7\u00e3o do banco de dados Oracle, nessa simula\u00e7\u00e3o vamos utilizar somente as op\u00e7\u00f5es do par\u00e2metro OPTIMIZER_MODE que s\u00e3o suportadas e verificar qual plano de execu\u00e7\u00e3o o Otimizador vai escolher para cada uma dessa op\u00e7\u00f5es.<\/span><\/p>\n

Caso tenha alguma d\u00favida na leitura do plano de execu\u00e7\u00e3o gerado pelo pacote DBMS_XPLAN consulte a s\u00e9rie de artigos que vai ajuda-lo a interpretar essas informa\u00e7\u00f5es:<\/span><\/p>\n

1. Como verificar a ordem que as opera\u00e7\u00f5es s\u00e3o realizadas<\/a><\/span><\/strong>
\n2. Como interpretar os valores estat\u00edsticos estimados<\/a><\/span><\/strong>
\n3. Como interpretar os valores estat\u00edsticos coletados durante a execu\u00e7\u00e3o<\/a><\/span><\/strong>
\n4. Como interpretar as se\u00e7\u00f5es n\u00e3o estat\u00edsticas do plano de execu\u00e7\u00e3o<\/a><\/span><\/strong><\/p>\n

 <\/p>\n

Consulta com a op\u00e7\u00e3o ALL_ROWS<\/span><\/h2>\n

O par\u00e2metro OPTIMIZER_MODE \u00e9 definido na cria\u00e7\u00e3o do banco de dados Oracle, mas para nossas simula\u00e7\u00f5es vamos redefini-lo a n\u00edvel de sess\u00e3o, ele tamb\u00e9m pode ser definido a n\u00edvel de instru\u00e7\u00e3o SQL utilizando o “Hint opt_param(‘optimizer_mode’,’all_rows’)”. Na simula\u00e7\u00e3o abaixo ser\u00e3o excluidas algumas linhas do resultado da execu\u00e7\u00e3o da consulta pois elas n\u00e3o fornecem informa\u00e7\u00f5es relevantes para o objetivo dessa demonstra\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
<\/div><\/td>
sh@<\/span>LAB11 ><\/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
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.00<\/span>
\nsh@<\/span>LAB11 ><\/span>
\n
\n
\nALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\nalter session set optimizer_mode=<\/span>ALL_ROWS;<\/span>
\n
\nselect \/* tst201 *\/<\/span>
\n       C.<\/span>CUST_FIRST_NAME,<\/span>
\n       C.<\/span>CUST_LAST_NAME,<\/span>
\n       P.<\/span>UNIT_PRICE,<\/span>
\n       S.<\/span>QUANTITY_SOLD
\n  from CUSTOMERS C,<\/span>
\n       COSTS P,<\/span>
\n       SALES S
\n where C.<\/span>CUST_ID =<\/span> S.<\/span>CUST_ID
\n   and P.<\/span>PROD_ID =<\/span> S.<\/span>PROD_ID
\n   and S.<\/span>PROD_ID =<\/span> 136<\/span>;<\/span>
\n
\ncolumn sql_id new_value m_sql_id
\ncolumn child_number new_value m_child_no
\n
\nSELECT sql_id,<\/span> child_number
\n  FROM v$sql<\/span>
\n WHERE sql_text LIKE '%tst201%'<\/span>
\n   AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\n
\n
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'6xt7djqzsq6x5'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst201 *\/<\/span>        C.<\/span>CUST_FIRST_NAME,<\/span>        C.<\/span>CUST_LAST_NAME,<\/span>
\n      P.<\/span>UNIT_PRICE,<\/span>        S.<\/span>QUANTITY_SOLD   from CUSTOMERS C,<\/span>
\nCOSTS P,<\/span>        SALES S  where C.<\/span>CUST_ID =<\/span> S.<\/span>CUST_ID    and P.<\/span>PROD_ID =<\/span>
\nS.<\/span>PROD_ID    and S.<\/span>PROD_ID =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3070703410<\/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>  38340<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    1954<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  HASH<\/span><\/a> JOIN<\/span><\/a>                           |<\/span>                |<\/span>      1<\/span> |<\/span>    809K|<\/span>  38340<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    1954<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   PARTITION RANGE<\/span><\/a> ALL                |<\/span>                |<\/span>      1<\/span> |<\/span>   1140<\/span> |<\/span>     54<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      61<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS BY LOCAL INDEX ROWID |<\/span> COSTS          |<\/span>     28<\/span> |<\/span>   1140<\/span> |<\/span>     54<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      61<\/span> |<\/span>
\n|<\/span>   4<\/span> |<\/span>     BITMAP CONVERSION TO ROWIDS      |<\/span>                |<\/span>     16<\/span> |<\/span>        |<\/span>     54<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      24<\/span> |<\/span>
\n|*<\/span>  5<\/span> |<\/span>      BITMAP INDEX SINGLE VALUE       |<\/span> COSTS_PROD_BIX |<\/span>     16<\/span> |<\/span>        |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      24<\/span> |<\/span>
\n|*<\/span>  6<\/span> |<\/span>   HASH<\/span><\/a> JOIN<\/span><\/a>                          |<\/span>                |<\/span>      1<\/span> |<\/span>    710<\/span> |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    1893<\/span> |<\/span>
\n|<\/span>   7<\/span> |<\/span>    PARTITION RANGE<\/span><\/a> ALL               |<\/span>                |<\/span>      1<\/span> |<\/span>    710<\/span> |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      45<\/span> |<\/span>
\n|<\/span>   8<\/span> |<\/span>     TABLE ACCESS BY LOCAL INDEX ROWID|<\/span> SALES          |<\/span>     28<\/span> |<\/span>    710<\/span> |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      45<\/span> |<\/span>
\n|<\/span>   9<\/span> |<\/span>      BITMAP CONVERSION TO ROWIDS     |<\/span>                |<\/span>     16<\/span> |<\/span>        |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      32<\/span> |<\/span>
\n|*<\/span> 10<\/span> |<\/span>       BITMAP INDEX SINGLE VALUE      |<\/span> SALES_PROD_BIX |<\/span>     16<\/span> |<\/span>        |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      32<\/span> |<\/span>
\n|<\/span>  11<\/span> |<\/span>    TABLE ACCESS FULL                 |<\/span> CUSTOMERS      |<\/span>      1<\/span> |<\/span>  55500<\/span> |<\/span>  55500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span> |<\/span>    1848<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> access(<\/span>"P"<\/span>.<\/span>"PROD_ID"<\/span>=<\/span>"S"<\/span>.<\/span>"PROD_ID"<\/span>)<\/span>
\n   5<\/span> -<\/span> access(<\/span>"P"<\/span>.<\/span>"PROD_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n   6<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>=<\/span>"S"<\/span>.<\/span>"CUST_ID"<\/span>)<\/span>
\n  10<\/span> -<\/span> access(<\/span>"S"<\/span>.<\/span>"PROD_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n34<\/span> rows selected.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span>
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No plano de execu\u00e7\u00e3o acima observamos que a consulta retornou cerca de 38 mil linhas em seis cent\u00e9simos de segundo.<\/span><\/p>\n

Consulta com a op\u00e7\u00e3o FIRST_ROWS<\/span><\/h2>\n

Agora vamos executar a consulta novamente, s\u00f3 que desta vez vamos alterar o par\u00e2metro OPTIMIZER_MODE a n\u00edvel de sess\u00e3o para FIRST_ROWS e observar o plano de execu\u00e7\u00e3o escolhido pelo Otimizador.<\/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
<\/div><\/td>
ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\nalter session set optimizer_mode=<\/span>FIRST_ROWS;<\/span>
\n
\nselect \/* tst203 *\/<\/span>
\n       C.<\/span>CUST_FIRST_NAME,<\/span>
\n       C.<\/span>CUST_LAST_NAME,<\/span>
\n       P.<\/span>UNIT_PRICE,<\/span>
\n       S.<\/span>QUANTITY_SOLD
\n  from CUSTOMERS C,<\/span>
\n       COSTS P,<\/span>
\n       SALES S
\n where C.<\/span>CUST_ID =<\/span> S.<\/span>CUST_ID
\n   and P.<\/span>PROD_ID =<\/span> S.<\/span>PROD_ID
\n   and S.<\/span>PROD_ID =<\/span> 136<\/span>;<\/span>
\n
\ncolumn sql_id new_value m_sql_id
\ncolumn child_number new_value m_child_no
\n
\nSELECT sql_id,<\/span> child_number
\n  FROM v$sql<\/span>
\n WHERE sql_text LIKE '%tst203%'<\/span>
\n   AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSELECT *<\/span>
\n  FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\n
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'d03nf1xc2z26r'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst203 *\/<\/span>        C.<\/span>CUST_FIRST_NAME,<\/span>        C.<\/span>CUST_LAST_NAME,<\/span>
\n      P.<\/span>UNIT_PRICE,<\/span>        S.<\/span>QUANTITY_SOLD   from CUSTOMERS C,<\/span>
\nCOSTS P,<\/span>        SALES S  where C.<\/span>CUST_ID =<\/span> S.<\/span>CUST_ID    and P.<\/span>PROD_ID =<\/span>
\nS.<\/span>PROD_ID    and S.<\/span>PROD_ID =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1863578684<\/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>  38340<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.28<\/span> |<\/span>   49313<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  NESTED LOOPS                         |<\/span>                |<\/span>      1<\/span> |<\/span>    809K|<\/span>  38340<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.28<\/span> |<\/span>   49313<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS                        |<\/span>                |<\/span>      1<\/span> |<\/span>    809K|<\/span>  38340<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.15<\/span> |<\/span>   22333<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    NESTED LOOPS                       |<\/span>                |<\/span>      1<\/span> |<\/span>    710<\/span> |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    2879<\/span> |<\/span>
\n|<\/span>   4<\/span> |<\/span>     PARTITION RANGE<\/span><\/a> ALL               |<\/span>                |<\/span>      1<\/span> |<\/span>    710<\/span> |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     747<\/span> |<\/span>
\n|<\/span>   5<\/span> |<\/span>      TABLE ACCESS BY LOCAL INDEX ROWID|<\/span> SALES          |<\/span>     28<\/span> |<\/span>    710<\/span> |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     747<\/span> |<\/span>
\n|<\/span>   6<\/span> |<\/span>       BITMAP CONVERSION TO ROWIDS     |<\/span>                |<\/span>     16<\/span> |<\/span>        |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      37<\/span> |<\/span>
\n|*<\/span>  7<\/span> |<\/span>        BITMAP INDEX SINGLE VALUE      |<\/span> SALES_PROD_BIX |<\/span>     16<\/span> |<\/span>        |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      37<\/span> |<\/span>
\n|<\/span>   8<\/span> |<\/span>     TABLE ACCESS BY INDEX ROWID       |<\/span> CUSTOMERS      |<\/span>    710<\/span> |<\/span>      1<\/span> |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    2132<\/span> |<\/span>
\n|*<\/span>  9<\/span> |<\/span>      INDEX UNIQUE SCAN                |<\/span> CUSTOMERS_PK   |<\/span>    710<\/span> |<\/span>      1<\/span> |<\/span>    710<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1422<\/span> |<\/span>
\n|<\/span>  10<\/span> |<\/span>    PARTITION RANGE<\/span><\/a> ALL                |<\/span>                |<\/span>    710<\/span> |<\/span>        |<\/span>  38340<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.13<\/span> |<\/span>   19454<\/span> |<\/span>
\n|<\/span>  11<\/span> |<\/span>     BITMAP CONVERSION TO ROWIDS       |<\/span>                |<\/span>  19880<\/span> |<\/span>        |<\/span>  38340<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.11<\/span> |<\/span>   19454<\/span> |<\/span>
\n|*<\/span> 12<\/span> |<\/span>      BITMAP INDEX SINGLE VALUE        |<\/span> COSTS_PROD_BIX |<\/span>  19880<\/span> |<\/span>        |<\/span>   3550<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.08<\/span> |<\/span>   19454<\/span> |<\/span>
\n|<\/span>  13<\/span> |<\/span>   TABLE ACCESS BY LOCAL INDEX ROWID   |<\/span> COSTS          |<\/span>  38340<\/span> |<\/span>   1140<\/span> |<\/span>  38340<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.10<\/span> |<\/span>   26980<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   7<\/span> -<\/span> access(<\/span>"S"<\/span>.<\/span>"PROD_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n   9<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>=<\/span>"S"<\/span>.<\/span>"CUST_ID"<\/span>)<\/span>
\n  12<\/span> -<\/span> access(<\/span>"P"<\/span>.<\/span>"PROD_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n       filter(<\/span>"P"<\/span>.<\/span>"PROD_ID"<\/span>=<\/span>"S"<\/span>.<\/span>"PROD_ID"<\/span>)<\/span>
\n
\n
\n36<\/span> rows selected.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span>
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Nesta execu\u00e7\u00e3o o Otimizador alterou drasticamente o plano de execu\u00e7\u00e3o e o tempo total de execu\u00e7\u00e3o da consulta aumentou de 6 para 28 cent\u00e9simos de segundos, algu\u00e9m poderia dizer que o Otimizador errou pois pela defini\u00e7\u00e3o o plano de execu\u00e7\u00e3o deveria ser mais r\u00e1pido pois a op\u00e7\u00e3o FIRST_ROWS \u00e9 destinada a aplica\u00e7\u00f5es OLTP que precisam ter um tempo de resposta excelente, mas o Otimizador n\u00e3o errou ele fez exatamente o que foi prometido para essa op\u00e7\u00e3o, veja que neste plano de execu\u00e7\u00e3o a opera\u00e7\u00e3o utilizada para fazer a jun\u00e7\u00e3o das tabelas foi “NESTED LOOPS”, essa opera\u00e7\u00e3o permite que as linhas sejam entregues ao cliente da consulta imediatamente, ou seja, a primeira linha vai ser entregue assim que for lida e antes do plano de execu\u00e7\u00e3o ser conclu\u00eddo, ao passo que na consulta com a op\u00e7\u00e3o ALL_ROWS foi utilizado a opera\u00e7\u00e3o “HASH JOIN” que vai entregar a linhas somente quando for finalizado o plano de execu\u00e7\u00e3o.<\/span><\/p>\n

N\u00e3o vamos publicar a simula\u00e7\u00e3o da op\u00e7\u00e3o FIRST_ROWS_N pois para esta consulta o plano de execu\u00e7\u00e3o ser\u00e1 o mesmo da op\u00e7\u00e3o FIRST_ROWS.<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

Quando estivermos analisando o desempenho de uma instru\u00e7\u00e3o SQL \u00e9 muito importante verificar qual op\u00e7\u00e3o esta sendo utilizada no par\u00e2metro OPTIMIZER_MODE, pois essa defini\u00e7\u00e3o influ\u00eancia diretamente o Otimizador na escolha do melhor plano de execu\u00e7\u00e3o e eventualmente o plano escolhido pode n\u00e3o ser exatamente aquele que se espera.<\/span><\/p>\n

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

http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41573\/optimops.htm#PFGRF10102<\/a><\/strong><\/p>\n

https:\/\/docs.oracle.com\/cd\/E18283_01\/server.112\/e10831\/overview.htm#sthref6<\/a><\/strong><\/p>\n

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

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

O Otimizador da Oracle foi desenvolvido para executar as instru\u00e7\u00f5es SQL de forma r\u00e1pida, as primeiras vers\u00f5es do Otimizador eram baseadas em regras, porem esse modelo demonstrou ser ineficaz pois o banco de dados e muito vol\u00e1til e os dados sofrem modifica\u00e7\u00f5es constantemente. Atualmente o Otimizador baseado em regra n\u00e3o \u00e9 mais suportado, em seu lugar temos o\u00a0Otimizador baseado em<\/p>\n","protected":false},"author":2,"featured_media":242,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[65,70,6,42,36,69,68,10,29,11,45,61],"class_list":["post-241","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cost-base-optimizer","tag-ajuste-de-desempenho","tag-all_rows","tag-cbo","tag-cost-base-optimizer","tag-execution-plan","tag-first_rows","tag-optimizer_mode","tag-otimizador","tag-performance-tuning","tag-plano-de-execucao","tag-sql-statement-analysis","tag-tempo-de-reposta"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/241","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=241"}],"version-history":[{"count":16,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/241\/revisions"}],"predecessor-version":[{"id":2061,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/241\/revisions\/2061"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/242"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=241"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=241"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=241"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}