Essa é uma série de posts sobre a nova feature “Oracle In-Memory Column Store” presente na versão do Oracle 12c. O Oracle há muitos anos, tem fornecido a leitura de blocos diretamente da memória RAM através do buffer cache (v$bh). Ao longo desses anos a Oracle tem feito um grande trabalho, aperfeiçoando cada vez mais o algoritmo LRU afim de otimizar as buscas por blocos quentes e mantê-los cada vez por mais tempo em memória para um acesso muito mais rápido, servindo como um verdadeiro “cache” para o banco de dados.
Com a vinda do IMDB, a arquitetura e a performance muda completamente comparado a leitura no buffer cache da memória do database, a começar pela maneira como o Oracle armazena os dados em memória. Assim como o nome da feature sugere, o Oracle In-Memory Column Store, passa agora a armazenar os dados de forma colunar, totalmente otimizado para a leitura via SDRAM, trabalhando como um verdadeiro DBMS orientado a coluna: Column-oriented DBMS
Não é preciso dizer que a RAM é muito mais rápida que um acesso a disco (isso todo mundo já sabe), assim não é nem preciso comentar ou mostrar aqui, que com a utilização do IMDB o ganho de performance é absurdo. Mas … será que o IMDB vem para resolver todo e qualquer tipo de problema na performance do ambiente?! O que pretendo apresentar nessa série de posts é justamente casos como esses, evidências e provas na utilização da nova feature, que assim como qualquer outra, apresentar suas vantagens e desvantagens e melhores formas de se utilizar.
É incrível a quantidade de DBAs e empresas que acreditam só em mágica, que com apenas um “alter table” todos os problemas de performance irão desaparecer. IMDB não é sinônimo de “desligar o cérebro do DBA”.
A partir de agora, vou apresentar um comportamento bastante incomum em alguns ambientes com o Oracle In memory. Através do utilitário Swingbench criei a tabela SOE.ORDER_ITEMS de 12G. Essa será a nossa tabela teste para o uso do IMDB. Aqui mais detalhes da tabela:
sys@ora01 SQL> @sizesoe.order_items
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------- ------------------------------ ------------------ -------------
SOE ORDER_ITEMS TABLE 12,398.0
-------------
sum 12,398.0
1 rowselected.
sys@ora01 SQL> -- Atualizando as estatísticas da tabela:
sys@ora01 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SOE', 'ORDER_ITEMS',
method_opt => 'FOR TABLE FOR ALL COLUMNS SIZE AUTO', cascade =>true);
PL/SQL procedure successfullycompleted.
sys@ora01 SQL> @tbstatsoe.order_items
=============================================
=== O B J E C T T A B L E ===
=============================================
OWNER: SOE TABLE: ORDER_ITEMS
Name Null? Type
------------------------------- -------- ----------------------------
1 ORDER_ID NOT NULL NUMBER(12)
2 LINE_ITEM_ID NOT NULL NUMBER(3)
3 PRODUCT_ID NOT NULL NUMBER(6)
4 UNIT_PRICE NUMBER(8,2)
5 QUANTITY NUMBER(8)
6 DISPATCH_DATE DATE
7 RETURN_DATE DATE
8 GIFT_WRAP VARCHAR2(20)
9 CONDITION VARCHAR2(20)
10 SUPPLIER_ID NUMBER(6)
11 ESTIMATED_DELIVERY DATE
================================================================================
TableStatistics
================================================================================
TABLE_NAME : ORDER_ITEMS
STATUS : VALID
LAST_ANALYZED : 29/03/2016 22:08:46
PARTITIONED : NO
TEMPORARY : N
DEGREE : 1
BUFFER_POOL : DEFAULT
CACHE : NO
CELL_FLASH_CACHE : DEFAULT
COMPRESSION : DISABLED
RESULT_CACHE : DEFAULT
ROW_MOVEMENT : DISABLED
LOGGING : YES
SAMPLE_SIZE : 85789721
==================================================================================
Column Statistics
================================================================================================================================
ID# NAME ANALYZED NDV DENSITY # NULLS # BUCKETS SAMPLE HISTOGRAM
================================================================================================================================
1 ORDER_ID 29-03-16 22:08 28434432 0.00000004 0 1 85789721 NONE
2 LINE_ITEM_ID 29-03-16 22:08 5 0.20000000 0 1 85789721 NONE
3 PRODUCT_ID 29-03-16 22:08 999 0.00100100 0 1 85789721 NONE
4 UNIT_PRICE 29-03-16 22:08 2000 0.00050000 0 1 85789721 NONE
5 QUANTITY 29-03-16 22:08 10 0.10000000 0 1 85789721 NONE
6 DISPATCH_DATE 29-03-16 22:08 124 0.00806452 0 1 85789721 NONE
7 RETURN_DATE 29-03-16 22:08 4507 0.00022188 84059125 1 1730596 NONE
8 GIFT_WRAP 29-03-16 22:08 6 0.16666667 0 1 85789721 NONE
9 CONDITION 29-03-16 22:08 3 0.33333333 0 1 85789721 NONE
10 SUPPLIER_ID 29-03-16 22:08 999 0.00100100 0 1 85789721 NONE
11 ESTIMATED_DELIVERY 29-03-16 22:08 4507 0.00022188 0 1 85789721 NONE
================================================================================================================================
IndexInformation
=====================================================================================================================================
INDEX_NAME COLUMNS ANALYZED DIST_KEYS DENSITY NUM_ROWS UNIQUE VISIBLE STATUS
=====================================================================================================================================
ITEM_ORDER_IX ORDER_ID 29-03-16 22:08 28434432 0.0000000 82834901 NO YES OK
ITEM_PRODUCT_IX PRODUCT_ID 29-03-16 22:08 999 0.0010010 79984685 NO NO OK
ORDER_ITEMS_PK LINE_ITEM_ID ,ORDER_ID 29-03-16 22:08 85615194 0.0000000 85615194 YES YES OK
sys@ora01 SQL>Veja que nesse momento, a tabela SOE.ORDER_ITEMS está com o InMemory desabilitado:
sys@ora01 SQL>selectowner, table_name, inmemoryfromdba_tableswhereowner='SOE' andtable_name='ORDER_ITEMS';
OWNER TABLE_NAME INMEMORY
------------------------ ------------------------------ --------
SOE ORDER_ITEMS DISABLED
1 rowselected.Vamos executar uma consulta de encontro a tabela e analisar os resultados. Veja que estou forçando a consulta a ser executada em serial e obtendo o máximo de estatísticas para análise, isso através dos hints NO_PARALLEL e GATHER_PLAN_STATISTICS respectivamente.
sys@ora01 SQL> SET TIMING ON
sys@ora01 SQL> SELECT /*+ monitor gather_plan_statisticsno_parallel */ SUM(quantity)
FROM soe.order_items WHERE product_id=:p;
SUM(QUANTITY)
-------------
1376607
Elapsed: 00:00:43.51
1 rowselected.
sys@ora01 SQL> -- Buscando o sqlid da query acima
sys@ora01 SQL> @lastsql
HASH_VALUE SQL_ID CH# PLAN_HASH HASH_HEX SQL_TEXT
---------- ------------- ----- ------------ ---------- -------------------------------------------------------
3284254310 97h2yq31w3gm6 0 2407813087 c3c1be66 SELECT /*+ MONITOR GATHER_PLAN_STATISTICS NO_PARALLEL
*/ sum(QUANTITY) fromsoe.order_itemswhere PRODUCT_ID=:pVamos analisar agora, a consulta 97h2yq31w3gm6:
sys@ora01 SQL> SET HEADING OFF LINES 32767
sys@ora01 SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '97h2yq31w3gm6',
report_level=>'ALL', type => 'TEXT') as report FROM dual;
SQL MonitoringReport
SQL Text
------------------------------
SELECT /*+ monitor gather_plan_statisticsno_parallel */ SUM(quantity) FROM soe.order_items WHERE product_id=:p
Global Information
------------------------------
Status : DONE (ALL ROWS)
SQL ID : 97h2yq31w3gm6
SQL Execution ID : 16777216
Duration : 44s
Module/Action : SQL*Plus/-
FetchCalls : 1
Binds
=====================================
| Name | Position | Type | Value |
=====================================
| :P | 1 | NUMBER | 509 |
=====================================
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 44 | 4.92 | 39 | 1 | 251K | 250K | 2GB |
================================================================
SQL Plan MonitoringDetails (Plan Hash Value=2407813087)
===========================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | ActivityDetail |
| | | | (Estim) | | Active(s)| Active| | (Actual)| Reqs | Bytes | (%) | (# samples) |
===========================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 39 | +2 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 39 | +2 | 1 | 1 | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS | 85876 | 697 | 40 | +1 | 1 | 306K | 249K | 2GB | 100.00 | db file parallelread (40)|
| 3 | INDEX RANGE SCAN | ITEM_PRODUCT_IX | 85876 | 2 | 39 | +2 | 1 | 306K | 643 | 5MB | | |
===========================================================================================================================================================================
1 rowselected.
sys@ora01 SQL> SET HEADING ONTemos aqui o plano da nossa consulta de encontra a tabela ORDER_ITEMS. Veja que a consulta levou cerca de 44s para ser processada, sendo que, 39s foram gastos em I/O e 5s em CPU. A leitura total em disco foi cerca de 2G.
A partir de agora, iniciaremos o nosso teste com o Oracle In-memory 12c. O objetivo aqui, é colocar a nossa tabela ORDER_ITEMS em modo In-MemoryColumnStore e realizar novamente a mesma consulta e obter algumas conclusões. O primeiro passo, é popular a coluna na memória:
sys@ora01 SQL> ALTER TABLE soe.order_items INMEMORY;
Tablealtered.
sys@ora01 SQL> ALTER SESSION SET "_inmemory_populate_wait"=TRUE;
Sessionaltered.
sys@ora01 SQL> EXEC DBMS_INMEMORY.POPULATE('SOE','ORDER_ITEMS');
PL/SQL procedure successfullycompleted.
sys@ora01 SQL> COLUMN seg_inmem_size FORMAT 99,999,990.9 HEADING 'In-Memory|SIZE_MB'
sys@ora01 SQL> COLUMN seg_orig_size_megs FORMAT 99,999,990.9 HEADING 'ORIGINAL|SIZE_MB'
sys@ora01 SQL> COLUMN seg_megs_not_populated FORMAT 99,999,990.9 HEADING 'NOT POPULATE|SIZE_MB'
sys@ora01 SQL> COLUMN populate_status HEADING 'POPULATE|STATUS'
sys@ora01 SQL> SELECT
2 inst_id
3 ,v.owner
4 ,v.segment_name
5 ,v.populate_status
6 ,v.inmemory_size/1024/1024 seg_inmem_size
7 ,v.bytes/(1024*1024) seg_orig_size_megs
8 ,v.bytes_not_populated/(1024*1024) seg_megs_not_populated
9 ,v.inmemory_priority
10 ,v.inmemory_compression
11 FROM v$im_segments v
12 WHERE UPPER(segment_name) LIKE UPPER(CASE WHEN INSTR('&1','.') > 0
THEN substr('&1',instr('&1','.')+1) ELSE '&1' END)
13 AND UPPER(owner) LIKE UPPER(CASE WHEN INSTR('&1','.') > 0 THEN SUBSTR('&1',1,instr('&1','.')-1) ELSE user END)
14 /
POPULATE In-Memory ORIGINAL NOT POPULATE In-Memory In-Memory
OWNER SEGMENT_NAME STATUS SIZE_MB SIZE_MB SIZE_MB PRIORITY COMPRESSION
---------------- ----------------- --------- ------------- ------------- ------------- -------- -------------
SOE ORDER_ITEMS COMPLETED 3,554.1 12,398.0 0.0 NONE FOR QUERY LOW
1 rowselected.Como mostra a viewv$im_segments, veja que o status da nossa tabela está totalmente populada na área do InMemory, com um tamanho aproximadamente de 3500M.
Com a tabela na memória, iremos executar a mesma consulta e verificar os resultados, mas antes, vamos executar um flush no buffer cache/shared pool, para que os testes anteriores não sejam levados em consideração.
sys@ora01 SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
sys@ora01 SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
sys@ora01 SQL> SELECT /*+ monitor gather_plan_statisticsno_parallel */ SUM(quantity)
FROM soe.order_items WHERE product_id=:p;
SUM(QUANTITY)
-------------
1376607
Elapsed: 00:00:43.03
1 rowselected.
sys@ora01 SQL> @lastsql
HASH_VALUE SQL_ID CH# PLAN_HASH HASH_HEX SQL_TEXT
---------- ------------- ----- ------------ ---------- ----------------------------------------------------------
3284254310 97h2yq31w3gm6 0 2407813087 c3c1be66 SELECT /*+ monitor gather_plan_statisticsno_parallel
*/ SUM(quantity) FROM soe.order_items WHERE product_id=:p
sys@ora01 SQL> SET HEADING OFF LINES 32767
sys@ora01 SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '97h2yq31w3gm6',
report_level=>'ALL', type => 'TEXT') as report FROM dual;
SQL MonitoringReport
SQL Text
------------------------------
SELECT /*+ monitor gather_plan_statisticsno_parallel */ SUM(quantity) FROM soe.order_items WHERE product_id=:p
Global Information
------------------------------
Status : DONE (ALL ROWS)
SQL ID : 97h2yq31w3gm6
SQL Execution ID : 16777216
Duration : 44s
Module/Action : SQL*Plus/-
FetchCalls : 1
Binds
=====================================
| Name | Position | Type | Value |
=====================================
| :P | 1 | NUMBER | 509 |
=====================================
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 44 | 4.92 | 39 | 1 | 251K | 250K | 2GB |
================================================================
SQL Plan MonitoringDetails (Plan Hash Value=2407813087)
===============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | ActivityDetail |
| | | | (Estim)| | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
===============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 39 | +2 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 39 | +2 | 1 | 1 | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS | 85876 | 697 | 40 | +1 | 1 | 306K | 249K | 2GB | 100.00 | db file parallelread (40) |
| 3 | INDEX RANGE SCAN | ITEM_PRODUCT_IX | 85876 | 2 | 39 | +2 | 1 | 306K | 643 | 5MB | | |
===============================================================================================================================================================================
1 rowselected.
sys@ora01 SQL> SET HEADING ONNada mudou em relação a primeira execução. Veja que interessante, mesmo com a minha tabela totalmente carregada na memória via IMDB a quantidade de leitura física da primeira execução com a segunda foi exatamente a mesma. O tempo total da consulta também não mudou, levamos cerca de ~ 44s para retornar os dados.
Se a ORDER_ITEMS está agora totalmente disponível via InMemoryColumnStore, porque o Oracle está fazendo a mesma quantidade de leitura em disco? Ele não deveria buscar os dados em memória e evitar essa grande quantidade de leitura em disco?
Como regra geral (assim como acontece no SmartScan do Exadata), para que o otimizador do Oracle decida ir vi InMemory, um acesso “FULL SCAN” deve ser feito na tabela. Observe no plano de execução da consulta acima, que o caminho escolhido pelo Oracle é através do INDEX ITEM_PRODUCT_IX (via INDEX RANGE SCAN).
===========================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | ActivityDetail |
| | | | (Estim) | | Active(s)| Active | | (Actual)| Reqs | Bytes | (%) | (# samples) |
===========================================================================================================================================================================
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS | 85876 | 697 | 40 | +1 | 1 | 306K | 249K | 2GB | 100.00 | db file parallelread (40)
| 3 | INDEX RANGE SCAN | ITEM_PRODUCT_IX | 85876 | 2 | 39 | +2 | 1 | 306K | 643O Whitepaper do Oracle DatabaseIn-Memory 12c, é bem claro ao dizer que a busca dos dados via In-memory será feito apenas quando o acesso na tabela for via FULL SCAN. Assim jamais a nossa consulta acima irá consulta os dados presentes na nossa RAM, já que o otimizador está escolhendo o caminho através do index ITEM_PRODUCT_IX.
Podemos resolver essa situação adicionando o hint FULL na consulta, ou deixando o Index ITEM_PRODUCT_IX invisible. Veja só o resultado:
sys@ora01 SQL>alter index soe.ITEM_PRODUCT_IXinvisible;
Index altered.
sys@ora01 SQL> SELECT /*+ MONITOR GATHER_PLAN_STATISTICS NO_PARALLEL */ sum(QUANTITY)
from soe.order_itemswhere PRODUCT_ID=:p;
SUM(QUANTITY)
-------------
1376607
Elapsed: 00:00:00.05
1 rowselected.
sys@ora01 SQL> SET HEADING OFF LINES 32767
sys@ora01 SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '97h2yq31w3gm6',
report_level=>'ALL', type => 'TEXT') as report FROM dual;
SQL MonitoringReport
SQL Text
------------------------------
SELECT /*+ MONITOR GATHER_PLAN_STATISTICS NO_PARALLEL */ sum(QUANTITY)
from soe.order_itemswhere PRODUCT_ID=:p
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (1150:44245)
SQL ID : 97h2yq31w3gm6
SQL Execution ID : 16777217
Duration : .041563s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program :sqlplus@Flavios-MacBook-Pro.local (TNS V1-V3)
FetchCalls : 1
Binds
======================================
| Name | Position | Type | Value |
======================================
| :P | 1 | NUMBER | 509 |
======================================
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes|
===========================================================================
| 0.04 | 0.04 | 0.00 | 0.00 | 1 | 249 | 18 | 144KB |
===========================================================================
SQL Plan MonitoringDetails (Plan Hash Value=3419397814)
======================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | ActivityDetail |
| | | | (Estim) | | Active(s)| Active | | (Actual)| Reqs | Bytes | (%) | (# samples) |
======================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | |
| 2 | TABLE ACCESS INMEMORY FULL | ORDER_ITEMS | 85876 | 7880 | 1 | +0 | 1 | 306K | 12 | 98304 | | |
======================================================================================================================================================
1 rowselected.
sys@ora01 SQL>Aqui podemos notar o poder do INMEMORY, observe o tempo, caiu de 44s para apenas 0.4s. Veja também que agora a operação de consulta não foi mais via INDEX RANGE SCAN e sim via TABLE ACCESS INMEMORY FULL, o que mostra que a busca dos dados foi exclusivamente na memória para a tabela ORDER_ITEMS. Outro ponto importante é a quantidade de leitura física que de 2G caiu para 144KB. O tempo 0.4s foi exclusivamente o tempo da CPU em processar os dados (CPU Time(s)). Chegamos aqui, com algumas conclusões:
- Vou ter performance colocando todas as minhas tabelas em IMDB? É necessário testar, não tem outro caminho. Cada ambiente é única e existe muita variável no meio do caminho. Como os testes acima, mesmo ativando o IMDB, não vimos nenhuma melhora até analisar e corrigir.
- Porque o Oracle decide um acesso via INDEX, sendo que meus dados estão na memória? Bom, isso vou explicar melhor em um próximo post, mas basicamente o Oracle considera o custo do INDEX menor, por isso acaba optando pela utilização do INDEX. Alguns parâmetros no banco também afetam essa decisão, vamos conferir isso melhor nos próximos posts.
- Se o IMDB só funciona via FULL SCAN, devo deixar todos meus INDEXES invisíveis? NUNCA!!! Isso é um mito que surgiu com o Exadata. É importante lembra que tanto no IMDB como no Exadata, o INDEX não é um vilão. Um acesso via INDEX pode muitas vezes ser muito mais benéfico, já que o objetivo do INDEX é justamente economizar leitura em disco. A TI é um mundo lógico e deve ser sempre levado, como os gringos gostam de falar, com systematic approach. Não existe mágica. Nos próximos posts, vou mostrar melhor esse tema.
- Vou precisar mudar minha aplicação para utilizar o IMDB? Como está na documentação Oracle, você não precisa mover uma vírgula da aplicação para utilizar o IMDB. Agora fazemos outra pergunta: Vou ter performance em todas as tabelas que coloquei no INMEMORY? Mais uma vez …. Depende! É necessário testar.
- O IMDB realmente é performático? Sim, extremamente performático comparado com tudo que temos no mercado! Como disse no começo do post, não é necessário explicar a imensa diferença de performance da RAM contra o disco, todos já sabem. É necessário no entanto saber aproveitar e saber utilizar a feature da melhor forma. Como vimos nesse simples exemplo, uma consulta teste, caiu de 44s para 0.4s, e o mais importante, sem mudar uma vírgula na consulta.
- Minha aplicação OLTP cheia de INDEX, vou ter algum ganho com o IMDB? Provavelmente. Mas uma vez: É necessário testar! Deve-se sempre avaliar, estatísticas, index, hints, etc …
Aguarde os próximos posts, tem muita coisa interessante sobre essa nova incrível feature do Oracle.