In the end, if you want to seriously reduce the effective size of a database (after using all innovations on the infrastructure level) is to move data out of the database on to something else. This is a bit against Oracle’s preferred approach as they propose to hold as much of the application data in the database for as long as possible (I wonder why…)
We could separate all archiving methods into two categories:
Methods that don’t change the RDBMS representation and just move tables or records to a different location in the same or different database;
Methods that convert database records into something else and remove it from the database layer completely
Another technique that Oracle has improved as of version 11g is compression. In versions up to 10g you could only compress an entire table, and after that, random performance on a compressed table was poor. It worked well for data warehouses where I/O bandwidth is reduced (compressed data can be read quicker from disk than uncompressed) but only in specific cases.
In 11g Oracle has introduced “advanced” compression. I will not go into details, but it allows compression on a much more granular basis, so that OLTP applications can benefit, and it works on a record-by-record basis. Oracle claims this reduces the total database size (no-brainer 🙂 ) and therefore also the backup size (thereby ignoring the effects of tape compression that most customers use, so your mileage may vary). Data can only be compressed once, so the size of a normal database on tape compared to a compressed one will probably not be different with tape compression enabled.
This is an article I wrote a while ago (late 2009), a while after EMC introduced Enterprise Flash Drives (EFD’s). Although more tooling is available these days to automate the tiering of storage, the basic concepts are still very valid, and the article might be a good explanation of the basic concept of database storage tiering and what we want to achieve with this strategy.
I recommend you read Flash Drives first to get some background knowledge before continuing with ILM.
Innovation with Flash Drives
The innovation in disk drive technology with Enterprise Flash Drives (EFD’s – also known as Solid State Disk or SSD’s) is capable of solving the problem of low random performance when using mechanical disk drives.
Here is a comparison of power consumption of various current drive types:
Power per Terabyte
This picture shows the amount of energy to store 1 Terabyte of information. As this would only require one 1-Terabyte SATA drive, this is the most energy efficient (as long as you don’t need much performance). The smaller the capacity, the more drives you need to store 1 Terabyte and therefore smaller drives are less energy efficient just storing data. Faster drives (15,000 rpm) are also the most energy hungry drives so the faster the drive spins, the more energy is needed per terabyte.
This is an article I wrote a while ago (mid 2009), a while after EMC introduced Enterprise Flash Drives (EFD’s) and albeit a bit outdated, it is a valid introduction to Enterprise Flash Drive technology.
It also explains the differences between consumer grade flash (the SSD drive in your laptop or tablet) versus enterprise grade (the stuff that makes business applications screamingly fast)
Enterprise Flash Drive technology
Over the last 30 years, we have seen an enormous improvement in hard disk capacity. Back then a gigabyte drive was quite something and using a state-of-the-art SCSI interface, it could transfer a whopping three megabytes per second!
Currently modern disk drives are smaller and can easily store a terabyte of capacity or more – a 1000-fold improvement! (And the 1.5 and 2 terabyte drives are on the way). With modern fibre channel interfaces, a disk can transfer up to 4 Gigabit (about 400 megabytes) per second which is a 133 times improvement over 30 years!
Fortunately disk drive technology has more or less kept up with Moore’s law for microprocessors regarding capacity and channel bandwidth.
There is a problem, however. First of all the hunger for computer storage is outpacing the increase in disk capacities, so we have to buy more and more disks to satisfy our needs (IDC findings show that the worldwide information created yearly is increasing by about 60% each year).
The second problem is that the access performance of disk drives has not improved that much. In other words, to find information on a disk drive it still requires mechanical movements. The drive arm has to be moved into position over the right disk track and then the disk has to rotate until the data can be accessed by the arm. This takes a few milliseconds and depends largely on the rotational speed of the disks – and this has not improved that much. 30 years ago the regular rotational speed was 3600 rpm, where today’s high-performance drives offer 15,000 rpm – a 4-fold improvement resulting in access times from 24 milliseconds 30 years ago to 6 milliseconds today (note that this is the average time to read data on a random location on the disk if the disk is idle). An important negative side effect of increasing rotational speed is that the power consumption of the drive increases almost exponentially – this is why there were experiments with 20,000 rpm drives but these are not widely adopted into the market. Any faster and the drive will overheat or require special cooling.
