| Previous | Table of Contents | Next |
by Ryan K. Stephens
There are many tiers of responsibility associated with the tasks of an Oracle database administrator. Technology surpasses itself each year, especially in the world of Oracle and relational database systems. New tools are constantly made available to the DBA to aid in overall job effectiveness. However, no tool can altogether replace a knowledgeable DBA who can not only install, implement, and configure a database, but can monitor the database and its users, combine database concepts with business rules, and make the best decisions for the maintenance of the database to keep it in tune with itself and other systems belonging to the enterprise.
One of the most robust tools for this purpose is Oracle Enterprise Manager by Oracle Corporation. This tool was designed to assist a database administrator, as would a right-hand man who oversees database activity and offers recommendations to the database administrator to improve database performance. Many of the features in Enterprise Manager can be customized according to each database, helping the DBA to monitor and manage multiple databases with ease. Manual intervention still has its place for many tasks, but the automation of most database systems has drastically increased with client/server technology.
This chapter discusses the tasks common to a DBA with respect to routine maintenance and scheduling for an existing Oracle database. Just like an automobile, a database must be monitored for deficiencies. Some individuals stay on top of vehicle maintenance and tune-ups more than others. Consequently, some vehicles are more reliable and perform much better than others. The same concept holds true with a database. If the DBA does not closely monitor the database and maintain it regularly, its performance will gradually begin to falter until it’s no longer acceptable to the end user, resulting in the inability to support an enterprise’s application(s).
Just as important as monitoring and regular maintenance is auditing. Auditing is another way of monitoring the database, in that the DBA can easily measure events that occur in it. After you have set up processes to audit the database’s activity and monitor its elements, you can use job queues to schedule jobs at the database level. In other words, much of the monitoring and auditing process can be automated. This chapter mainly focuses on issues involved in regular database maintenance, database auditing, and the scheduling of jobs at the database level.
Examples will be shown specifically on auditing and scheduling jobs. Also included are a number of common monitoring scripts that can be used in the management of almost any Oracle database.
Before discussing the intricacies of database monitoring and maintenance, we must discuss the types of processing that occur in the database, including:
We will also discuss some data dictionary views that may be used to retrieve pertinent processing information. These views are used to monitor processing and provide measurements that allow the DBA to derive intelligent conclusions.
User processing is the primary justification for any database. Therefore, the end user must always be taken into consideration when you’re scheduling database activities. When we refer to user processing, we are basically talking about the user’s ability to access the required data to perform his or her job effectively and in a timely manner.
Within user processing, there are two main levels:
Query Processing
Query processing is of great importance, particularly in a data warehouse or decision support environment. Users must be able to extract the desired data in a timely manner, and without having to contend for system resources in the process. Care should be taken that any major processes that will tax the system aren’t scheduled to run during peak hours of query processing (or transactional processing, for that matter). During periods of intense query processing, the DBA should monitor the database to ensure that enough resources have been allocated, such as memory for shared SQL and sort operations. Also, the temporary tablespace should be adequate to handle the overflow of data used by sort operations that cannot be accommodated within allocated memory.
Transactional Processing
Transactional processing is the activity engaged in by end users in the population and manipulation of data in the database for a particular application. Most of these transactional commands involve the INSERT, UPDATE, and DELETE statements, although they’re often transparent to the user. Anymore, users need have very little, if any, knowledge of the underlying database system to manipulate the data within it. It is the DBA’s responsibility to periodically monitor the database during peak transactional activity, searching for bottlenecks and resource contention. One of the primary concerns with transactional activity is the management of the rollback segments. The database should be able to handle all concurrent transactions effectively. If the database is not monitored during transactional activity and adjusted appropriately, company productivity will inevitably suffer.
Later in this chapter, we’ll further discuss the process of monitoring user sessions. To monitor user sessions at the database level, the V$SESSION data dictionary view may be queried. User sessions can sometimes be monitored at the operating system level, to a limited degree. Many other avenues of approach can be taken regarding individual user sessions, space availability, and rollback segment contention.
| Previous | Table of Contents | Next |