13.1. Monitoring Tablespace Storage
Oracle 10g manages the disk space in two ways: reactively and proactively. Through database alerts, you are notified of tablespace disk space usage at two different levels: warning level and critical level. By default, the warning level is 85 percent, and the critical level is 97 percent. While these levels are by definition reactive, they can arguably be considered proactive in that you will have an opportunity to increase the amount of space in the tablespace before it runs out of space.
In a truly proactive manner, Oracle 10g collects statistics on space usage in the Automatic Workload Repository (AWR) at 30-minute intervals to assist you with tablespace and segment growth trend analysis and capacity planning. The AWR collects vital statistics and workload information, including CPU usage, user sessions, I/O usage, and many other metrics at 30-minute intervals and stores them in the SYSAUX tablespace for later analysis.
In the following sections, we will go into some of the details of how Oracle monitors tablespace usage. In addition, we will show you how you can view and modify the alert thresholds—both for an individual tablespace and for the database default—via the EM Database Control interface as well as via the PL/SQL package DBMS_SERVER_ALERT.
13.1.1. Space Usage Monitoring
If a tablespace does not have specific percentage thresholds defined, the database defaults of 85 percent for the warning level and 97 percent for the critical level apply. You can also change these default thresholds, as you will see in the next couple of sections.
The background process MMON checks for tablespace space problems every 10 minutes; alerts are triggered both when a threshold is exceeded and once again when the space usage for a tablespace falls back below the threshold. For example, assume that the default thresholds of 85 percent and 97 percent are in effect. Within a five-minute period, the USERS tablespace reaches 86 percent full, and MMON generates an alert. Fifteen minutes later, the USERS tablespace reaches 99 percent full, and MMON signals a second alert, this time a critical alert. You allocate a new datafile to the USERS tablespace to bring the overall space usage to 92 percent. The next time MMON checks for space problems in the USERS tablespace, the space usage has fallen back below the 97 percent threshold, and a third alert is sent to denote that the critical alert has been cleared. Note that the threshold is dynamically recalculated when storage is added to a tablespace that is under space pressure.
NOTE
For Oracle databases that have been upgraded from a previous version to Oracle 10g, all tablespace alerts are off by default.
Alerts are not necessary under a few conditions. For example, tablespaces that are read-only or are offline do not need thresholds defined, because their contents will not increase or decrease while they are read-only or offline.
Some tablespaces are defined as autoextensible. This presents a challenge to tablespace threshold monitoring, because even though the space usage of the datafile at a particular point in time may be at a warning or critical level, the datafile will automatically autoextend when it runs out of space. To avoid generating false alerts, thresholds on these tablespaces are computed in one of two ways: based on the maximum size specified when the tablespace was created or based on the maximum operating system file size, whichever is smaller.
Dictionary-managed tablespaces do not support server-generated alerts, which is yet another good reason to convert tablespaces from a previous version of Oracle to locally managed and to create all new tablespaces as locally managed.
13.1.2. Editing Thresholds with Enterprise Manager Database Control
You can edit space usage thresholds for tablespaces in one of two ways via EM Database Control: one from an overall threshold perspective and the other from an individual tablespace perspective.
To access the thresholds from a database-wide point of view, click the Manage Metrics link at the bottom of the EM Database Control database Home tab, and you'll see all possible database alerts listed as shown in Figure 13.1.
Clicking the Edit Thresholds button brings up the Edit Thresholds screen, where you can change one or more of these thresholds, as you can see in Figure 13.2.
Figure 13.1. All database thresholds
Figure 13.2. Editing thresholds
As the tip on this screen indicates, some metrics allow different thresholds for different objects of the same type such as tablespaces. For instance, if you select the Tablespace Space Used (%) metric (see Figure 13.3) and then click the Specify Multiple Thresholds button on the Edit Thresholds screen, you arrive at the Specify Multiple Thresholds: Tablespace Space Used (%) screen, shown in Figure 13.4.
Figure 13.3. Selecting the Tablespace Space Used (%) metric
Figure 13.4. Altering specific tablespace thresholds
Because the USERS tablespace tends to grow quickly, notice in Figure 13.4 that you set the thresholds for the tablespace at 60 percent and 85 percent, a bit lower than the defaults, so that you will have more time to allocate the space for the USERS tablespace when the alert is generated. Also, note that this screen has a place for a response action: It can range from a script containing a SQL command to automatically freeing up the space in the tablespace or adding a new datafile to the tablespace.
You can also edit the thresholds for a tablespace by clicking the Tablespaces link on the Administration tab of the EM Database Control Database Administration page. Clicking the link for the USERS tablespace, you see the general characteristics of the tablespace as shown in Figure 13.5.
Clicking the Thresholds link brings you to the Edit Tablespace: USERS screen (see Figure 13.6). Here, you can see the current space usage for the USERS tablespace and change the thresholds for the warning and critical levels. As with the previous example, the thresholds for the USERS tablespace were changed to 60 percent and 85 percent.
On this same screen, you have the option to change the database-wide defaults by clicking the Modify Database Defaults button, which opens the Modify Database Defaults screen (see Figure 13.7). Using this screen, you can edit the database's default thresholds or disable them completely.
Figure 13.5. Tablespace general characteristics
Figure 13.6. Editing tablespace thresholds
Referring to the Edit Tablespace: USERS screen (shown earlier in Figure 13.6), you want to apply your changes for the USERS tablespace thresholds. But before you do, you want to look at the SQL commands that will be executed by clicking the Show SQL button. As with most EM Database Control screens, you can brush up on the command-line syntax while enjoying the ease of use of a graphical user interface (GUI). Figure 13.8 shows the command that will be run when you click the Apply button.
Referring back to Figure 13.6, note that the USERS tablespace is already at 58.75 percent full. Let's see what happens when you add a few more segments to the USERS tablespace:
SQL> create table oe.customers_archive 2 tablespace users 3 as select * from oe.customers; Table created.
The thresholds screen for the USERS tablespace in Figure 13.9 shows that you have not only exceeded the warning level but also the critical level.
Within 10 minutes, the MMON process will notify you of the critical tablespace problem in one of three ways: via the EM Database Control Home tab, via an e-mail message sent to the e-mail address configured when the database was created, or using the script in the Response Action column, if one was specified, shown in Figure 13.4 when the tablespace thresholds were modified.
Figure 13.7. Editing database default thresholds
Figure 13.8. Showing SQL for tablespace thresholds
Figure 13.9. Viewing current tablespace usage
13.1.3. Using DBMS_SERVER_ALERT
The previous section demonstrated how you could view the actual SQL commands that EM Database Control uses to add, change, or modify space usage thresholds. The following sections will go into more detail about how the DBMS_SERVER_ALERT package works. The DBMS_SERVER_ALERT package contains a number of procedures that allow you to set, view, and modify a variety of alert conditions.
For managing space usage alerts, as with every other type of alert, the three procedures available are as follows:
13.1.3.1. SET_THRESHOLD
As the name implies, the SET_THRESHOLD procedure sets the threshold for a particular alert type. Table 13.1 describes the parameters for SET_THRESHOLD.
For monitoring tablespace space usage, only one metric object type is available: the TABLESPACE_PCT_FULL metric. The operators for exceeding a threshold are either OPERATOR_GE or OPERATOR_GT. OPERATOR_GE indicates that the current value of the metric is compared to the WARNING_VALUE or CRITICAL_VALUE using the greater than or equal to operator (=); similarly, OPERATOR_GT indicates that the current value of the metric is compared to WARNING_VALUE or CRITICAL_VALUE using the greater than operator (>). The object type is always OBJECT_TYPE_TABLESPACE.
Because the USERS2 tablespace in the database is an infrequently used tablespace and not part of the production environment, you want to raise the alert thresholds for space usage to reduce the total number of alerts you receive every day. In the following example, you are changing the warning threshold to 90 percent and the critical threshold to 99 percent. These thresholds will be compared to the percentage of space used in the USERS2 tablespace every minute, causing an alert the first time the threshold is exceeded for the tablespace USERS2:
SQL> execute 2 dbms_server_alert.set_threshold( 3 dbms_server_alert.tablespace_pct_full, 4 dbms_server_alert.operator_ge, 90, 5 dbms_server_alert.operator_ge, 99, 6 1, 1, null, 7 dbms_server_alert.object_type_tablespace,'USERS2'), PL/SQL procedure successfully completed.
The new threshold goes into effect immediately. The next time MMON runs, an alert will be generated if the space usage on the USERS2 tablespace is at 90 percent or higher.
13.1.3.2. GET_THRESHOLD
Similar to SET_THRESHOLD, the GET_THRESHOLD procedure retrieves the values of a defined alert. Table 13.2 describes the parameters for GET_THRESHOLD.
Not surprisingly, the parameters for GET_THRESHOLD are identical to SET_THRESHOLD, except that the values of WARNING_OPERATOR through CONSECUTIVE_OCCURRENCES are OUT parameters instead of IN. In the following example, you will retrieve the threshold values you set for the USERS tablespace earlier in this chapter:
SQL> begin 2 dbms_server_alert.get_threshold( 3 dbms_server_alert.tablespace_pct_full, 4 :warn_oper, :warn_value, :crit_oper, :crit_value,
5 :obs_per, :cons_oc, null, 6 dbms_server_alert.object_type_tablespace,'USERS'), 7 end; 8 / PL/SQL procedure successfully completed. SQL> print warn_value WARN_VALUE -------------------------------- 60 SQL> print crit_value CRIT_VALUE -------------------------------- 85
Setting the last parameter to NULL instead of to the tablespace name will retrieve the database-wide default values instead of the values for a particular tablespace.
13.1.3.3. EXPAND_MESSAGE
The EXPAND_MESSAGE procedure is very straightforward, translating a numeric message number to a text format. Table 13.3 describes the parameters for EXPAND_MESSAGE.
If additional values are returned along with the alert code number, they are specified using ARGUMENT_1 through ARGUMENT_5 and are substituted into the alert message as needed. For server alert message number 6, you can retrieve the text of the message as follows:
SQL> select dbms_server_alert.expand_message 2 (null,6,null,null,null,null,null) alert_msg 3 from dual; ALERT_MSG ----------------------------------- Read and write contention on database blocks was consuming significant database time. However, no single object was the predominant cause for this contention.
Rarely will you have to call EXPAND_MESSAGE; it is primarily used for third-party applications that read alert messages from the alert queue. The EM Database Control automatically retrieves the text of all alert messages.