As with most technology, Oracle database design has improved with each release. When it comes to maintenance, many tasks are now automated to help optimise the performance of the database; however, there are still some areas that are worth looking at when it comes to investigating performance.
One of these areas of interest is around fragmentation of tables.
What is Oracle table fragmentation?
Oracle table fragmentation happens when we, or an application we are using, update or delete data from a table. When this happens it leaves a “space” which doesn’t get re-used immediately and doesn’t get automatically released or freed up. A collection of these “spaces” in a table is known as fragmentation.
As a result of these updates and inserts a row of data can be split across more than one Oracle block which can decrease performance, sometimes significantly, this is because additional block access is required to read the row of data. This is also known as “Chained Rows”.
To put some context around it, I’ve seen searches take around 8 times longer on a table that is heavily fragmented...!
So what can be done about it?
The good news is that your Oracle database administrator (DBA) can run a search on the database for tables with a high number of chained rows, once identified there are a number of ways to “de-frag” them. A common method is to move the table and then rebuild the indexes; this will removed the “spaces” giving better performance.
Help is at hand
As always, Capita Technical services is available to provide assistance should you require it. In fact we will check and report on table fragmentation as part of an overall health check of your One architecture. To enquire about or to book a Healthcheck please e-mail firstname.lastname@example.org