Category Archives: Oracle Internals

This category list all articles about internal functionnalites of oracle, optimizer, statistics, internal components of the Oracle database

Result Cache concept and benefits

22/04/2014Oracle InternalsCyrille Modiano

This feature was first introduced in Oracle 11g and was meant to increase performance of repetitive queries returning the same data.
The Result Cache feature is interesting if your application always look for static data, or data that is rarely updated, for these reasons, it is firstly destinated to Data Warehouses databases (OLAP) as many users will look for the same data via reporting tools.
This is particularly useful when you query a large amount of data to return only a few rows, for example, the last month sales aggregated by week.
Oracle will have to look for a large amount of data to retrieve all sales and make the aggregate, but the result will be only 4 rows.
For the first query execution, the runtime will be standard but for the subsequent queries, it will be very fast.
This cache is part of the shared pool.

Continue reading Result Cache concept and benefits →

Partitioning without Enterprise Edition

31/01/2014Oracle InternalsCyrille Modiano

Partitioning is part of the Oracle Enterprise Edition, this is an expensive option.
Sometimes you just have one big table storing historical data in your database and this is just too much to pay. In that case you can use the procedure below to “emulate” the partitioning. I say “emulate” because you won’t have all the performance benefits and functionnalities of the partitionning. This method is called manual partitioning.

Manual partitioning concept

The concept is really simple, instead of creating partitions, you will create tables corresponding each to a range of values.
For exemple you have to keep 30 days of data with several millions rows of log every day.
If you have only one table, your table should have almost 100 millions rows at the end of the month which will result to bad performances in queries against this table.
What we will do is to create a table for each day of the month, each table will contain approximately 10 millions rows resulting to better performance.