Database Reorganization
Relational technology and SQL make data modification easy. Just issue an INSERT, UPDATE, or DELETE statement with the appropriate WHERE clause and the DBMS takes care of the actual data navigation and modification. In order to provide this level of abstraction, the DBMS handles the physical placement and movement of data on disk. Theoretically, this makes everyone happy. The programmer’s interface is simplified, and the RDBMS takes care of the hard part—manipulating the actual placement of data. However, things are not quite that simple. As the DBMS physically manages data, things can get out of place or disorganized, which can cause performance issues.
Every DBA has encountered a situation in whichh a query or application that used to perform well slows down after it has been in production for a while. These slowdowns have many potential causes—one of which can be database disorganization which occurs when a database’s logical and physical storage allocations contain many scattered areas of storage that are too small, not physically contiguous, or too disorganized to be used productively. The primary reasons causing disorganizations are unclustering (as data is added and changed it may not be in clustering sequence), fragmentation where data is scattered across many areas of storage, page splits where one page is split into two to accommodate new data, file extents where additional associated files are added to accommodate growth, and row chaining (or row migration) when updated data does not fit in the space it currently occupies, the DBMS finds space elsewhere, requiring a pointer to another page or block that slows subsequent access.
Reorganization can clear up all of these issues. Typically using a database utility program, reorganization will look for and remedy database disorganization. Sometimes an unload and reload of the data is required to facilitate reorganization.
Statistics queries from the system catalog can be used to determine when to reorganize a database object. Each DBMS provides a method of reading through the contents of the database and recording statistical information about each database object. Depending on the DBMS, this statistical information is stored either in the system catalog or in special pages within the database object itself.
Additional Approaches
This brief summary of database performance should be helpful as you embark on your tuning and optimization efforts. Of course, there are many more details than can be provided in a column of this nature, so use this as a jumping-off point to learn about more database performance techniques.