In-memory: Um recurso subutilizado

inmemory

Muitos profissionais que trabalham com banco de dados Oracle não utilizam o recurso IN-MEMORY pois esta Option não possui licença nas empresas onde trabalham, mas se você é um dos felizardos que podem utilizar esta Option não perca tempo, inclua esta poderosa ferramenta no seu arsenal de recursos para melhorar o desempenho das consultas.

Neste artigo vamos abordar rapidamente o conceito do recurso IN-MEMORY e na sequencia vamos apresentar um exemplo prático de como este recurso pode ajudar na melhoria de desempenho de uma instrução SQL.

 
 
 

Como todos sabemos, o banco de dados Oracle tradicionalmente armazena dados no formato de linha, onde cada novo registro é representado como uma nova linha em uma tabela com várias colunas. Este formato é ideal para para aplicações OLTP, pois permite um acesso rápido a todas as colunas de um registro que são carregadas e armazenadas juntas na memoria (BUFFER CACHE).

 

                      dual format memory

 

Com a implementação do recurso IN-MEMORY (IM Column Store) a partir da versão 12c, o banco de dados Oracle passou a oferecer mais um tipo de armazenamento, agora em formato de coluna, desta forma as aplicações OLAP cujas consultas acessam muitas linhas e poucas colunas vão se beneficiar desse novo formato. Estes dois formatos estão totalmente integrados a arquitetura do banco e o Otimizador é capaz de escolher o tipo de formato mais adequado para uma consulta visando obter o melhor tempo de resposta.

 
 
 

Para ver o recurso IN-MEMORY em ação, vamos apresentar um exemplo prático com as seguintes etapas:

1) Verificar se o recurso IN-MEMORY esta habilitado
2) Executar uma consulta sem utilizar o IN-MEMORY
3) Incluir as tabelas da consulta no IN-MEMORY
4) Executar a mesma consulta novamente utilizando o IN-MEMORY

 

1) Verificar se o recurso IN-MEMORY esta habilitado

 

Para habilitar o recurso IN-MEMORY dois parametros precisam estar configurados:

  • Configurar o parâmetro inmemory_query = ENABLE
  • Configurar o parâmetro inmemory_size = 100M (Tamanho minimo é 100MB)

A configuração do parâmetro inmemory_size requer uma reinicialização no banco de dados.

 

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
SQL > SELECT BANNER_FULL FROM V$VERSION where rownum < 2;

BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SQL > -- Verify Data in Memory
SQL > col SEGMENT_NAME for a30
SQL > col PARTITION_NAME for a30
SQL > SELECT segment_name,
  2         partition_name,
  3         inmemory_size / 1024 / 1024 as inmemory_size_mb,
  4         bytes / 1024 / 1024 as bytes_mb,
  5         populate_status,
  6         trunc(bytes / inmemory_size, 1) * 100 as compression_ratio
  7    FROM v$im_segments
  8   ORDER BY segment_name, partition_name;

não há linhas selecionadas

SQL > show parameters inmemory

NAME                                         TYPE        VALUE
-------------------------------------------  ----------- ------------------------------
inmemory_adg_enabled                         boolean     TRUE
inmemory_automatic_level                     string      OFF
inmemory_clause_default                      string
inmemory_expressions_usage                   string      ENABLE
inmemory_force                               string      DEFAULT
inmemory_max_populate_servers                integer     4
inmemory_optimized_arithmetic                string      DISABLE
inmemory_prefer_xmem_memcompress             string
inmemory_prefer_xmem_priority                string
inmemory_query                               string      DISABLE
inmemory_size                                big integer 100M
inmemory_trickle_repopulate_servers_percent  integer     1               
inmemory_virtual_columns                     string      MANUAL
inmemory_xmem_size                           big integer 0
optimizer_inmemory_aware                     boolean     TRUE
SQL >

 

2) Executar uma consulta sem utilizar o IN-MEMORY

 

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
SQL > set tab off;
SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL > ALTER SESSION SET current_schema=SH;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-064.1 */ c.cust_last_name,
  2         sum(s.amount_sold)
  3    FROM sh.customers c,
  4         sh.sales s
  5   WHERE c.cust_id = s.cust_id
  6     AND c.country_id = 52776
  7     AND s.channel_id = 2
  8     AND c.cust_last_name like 'Va%'
  9   GROUP BY c.cust_last_name
 10   ORDER BY 1;

CUST_LAST_NAME                           SUM(S.AMOUNT_SOLD)
---------------------------------------- ------------------
Vail                                                9854,94
Valdez                                              3749,04
Vale                                                 111,81
Vance                                                703,64
Vandermark                                            334,7
Vankirk                                              422,86
Vaughn                                                  610

7 linhas selecionadas.

SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW-064.1%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
33y6a0d2f69t8            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  33y6a0d2f69t8, child number 0
-------------------------------------
SELECT /* DBTW-064.1 */ c.cust_last_name,        sum(s.amount_sold)
FROM sh.customers c,        sh.sales s  WHERE c.cust_id = s.cust_id
AND c.country_id = 52776    AND s.channel_id = 2    AND
c.cust_last_name like 'Va%'  GROUP BY c.cust_last_name  ORDER BY 1

Plan hash value: 947950142

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |       |  1692 (100)|          |       |       |      7 |00:00:00.43 |    5928 |   4439 |
|   1 |  SORT ORDER BY         |           |      1 |      7 |   217 |  1692   (1)| 00:00:01 |       |       |      7 |00:00:00.43 |    5928 |   4439 |
|   2 |   HASH GROUP BY        |           |      1 |      7 |   217 |  1692   (1)| 00:00:01 |       |       |      7 |00:00:00.43 |    5928 |   4439 |
|*  3 |    HASH JOIN           |           |      1 |   3048 | 94488 |  1690   (1)| 00:00:01 |       |       |    195 |00:00:00.43 |    5928 |   4439 |
|*  4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |     83 |  1494 |   405   (1)| 00:00:01 |       |       |    110 |00:00:00.01 |    1457 |      0 |
|   5 |     PARTITION RANGE ALL|           |      1 |    258K|  3275K|  1284   (1)| 00:00:01 |     1 |    28 |    258K|00:00:00.37 |    4471 |   4439 |
|*  6 |      TABLE ACCESS FULL | SALES     |     28 |    258K|  3275K|  1284   (1)| 00:00:01 |     1 |    28 |    258K|00:00:00.34 |    4471 |   4439 |
-------------------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("C"."CUST_ID"="S"."CUST_ID")
   4 - filter(("C"."COUNTRY_ID"=52776 AND "C"."CUST_LAST_NAME" LIKE 'Va%'))
   6 - filter("S"."CHANNEL_ID"=2)

Note
-----
   - this is an adaptive plan


32 linhas selecionadas.

SQL >

 
Observe no plano de execução que a consulta foi concluida em 43 centésimos de segundo e acessou 5928 Buffers.

3) Incluir as tabelas da consulta no IN-MEMORY

Agora vamos incluir as duas tabelas da consulta no IN-MEMORY e criar um INMEMORY JOIN GROUP dessas tabelas na coluna utilizada pela consulta para fazer o JOIN.

 

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
SQL > alter table sh.sales inmemory no memcompress priority critical;

Tabela alterada.

SQL >
SQL > alter table sh.customers inmemory no memcompress priority critical;

Tabela alterada.

SQL >
SQL > CREATE INMEMORY JOIN GROUP sales_customers_jg (sh.sales(cust_id), sh.customers(cust_id));

Operação 253 bem-sucedida.

SQL >
SQL > -- Verify Data in Memory
SQL > COL segment_name FOR A30
SQL > COL partition_name FOR A30
SQL > SELECT segment_name,
  2         partition_name,
  3         inmemory_size / 1024 / 1024 as inmemory_size_mb,
  4         bytes / 1024 / 1024 as bytes_mb,
  5         populate_status,
  6         trunc(bytes / inmemory_size, 1) * 100 as compression_ratio
  7    FROM v$im_segments
  8   ORDER BY segment_name, partition_name;

SEGMENT_NAME                   PARTITION_NAME                 INMEMORY_SIZE_MB   BYTES_MB POPULATE_STAT COMPRESSION_RATIO
------------------------------ ------------------------------ ---------------- ---------- ------------- -----------------
CUSTOMERS                                                                10,25   11,34375 COMPLETED                   110
SALES                          SALES_Q1_1998                            2,4375  1,6484375 COMPLETED                    60
SALES                          SALES_Q1_1999                              2,25  2,4140625 COMPLETED                   100
SALES                          SALES_Q1_2000                              2,25    2,34375 COMPLETED                   100
SALES                          SALES_Q1_2001                              2,25  2,2890625 COMPLETED                   100
SALES                          SALES_Q2_1998                              1,25  1,3515625 COMPLETED                   100
SALES                          SALES_Q2_1999                              2,25   2,046875 COMPLETED                    90
SALES                          SALES_Q2_2000                              2,25  2,1015625 COMPLETED                    90
SALES                          SALES_Q2_2001                              2,25   2,390625 COMPLETED                   100
SALES                          SALES_Q3_1998                            2,4375    1,90625 COMPLETED                    70
SALES                          SALES_Q3_1999                              2,25    2,53125 COMPLETED                   110
SALES                          SALES_Q3_2000                              2,25  2,2265625 COMPLETED                    90
SALES                          SALES_Q3_2001                              2,25   2,484375 COMPLETED                   110
SALES                          SALES_Q4_1998                            2,4375    1,84375 COMPLETED                    70
SALES                          SALES_Q4_1999                              2,25  2,3515625 COMPLETED                   100
SALES                          SALES_Q4_2000                              2,25  2,1171875 COMPLETED                    90
SALES                          SALES_Q4_2001                            2,4375  2,6328125 COMPLETED                   100

