More and more customers decide to use MaxDB and with that, the number of customers asking for a comprehensive list of differences or a comparison increases as well.
In my mini-series “Mind the gap” I will try to shed some light on where the little or big differences between MaxDB and Oracle databases are and what to keep in mind when working with them.
Today I take a look on how the Oracle and MaxDB store the data that is put into the databases and what are the consequences of it. It’s quite a long text to read for a blog, but I promise you’ll be wiser when you finish it 😉
How Oracle does it
As more people have already learned a bit or two about Oracle database administration I start with it today.
In Oracle databases with SAP systems data is usually stored in tables, indexes or the partitions of tables and indexes.
These are just 4 types of what is called a segment in an Oracle database. There are many more segment types (e.g. like LOBs, clusters, temporary segments, undo segments etc.) but let us first take a general look.
Basically a segment is anything that allocates storage for data (so a view is not a segment, although you can read data from it).
These segments are stored into chunks of data blocks, called extents. So an extent is a group of blocks. Extents are stored in so called tablespaces that simply consist of the storage of one or more data files.
Thus we have this chain of storage abstraction:
SEGMENT --> DB BLOCKS/PAGES --> EXTENTS --> TABLESPACES --> DATAFILES
Whenever it’s necessary to allocate a new block to store the data for the segment the next free block in the extent is used for it.
When the extent has not enough free blocks available anymore a new extent is allocated from the free space in the tablespace.
This approach is both simple and effective.
Due to the extent wise allocation data of one segment is stored clustered together in the data files.
This makes reading all of this data (e.g. when performing a full table scan) rather quick as the latency only applies for the first block to be read – the next ones are readable in nearly no time.
Another consequence of this approach is that, due to the clustering of data into extents, a problem called fragmentation can occur.
This problem is present, when e.g. although many small pieces of free space in a tablespace exist in a tablespace a new extent cannot be allocated, because it is too large to fit into one of these small free extents.
A second problem with this approach is that it is heavily optimized for data insertion.
As soon as data is deleted the regained space is not available for other segments right away.
That’s right – once an extent is allocated it will stay allocated until the DBA takes care of it.
The problem is of course that although much data is deleted it may be possible that there is still some data present in all the extents.
Therefore – to regain storage space – a major task for the DBA is to reorganize the database.
Also one important characteristic is that data – once it’s written to a block on the disk – will stay in that position.
As long as no reorganization takes place, a specific row of a table will always be available at the very same offset in the very same block where it has been created. This is characteristic is especially used in indexes – Oracle indexes use the row-locations (rowids) for the reference from the index leaf entries to the data.
This is the reason why all indexes of a table need to be rebuilt after a table reorg.
On one side it’s pretty nice to access data via this rowid because it is the fastest way there is. The rowid points exactly to the right data file and the exact block and the position in that block – no index can ever be more effective.
On the other side all references to a specific rowid have to be updated once I want to have the data in another location.
And this is usually the case when a reorganization is done…
There are more advantages of the oracle-approach: due to the many levels of abstraction there are many options to customize and adapt the storage management to the special needs of your system.
One nice option is e.g. to take tablespaces or data files offline or to read-only mode to protect parts of the data from access.
It’s even possible to recover the database part wise – just because the mechanism Oracle employs for the recovery are bound to the way the data is stored.
How it is with MaxDB
MaxDB stores data is a very different manner.
First of all there are fewer levels of abstraction.
All data is stored in one big facility, called the data area.
The data area consists of one or more files, called data volumes. Within the data area all data is written to pages of 8K – similar to Oracle.
But in contrast to Oracle MaxDB automatically stripes the data evenly over all data volumes.
For MaxDB the storage abstraction looks like this:
DB FILES (tables/indexes) --> PAGES/BLOCKS --> DATA AREA --> DATA VOLUMES
The following picture should clarify this:
As you can see, the pages of all files (that’s what MaxDB calls ‘segments’) are distributed evenly over the whole data area.
All files allocate always just one block here and there – so when data is deleted the regained free space is exactly of the size we need to store new data.
No fragmentation whatsoever!
Reorganization free, automatic data distribution
Basically, whenever a page written out to the disks, MaxDB chooses the one that is filled the less.
Another important feature is the shadow-storage functionality. With each write of a page to the disk, the page will be stored at a new location. That way the old version of the page is still in place. To find the current versions of pages in the data volumes MaxDB uses an internal facility called ‘the converter’. As soon as the writing to the disks is successfully (savepoint) the converter knows, that the old versions of pages can be overwritten.
Thus free space is immediately reclaimed after a savepoint. There’s no need to manually reorganize the database.
Building on that concept MaxDB also supports the freezing of a specific db state – that feature is called ‘snapshot’. When pages are used for a snapshot, a copy of the old converter is kept. You can continue to work with the database as usual, but have the option to jump back to the state of the snapshot, at basically no time. This feature is quite useful for training or test systems to avoid the regular recoveries.
One common misconception
Up to SAP DB 7.3 the data distribution was managed differently.
There had been a feature that actively shifted data between the data volumes when the data was unevenly distributed. The problem with that approach is: pressure is on for the I/O subsystem without any use for business actions, that is: sql queries.So it was perfectly possible that your I/O subsystem was running on its edge while users did not do anything on the database. That is something not so desirable when you think of today’s storage systems that host many database instances.
Therefore this behavior had been changed to the “passive” redistribution scheme described above.
The one big consequence from that is that, it should be avoided to add just one single data volume to the database when you want to extend it.
Instead, add multiple datavolumes. Even if these datavolumes are smaller in size this will avoid a I/O hotspot as the new volumes would be the preferred locations for changed pages (see above)
So, today there is no active rebalancing of data in MaxDB instances. Even if you’ve heard the SAP DB administration course some years ago and you have been told there is – it is not. Not anymore 😉
My view to it
Storing the data efficiently and getting it back quick and reliable is one of the major problems when creating a dbms. And the specific requirements for what the data storage should actually do differ widely. Just think of the many different storage engines available for mySQL databases.
Oracle follows the all-in-one approach. The storage technologies available (I only mentioned the very basic, standard options, which are used most with SAP installations. There are _so_much_more!) for Oracle are immense and very flexible.
In turn they all are quite complex.
Even for a standard table and its indexes it’s necessary to provide a target tablespace. And for the target tablespace you’ve to define storage parameters etc. That is something many users don’t need and don’t want. Just observe how SAP went away from the multi-tablespace default setups and arrived finally with just a bunch of tablespaces, that mostly are configured just in one fashion (LMTS+ASSM = all automatic).
But that’s just what MaxDB does anyhow. It does everything automatic.
Once you’re in the position that you really gain from fine-tuning your storage, than Oracle is really the tool of choice as it is very flexible.
On the other hand, if you just want your database to hold your data – then MaxDB does the better job. You delete data – you get space back in the database. Just as you expect it, without any reorganization.
So once more it’s ease of use for MaxDB and flexibility for extreme cases with Oracle.
The cheat sheet ------------- 8< ------------ cut here ------------ 8< ------------
Tablespaces separate data storage within the database
All data is stored to a single location: the data area
Table rows are referenced by ‘ROWID’s and have a fixed location
Table rows are located by their primary key, so it doesn’t matter where the data is actually stored.
Space is allocated in chunks (extents), but not deallocated automatically.
Space is allocated page wise. Whenever a page is not used any longer, the space is immediately available again to any other object in the database.
Scan-operations that read loads of data sequentially are supported very good by storage systems
Scans need to read pages one-by-one so the support for ‘large reads’ of storage systems does usually not help much.
Documentation to read:
Oracle Database Concepts,
Documentation to read:
Concepts of the Database System,
---------------------- 8< ------------ cut here ------------ 8< --------------------