Forecasting

Forecasting growth can be performed automatically using linear analysis provided one basic assumption is made, and that is, the growth on the table is constant. By monitoring growth over a period of time, it becomes possible to fit a straight line to it, and then predict if a table will exceed its storage allocation.

Information regarding growth and planned growth can be easily extracted from the optimizer statistics gathered provided they are collected on a regular basis. If not, then the statistics need to be manually collected.

Extracting the data from each database is best performed using PL/SQL. A procedure is run that collects all the information, summarizes it, and stores this information into a temporary table. This table is then exported or unloaded (using SQL*Plus) to an operating system file. It is then loaded into the central DBA repository. Alternatively, the data can be copied over using a database link.

The following diagram shows usage of least squares to fit a straight line to a graph modeling storage growth for a hypothetical table (growth is assumed to be constant):

Once all the information has been collated, it's not possible to put together a checklist for doing maintenance on the database:

• Ensure that the locally managed tablespaces are configured with the correct block size. Review extents, and see which tables are candidates to be moved to a locally managed tablespace with a smaller or larger block size.

• Allow room in the tablespace for tables to grow. If each table increased in size by five extents, would the tablespace be able to extent to handle this growth?

• Review all indexes. The Oracle indexes self-balance, so generally do not need rebuilding. Review to see if the indexes are used or if new indexes on different keys are needed.

• Look at the placement of datafiles on the physical disk system. Look to see if there are any candidates for moving to high speed storage (SSD) or to other disks.

• Remove obsolete users and objects.

• Review and modify INIT.ORA parameters.

• Ensure that the database statistics are up-to-date for all object. Dictionary statistics are collection where possible system statistics are kept. Identify indexes as candidates for histogram statistics.

• Review security.

By following the previous steps and doing a thorough review of the database, satisfaction can be gained that the database is correctly tuned and will stay tuned for the period of 6 months. This will leave more time for you, as the DBA performs important tasks such as reviewing SQL code created by developers and ensuring this code is accessing the database optimally.

Securing the database

The next step is to ensure that the database is fully secured. This requires another change in philosophy.

Note

The guiding premise is:

The DBA owns the objects in the database and is responsible for them. The DBA does not own the data in each object. This responsibility is left to an application manager.

By owning each object in the database, the responsibility for ensuring that each object is backed up and can be recovered is firmly entrenched in the hands of the DBA. They also become responsible for ensuring that each object has the correct security on it and has sufficient storage. Other responsibilities include the management of indexes, constraints, synonyms, object tuning, and database links.

The contents of each table is not of importance. How an application works and hangs together is the responsibility of the developers and the application manager.

From this premise, the following work principles can be determined:

• The DBA is not allowed to run scripts on behalf of developers, which manipulate data in tables. As the DBA has no knowledge about the contents of data in tables, he or she is not in a good position to determine if the scripts are valid. There is also the potential for a security breach to occur.

• Developers should not run scripts in a production database, which will modify the structure of tables.

• The DBA has a right to know how tables are being manipulated. For example, what SQL statements are run against database tables?

In addition to the previous security controls, the security of the database should be reviewed when the 6 monthly maintenance review is being performed including:

• Ensure all database users point to the correct default and temporary tablespaces

• Check all users with DBA type privileges, and make sure they are valid

• Check operating system permission on all datafiles

• Redundant accounts are removed

• Roles and grants are valid and are pointing to the correct objects, privileges, and users