Database review

The review of the database is the most critical step, and approximately two working weeks should be devoted to it. The review involves:

• Analyzing information collected about the database from the previous 6 months and forecasting growth and database usage

• Liaising with groups (see the next table), and determining potential changes to the environment in the next 6 months, for example, there might be a plan to double the number of users who access the database

• Liaising with management to acquire extra storage and capacity based on forecasts; if due to cost constraints, this capacity cannot be acquired and then alternatives must be explored

The following table shows the areas the DBA should liaise with when performing a review:

Developers	Determine upgrades planned in the next 6 months, and review indexes and SQL statements.
Application Users	Review application usage and review data entry usage.
Application Management	Determine application changes in the next 6 months, and also, determine changes to capacity in the next 6 months.
System Administrators	Determine operating system changes planned in the next 6 months, and review capacity changes required in the next 6 months.
Storage Management	Determine if there are any hardware changes in the next 6 months, and review storage requirements for the next 6 months.
Network Management	Determine if there are any network changes changes in the next 6 months, and review capacity requirements for the next 6 months.

The key to the review is obtaining information. This is best handled by the DBA coding plus running scripts and then storing information about all the objects in the database. Oracle provides a large number of tools and capabilities to collect this information. The database is the best environment for the DBA and PL/SQL, the best tool. The information extracted can be broken up into coarse and fine grain:

Coarse Grain	Database Focus Area
	Tablespace
	Datafile
	UNDO
	Temporary
	SYSTEM
	REDO Logs
	Archives
	Parameters
	Network Load
	Audit trails and logs
Fine Grain	Database Focus Area
	Tables
	Indexes
	Triggers
	Constraints
	Objects
	External Tables
	Specialized Views
	Replication structures
	Built in packaged apps (Apex, Multimedia, Spatial)
	Optimization figures

The initial investment required in moving to a proactive environment is for the time to devote to building the scripts and programs required to extract the information from the database and then store it. It is this hurdle that is the hardest one to jump, as it is typically seen as a waste of time and effort. Unfortunately, there are no known tools in the market that perform perform all of this for you, but there is a large number that can assist and simplify the tasks.

It is important that information should be extracted on a daily basis and stored in a central repository (see the last diagram). This repository is rather like a data warehouse. Information extracted from the database is used for two purposes. The first, as discussed already, is used for the 6-monthly review. The second purpose is to test to see if emergency maintenance is required.

The following diagram shows the creation of a DBA warehouse:

It is important that the emergency maintenance report details only objects to those that need to be fixed immediately. The danger, which is common in a lot of environments, is information overload. By presenting too much information, the odds increase of vital pieces being overlooked and missed.