Oracle: Result Cache
O RESULT CACHE é uma das New Features do 11g, e é a que mais gosto, pois deve trazer ganho de desempenho a qualquer aplicação, sem nenhuma alteração em códigos.
Você gosta de Materialized Views? Pois o RESULT CACHE praticamente as torna desnecessárias, pois todos os resultados de SELECTs são armazenados, e continuam lá até que a tabela seja alterada, como em uma MV, mas sem o inconveniente de ter que configura-las, e nem sofrer o Overhead que elas podem causar em tabelas com grande volume de alterações.
Veja o exemplo simples a seguir. Criei uma tabelona, e um SELECT COUNT(*) leva de 10 a 20 segundos para ser executado.
Reparem que o tempo não diminui muito mesmo após executar o SELECT várias vezes, pois esta tabela não cabe toda em meu DB_CACHE_SIZE.
SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
2590224
Decorrido: 00:00:17.07
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
2590224
Decorrido: 00:00:20.79
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
2590224
Decorrido: 00:00:10.58Agora eu habilito o RESULT_CACHE para esta sessão. Este parâmetro pode ser alterado também para toda a instância.
SQL> ALTER SESSION SET RESULT_CACHE_MODE=FORCE;
SessÒo alterada.
Decorrido: 00:00:00.21Agora executo novamente o SELECT. Já na segunda execução (quando o resultado já está no RESULT_CACHE), o resultado é praticamente instantâneo.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
2590224
Decorrido: 00:00:11.82
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
2590224
Decorrido: 00:00:00.09
SQL>Mas não trate o RESULT_CACHE como uma bala de prata. Leia a documentação, pois vários parâmetros controlam seu comportamento.
É interessante dizer que o MySQL possui esta funcionalidade a quase 10 anos, se bem que me lembro que o algoritmo não funcionava também até a versão 5.0.33. É uma prova de que a diversidade agrega valor para todos: o Oracle só fica melhor a cada dia porque tem concorrentes.
O ganho vai ainda mais longe a partir do momento que as pessoas começarem a otimizar queries com o uso da result cache, principalmente fatorando subqueries com hint. Exemplo dos docs:
WITH summary AS
( SELECT /*+ RESULT_CACHE */ department_id, avg(salary) avg_sal
FROM hr.employees
GROUP BY department_id )
SELECT d.*, avg_sal
FROM hr.departments d, summary s
WHERE d.department_id = s.department_id;
Tem um poder muito grande em reporting para DW por exemplo, onde a cache raramente vai ser invalidada por DML nas tabelas e os níveis de agregação sobre fullscans são enormes.
Oi !
A Hint só é necessária se o parâmetro RESULT_CACHE_MODE estiver em MANUAL, que é o padrão, mas eu também prefiro alterar os SELECT, se temos acesso a aplicação.
Gostei da observação sobre as Subqueries, bem pensado.