Qual a melhor opção para o parâmetro optimizer_mode?

janeiro 10, 2016 V. J.A. Cost Base Optimizer

O Otimizador da Oracle foi desenvolvido para executar as instruções SQL de forma rápida, as primeiras versões do Otimizador eram baseadas em regras, porem esse modelo demonstrou ser ineficaz pois o banco de dados e muito volátil e os dados sofrem modificações constantemente. Atualmente o Otimizador baseado em regra não é mais suportado, em seu lugar temos o Otimizador baseado em CUSTO, esse novo modelo é mais versátil pois esta constantemente registrando a modificações dos dados e tem a capacidade de adaptação a essas modificações.

Parâmetro OPTIMIZER_MODE

Na configuração do banco de dados Oracle temos que configurar o parâmetro (OPTIMIZER_MODE) que vai ajudar o Otimizador a tomar as melhores decisões baseado no perfil definido para um determinado banco de dados. As opções possíveis para este parâmetro são:

ALL_ROWS – Esta opção diz ao Otimizador para obter todas as linhas rapidamente e as aplicações que mais se beneficiam dessa características são aplicações BATCH, aplicações que geram relatórios, aplicações do tipo OLAP. Esta é a opção padrão de instalação da versão Oracle Database 11gR2.

FIRST_ROWS – Esta opção diz ao Otimizador para obter a primeira linha rapidamente e as aplicações que mais se beneficiam dessa características são aplicações OLTP, ou aplicações que realizam muitas transações ONLINE.

FIRST_ROWS_n (1|10|100|1000) – Esta opção diz ao Otimizador para obter as primeiras “n” linhas rapidamente, indicado para aplicações que precisam retornar um grupo de linhas rapidamente como por exemplo, carregar um página WEB.

CHOOSE – Esta opção não é mais suportada mas continua disponível na versão Oracle Database 11gR2.

RULE – Esta opção não é mais suportada mas continua disponível na versão Oracle Database 11gR2.

Demonstração do parâmetro OPTIMIZER_MODE

Para ilustrar na prática a teoria acima, vamos fazer uma simulação simples com uma consulta utilizando o “SAMPLE SCHEMA SH” que faz parte da instalação do banco de dados Oracle, nessa simulação vamos utilizar somente as opções do parâmetro OPTIMIZER_MODE que são suportadas e verificar qual plano de execução o Otimizador vai escolher para cada uma dessa opções.

Caso tenha alguma dúvida na leitura do plano de execução gerado pelo pacote DBMS_XPLAN consulte a série de artigos que vai ajuda-lo a interpretar essas informações:

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

1. Como verificar a ordem que as operações são realizadas
2. Como interpretar os valores estatísticos estimados
3. Como interpretar os valores estatísticos coletados durante a execução
4. Como interpretar as seções não estatísticas do plano de execução

Consulta com a opção ALL_ROWS

O parâmetro OPTIMIZER_MODE é definido na criação do banco de dados Oracle, mas para nossas simulações vamos redefini-lo a nível de sessão, ele também pode ser definido a nível de instrução SQL utilizando o “Hint opt_param(‘optimizer_mode’,’all_rows’)”. Na simulação abaixo serão excluidas algumas linhas do resultado da execução da consulta pois elas não fornecem informações relevantes para o objetivo dessa demonstração.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

sh@LAB11 > SELECT * FROM V$VERSION where rownum < 2;

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

Elapsed: 00:00:00.00
sh@LAB11 >

ALTER SESSION SET statistics_level=ALL;
alter session set optimizer_mode=ALL_ROWS;

select /* tst201 */
C.CUST_FIRST_NAME,
C.CUST_LAST_NAME,
P.UNIT_PRICE,
S.QUANTITY_SOLD
from CUSTOMERS C,
COSTS P,
SALES S
where C.CUST_ID = S.CUST_ID
and P.PROD_ID = S.PROD_ID
and S.PROD_ID = 136;

column sql_id new_value m_sql_id
column child_number new_value m_child_no

SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%tst201%'
AND sql_text NOT LIKE '%v$sql%';

