| Previous | Table of Contents | Next |
There are countless scripts that may be created to perform regular database monitoring. In addition to scripts that the DBA can create and customize, Oracle provides utilities and tools that automate the overall tasks of database monitoring. Some of these utilities include
The UTLBSTAT.SQL and UTLESTAT.SQL Utilities
The UTLBSTAT and UTLESTAT utilities are used to gather overall database statistics, which are used mainly to tune the performance of a database. These scripts are provided by Oracle Corporation as part of the software, and are typically stored under $ORACLE_HOME/rdbms/admin.
UTLBSTAT.SQL is run first, to begin the process of gathering statistics. This script must be run while you’re connected to a DBA account. Before running this script, be sure to modify the TABLESPACE clause for the tables being created by the script. Those tables are used to store performance statistics from the database as of the time UTLBSTAT.SQL is run.
Upon execution of UTLBSTAT.SQL, the following dynamic performance views are queried to initially populate the statistic tables created:
After a predetermined amount of time (normally after the end of a large process), the UTLESTAT.SQL script is run to end the process of gathering statistics, generating an output file called report.txt. The output of this script offers recommendations for adjusting database parameters to improve performance. These scripts are typically run from Server Manager.
UTLLOCKT.SQL and CATBLOCK.SQL
The UTLLOCKT.SQL script displays locks in the database and sessions that are waiting for locks to be released. Before you run UTLLOCKT.SQL, CATBLOCK.SQL must first be run (typically from SQL*Plus or Server Manager). CATBLOCK.SQL builds the “lock views” used by UTLLOCKT.SQL.
NOTE:
In addition to the tools that are provided by Oracle Corporation, many third-party products have been made available to the DBA that are tailored specifically to an Oracle database. A couple of these tools are Patrol by BMC Software and SQLTrace by Platinum Technology. Be sure to check all of your options to optimize the task of monitoring database activity.
In this chapter, we have covered routine tasks that need to be performed by the DBA. Although all these monitoring and auditing tasks can be accomplished manually, the fact that Oracle provides a way to automate them should be taken into consideration. Similar to the way a crontab job is scheduled on a UNIX system, Oracle has the capability of scheduling jobs in the database. These jobs can include practically any designated task, such as monitoring scripts, auditing, creating general maintenance scripts, and gathering database statistics. Oracle jobs are based on PL/SQL, typically some stored procedure in the database. The concepts from the previous scripts in this chapter may be used to set up Oracle jobs. This section discusses job scheduling in the Oracle environment.
Database jobs are scheduled in what is called a job queue, which designates which jobs have been scheduled, where to find the jobs, when to execute them, how often to execute them, and so on. Although these queues are directly managed by Oracle, Oracle provides the user with methods for communicating with these queues in such a way that jobs can be scheduled, modified, and removed from the queues.
Oracle job queues can be compared to those on a UNIX operating system. When a crontab job is set up, the jobs to be executed are placed in a queue. As the user setting up the job, you supply the time to execute it, the frequency, and the name (which is often a shell script or an SQL script file). Crontab jobs are executed according to the specified frequency until the job is removed from the queue. Oracle job queues work in a similar fashion.
There are two levels of job queue management:
The first task is accomplished by the DBA. SNP processes must first be initialized before jobs can be scheduled within the database. The second task is accomplished by any user with the appropriate privileges. Many users other than the DBA need to schedule jobs to automate everyday tasks.
How Are Queued Jobs Executed?
Job queues are executed by SNP background processes, which are initialized when the applicable database instance is started. The following section describes the initialization parameters involved in defining the characteristics of these SNP background processes. These processes check for jobs that need to be executed in the database and execute those jobs. The more simultaneous jobs being executed, the more SNP background processes that may need to be initialized.
NOTE:
The maximum number of SNP background processes allowed by Oracle is 36. When a background process fails, the database instance as a whole is not affected. Oracle simply restarts failed SNP background processes.
Database Parameters
A database may have multiple job queues. These job queues are driven by SNP background processes, which are initialized by the DBA through the following parameters in the database initialization parameter file:
| JOB_QUEUE_PROCESSES | Identifies the number of SNP background processes to be used by the database instance. |
| JOB_QUEUE_INTERVAL | Identifies the wake-up interval (in seconds) for SNP background processes, at which time the processes check for jobs to be executed. |
These parameters do not take effect until the database is shut down and restarted.
| Previous | Table of Contents | Next |