Appendix C. Proactive Database Tuning

Let's face it, managing a database environment can be very difficult to do. Who amongst you as a database administrator (DBA) has constantly found yourselves reacting to each situation that occurs? The table runs out of storage near budget time, and when fixing it, you find there is no room left on the disk to expand the tablespace the table is in. By now, you are in a panic mode, trying to find some place to put the data file in, but there isn't any room left (anywhere). Why didn't we buy some more disk when we had the chance? Have you asked yourself this question? That's right, you never had time to request it, because you were too busy fixing problems.

As a database administrator, you will find that you are constantly fire fighting, sometimes controlling the blaze but never putting it out. Only by moving to a proactive environment can you overcome the burdens and inefficiencies of the reactive environment you might be in, and in doing so, enter an optimally controlled and managed one. Such an environment offers many benefits, including a reduction in database downtime, a finely tuned database and an improvement in productivity.

So, why the need to always react? The answer is found in the work practices of the environment. Taking a step back and then looking at how you are doing your work is the first step, but more on this later. Let's first have a look at why this situation has occurred.

With the need to cut back on resources and increase productivity, the workload of the DBA can be cut first, because it is seen as not directly benefiting the client. With the resources cut right back, only activities which are seen to be important can be focused on.

Let's have a look at two typical scenarios that are most likely to be encountered:

• The users of an application have complained bitterly to management about its slow performance. Management is now asking you to drop everything and fix it as a matter of high urgency. This means spending a large amount of time tracking the problem. This involves looking to see there is a problem with the tuning of the application, a problem with the tuning of the database, or if there is insufficient machine capacity. In all cases, it is up to you to find where the fault is, and this takes time.

• A table modification change is urgently required in the production database. The users are in desperate need of it. As the DBA, you were just told to implement the change and take it for granted that it will not impact the database. As it is urgent, there is no time to properly review what is happening in the upgrade. The next day the discovery is made that the upgrade has forced some tables to dramatically increase in size, and there is a critical shortage of free space left, and this now has to be addressed.

In both cases, time and effort is being spent doing extra work, which could have been prevented with the right amount of planning.

So, to do your job properly requires a fundamental change in how you do your work, with the aim to become proactive. The goals are simple but can be difficult to implement:

• Anticipate and prevent problems before they occur

• Optimally tune the database

• Optimally manage storage

• Optimally tune the network

• Minimize impact to the database

There are numerous challenges following such goals. Optimally managed resources ensure that the environment is efficiently tuned and managed. Also, an optimally managed environment ensures that your resources are used efficiently and cost effectively.

Minimizing impact to the database involves reducing the amount of maintenance that is applied to the database. This, in turn, will ensure that there is an increase in uptime for the database and reduce the risk of an error; especially, an error resulting from fatigue caused by working on the database at odd hours of the night.

The environment and the DBA

Moving to a proactive environment is easier. There are a number of hurdles that must be jumped, but when reached the benefits are worth the effort.

To start, a complete change in the philosophy for how a database is managed is needed. The role of the DBA needs to be revised. By focusing on such a new role, the move to a proactive environment will be so much easier.

Note

The new role of the DBA can be stated as:

To ensure that the database performs optimally, it is fully secured and can be recovered in the time of need.

To achieve this, the DBA can no longer be consigned to the back room, out of sight, and out of mind. The DBA has to become more actively involved.

Ensuring optimal performance

The first part (ensure that the database performs optimally) is the most difficult to implement and involves a number of steps.

For starters, one has to throw out of the window the concept that performance tuning is an action that is done after the application is built. In the database of today, application and database tuning go hand in hand and must be factored in from the very beginning.

There are three critical inputs, which must be analyzed when an application is built. They are the user interface, performance, and the database. Each interacts with the other and each has equal weighting (see the next diagram).

Only with the advent of GUI application programming, has the issue of the user interface become apparent. An efficient GUI design means that the application is efficiently used, resulting in a reduction in database and network calls.

The design of the database must take into account the performance, and the user interface will also affect the design.

The following diagram shows the three most critical inputs for application development from the DBA viewpoint:

All three inputs into the application design require co-ordination by the DBAs. They have the knowledge on how the application works in the environment and are in the best position to control how the application integrates with the database.

Cyclic maintenance

To ensure that the database environment is optimally tuned, the move must be away from reacting to events, and instead actively planning and then tuning the database. There is a balance between constantly performing maintenance on the database and not interfering with the database. At times, it is true when it is said that problems occur only after the maintenance has been done on the database. So, a good period to aim for, where no work is done on the database, is about 6 months (depending on the volatility of a database application, the period can range from 4 to 8 months).

Maintenance is introduced and performed on a cyclic basis. The cycle involves reviewing the database, performing maintenance, and then leaving the database alone (see the next diagram). Once the review is performed two to three months after the database has been created, the newly created databases are prone to change.

The aim is to perform a full database reorganization and tune every 6 months. Performing this maintenance more frequently will be disruptive to the users and unnecessary. Performing it less frequently will result in the database becoming out of tune and the danger that objects will grow beyond their original storage allocations. If, for example, a 2-year period was used, then it would be quite difficult to predict storage requirements. There are too many factors to take into consideration and uncertainties that can occur.

Once the maintenance has been performed, the database is not touched except if an emergency occurs. So, the role of the DBA changes further, and they must now become an expert in forecasting to calculate storage and CPU requirements for a 6-month period.

Emergency maintenance is only performed when something drastic occurs, and the stability of the database is threatened. The examples include a table not being able to grow or the PROCESS parameter being exceeded in the INIT.ORA file. In these cases, emergency maintenance has to be performed, because an event unforeseen in the initial planning was missed. These events happen but should occur rarely. If they occur frequently, then the review has not been performed correctly, and procedures should be adjusted accordingly.

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.

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

Data recovery

The final step in moving to a proactive environment is to ensure that the database can be recovered in time of need.

To achieve this, an actual recovery of the production database must be performed at least once a year to check that:

• The backups are working correctly

• There are storage devices available that can be used to recover the database

• There is sufficient knowledge and expertise in recovering the database

• The time to perform a recovery is understood

The back-up strategy should be reviewed and the following questions should be asked:

• If the database increases in size, can the backups cope?

• Will the size of export files grow? If so, is there sufficient storage to contain them?

• Will the length of time for a backup to run increase?

• Is there sufficient disk storage to handle an increase in the size of the database?

The ability to perform a recovery includes testing the following scenarios:

• A datafile is lost

• The redo logs are lost (and mirroring is not activated)

• The latest backup failed, and recovery has to be performed from an older backup

Once the move has been made into a proactive environment, discipline is required to ensure that the environment remains stable. This means that regular database reviews have to be performed, security enforced, and recovery procedures tested. It is very easy to slip and move back into a reactive environment.

So, the encouragement is there to move to a proactive database environment. Such an environment offers a lot of advantages, including an increase in database uptime, minimizing the chance of problems and errors occurring, finding problems quickly, and also an improvement in productivity.

It is not easy to move to such an environment and when reached it requires discipline to maintain it. Once reached though, the benefits are many and should offer you greater control and flexibility in managing the database.