sh@LAB11 > SELECT *
2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
old 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
new 2: FROM TABLE (dbms_xplan.display_cursor ('6xt7djqzsq6x5', 0,'basic iostats last'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst201 */ C.CUST_FIRST_NAME, C.CUST_LAST_NAME,
P.UNIT_PRICE, S.QUANTITY_SOLD from CUSTOMERS C,
COSTS P, SALES S where C.CUST_ID = S.CUST_ID and P.PROD_ID =
S.PROD_ID and S.PROD_ID = :"SYS_B_0"

Plan hash value: 3070703410

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 38340 |00:00:00.06 | 1954 |
|* 1 | HASH JOIN | | 1 | 809K| 38340 |00:00:00.06 | 1954 |
| 2 | PARTITION RANGE ALL | | 1 | 1140 | 54 |00:00:00.01 | 61 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | COSTS | 28 | 1140 | 54 |00:00:00.01 | 61 |
| 4 | BITMAP CONVERSION TO ROWIDS | | 16 | | 54 |00:00:00.01 | 24 |
|* 5 | BITMAP INDEX SINGLE VALUE | COSTS_PROD_BIX | 16 | | 5 |00:00:00.01 | 24 |
|* 6 | HASH JOIN | | 1 | 710 | 710 |00:00:00.05 | 1893 |
| 7 | PARTITION RANGE ALL | | 1 | 710 | 710 |00:00:00.01 | 45 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 28 | 710 | 710 |00:00:00.01 | 45 |
| 9 | BITMAP CONVERSION TO ROWIDS | | 16 | | 710 |00:00:00.01 | 32 |
|* 10 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | 16 | | 5 |00:00:00.01 | 32 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 55500 |00:00:00.02 | 1848 |
-----------------------------------------------------------------------------------------------------------------

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

1 - access("P"."PROD_ID"="S"."PROD_ID")
5 - access("P"."PROD_ID"=:SYS_B_0)
6 - access("C"."CUST_ID"="S"."CUST_ID")
10 - access("S"."PROD_ID"=:SYS_B_0)

34 rows selected.

Elapsed: 00:00:00.02
sh@LAB11 >

No plano de execução acima observamos que a consulta retornou cerca de 38 mil linhas em seis centésimos de segundo.

Consulta com a opção FIRST_ROWS

Agora vamos executar a consulta novamente, só que desta vez vamos alterar o parâmetro OPTIMIZER_MODE a nível de sessão para FIRST_ROWS e observar o plano de execução escolhido pelo Otimizador.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

ALTER SESSION SET statistics_level=ALL;
alter session set optimizer_mode=FIRST_ROWS;

select /* tst203 */
C.CUST_FIRST_NAME,
C.CUST_LAST_NAME,
P.UNIT_PRICE,
S.QUANTITY_SOLD
from CUSTOMERS C,
COSTS P,
SALES S
where C.CUST_ID = S.CUST_ID
and P.PROD_ID = S.PROD_ID
and S.PROD_ID = 136;

column sql_id new_value m_sql_id
column child_number new_value m_child_no

SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%tst203%'
AND sql_text NOT LIKE '%v$sql%';

SELECT *
FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

sh@LAB11 > SELECT *
2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
old 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
new 2: FROM TABLE (dbms_xplan.display_cursor ('d03nf1xc2z26r', 0,'basic iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst203 */ C.CUST_FIRST_NAME, C.CUST_LAST_NAME,
P.UNIT_PRICE, S.QUANTITY_SOLD from CUSTOMERS C,
COSTS P, SALES S where C.CUST_ID = S.CUST_ID and P.PROD_ID =
S.PROD_ID and S.PROD_ID = :"SYS_B_0"

Plan hash value: 1863578684

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 38340 |00:00:00.28 | 49313 |
| 1 | NESTED LOOPS | | 1 | 809K| 38340 |00:00:00.28 | 49313 |
| 2 | NESTED LOOPS | | 1 | 809K| 38340 |00:00:00.15 | 22333 |
| 3 | NESTED LOOPS | | 1 | 710 | 710 |00:00:00.01 | 2879 |
| 4 | PARTITION RANGE ALL | | 1 | 710 | 710 |00:00:00.01 | 747 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 28 | 710 | 710 |00:00:00.01 | 747 |
| 6 | BITMAP CONVERSION TO ROWIDS | | 16 | | 710 |00:00:00.01 | 37 |
|* 7 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | 16 | | 5 |00:00:00.01 | 37 |
| 8 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 710 | 1 | 710 |00:00:00.01 | 2132 |
|* 9 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 710 | 1 | 710 |00:00:00.01 | 1422 |
| 10 | PARTITION RANGE ALL | | 710 | | 38340 |00:00:00.13 | 19454 |
| 11 | BITMAP CONVERSION TO ROWIDS | | 19880 | | 38340 |00:00:00.11 | 19454 |
|* 12 | BITMAP INDEX SINGLE VALUE | COSTS_PROD_BIX | 19880 | | 3550 |00:00:00.08 | 19454 |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID | COSTS | 38340 | 1140 | 38340 |00:00:00.10 | 26980 |
------------------------------------------------------------------------------------------------------------------

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

7 - access("S"."PROD_ID"=:SYS_B_0)
9 - access("C"."CUST_ID"="S"."CUST_ID")
12 - access("P"."PROD_ID"=:SYS_B_0)
filter("P"."PROD_ID"="S"."PROD_ID")

36 rows selected.

Elapsed: 00:00:00.03
sh@LAB11 >

Nesta execução o Otimizador alterou drasticamente o plano de execução e o tempo total de execução da consulta aumentou de 6 para 28 centésimos de segundos, alguém poderia dizer que o Otimizador errou pois pela definição o plano de execução deveria ser mais rápido pois a opção FIRST_ROWS é destinada a aplicações OLTP que precisam ter um tempo de resposta excelente, mas o Otimizador não errou ele fez exatamente o que foi prometido para essa opção, veja que neste plano de execução a operação utilizada para fazer a junção das tabelas foi “NESTED LOOPS”, essa operação 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ção ser concluído, ao passo que na consulta com a opção ALL_ROWS foi utilizado a operação “HASH JOIN” que vai entregar a linhas somente quando for finalizado o plano de execução.

Não vamos publicar a simulação da opção FIRST_ROWS_N pois para esta consulta o plano de execução será o mesmo da opção FIRST_ROWS.

Conclusão

Quando estivermos analisando o desempenho de uma instrução SQL é muito importante verificar qual opção esta sendo utilizada no parâmetro OPTIMIZER_MODE, pois essa definição influência diretamente o Otimizador na escolha do melhor plano de execução e eventualmente o plano escolhido pode não ser exatamente aquele que se espera.