O pacote DBMS_XPLAN fornece uma maneira fácil para exibir o plano de execução em vários formatos pré-definidos, essa flexibilidade e o nível de detalhes estatísticos que o pacote oferece o torna uma ferramenta imprescindível num processo de tuning de uma instrução SQL. Você pode usar o pacote para exibir uma…
In-memory: Um recurso subutilizado
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).
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.
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:
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/