17 linhas selecionadas.

SQL >

 

Usando a visão V$IM_SEGMENTS podemos verificar a situação dos objetos na área de memória do IN-MEMORY, observe que o campo POPULATE_STATUS indica que todos os objetos foram carregados na memoria (COMPLETED).

4) Executar a mesma consulta novamente utilizando o IN-MEMORY

 

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
SQL > alter session set inmemory_query = ENABLE;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-064.2 */ c.cust_last_name,
  2         sum(s.amount_sold)
  3    FROM sh.customers c,
  4         sh.sales s
  5   WHERE c.cust_id = s.cust_id
  6     AND c.country_id = 52776
  7     AND s.channel_id = 2
  8     AND c.cust_last_name like 'Va%'
  9   GROUP BY c.cust_last_name
 10   ORDER BY 1;

CUST_LAST_NAME                           SUM(S.AMOUNT_SOLD)
---------------------------------------- ------------------
Vail                                                9854,94
Valdez                                              3749,04
Vale                                                 111,81
Vance                                                703,64
Vandermark                                            334,7
Vankirk                                              422,86
Vaughn                                                  610

7 linhas selecionadas.

SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW-064.2%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
6gt9k1gbpv16v            0
6gt9k1gbpv16v            1

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6gt9k1gbpv16v, child number 1
-------------------------------------
SELECT /* DBTW-064.2 */ c.cust_last_name,        sum(s.amount_sold)
FROM sh.customers c,        sh.sales s  WHERE c.cust_id = s.cust_id
AND c.country_id = 52776    AND s.channel_id = 2    AND
c.cust_last_name like 'Va%'  GROUP BY c.cust_last_name  ORDER BY 1

Plan hash value: 2625326473

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |      1 |        |       |    96 (100)|          |       |       |      7 |00:00:00.03 |      18 |
|   1 |  SORT ORDER BY                  |           |      1 |      7 |   217 |    96  (28)| 00:00:01 |       |       |      7 |00:00:00.03 |      18 |
|   2 |   HASH GROUP BY                 |           |      1 |      7 |   217 |    96  (28)| 00:00:01 |       |       |      7 |00:00:00.03 |      18 |
|*  3 |    HASH JOIN                    |           |      1 |   3048 | 94488 |    94  (26)| 00:00:01 |       |       |    195 |00:00:00.03 |      18 |
|   4 |     JOIN FILTER CREATE          | :BF0000   |      1 |   3048 | 94488 |    94  (26)| 00:00:01 |       |       |    110 |00:00:00.01 |       2 |
|*  5 |      TABLE ACCESS INMEMORY FULL | CUSTOMERS |      1 |     83 |  1494 |    16   (7)| 00:00:01 |       |       |    110 |00:00:00.01 |       2 |
|   6 |     JOIN FILTER USE             | :BF0000   |      1 |    258K|  3275K|    77  (29)| 00:00:01 |       |       |    413 |00:00:00.03 |      16 |
|   7 |      PARTITION RANGE ALL        |           |      1 |    258K|  3275K|    77  (29)| 00:00:01 |     1 |    28 |    413 |00:00:00.03 |      16 |
|*  8 |       TABLE ACCESS INMEMORY FULL| SALES     |     28 |    258K|  3275K|    77  (29)| 00:00:01 |     1 |    28 |    413 |00:00:00.03 |      16 |
-------------------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("C"."CUST_ID"="S"."CUST_ID")
   5 - inmemory(("C"."COUNTRY_ID"=52776 AND "C"."CUST_LAST_NAME" LIKE 'Va%'))
       filter(("C"."COUNTRY_ID"=52776 AND "C"."CUST_LAST_NAME" LIKE 'Va%'))
   8 - inmemory(("S"."CHANNEL_ID"=2 AND SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID")))
       filter(("S"."CHANNEL_ID"=2 AND SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID")))

Note
-----
   - this is an adaptive plan


36 linhas selecionadas.

SQL >

 

Observe no plano de execução que as duas tabelas foram acessadas através da operação “TABLE ACCESS INMEMORY FULL”, alem disso na seção “Predicate Information” podemos verificar que aparece a palavra “inmemory” indicando que os filtros foram realizados na área de memoria do IN-MEMORY.
O tempo de resposta da consulta caiu de 43 centésimos de segundos para 3 centésimos (14 vezes mais rápido) e o numero de Buffers acessados caiu de 5928 para 18.

Referências

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

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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