What's in a Name?: Improved Tablespace Management
Tablespace management gets a boost thanks to a sparser SYSTEM, support for defining a default tablespace for users, new SYSAUX, and even renaming
How many times you have pulled your hair out in frustration over users creating segments other than SYS and SYSTEM in the SYSTEM tablespace?
Prior to Oracle9i Database, if the DEFAULT TABLESPACE was not specified when the user was created, it would default to the SYSTEM tablespace. If the user did not specify a tablespace explicitly while creating a segment, it was created in SYSTEM—provided the user had quota there, either explicitly granted or through the system privilege UNLIMITED TABLESPACE. Oracle9i alleviated this problem by allowing the DBA to specify a default, temporary tablespace for all users created without an explicit temporary tablespace clause.
In Oracle Database 10g, you can similarly specify a default tablespace for users. During database creation, the CREATE DATABASE command can contain the clause DEFAULT TABLESPACE . After creation, you can make a tablespace the default by issuing
ALTER DATABASE DEFAULT TABLESPACE ;
All users created without the DEFAULT TABLESPACE clause will have as their default. You can change the default tablespace at any time through this ALTER command, which allows you to specify different tablespaces as default at different points.
Important note: the default tablespaces of all users with the old tablespace are changed to , even if something else was specified explicitly for some users. For instance, assume the default tablespaces for users USER1 and USER2 are TS1 and TS2 respectively, specified explicitly during user creation. The current default tablespace for the database is TS2, but later, the database default tablespace is changed to TS1. Even though USER2's default tablespace was explicitly specified as TS2, it will be changed to TS1. Beware this side effect!
If the default tablespace is not specified during the database creation, it defaults to SYSTEM. But how do you know which tablespace is default for the existing database? Issue the following query:
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
The DATABASE_PROPERTIES view shows some very important information, in addition to the default tablespace—such as the default temporary tablespace, global database name, time zone, and much more.
Default Tablespace for Nonessential Schemas
Several schemas such as the intelligent agent user DBSNMP, data mining user ODM are not directly related to user operations, but are important to database integrity nonetheless. Some of these schemas used to have SYSTEM as their default tablespace — another reason for the proliferation of objects inside that special tablespace.
Oracle Database 10g introduces a new tablespace called SYSAUX that holds the objects of these schemas. This tablespace is created automatically during database creation and is locally managed. The only change allowed is the name of the datafile.
This approach supports recovery when the corruption of SYSTEM requires a full database recovery. Objects in SYSAUX can be recovered as any normal user object while the database itself remains operational.
But what if you want to move some of these schemas in SYSAUX to a different tablespace? Take, for instance, the objects used by LogMiner, which often grow in size to eventually fill up the tablespace. For manageability reasons, you may consider moving them to their own tablespace. But what is the best way to do that?
As a DBA it's important for you to know the correct procedure for moving these special objects. Fortunately, Oracle Database 10g provides a new view to take the guesswork out. This view, V$SYSAUX_OCCUPANTS, lists the names of the schemas in the tablespace SYSAUX, their description, the space currently used, and how to move them. (See Table 1.)
Note how LogMiner is shown as clearly occupying 7,488 KB. It's owned by the schema SYSTEM, and to move the objects, you would execute the packaged procedure SYS.DBMS_LOGMNR_D.SET_TABLESPACE. For STATSPACK objects, however, the view recommends the export/import approach, and for Streams, there is no move procedure—thus, you can't easily move them from the SYSAUX tablespace. The column MOVE_PROCEDURE shows correct moving procedures for almost all tools resident in the SYSAUX by default. The move procedures can also be used in the reverse direction to get objects back into the SYSAUX tablespace.
Renaming a Tablespace
It is common in data warehouse environments, typically for data mart architectures, to transport tablespaces between databases. But the source and target databases must not have tablespaces with the same names. If there are two tablespaces with the same name, the segments in the target tablespace must be moved to a different one and the tablespace recreated—a task easier said than done.
Oracle Database 10g offers a convenient solution: you can simply rename an existing tablespace (SYSTEM and SYSAUX excepted), whether permanent or temporary, using the following command:
ALTER TABLESPACE RENAME TO ;
This capability can also come in handy during the archiving process. Assume you have a range-partitioned table for recording sales history, and a partition for each month lives in a tablespace named after the month—for example, the partition for January is named JAN and resides in a tablespace named JAN. You have a 12-month retention policy. In January 2004, you will be able to archive the January 2003 data. A rough course of action will be something similar to the following:
Create a stand-alone table JAN03 from the partition JAN using ALTER TABLE EXCHANGE PARTITION.
Rename the tablespace to JAN03.
Create a transportable tablespace set for the tablespace JAN03
Rename tablespace JAN03 back to JAN.
Exchange the empty partition back to the table.
Steps 1, 2, 4 and 5 are straightforward and do not overly consume resources such as redo and undo space. Step 3 is merely copying the file and exporting only the data dictionary information for JAN03, which is also a very easy process. Should you need to reinstate the partition archived earlier, the procedure is as simple as reversing the same process.
Oracle Database 10g is quite intelligent in the way it handles these renames. If you rename the tablespace used as the UNDO or the default temporary one, it could cause confusion. But the database automatically adjusts the necessary records to reflect the change. For instance, changing the name of the default tablespace from USERS to USER_DATA automatically changes the view DATABASE_PROPERTIES. Before the change, the query:
select property_value from database_properties
where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
returns USERS. After the following statement is run
alter tablespace users rename to user_data;
The above query returns USER_DATA, as all the references to USERS have been changed to USER_DATA.
Changing the default temporary tablespace does the same thing. Even changing the UNDO tablespace name triggers the change in the SPFILE as shown below.
SQL> select value from v$spparameter where name = 'undo_tablespace';
VALUE
--------
UNDOTBS1
SQL> alter tablespace undotbs1 rename to undotbs;
Tablespace altered.
SQL> select value from v$spparameter where name = 'undo_tablespace';
VALUE
--------
UNDOTBS
Conclusion
Object handling has steadily improved over the course of several recent Oracle versions. Oracle8i introduced the table move from one tablespace to another, Oracle 9i Database R2 introduced the column renaming, and now—the last frontier—the renaming of a tablespace itself is possible. These enhancements significantly ease DBA tasks, especially in data warehouse or mart environments.
Export/Import on Steroids: Oracle Data Pump
Data movemement gets a big lift with Oracle Database 10g utilities
Until now, the export/import toolset has been the utility of choice for transferring data across multiple platforms with minimal effort, despite common complaints about its lack of speed. Import merely reads each record from the export dump file and inserts it into the target table using the usual INSERT INTO command, so it's no surprise that import can be a slow process.
Enter Oracle Data Pump, the newer and faster sibling of the export/import toolkit in Oracle Database 10g, designed to speed up the process many times over.
Data Pump reflects a complete overhaul of the export/import process. Instead of using the usual SQL commands, it provides proprietary APIs to load and unload data significantly faster. In my tests, I have seen performance increases of 10-15 times over export in direct mode and 5-times-over performance increases in the import process. In addition, unlike with the export utility, it is possible to extract only specific types of objects such as procedures.
Data Pump Export
The new utility is known as expdp to differentiate it from exp, the original export. In this example, we will use Data Pump to export a large table, CASES, about 3GB in size. Data Pump uses file manipulation on the server side to create and read files; hence, directories are used as locations. In this case, we are going to use the filesystem /u02/dpdata1 to hold the dump files.
create directory dpdata1 as '/u02/dpdata1';
grant read, write on directory dpdata1 to ananda;
Next, we will export the data:
expdp ananda/abc123 tables=CASES directory=DPDATA1
dumpfile=expCASES.dmp job_name=CASES_EXPORT
Let's analyze various parts of this command. The userid/password combination, tables, and dumpfile parameters are self-explanatory. Unlike the original export, the file is created on the server (not the client). The location is specified by the directory parameter value DPDATA1, which points to /u02/dpdata1 as created earlier. The process also creates a log file, again on the server, in the location specified by the directory parameter. By default, a directory named DPUMP_DIR is used by this process; so it can be created instead of the DPDATA1.
Note the parameter job_name above, a special one not found in the original export. All Data Pump work is done though jobs. Data Pump jobs, unlike DBMS jobs, are merely server processes that process the data on behalf of the main process. The main process, known as a master control process, coordinates this effort via Advanced Queuing; it does so through a special table created at runtime known as a master table. In our example, if you check the schema of the user ANANDA while expdp is running you will notice the existence of a table CASES_EXPORT, corresponding to the parameter job_name. This table is dropped when expdp finishes.
Export Monitoring
While Data Pump Export (DPE) is running, press Control-C; it will stop the display of the messages on the screen, but not the export process itself. Instead, it will display the DPE prompt as shown below. The process is now said to be in "interactive" mode:
Export>
This approach allows several commands to be entered on that DPE job. To find a summary, use the STATUS command at the prompt:
Export> status
Job: CASES_EXPORT
Operation: EXPORT
Mode: TABLE
State: EXECUTING
Degree: 1
Job Error Count: 0
Dump file: /u02/dpdata1/expCASES.dmp
bytes written = 2048
Worker 1 Status:
State: EXECUTING
Object Schema: DWOWNER
Object Name: CASES
Object Type: TABLE_EXPORT/TBL_TABLE_DATA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1
Completed Rows: 4687818
Remember, this is merely the status display. The export is working in the background. To continue to see the messages on the screen, use the command CONTINUE_CLIENT from the Export> prompt.
Parallel Operation
You can accelerate jobs significantly using more than one thread for the export, through the PARALLEL parameter. Each thread creates a separate dumpfile, so the parameter dumpfile should have as many entries as the degree of parallelism. Instead of entering each one explicitly, you can specify wildcard characters as filenames such as:
expdp ananda/abc123 tables=CASES directory=DPDATA1
dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export
Note how the dumpfile parameter has a wild card %U, which indicates the files will be created as needed and the format will be expCASES_nn.dmp, where nn starts at 01 and goes up as needed.
In parallel mode, the status screen will show four worker processes. (In default mode, only one process will be visible.) All worker processes extract data simultaneously and show their progress on the status screen.
It's important to separate the I/O channels for access to the database files and the dumpfile directory filesystems. Otherwise, the overhead associated with maintaining the Data Pump jobs may outweigh the benefits of parallel threads and hence degrade performance. Parallelism will be in effect only if the number of tables is higher than the parallel value and the tables are big.
Database Monitoring
You can get more information on the Data Pump jobs running from the database views, too. The main view to monitor the jobs is DBA_DATAPUMP_JOBS, which tells you how many worker processes (column DEGREE) are working on the job. The other view that is important is DBA_DATAPUMP_SESSIONS, which when joined with the previous view and V$SESSION gives the SID of the session of the main foreground process.
select sid, serial#
from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;
This instruction shows the session of the foreground process. More useful information is obtained from the alert log. When the process starts up, the MCP and the worker processes are shown in the alert log as follows:
kupprdp: master process DM00 started with pid=23, OS id=20530 to execute -
SYS.KUPM$MCP.MAIN('CASES_EXPORT', 'ANANDA');
kupprdp: worker process DW01 started with worker id=1, pid=24, OS id=20532 to execute -
SYS.KUPW$WORKER.MAIN('CASES_EXPORT', 'ANANDA');
kupprdp: worker process DW03 started with worker id=2, pid=25, OS id=20534 to execute -
SYS.KUPW$WORKER.MAIN('CASES_EXPORT', 'ANANDA');
It shows the PID of the sessions started for the data pump operation. You can find the actual SIDs using this query:
select sid, program from v$session where paddr in
(select addr from v$process where pid in (23,24,25));
The PROGRAM column will show the process DM (for master process) or DW (the worker proceses), corresponding to the names in the alert log file. If a parallel query is used by a worker process, say for SID 23, you can see it in the view V$PX_SESSION to find it out. It will show you all the parallel query sessions running from the worker process represented by SID 23:
select sid from v$px_session where qcsid = 23;
Additional useful information can be obtained from the view V$SESSION_LONGOPS to predict the time it will take to complete the job.
select sid, serial#, sofar, totalwork
from v$session_longops
where opname = 'CASES_EXPORT'
and sofar != totalwork;
The column totalwork shows the total amount of work, of which the sofar amount has been done up until now--which you can then use to estimate how much longer it will take.
Data Pump Import
Data import performance is where Data Pump really stands out, however. To import the data exported earlier, we will use
impdp ananda/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import
The default behavior of the import process is to create the table and all associated objects, and to produce an error when the table exists. Should you want to append the data to the existing table, you could use TABLE_EXISTS_ACTION=APPEND in the above command line.
As with Data Pump Export, pressing Control-C on the process brings up the interactive mode of Date Pump Import (DPI); again, the prompt is Import>.
Operating on Specific Objects
Ever had a need to export only certain procedures from one user to be recreated in a different database or user? Unlike the traditional export utility, Data Pump allows you to export only a particular type of object. For instance, the following command lets you export only procedures, and nothing else--no tables, views, or even functions:
expdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp include=PROCEDURE
To export only a few specific objects--say, function FUNC1 and procedure PROC1--you could use
expdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp
include=PROCEDURE:\"=\'PROC1\'\",FUNCTION:\"=\'FUNC1\'\"
This dumpfile serves as a backup of the sources. You can even use it to create DDL scripts to be used later. A special parameter called SQLFILE allows the creation of the DDL script file.
impdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql
This instruction creates a file named procs.sql in the directory specified by DPDATA1, containing the scripts of the objects inside the export dumpfile. This approach helps you create the sources quickly in another schema.
Using the parameter INCLUDE allows you to define objects to be included or excluded from the dumpfile. You can use the clause INCLUDE=TABLE:"LIKE 'TAB%'" to export only those tables whose name start with TAB. Similarly, you could use the construct INCLUDE=TABLE:"NOT LIKE 'TAB%'" to exclude all tables starting with TAB. Alternatively you can use the EXCLUDE parameter to exclude specific objects.
Data Pump can also be used to transport tablespaces using external tables; it's sufficiently powerful to redefine parallelism on the fly, attach more tables to an existing process, and so on (which are beyond the scope of this article; see Oracle Database Utilities 10g Release 1 10.1 for more details). The following command generates the list of all available parameters in the Data Pump export utility:
expdp help=y
Similarly, impdp help=y will show all the parameters in DPI.
While Data Pump jobs are running, you can pause them by issuing STOP_JOB on the DPE or DPI prompts and then restart them with START_JOB. This functionality comes in handy when you run out of space and want to make corrections before continuing.
Flashback Table
Reinstating an accidentally dropped table is effortless using the Flashback Table feature in Oracle Database 10g
Here's a scenario that happens more often than it should: a user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible. (In some cases, this unfortunate user may even have been you, the DBA!)
Oracle9i Database introduced the concept of a Flashback Query option to retrieve data from a point in time in the past, but it can't flash back DDL operations such as dropping a table. The only recourse is to use tablespace point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.
Enter the Flashback Table feature in Oracle Database 10g, which makes the revival of a dropped table as easy as the execution of a few statements. Let's see how this feature works.
Drop That Table!
First, let's see the tables in the present schema.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------ ------- ----------
RECYCLETEST TABLE
Now, we accidentally drop the table:
SQL> drop table recycletest;
Table dropped.
Let's check the status of the table now.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE
The table RECYCLETEST is gone but note the presence of the new table BIN$04LhcpndanfgMAAAAAANPw==$0. Here's what happened: The dropped table RECYCLETEST, instead of completely disappearing, was renamed to a system-defined name. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$04LhcpndanfgMAAAAAANPw==$0, preserving the complete object structure of the dropped table.
The table and its associated objects are placed in a logical container known as the "recycle bin," which is similar to the one in your PC. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The recycle bin is merely a logical structure that catalogs the dropped objects. Use the following command from the SQL*Plus prompt to see its content (you'll need SQL*Plus 10.1 to do this):
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31
This shows the original name of the table, RECYCLETEST, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
FLASHBACK COMPLETE.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RECYCLETEST TABLE
Voila! The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.
Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:
PURGE RECYCLEBIN;
But what if you want to drop the table completely, without needing a flashback feature? In that case, you can drop it permanently using:
DROP TABLE RECYCLETEST PURGE;
This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been pre-10g.
Managing the Recycle Bin
If the tables are not really dropped in this process--therefore not releasing the tablespace--what happens when the dropped objects take up all of that space?
The answer is simple: that situation does not even arise. When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed.
Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.
In addition, there are several ways you can manually control the recycle bin. If you want to purge the specific table named TEST from the recycle bin after its drop, you could issue
PURGE TABLE TEST;
or using its recycle bin name:
PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";
This command will remove table TEST and all dependent objects such as indexes, constraints, and so on from the recycle bin, saving some space. If, however, you want to permanently drop an index from the recycle bin, you can do so using:
purge index in_test1_01;
which will remove the index only, leaving the copy of the table in the recycle bin.
Sometimes it might be useful to purge at a higher level. For instance, you may want to purge all the objects in recycle bin in a tablespace USERS. You would issue:
PURGE TABLESPACE USERS;
You may want to purge only the recycle bin for a particular user in that tablespace. This approach could come handy in data warehouse-type environments where users create and drop many transient tables. You could modify the command above to limit the purge to a specific user only:
PURGE TABLESPACE USERS USER SCOTT;
A user such as SCOTT would clear his own recycle bin with
PURGE RECYCLEBIN;
You as a DBA can purge all the objects in any tablespace using
PURGE DBA_RECYCLEBIN;
As you can see, the recycle bin can be managed in a variety of different ways to meet your specific needs.
Table Versions and Flashback
Oftentimes the user might create and drop the same table several times, as in:
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (1);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (2);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (3);
COMMIT;
DROP TABLE TEST;
At this point, if you were to flash-back the table TEST, what would the value of the column COL1 be? Conventional thinking might suggest that the first version of the table is retrieved from the recycle bin, where the value of column COL1 is 1. Actually, the third version of the table is retrieved, not the first. So the column COL1 will have the value 3, not 1.
At this time you can also retrieve the other versions of the dropped table. However, the existence of a table TEST will not let that happen. You have two choices:
Use the rename option:
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;
which will reinstate the first version of the table to TEST1 and the second versions to TEST2. The values of the column COL1 in TEST1 and TEST2 will be 1 and 2 respectively. Or,
Use the specific recycle-bin names of the table to restore. To do that, first identify the table's recycle bin names and then issue:
FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST1;
That will restore the two versions of the dropped table.
Be Warned...
The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table.
The information is kept in the view named USER_RECYCLEBIN. Before flashing-back the table, use the following query to retrieve the old names.
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'RECYCLETEST')
AND ORIGINAL_NAME != 'RECYCLETEST';
OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpnianfgMAAAAAANPw==$0 IN_RT_01 INDEX
BIN$04LhcpnganfgMAAAAAANPw==$0 TR_RT TRIGGER
After the table is flashed-back, the indexes and triggers on the table RECYCLETEST will be named as shown in the OBJECT_NAME column. From the above query, you can use the original name to rename the objects as follows:
ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;
One notable exception is the bitmap indexes. When they are dropped, they are not placed in the recycle bin--hence they are not retrievable. The constraint names are also not retrievable from the view. They have to be renamed from other sources.
Other Uses of Flashback Tables
Flashback Drop Table is not limited to reversing the drop of the table. Similar to flashback queries, you can also use it to reinstate the table to a different point in time, replacing the entire table with its "past" version. For example, the following statement reinstates the table to a System Change Number (SCN) 2202666520.
FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;
This feature uses Oracle Data Pump technology to create a different table, uses flashback to populate the table with the versions of the data at that SCN, and then replaces the original table with the new table. To find out how far you can flashback the table, you could use the versioning feature of Oracle Database 10g. (See the Week 1 installment of this series for more details.) It is also possible to specify a timestamp instead of SCN in the flashback clause.
Automatic Workload Repository
Learn to use the new feature that collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information
When you have a database performance problem, what is the first thing you do to address it? One common approach is to see if a pattern exists: Answering questions such as "Is the same problem recurrent?", "Does it occur during a specific time period?", and "Is there a link between two problems?" will almost always lead to a better diagnosis.
As a DBA you probably have invested in a third-party or homegrown tool to collect elaborate statistics during database operation and derive performance metrics from them. In a crisis, you access those metrics for comparisons to the present. Replaying these past events can shed light on current problems, so continuously capturing relevant statistics becomes important for performance analysis.
For some time, Oracle's solution in this area has been its built-in tool, Statspack. While it can prove invaluable in certain cases, it often lacks the robustness required by performance troubleshooting exercises. Oracle Database 10g offers a significant improvement: the Automatic Workload Repository (AWR). The AWR installs along with the database and captures not only statistics, but the derived metrics as well.
A Quick Test Drive
AWR capability is best explained quickly by the report it produces from collected statistics and metrics, by running the script awrrpt.sql in the $ORACLE_HOME/rdbms/admin directory. This script, in its look and feel, resembles Statspack; it shows all the AWR snapshots available and asks for two specific ones as interval boundaries. It produces two types of output: text format, similar to that of the Statspack report but from the AWR repository, and the default HTML format, complete with hyperlinks to sections and subsections, providing quite a user-friendly report. Run the script and take a look at the report now to get an idea about capabilities of the AWR.
Implementation
Now let's explore how AWR is designed and structured. Basically, AWR is an Oracle built-in tool that collects performance related statistics and derives performance metrics from them to track a potential problem. Unlike Statspack, snapshots are collected automatically every hour by a new background process called MMON and its slave processes. To save space, the collected data is automatically purged after 7 days. Both the snapshot frequency and retention time can be modified by the user. To see the present settings, you could use:
select snap_interval, retention
from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
------------------- -------------------
+00000 01:00:00.0 +00007 00:00:00.0
This SQL shows that the snapshots are taken every hour and the collections are retained 7 seven days. To change the settings--say, for snapshot intervals of 20 minutes and a retention period of two days--you would issue the following. The parameters are specified in minutes.
begin
dbms_workload_repository.modify_snapshot_settings (
interval => 20,
retention => 2*24*60
);
end;
AWR uses several tables to store the collected statistics, all stored under the SYS schema in the new special tablespace named SYSAUX, and named in the format WRM$_* and WRH$_*. The former type stores metadata information such as the database being examined and the snapshots taken, and the latter type holds the actual collected statistics. (As you might have guessed, H stands for "historical" and M stands for "metadata.") There are several views with the prefix DBA_HIST_ built upon these tables, which can be used to write your own performance diagnosis tool. The names of the views directly relate to the table; for example, the view DBA_HIST_SYSMETRIC_SUMMARY is built upon the table WRH$_SYSMETRIC_SUMMARY.
The AWR history tables capture a lot more information than Statspack, including tablespace usage, filesystem usage, even operating system statistics. A complete list of these tables can be seen from the data dictionary through:
select view_name from user_views where view_name like 'DBA\_HIST\_%' escape '\';
The view DBA_HIST_METRIC_NAME defines the important metrics the AWR collects, the groups to which they belong, and the unit in which they are collected. For example, here is one record (in vertical format):
DBID : 4133493568
GROUP_ID : 2
GROUP_NAME : System Metrics Long Duration
METRIC_ID : 2075
METRIC_NAME : CPU Usage Per Sec
METRIC_UNIT : CentiSeconds Per Second
It shows that a metric "CPU Usage Per Sec" is measured in units of "CentiSeconds Per Second" and belongs to a metric group "System Metrics Long Duration." This record can be joined with other tables such as DBA_HIST_SYSMETRIC_SUMMARY to get the activity, as in:
select begin_time, intsize, num_interval, minval, maxval, average, standard_deviation sd
from dba_hist_sysmetric_summary where metric_id = 2075;
BEGIN INTSIZE NUM_INTERVAL MINVAL MAXVAL AVERAGE SD
----- ---------- ------------ ------- ------- -------- ----------
11:39 179916 30 0 33 3 9.81553548
11:09 180023 30 21 35 28 5.91543912
... and so on ...
Here we see how the CPU time was consumed in centi-seconds. The standard deviation adds to our analysis by helping ascertain whether the average figure reflects the actual workload. In the first records, the average is 3 centi-seconds in CPU per second elapsed, but the standard deviation is 9.81, meaning the average of 3 is not reflective of the workload. In the second example, the value 28, with a standard deviation of 5.9, is more representative. This type of information trends help understanding the effects of several environmental parameters on performance metrics.
Using the Statistics
So far we have seen what AWR collects; now let's see what it does with the data.
Most performance problems do not exist in isolation, but rather leave tell-tale signs that will lead to the eventual root cause of the problem. Let's use a typical tuning exercise: You notice that the system is slow and decide to look into the waits. Your examination reveals that the "buffer busy wait" is very high. What could be the problem? There are several possibilities: there could be a monotonically increasing index, a table so packed that a single block is asked to be loaded to memory very quickly, or some other factors. In any case, first you want identify the segment in question. If it's an index segment, you could decide to rebuild it; change it to a reverse key index; or convert it to a hash-partitioned index introduced in Oracle Database 10g. If it's a table, you could consider changing storage parameters to make it less dense or move it over to a tablespace with automatic segment space management.
Your plan of attack is generally methodical and usually based your knowledge of various events and your experience in dealing with them. Now imagine if the same thing were done by an engine - an engine that captures metrics and deduces possible plans based on pre-determined logic. Wouldn't your job be easier?
That engine, now available in Oracle Database 10g, is known as Automatic Database Diagnostic Monitor (ADDM). To arrive at a decision, ADDM uses the data collected by AWR. In the above discussion, ADDM can see that the buffer busy waits are occurring, pull the appropriate data to see the segments on which it occurs, evaluate its nature and composition, and finally offer solutions to the DBA. After each snapshot collection by AWR, the ADDM is invoked to examine the metrics and generate recommendations. So, in effect you have a full-time robotic DBA analyzing the data and generating recommendations proactively, freeing you to attend to more strategic issues.
To see the ADDM recommendations and the AWR repository data, use the new Enterprise Manager 10g console on the page named DB Home. To see the AWR reports, you can navigate to them from Administration, then Workload Repository, and then Snapshots. We'll examine ADDM in greater detail in a future installment.
You can also specify alerts to be generated based on certain conditions. These alerts, known as Server Generated Alerts, are pushed to an Advanced Queue, from where they can be consumed by any client listening to it. One such client is Enterprise Manager 10g, where the alerts are displayed prominently.
Time Model
When you have a performance problem, what comes to mind first to reduce the response time? Obviously, you want to eliminate (or reduce) the root cause of the factor that adds to the time. How do you know where the time was spent--not waiting, but actually doing the work?
Oracle Database 10g introduces time models for identifying the time spent in various places. The overall system time spent is recorded in the view V$SYS_TIME_MODEL. Here is the query and its output.
STAT_NAME VALUE
------------------------------------- --------------
DB time 58211645
DB CPU 54500000
background cpu time 254490000
sequence load elapsed time 0
parse time elapsed 1867816
hard parse elapsed time 1758922
sql execute elapsed time 57632352
connection management call elapsed time 288819
failed parse elapsed time 50794
hard parse (sharing criteria) elapsed time 220345
hard parse (bind mismatch) elapsed time 5040
PL/SQL execution elapsed time 197792
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 593992
Java execution elapsed time 0
bind/define call elapsed time 0
Note the statistic named DB Time, which represents the time spent in the database since the instance startup. Run the sample workload and select the statistic value from the view again. The difference should represent the time spent in the database for that workload. After another round of tuning, perform the same analysis and that difference will show the change in DB Time after the tuning, which can be compared to first change to examine the effect of the tuning exercise on the database time.
In addition to the database time, the V$SYS_TIME_MODEL view shows a whole lot of other statistics, such as time spent in different types of parsing and even PL/SQL compilation.
This view shows the overall system times as well; however, you may be interested in a more granular view: the session level times. The timing stats are captured at the session level as well, as shown in the view V$SESS_TIME_MODEL, where all the stats of the current connected sessions, both active and inactive, are visible. The additional column SID specifies the SID of the sessions for which the stats are shown.
In previous releases, this type of analysis was impossible to get and the user was forced to guess or derive from a variety of sources. In Oracle Database 10g, getting this information is a snap.
Active Session History
The view V$SESSION in Oracle Database 10g has been improved; the most valuable improvement of them all is the inclusion of wait events and their duration, eliminating the need to see the view V$SESSION_WAIT. However, since this view merely reflects the values in real time, some of the important information is lost when it is viewed later. For instance, if you select from this view to check if any session is waiting for any non-idle event, and if so, the event in question, you may not find anything because the wait must have been over by the time you select it.
Enter the new feature Active Session History (ASH), which, like AWR, stores the session performance statistics in a buffer for analysis later. However, unlike AWR, the storage is not persistent in a table but in memory, and is shown in the view V$ACTIVE_SESSION_HISTORY. The data is polled every second and only the active sessions are polled. As time progresses, the old entries are removed to accommodate new ones in a circular buffer and shown in the view. To find out how many sessions waited for some event, you would use
select session_id||','||session_serial# SID, n.name, wait_time, time_waited
from v$active_session_history a, v$event_name n
where n.event# = a.event#
This command tells you the name of the event and how much time was spent in waiting. If you want to drill down to a specific wait event, additional columns of ASH help you with that as well. For instance, if one of the events the sessions waited on is buffer busy wait, proper diagnosis must identify the segments on which the wait event occurred. You get that from the ASH view column CURRENT_OBJ#, which can then be joined with DBA_OBJECTS to get the segments in question.
ASH also records parallel query server sessions, useful to diagnose the parallel query wait events. If the record is for a parallel query slave process, the SID of the coordinator server session is identified by QC_SESSION_ID column. The column SQL_ID records the ID of the SQL statement that produced the wait event, which can be joined with the V$SQL view to get the offending SQL statement. To facilitate the identification of the clients in a shared user environment like a web application, the CLIENT_ID column is also shown, which can be set by DBMS_SESSION.SET_IDENTIFIER.
Since ASH information is so valuable, wouldn't it be nice if it were stored in a persistent manner similar to AWR? Fortunately, it is; the information is flushed to the disk by the MMON slave to the AWR table, visible through the view DBA_HIST_ACTIVE_SESS_HISTORY.
Manual Collection
Snapshots are collected automatically by default, but you can also collect them on demand. All AWR functionality has been implemented in the package DBMS_WORKLOAD_REPOSITORY. To take a snapshot, simply issue:
execute dbms_workload_repository.create_snapshot
It immediately takes a snapshot, recorded in the table WRM$_SNAPSHOT. The metrics collected are for the TYPICAL level. If you want to collect more detailed statistics, you can set the parameter FLUSH_LEVEL to ALL in the above procedure. The stats are deleted automatically but can also be deleted manually by calling the procedure drop_snapshot_range().
Baseline
A typical performance tuning exercise starts with a capturing a baseline set of metrics, making changes, and then taking another baseline set. These two sets can be compared to examine the effect of the changes made. In AWR, the same kind of analogy can be implemented for existing snapshots taken. Suppose a particularly resource intensive process named apply_interest ran between 1:00 and 3:00PM, corresponding to snapshot IDs 56 through 59. We could define a baseline named apply_interest_1 for these snapshots:
exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1')
This action marks the snapshots 56 through 59 as part of a baseline named above. Checking for existing baselines:
select * from dba_hist_baseline;
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ----------- -------------------- ------------- -----------
4133493568 1 apply_interest_1 56 59
After a few tuning steps, we can create another baseline--called, say apply_interest_2--and compare the metrics for only those snapshots related to these two baselines. Isolating snapshots to only a few sets like this helps in studying the effects of tuning on performance metrics. You can drop the baselines after the analysis using the procedure drop_baseline(); the snapshots will be preserved. Also, when the purge routine kicks in to delete the old snapshots, the ones related to baselines are not purged, allowing for further analysis.
Conclusion
This installment was intended to be merely an introduction to the very rudimentary aspects of the AWR. For a more complete coverage, see Oracle Database 10g documentation. Furthermore, an excellent treatise on AWR and ADDM can be found in the technical whitepaper The Self-Managing Database: Automatic Performance Diagnosis. In Week 18, you will learn more about ADDM and using it to solve real-life problems.
Automatic Storage Management
Finally, DBAs can free themselves from the mundane yet common tasks of adding, shifting, and removing storage disks at no additional cost
You just received a brand-new server and storage subsystem for a new Oracle database. Aside from operating system configuration, what is your most important before you can create the database? Obviously, it's creating the storage system layout—or more specifically, choosing a level of protection and then building the necessary Redundant Array of Inexpensive Disks (RAID) sets.
Setting up storage takes a significant amount of time during most database installations. Zeroing on a specific disk configuration from among the multiple possibilities requires careful planning and analysis, and, most important, intimate knowledge of storage technology, volume managers, and filesystems. The design tasks at this stage can be loosely described as follows (note that this list is merely representative; tasks will vary by configuration):
Confirm that storage is recognized at the OS level and determine the level of redundancy protection that might already be provided (hardware RAID).
Assemble and build logical volume groups and determine if striping or mirroring is also necessary.
Build a file system on the logical volumes created by the logical volume manager.
Set the ownership and privileges so that the Oracle process can open, read, and write to the devices.
Create a database on that filesystem while taking care to create special files such as redo logs, temporary tablespaces, and undo tablespaces in non-RAID locations, if possible.
In most shops, the majority of these steps are executed by someone with lots of knowledge about the storage system. That "someone" is usually not the DBA.
Notice, however, that all these tasks—striping, mirroring, logical filesystem building—are done to serve only one type of server, our Oracle Database. So, wouldn't it make sense for Oracle to offer some techniques of its own to simplify or enhance the process?
Oracle Database 10g does exactly that. A new and exciting feature, Automatic Storage Management (ASM), lets DBAs execute many of the above tasks completely within the Oracle framework. Using ASM you can transform a bunch of disks to a highly scalable (and the stress is on the word scalable) and performant filesystem/volume manager using nothing more than what comes with Oracle Database 10g software at no extra cost. And, no, you don't need to be an expert in disk, volume managers, or file system management.
In this installment, you will learn enough about ASM basics to start using it in real-world applications. As you might guess, this powerful feature warrants a comprehensive discussion that would go far beyond our current word count, so if you want to learn more, I've listed some excellent sources of information at the conclusion.
What is ASM?
Let's say that you have 10 disks to be used in the database. With ASM, you don't have to create anything on the OS side; the feature will group a set of physical disks to a logical entity known as a diskgroup. A diskgroup is analogous to a striped (and optionally mirrored) filesystem, with important differences: it's not a general-purpose filesystem for storing user files and it's not buffered. Because of the latter, a diskgroup offers the advantage of direct access to this space as a raw device yet provides the convenience and flexibility of a filesystem.
Logical volume managers typically use a function, such as hashing to map the logical address of the blocks to the physical blocks. This computation uses CPU cycles. Furthermore, when a new disk (or RAID-5 set of disks) is added, this typical striping function requires each bit of the entire data set to be relocated.
In contrast, ASM uses a special Oracle Instance to address the mapping of the file extents to the physical disk blocks. This design, in addition to being fast in locating the file extents, helps while adding or removing disks because the locations of file extents need not be coordinated. This special ASM instance is similar to other filesystems in that it must be running for ASM to work and can't be modified by the user. One ASM instance can service a number of Oracle databases instances on the same server.
This special instance is just that: an instance, not a database where users can create objects. All the metadata about the disks are stored in the diskgroups themselves, making them as self-describing as possible.
So in a nutshell, what are the advantages of ASM?
Disk Addition—Adding a disk becomes very easy. No downtime is required and file extents are redistributed automatically.
I/O Distribution—I/O is spread over all the available disks automatically, without manual intervention, reducing chances of a hot spot.
Stripe Width—Striping can be fine grained as in Redo Log Files (128K for faster transfer rate) and coarse for datafiles (1MB for transfer of a large number of blocks at one time).
Buffering—The ASM filesystem is not buffered, making it direct I/O capable by design.
Kernelized Asynch I/O—There is no special setup necessary to enable kernelized asynchronous I/O, without using raw or third-party filesystems such as Veritas Quick I/O.
Mirroring—Software mirroring can be set up easily, if hardware mirroring is not available.
Creating an ASM-enabled Database, Step by Step
Here's a concrete example of how you would create an ASM-enabled database:
1. Set up an ASM Instance
You create an ASM instance via the Database Creation Assistant by specifying the following initialization parameter:
INSTANCE_TYPE = ASM
You should start the instance up when the server is booted, and it should be one of the last things stopped when the server is shut down.
By default the value of this parameter is RDBMS, for regular databases.
2. Set up a Disk Group
After starting the ASM instance, create a disk group with the available disks.
CREATE DISKGROUP dskgrp1
EXTERNAL REDUNDANCY
DISK
'/dev/d1',
'/dev/d2',
'/dev/d3',
'/dev/d4',
... and so on for all the specific disks ...
;
In the above command, we have instructed the database to create a diskgroup named dksgrp1 with the physical disks named /dev/d1, /dev/d2, and so on. Instead of giving disks separately, you can also specify disk names in wildcards in the DISK clause as follows.
DISK '/dev/d*'
In the above command, we have specified a clause EXTERNAL REDUNDANCY, which indicates that the failure of a disk will bring down the diskgroup. This is usually the case when the redundancy is provided by the hardware, such as mirroring. If there is no hardware based redundancy, the ASM can be set up to create a special set of disks called failgroup in the diskgroup to provide that redundancy.
CREATE DISKGROUP dskgrp1
NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK
'/dev/d1',
'/dev/d2',
FAILGROUP failgrp2 DISK
'/dev/d3',
'/dev/d4';
Although it may appear as such, d3 and d4 are not mirrors of d1 and d2. Rather, ASM uses all the disks to create a fault-tolerant system. For instance, a file on the diskgroup might be created in d1 with a copy maintained on d4. A second file may be created on d3 with copy on d2, and so on. Failure of a specific disk allows a copy on another disk so that the operation can continue. For example, you could lose the controller for both disks d1 and d2 and ASM would mirror copies of the extents across the failure group to maintain data integrity.
3. Create Tablespace
Now create a tablespace in the main database using a datafile in the ASM-enabled storage.
CREATE TABLESPACE USER_DATA DATAFILE '+dskgrp1/user_data_01'
SIZE 1024M
/
That's it! The setup process is complete.
Note how the diskgroup is used as a virtual filesystem. This approach is useful not only in data files, but in other types of Oracle files as well. For instance, you could create online redo log files as
LOGFILE GROUP 1 (
'+dskgrp1/redo/group_1.258.3',
'+dskgrp2/redo/group_1.258.3'
) SIZE 50M,
...
Further Resources
As mentioned earlier, this article is not designed to offer all that is to know about the ASM feature and make you an expert, simply due to the sheer volume of information associated. However, don't despair; there is plenty of help available here on Oracle Technology Network:
"Storage on Automatic," by Lannes Morris-Murphy, is an excellent introductory article on ASM.
ASMLib, a library of the ASM features for Linux, extends ASM functionality. This page also links to technical references and source code for the library modules.
Chapter 12 of the Oracle Database Administrator's Guide 10g Release 1 (10.1) fully explains the concepts behind ASM.
Even archived log destinations can also be set to a diskgroup. Pretty much everything related to Oracle Database can be created in an ASM-based diskgroup. For example, backup is another great use of ASM. You can set up a bunch of inexpensive disks to create the recovery area of a database, which can be used by RMAN to create backup datafiles and archived log files. (In the next installment about RMAN in Oracle Database 10g, you'll learn in detail how to use that capability to your advantage.)
Please bear in mind however that ASM supports files created by and read by the Oracle Database only; it is not a replacement for a general-purpose filesystem and cannot store binaries or flat files.
Maintenance
Let's examine some typical tasks needed to maintain the diskgroups. From time to time, you may have to add additional disks into the diskgroup dskgrp1 to accommodate growing demand. You would issue:
alter diskgroup dskgrp1 add disk '/dev/d5';
To find out what disks are in what diskgroup, you would issue:
select * from v$asm_disk;
This command shows all the disks managed by the ASM instance for all the client databases. Of these disks, you may decide to remove a disk with:
alter diskgroup dskgrp1 drop disk diskb23;
Conclusion
The introduction of ASM provides a significant value in making it much easier to manage files in an Oracle database. Using this bundled feature, you can easily create a very scalable and performant storage solution from a set of disks. Any dynamic database environment requires the addition, shifting, and removal of disks, and ASM provides the necessary toolset to free the DBA from those mundane tasks.
RMAN
RMAN becomes more powerful with a redesigned incremental backup scheme, offline recovery of incremental backups, previewing restore, recovering through resetlogs, file compression, and much more
Most people would agree that RMAN is the de facto tool of choice for Oracle database backup. But as powerful as they were, early versions of RMAN left something to be desired. Like many DBAs, I had pet peeves about the absence of what I consider to be must-have features.
Fortunately, Oracle Database 10g addresses many of these issues by incorporating many desirable features, making RMAN an even more powerful and useful tool. Let's take a look.
Incremental Backups Revisited
RMAN includes an option for incremental backups. But truthfully, how often do you use it? Probably occasionally, or possibly even never.
This option instructs the tool to back up blocks that have changed since the last incremental backup at the same level or below. For instance, a full backup (level_0) is taken on day 1 and two incrementals of level_1 are taken on days 2 and 3. The latter two merely back up the changed blocks between days 1 and 2 and days 2 and 3, not across the entire backup time. This strategy reduces backup size, requiring less space, and narrows the backup window, reducing the amount of data moving across the network.
The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files—hence, no media recovery is possible. Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical. Rather, doing incremental backups in RMAN is an ideal alternative.
So why do many DBAs do incremental backups only rarely? One reason is that in Oracle9i and below, RMAN scans all the data blocks to identify candidates for backup. This process puts so much stress on the system that doing incrementals becomes impractical.
Oracle Database 10g RMAN implements incremental backups in a manner that disposes of that objection. It uses a file, analogous to journals in filesystems, to track the blocks that have changed since the last backup. RMAN reads this file to determine which blocks are to be backed up.
You can enable this tracking mechanism by issuing the following command:
SQL> alter database enable block change tracking using file '/rman_bkups/change.log';
This command creates a binary file called /rman_bkups/change.log for tracking purposes. Conversely, you can disable tracking with
SQL> alter database disable block change tracking;
To see whether change tracking is currently enabled, you can query:
SQL> select filename, status from v$block_change_tracking;
Flash Recovery Area
Flashback queries, introduced in Oracle9i, depend on undo tablespace to flash-back to a prior version, thereby limiting its ability go too far into the past. Flash recovery provided an alternative solution by creating flashback logs, which are similar to redo logs, to revert the database to a prior state. In summary, you create a flash recovery area for the database, specify its size, and place the database in flash recovery mode with the following SQL commands:
alter system set db_recovery_file_dest = '/ora_flash_area';
alter system set db_recovery_file_dest_size = 2g;
alter system set db_flashback_retention_target = 1440;
alter database flashback on;
The database must be in archive log mode to be flashback-enabled. That process creates Oracle Managed Files in the directory /ora_flash_area, with a total size of up to 2GB. The database changes are written to these files and can be used to quickly recover the database to a point in the past.
By default, RMAN also uses /ora_flash_area to store backup files; thus, RMAN backups are stored on disk, not tape. For that reason, you have the ability to specify how many days you need to keep backups. After that period, the files are automatically deleted if more space is required.
The flash recovery area needn't be a filesystem or a directory, however—alternatively, it could be an Automatic Storage Management (ASM) diskgroup. In that case, the flash recovery area is specified by:
alter system set db_recovery_file_dest = '+dskgrp1';
Consequently, using ASM and RMAN in combination, you can build a highly scaleable, fault-tolerant storage system using cheap disks such as Serial ATA or SCSI drives, with no additional software required. (For more details about ASM, see the Week 8 installment in this series.) This approach not only makes the backup process much faster but also cheap enough to compete with the tape-based approach.
An additional benefit is protection against user errors. Because ASM files are not true filesystems, they are less likely to be corrupted accidentally by DBAs and sysadmins.
Incremental Merge
Let's say you have the following backup schedule:
Sunday - Level 0 (full), with tag level_0
Monday - Level 1 (incremental) with tag level_1_mon
Tuesday - Level 1 (incremental) with tag level_1_tue
and so on. If the database fails on Saturday, prior to 10g you would have had to restore the tag level_0 and then apply all six incrementals. It would have taken a long time, which is another reason many DBAs shun incremental backups.
Oracle Database 10g RMAN radically changes that equation. Now, your incremental backup command looks like this:
RMAN> backup incremental level_1 for recover of copy with tag level_0 database;
Here we have instructed RMAN to make an incremental level_1 backup and merge that with the full backup copy with the tag level_0. After this command, level_0 becomes a full backup of that day.
So, on Tuesday, the backup with tag level_0, when merged with incremental level_1 backup, becomes identical to the full Tuesday backup. Similarly, the incremental taken on Saturday, when applied to the backup on disk, will be equivalent to a full level_0 Saturday backup. If the database fails on Saturday, you just need to restore the level_0 backup plus a few archive logs to bring the database into a consistent state; there is no need to apply additional incrementals. This approach cuts down recovery time dramatically, speeds backup, and eliminates the need to make a full database backup again.
Compressed Files
With disk-based backups in the flash recovery area, you still have a big limitation: disk space. Especially when going across a network—as is usually the case—it's advisable to create as small a backup set as possible. In Oracle Database 10g RMAN, you can compress files inside the backup command itself:
RMAN> backup as compressed backupset incremental level 1 database;
Note the use of the clause COMPRESSED. It compresses backup files with an important difference: while restoring, RMAN can read the files without uncompressing. To confirm compression, check for the following message in the output:
channel ORA_DISK_1: starting compressed incremental level 1 datafile backupset
Furthermore, you can verify that the backup was compressed by checking the RMAN list output:
RMAN> list output;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Incr 1 2M DISK 00:00:00 26-FEB-04
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20040226T100154
Piece Name: /ora_flash_area/SMILEY10/backupset/2004_02_26/o1_mf_ncsn1_TAG20040226T100154_03w2m3lr_.bkp
Controlfile Included: Ckp SCN: 318556 Ckp time: 26-FEB-04
SPFILE Included: Modification time: 26-FEB-04
As with any compression process, this approach puts pressure on CPUs. As a tradeoff, you can keep more RMAN backups on disk that are readily available for restore-and-recover operations. Alternatively, you can make RMAN backups at the Physical Standby Database that can be used to recover the primary database. That approach will offload backup resourses to another host.
Look Before You Leap: Recovery Preview
In Oracle Database 10g, RMAN has gone one more step ahead by providing the ability to preview the backups required to perform a restore operation.
RMAN> restore database preview;
Listing 1 shows the output of this operation. You can also preview specific restore operations; for example:
restore tablespace users preview;
Preview allows you to ensure the recovery readiness of your backup infrastructure by making periodic and regular checks.
Resetlogs and Recovery
Let's imagine that you have lost the current online redo log files and you have to perform an incomplete database recovery—a rare but not unheard of situation. The biggest problem is resetlogs; after incomplete recovery you must open the database with the resetlogs clause, which sets the sequence number of the log threads to 1, making your earlier backups obsolete in RMAN and making the recovery operation more of a challenge.
In Oracle9i and below, if you need to restore the database to a version prior to resetlogs, you have to restore to a different incarnation. In Oracle Database 10g, you don't have to do that. Thanks to additional infrastructure in the control file, RMAN can now readily use all backups, before and after a resetlogs operation, to recover the Oracle database. There is no need to shut down the database to make a backup. This new capability means that the database can be re-opened immediately for the user community after a resetlogs operation.
Ready for RMAN
The enhancements in Oracle Database 10g RMAN make it an even more compelling tool in your backup strategy. The improvements to the incremental backup process alone make RMAN tough to ignore.
Wait Interface
For immediate performance problems not yet captured by ADDM, the 10g wait interface provides valuable data for diagnosis
"The database is too slow!"
These words are usually uttered with grimness by an unhappy user. If you're like me, you've heard them way too many times in your DBA career.
Well, what do you do to address the problem? Apart from ignoring the user (a luxury that most of us cannot afford), your probable first line of attack is to see if any session is waiting for anything inside or outside the database.
Oracle provides a simple but elegant mechanism for doing that: the view V$SESSION_WAIT. This view reveals a variety of information to help your diagnosis, such as the events a session is waiting for or has waited for, and for how long and how many times. For instance, if the session is waiting for the event "db file sequential read," the columns P1 and P2 show the file_id and block_id for the block the session is waiting for.
For most wait events this view is sufficient, but it is hardly a robust tuning tool for at least two important reasons:
The view is a snapshot of the present. When the waits cease to exist, the history of those waits experienced by the session earlier disappears too, making after-effect diagnosis difficult. V$SESSION_EVENT provides cumulative but not very detailed data.
V$SESSION_WAIT contains information only about wait events; for all other relevant information such as the userid and terminal you have to join it with the view V$SESSION.
In Oracle Database 10g, the wait interface has been radically redesigned to provide more information with less DBA intervention. In this article, we will explore those new features and see how they aid us in the diagnosis of performance problems. For most of the performance problems, you will get an extended analysis from Automatic Database Diagnostic Manager (ADDM), but for immediate problems not yet captured by ADDM, the wait interface provides valuable data for diagnosis.
Enhanced Session Waits
The first enhancement involves V$SESSION_WAIT itself. It's best explained through an example.
Let's imagine that your user has complained that her session is hanging. You found out the session's SID and selected the record from the view V$SESSION_WAIT for that SID. The output is shown below.
SID : 269
SEQ# : 56
EVENT : enq: TX - row lock contention
P1TEXT : name|mode
P1 : 1415053318
P1RAW : 54580006
P2TEXT : usn<<16 | slot
P2 : 327681
P2RAW : 00050001
P3TEXT : sequence
P3 : 43
P3RAW : 0000002B
WAIT_CLASS_ID : 4217450380
WAIT_CLASS# : 1
WAIT_CLASS : Application
WAIT_TIME : -2
SECONDS_IN_WAIT : 0
STATE : WAITED UNKNOWN TIME
Note the columns shown in bold; of those columns, WAIT_CLASS_ID, WAIT_CLASS#, and WAIT_CLASS are new in 10g. The column WAIT_CLASS indicates the type of the wait that must be either addressed as a valid wait event or dismissed as an idle one. In the above example, the wait class is shown as Application, meaning that it's a wait that requires your attention.
This column highlights those few records that could prove most relevant for your tuning. For example, you could use a query like the following to get the wait sessions for events.
select wait_class, event, sid, state, wait_time, seconds_in_wait
from v$session_wait
order by wait_class, event, sid
/
Here is a sample output:
WAIT_CLASS EVENT SID STATE WAIT_TIME SECONDS_IN_WAIT
---------- -------------------- ---------- ------------------- ---------- ---------------
Application enq: TX - 269 WAITING 0 73
row lock contention
Idle Queue Monitor Wait 270 WAITING 0 40
Idle SQL*Net message from client 265 WAITING 0 73
Idle jobq slave wait 259 WAITING 0 8485
Idle pmon timer 280 WAITING 0 73
Idle rdbms ipc message 267 WAITING 0 184770
Idle wakeup time manager 268 WAITING 0 40
Network SQL*Net message to client 272 WAITED SHORT TIME -1 0
Here you can see that several events (such as Queue Monitor Wait and JobQueue Slave) are clearly classified as Idle events. You could eliminate them as nonblocking waits; however, sometimes these "idle" events can indicate an inherent problem. For example, the SQL*Net-related events may indicate high network latency, among other factors.
The other important thing to note is the value of WAIT_TIME as -2. Some platforms such as Windows do not support a fast timing mechanism. If the initialization parameter TIMED_STATISTICS isn't set on those platforms, accurate timing statistics can't be determined. In such cases, a very large number is shown in this column in Oracle9i, which clouds the issue further. In 10g, the value -2 indicates this condition—the platform does not support a fast timing mechanism and TIMED_STATISTICS is not set. (For the remainder of the article, we will assume the presence of a fast timing mechanism.)
Sessions Show Waits Too
Remember the long-standing requirement to join V$SESSION_WAIT to V$SESSION in order to get the other details about the session? Well, that's history. In 10g, the view V$SESSION also shows the waits shown by V$SESSION_WAIT. Here are the additional columns of the view V$SESSION that show the wait event for which the session is currently waiting.
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
The columns are identical to those in V$SESSION_WAIT and display the same information, eliminating the need to look in that view. So, you need to check only one view for any sessions waiting for any event.
Let's revisit the original problem: The session with SID 269 was waiting for the event enq: TX - row lock contention, indicating that it is waiting for a lock held by another session. To diagnose the problem, you must identify that other session. But how do you do that?
In Oracle9i and below, you might have to write a complicated (and expensive) query to get the SID of the lock holding session. In 10g, all you have to do is issue the following query:
select BLOCKING_SESSION_STATUS, BLOCKING_SESSION
from v$session
where sid = 269
BLOCKING_SE BLOCKING_SESSION
----------- ----------------
VALID 265
There it is: the session with SID 265 is blocking the session 269. Could it be any easier?
How Many Waits?
The user is still in your cubicle because her question is still not answered satisfactorily. Why has her session taken this long to complete? You can find out by issuing:
select * from v$session_wait_class where sid = 269;
The output comes back as:
SID SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
---- ------- ------------- ----------- ------------- ----------- -----------
269 1106 4217450380 1 Application 873 261537
269 1106 3290255840 2 Configuration 4 4
269 1106 3386400367 5 Commit 1 0
269 1106 2723168908 6 Idle 15 148408
269 1106 2000153315 7 Network 15 0
269 1106 1740759767 8 User I/O 26 1
Note the copious information here about the session's waits. Now you know that the session has waited 873 times for a total of 261,537 centi-seconds for application-related waits, 15 times in network-related events, and so on.
Extending the same principle, you can see the system-wide statistics for wait classes with the following query. Again, the time is in centi-seconds.
select * from v$system_wait_class;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
------------- ----------- ------------- ----------- -----------
1893977003 0 Other 2483 18108
4217450380 1 Application 1352 386101
3290255840 2 Configuration 82 230
3875070507 4 Concurrency 80 395
3386400367 5 Commit 2625 1925
2723168908 6 Idle 645527 219397953
2000153315 7 Network 2125 2
1740759767 8 User I/O 5085 3006
4108307767 9 System I/O 127979 18623
Most problems do not occur in isolation; they leave behind tell-tale clues that can be identified by patterns. The pattern can be seen from a historical view of the wait classes as follows.
select * from v$waitclassmetric;
This view stores the statistics related to wait classes over the last minute.
select wait_class#, wait_class_id,
average_waiter_count "awc", dbtime_in_wait,
time_waited, wait_count
from v$waitclassmetric
/
WAIT_CLASS# WAIT_CLASS_ID AWC DBTIME_IN_WAIT TIME_WAITED WAIT_COUNT
----------- ------------- ---- -------------- ----------- ----------
0 1893977003 0 0 0 1
1 4217450380 2 90 1499 5
2 3290255840 0 0 4 3
3 4166625743 0 0 0 0
4 3875070507 0 0 0 1
5 3386400367 0 0 0 0
6 2723168908 59 0 351541 264
7 2000153315 0 0 0 25
8 1740759767 0 0 0 0
9 4108307767 0 0 8 100
10 2396326234 0 0 0 0
11 3871361733 0 0 0 0
Note the WAIT_CLASS_ID and related statistics. For the value 4217450380, we saw that the 2 sessions waited for this class in the last minute for a total of 5 times and for 1,499 centi-seconds. But what is this wait class? You can get that information from V$SYSTEM_WAIT_CLASS as shown above—it's the class Application.
Note the column named DBTIME_IN_WAIT, a very useful one. From the our Week 6 installment on Automatic Workload Repository (AWR), you may recall that in 10g time is reported in finer granularity and that the exact time spent inside the database can be ascertained. DBTIME_IN_WAIT shows the time spent inside the database.
Everyone Leaves a Trail
Finally the user leaves and you breathe a sigh of relief. But you may still want to get to the bottom of what different waits contributed to the problem in her session in the first place. Sure, you can easily get the answer by querying V$SESSION_WAIT—but unfortunately, the wait events are not present now and hence the view does not have any records of them. What would you do?
In 10g, a history of the session waits is maintained automatically for the last 10 events of active sessions, available through the view V$SESSION_WAIT_HISTORY. To find out these events, you would simply issue:
select event, wait_time, wait_count
from v$session_wait_history
where sid = 265
/
EVENT WAIT_TIME WAIT_COUNT
------------------------------ ---------- ----------
log file switch completion 2 1
log file switch completion 1 1
log file switch completion 0 1
SQL*Net message from client 49852 1
SQL*Net message to client 0 1
enq: TX - row lock contention 28 1
SQL*Net message from client 131 1
SQL*Net message to client 0 1
log file sync 2 1
log buffer space 1 1
When the sessions become inactive or disconnected, the records disappear from that view. However, the history of these waits is maintained in AWR tables for further analysis. The view that shows the session waits from the AWR is V$ACTIVE_SESSION_HISTORY. (Again, for more information about AWR, see Week 6 of this series.)
Conclusion
Analyzing performance problems become very easy with the enhancement of the wait model in Oracle Database 10g. The availability of the history of session waits helps you diagnose the problem after the session has finished experiencing them. Classification of waits into wait classes also helps you understand the impact of each type of wait, which comes handy in when developing a proper rectification approach.
Enterprise Manager 10g
Finally, a tool that serves as one-stop-shop for Oracle administration and management—whether by novices or experts
What tool do you use in your day-to-day DBA-related activities? It's a question I asked recently in a user group meeting.
The answers varied depending on the DBA's work experience. Most senior administrators expressed a preference for simple command-line SQL*Plus (my personal favorite), with the rest dividing their allegiances among a handful of third-party products. The same question, however, yielded a different response from entry-level DBAs: among that group, Enterprise Manager (EM) was clearly the tool of choice.
It's not hard to understand these preferences. Oracle Enterprise Manager has been steadily perfected since its introduction several years ago, beginning as the character-mode display SQL*DBA, evolving into a client OS-based tool, and finally taking on a Java flavor. The information presented by EM was sufficiently detailed for most DBA tasks, serving as a solution for users who were either too reluctant or too busy to learn a new syntax and wanted a GUI tool for managing common database chores such as adding users, modifying datafiles, and checking on rollback segments. The diagnostic pack supplied much-needed GUI support for performance tuning.
However, one of the major issues hampering EM's widespread adoption was its inability to keep pace with the development of the database server itself. For example, the Oracle9i Database version of EM doesn't support subpartitioning, a feature first introduced in Oracle8i.
The new version of EM in Oracle Database 10g changes that equation. It has a new architecture, a new interface, and most important, a very powerful and complete toolbox catering to all DBA skillsets—from novices to advanced users. And best of all, it's part of the installation itself without any additional cost. If you are evaluating third-party tools, you can certainly throw EM into the mix to light a fire under the competition. Even if you are an "in-command-line-we-trust" kind of DBA (like me), you will greatly appreciate how EM can help you in several situations.
In this installment I will introduce you to the new EM. Because the tool is so vast in scope, it will be impossible to cover the entire spectrum of features; instead, I will explain a few basics and offer pointers to additional material. Keeping in the spirit of this series, I will provide practical examples that demonstrate the use of the tool to solve real-life problems.
Architecture
EM 10g is installed by default when you install the 10g software. Conceptually, it differs from previous versions in that instead of being a client-installed tool, it's actually an HTTP server (called DB Console) sitting on the database server itself. (See Figure 1.) You can use any browser to see the EM interface.
The port number for DB Console is found in $ORACLE_HOME/install/portlist.ini. Here is an example of a file; ports in your case may be different.
Ultra Search HTTP port number = 5620
iSQL*Plus HTTP port number = 5560
Enterprise Manager Agent Port =
Enterprise Manager Console HTTP Port (starz10) = 5500
Enterprise Manager Agent Port (starz10) = 1830
From this file we know that the Agent for the database starz10 listens on the port 1830 and the EM console listens on 5500. We can invoke the EM logon screen by entering the following URL:
http://starz/em/console/logon/logon
This URL brings up a logon screen where you can log on as a DBA user. For our example, we will log in as SYS.
Main Database Home Page
After logon, the main database home page comes up. The top portion of the home page enables a quick glance at important details.
Some of the most important points in the above figure have been circled and annotated with numbered references in this article. First, note the section labeled "General" (1); this section shows some most rudimentary details about the database, such as the fact that the database has been up since March 20 as well as the instance name. The Oracle Home is shown as a hyperlink, which, when clicked, shows all the products and all other Oracle databases sharing that home. The hyperlink for Listeners shows all the databases and instances registered with the listener, whose name is shown immediately below. Finally, it shows the host name (starz).
In section named "Host CPU" (2), the CPU details are shown at a glance. Section "Active Sessions" (3) shows the active sessions and what they are doing at the moment (4). We see from the above that 99% of the time spent by the sessions is in waiting. (We will find the cause of these waits later.) The section on "High Availability" (5) shows availability-related information. For example, the value of "Instance Recovery Time," which is the value of MTTR Target for the instance, determines how much time may be required for instance crash recovery.
The section on "Space Usage" (6) is interesting: it shows warnings associated with 23 segments. (Again, more on these warnings later.) The section "Diagnostic Summary" (7) provides a synopsis of database well being. The number of performance findings indicates how many issues were proactively identified by the Automatic Database Diagnostic Monitor (ADDM), the new self-diagnostic engine in 10g. EM also automatically analyzes your environment to determine if any recommended best practices are being violated; the result of this analysis is presented in the "Policy Violation" section. Finally, EM scans the alert log and shows any recent ORA errors. This information is invaluable—automatic scanning of Oracle errors in the alert log saves you the considerable trouble of manually searching for them.
The bottom part of the database home page, shown in Figure 3, we see some of these messages in more detail. The section "Alerts" (1) shows all the relevant alerts that require your attention, each of which can be easily configured. Take the first one (2), for example, which shows that the Archiver process is hanging for some reason. Of course, the next course of action is to determine why. To find out, just click on it. You will be shown more details from the alert.log file containing the error. In this case, the culprit was a filled-up flashback recovery area; we just need to clear it up so the Archiver can start working again.
Another alert (3) is about a wait: the database is waiting 69% of the time for a wait related to the wait class "Application." Remember how the top part of the home page indicates that a session is waiting? This alert shows us what it is waiting on. Clicking on the hyperlink will immediately show you the actual waits.
The next alert (4) shows an audit entry, that the user SYS connected to the database from a certain client machine. Again, by clicking on the hyperlink you can reveal all the details about the connection. The last alert (5) shows that some objects are invalid. Clicking on the hyperlink will get you to the screen where the invalid objects are validated.
As you can see, the database home page serves as a dashboard for everything that needs your attention. Instead of cluttering the screen with detailed information, the interface has been made quite succinct with those details just a click away. You could compile all this information manually, but it would take a lot of time and effort. EM 10g provides an out-of-the-box solution.
General Usage
Let's see how some of the more common tasks are accomplished through the new EM.
One common task is to alter a table and its corresponding indexes. From the Database home page, choose the "Administration" tab as shown in Figure 3 and reference the item marked 6. From this page you can administer the database to configure undo segments, create tablespaces and schema objects, set up resource manager, use the new Scheduler (to be covered in a future installment), and more. Choose "Tables" from there, which brings up a screen.
Note the flashlight symbol highlighted inside a red circle; this is the button for bringing up a list of values. In the screen shown in the figure, you can click on the LOV symbol to bring up a list of users in the database and select one from the list. Clicking on the button "Go" brings up a list of tables for that user. You can also specify a wildcard with the "%" sign—for example, by using %TRANS% to bring up all the tables with the word TRANS in the name.
Let's see an example. Choose the table TRANS to modify a column there. Clicking on the hyperlink brings up the "Edit Table" screen.
If you want to modify the column ACTUAL_RATE from NUMBER(10) to NUMBER(11), you can modify the number (Ref 1) and click "Apply." To see the actual SQL statement used to accomplish this task, can click the button "Show SQL."
Another important piece of information is available in the same screen: the growth trend. As you will learn in a future installment on segment management, it is possible to observe object growth over a period of time. This screen offers that same information but in a graphical manner. To see the screen, click on the tab "Segments" (Figure 5 Ref 2). This brings up the segment screen.
Note the item marked inside the red circles. The screen shows how much space is allocated to the segment (2), how much is actually used (1), and how much is wasted (3). On the bottom part of the screen (4), you can see a graph of the space used and allocated for the object. In this example, the pattern of the table usage has been steady—hence the straight line.
You can perform other administrative operations on the table using the tabs for that purpose, such as "Constraints" for managing constraints.
Performance Tuning Using EM
As you've learned up to this point, although EM's look-and-feel has changed, it offers at least as much functionality as the previous Java version. However, unlike the latter, EM now also supports newer Oracle Database functionality. For example, EM can now handle subpartitions.
However, experienced DBAs will want more from the tool—especially for troubleshooting problems or proactive performance tuning. Let's use an example. Recall from the previous section that our database is waiting on the "Application" wait class as shown in the database home page (Figure 3 Ref 3) and that we need to diagnose the cause. One of the key things to understand in any tuning process is how various components such as CPU, disk, and host subsystems interact, so it helps if all these variables are viewed together in context. To do that, choose the "Performance" tab from the Database home page.
Note how all the metrics have been aligned on the same timeline, which makes viewing their interdependencies easier. Note the spike (3), which corresponds to the Scheduler task. It shows that some seven sessions were waiting for Scheduler-related waits at that time. So, what was the impact? Note the CPU metrics located in the same place (the green area)—they indicate the maximum CPU ever used, as shown in the graph by the broken line (4). Before and after that point, we don't see the CPU spikes occurring, which provides one clue. Note the spike in CPU run queue length (1), which is a direct consequence of the Scheduler, which might have generated an excessive memory requirement, having caused the increased paging activity (2). As you can see, all the symptoms fall in line to enable a better understanding of the database load "profile."
Note the spikes at the end of the timeline—increases in Run Queue Length (5) and Paging Rate (6)—which correlate to another spike in Physical Reads (7). What is the cause?
By comparing the graph "Sessions: Waiting and Working" with the time the spikes were occurring, we can see that most of the sessions were waiting on the "Application" wait class. But we need to find out exactly what it was waiting on during that time period. Click on the area at that time, which brings up the Active Sessions screen.
The screen shows that the sessions were waiting for the wait event enq: TX � row lock contention. So what was the SQL statement that caused it? Simple: The SQL ID of the statement 8rkquk6u9fmd0 is shown on the screen itself (inside the red circle). Click on the SQL ID to bring up the SQL screen.
On this screen you can see the SQL statement and relevant details about it, including the execution plan. It turns out that this SQL was causing row lock contention, so application design may be a source of the problem.
Latch Contention
Suppose that clicking on the "Performance" tab.
In the figure, note the metrics highlighted inside the red rectangle. You can see a lot of CPU-related waits around 12:20AM, which resulted in a large run queue in the CPU. We need to diagnose this wait.
First, click on the graph on the area shown for CPU contention (marked with "Click Here" on the figure) to see that particular wait in detail.
Note the shaded box in the "Active Sessions Working: CPU Used" graph (1). You can drag it using the mouse to place the focus. This operation causes the pie charts below (2 and 3) to be calculated only within the timeframe contained in that box. From there we see that a specific SQL with id 8ggw94h7mvxd7 is working extra hard (2). We also see that the user session with username ARUP and SID 265 is a top worker (3). Click on the session to see the details. This operation brings up a "Session Details" screen. Click on the tab "Wait Events" to bring up the details of the wait events experienced by the session.
In this screen, note the longest wait of 118 centiseconds, highlighted inside a red circle, which is waiting for a library cache. When you click on the hyperlink for "Latch: Library Cache,".
This screen provides some unique information not available in pre-10g databases. While diagnosing our latch contention issue, how do you know whether the 118 centi-second wait comprises many small waits in several sessions or just one large wait in only one session, thereby skewing the data?
The histograms come to our rescue here. From the figure, you know that some 250 times sessions had a wait of 1 millisecond (highlighted inside a circle). Sessions waited some 180 times somewhere between 4 and 8 milliseconds. This screen shows that the waits are typically for small durations, making them insignificant symptoms of latch contention.
From the database home page you can access ADDM, SQL Access Advisor, and other Advisors by clicking on the tab marked "Advisor Central." ADDM runs automatically as metrics are collected and the results are posted immediately on the Advisor Central page, which when clicked shows the recommendations made by ADDM. The SQL Tuning Advisor also examines these metrics and communicates its recommendations on this page. (We'll examine ADDM and SQL Tuning Advisor in much more detail in a future installment.)
Maintenance Made Easy
The tab marked "Maintenance" on the Database home page is a launching pad for common maintenance activities such as backup and recovery, data export or import (Data Pump), database cloning, much more. From this screen you can also edit the rationale for the best practices on which Policy Violations alerts are based.
Conclusion
As explained earlier, this discussion is just the tip of a very large iceberg. It was not my intention in this article to offer a comprehensive overview; rather, I wanted to provide a quick look at specific activities that span the skill-set spectrum.
Segment Management
Manage storage in segments efficiently with Oracle Database 10g—by reclaiming wasted space, reorganizing tables online, and estimating growth trends
Recently, I was asked to evaluate an RDBMS that competes with Oracle Database. During the vendor's presentation, the feature that registered the biggest "wow" factor in the audience was its support for online reorganizations—the product can relocate data blocks to make the equivalent of segments more compact online, without affecting current users.
At that time, Oracle did not offer such a capability in Oracle9i Database. But now, with Oracle Database 10g, you can easily reclaim wasted space and compact objects online—just for starters.
Before examining the feature, however, let's take a look at the "traditional" approach to this task.
Current Practices
Consider a segment, such as a table, where the blocks are filled up as shown in Figure 1. During normal operation, some rows are deleted, as shown in Figure 2. Now we have a lot of wasted space: (i) between the previous end of the table and the existing block and (ii) inside the blocks where some of the rows have not been deleted.
Oracle does not release that space for use by other objects for a simple reason: because that space is reserved for new inserts and to accommodate the growth of existing rows. The highest space occupied is known as a High Water Mark (HWM), as shown in Figure 2.
There are two main problems with this approach, however:
When a user issues a full table scan, Oracle must scan the segment all the way up to the HWM, even though it does not find anything. This task extends full table scan time.
When rows are inserted with direct path—for example, through Direct Load Insert (insert with the APPEND hint) or through the SQL*Loader direct path—the data blocks are placed directly above the HWM. The space below it remains wasted.
In Oracle9i and below, you can reclaim space by dropping the table, recreating it, and then reloading the data; or by moving the table to a different tablespace using the ALTER TABLE MOVE command. Both these processes must occur offline. Alternatively, you can use the online table reorganization feature, but that requires at least double the space of the existing table.
In 10g, this task has become trivial; you can now shrink segments, tables, and indexes to reclaim free blocks and give them to the database for other uses, provided that Automatic Segment Space Management (ASSM) is enabled in your tablespace. Let's see how.
Segment Management the 10g Way
Suppose you have a table BOOKINGS, which holds online bookings from the website. After the booking is confirmed, it's stored in an archival table BOOKINGS_HIST and the row is deleted from BOOKINGS. The time between booking and confirmation varies widely among customers, so a lot of rows are inserted above the HWM of the table because sufficient space is not available from the deleted rows.
Now you need to reclaim wasted space. First, find out exactly how much space is wasted in that segment that can be reclaimed. Because this is in an ASSM-enabled tablespace, you have to use the procedure SPACE_USAGE of the package DBMS_SPACE, as shown below.
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => 'BOOKINGS',
segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
end;
/
The output is:
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 4148 Bytes = 0
Full Blocks = 2 Bytes = 16384
The output shows that there are 4,148 blocks with 75-100% free space (FS4); no other free blocks are available. There are only 2 full blocks. The 4,148 blocks can be recovered.
Next, you must ensure that the table is row-movement enabled. If it's not, you can enable it with:
alter table bookings enable row movement;
or via Enterprise Manager 10g, on the Administration page. You should also ensure that all rowid-based triggers are disabled on this table because the rows are moved and the rowids could change.
Finally, you can reorganize the existing rows of the table with:
alter table bookings shrink space compact;
This command re-distributes the rows inside the blocks as shown in Figure 3, resulting in more free blocks under the HWM—but the HWM itself is not disturbed.
Figure 3: The blocks of the table after the rows are reorganized.
After the operation, let's see the change in space utilization. Using the PL/SQL block shown in the first step, you can see how the blocks are organized now:
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 1 Bytes = 0
FS4 Blocks = 0 Bytes = 0
Full Blocks = 2 Bytes = 16384
Note the important change here: the number of FS4 blocks (with 75-100% free space) is now 0, down from 4,148. We also see an increase in FS3 blocks (50-75% free space) from 0 to 1. However, because the HWM has not been reset, the total space utilization remains the same. We can check the space used with:
SQL> select blocks from user_segments where segment_name = 'BOOKINGS';
BLOCKS
---------
4224
The number of blocks occupied by the table—4,224—remains the same because the HWM has not moved from its original position. You can move the HWM to a lower position and reclaim the space with
alter table bookings shrink space;
Note that the clause COMPACT is not present. This operation will return the unused blocks to the database and reset the HWM. You can test it by checking the space allocated to the table:
SQL> select blocks from user_segments where segment_name = 'BOOKINGS';
BLOCKS
----------
8
The number of blocks is down from 4,224 to 8; all the unused space inside the table was returned to the tablespace for use in other segments, as shown in Figure 4.
This shrink operation occurs completely online and does not affect users.
You can also compact the indexes of the table in one statement:
alter table bookings shrink space cascade;
The online shrink command is a powerful feature for reclaiming wasted space and resetting the HWM. I consider the latter—resetting of the HWM—the most useful result of this command because it improves the performance of full table scans.
Finding Candidates for Shrinking
Before performing an online shrink, you may want to find out the biggest bang-for-the-buck by identifying the segments that can be most fully compressed. Simply use the built-in function verify_shrink_candidate in the package dbms_space. Execute this PL/SQL code to test if the segment can be shrunk to 1,300,000 bytes:
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',1300000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
/
PL/SQL procedure successfully completed.
SQL> print x
X
--------------------------------
T
If you use a low number for the target shrinkage, say 3,000:
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',30000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
the value of the variable x is set to 'F', meaning the table cannot be shrunk to 3,000 bytes.
Taking the Guesswork Out of Index Space Requirements
Now let's say you are about to embark on the task of creating an index on a table, or perhaps a set of tables. Besides the usual structural elements such as columns and uniqueness, the most important thing you have to consider is the expected size of the index—you must ensure that the tablespace has enough space to hold the new index.
With Oracle9i Database and below, many DBAs use tools ranging from spreadsheets to standalone programs to estimate the size of the future index. In 10g, this task has become extremely trivial through the use of the DBMS_SPACE package. Let's see it in action.
We are asked to create an index on the columns booking_id and cust_name of the table BOOKINGS. How much space does the proposed index need? All you do is execute the following PL/SQL script.
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_bookings_hist_01 on bookings_hist '||
'(booking_id, cust_name) tablespace users',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
The output is:
Used Bytes = 7501128
Allocated Bytes = 12582912
Suppose you want to use some parameters that will potentially increase the size of the index—for example, specifying an INITRANS parameter of 10.
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_bookings_hist_01 on bookings_hist '||
'(booking_id, cust_name) tablespace users initrans 10',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
The output is:
Used Bytes = 7501128
Allocated Bytes = 13631488
Note the increase in the allocated bytes from specifying a higher INITRANS. Using this approach you can easily determine the impact of the index on storage space.
You should be aware of two important caveats, however. First, this process applies only to tablespaces with SEGMENT SPACE MANAGEMENT AUTO turned on. Second, the package calculates the estimated size of the index from the statistics on the table. Hence it's very important to have relatively fresh statistics on the tables. But beware: the absence of statistics on the table will not result in an error in the use of the package, but will yield a wrong result.
Estimating Table Size
Suppose there is a table named BOOKINGS_HIST, which has the average row length of 30,000 rows and the PCTFREE parameter of 20. What if you wanted to increase the parameter PCT_FREE to 3—by what amount will the table increase in size? Because 30 is a 10% increase over 20, will the size go up by 10%? Instead of asking your psychic, ask the procedure CREATE_TABLE_COST inside the package DBMS_SPACE. Here is how you can estimate the size:
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_table_cost (
tablespace_name => 'USERS',
avg_row_size => 30,
row_count => 30000,
pct_free => 20,
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line('Used: '||l_used_bytes);
dbms_output.put_line('Allocated: '||l_alloc_bytes);
end;
/
The output is:
Used: 1261568
Allocated: 2097152
Changing the table's PCT_FREE parameter to 30 from 20, by specifying
pct_free => 30
we get the output:
Used: 1441792
Allocated: 2097152
Note how the used space has increased from 1,261,568 to 1,441,792 because the PCT_FREE parameter conserves less room in the data block for user data. The increase is about 14%, not 10%, as expected. Using this package you can easily calculate the impact of parameters such as PCT_FREE on the size of the table, or of moving the table to a different tablespace.
Predicting the Growth of a Segment
It's holiday weekend and Acme Hotels is expecting a surge in demand. As a DBA, you are trying to understand the demand so that you can ensure there is enough space available. How do you predict the space utilization of the table?
Just ask 10g; you will be surprised how accurately and intelligently it can make that prediction for you. You simply issue this query:
select * from
table(dbms_space.OBJECT_GROWTH_TREND
('ARUP','BOOKINGS','TABLE'));
The function dbms_space.object_growth_trend() returns record in PIPELINEd format, which can be displayed by the TABLE() casting. Here is the output:
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------ ----------- ----------- ------------
05-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
06-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
07-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
08-MAR-04 08.51.24.421081 PM 126190859 1033483971 INTERPOLATED
09-MAR-04 08.51.24.421081 PM 4517094 4587520 GOOD
10-MAR-04 08.51.24.421081 PM 127469413 1044292813 PROJECTED
11-MAR-04 08.51.24.421081 PM 128108689 1049697234 PROJECTED
12-MAR-04 08.51.24.421081 PM 128747966 1055101654 PROJECTED
13-MAR-04 08.51.24.421081 PM 129387243 1060506075 PROJECTED
14-MAR-04 08.51.24.421081 PM 130026520 1065910496 PROJECTED
The output clearly shows the size of the table BOOKINGS at various times as shown in the column TIMEPOINT, in the TIMESTAMP datatype. The SPACE_ALLOC column shows the bytes allocated to the table and the SPACE_USAGE column shows how many of those bytes have been used. This information is collected by the Automatic Workload Repository, or AWR (see Week 6 of this series), every day. In the above output, the data was collected well on March 9, 2004, as indicated by the value of the column QUALITY - "GOOD." The space allocated and usage figures are accurate for that day. However, for all subsequent days, the value of this column is PROJECTED, indicating that the space calculations are projected from the data collected by the AWR facility—not collected directly from the segment.
Note the values in this column prior to March 9—they are all INTERPOLATED. In other words, the value was not really collected or projected, but simply interpolated from the usage pattern for whatever data is available. Most likely the data was not available at that time and hence the values had to be interpolated.
Conclusion
With the availability of segment level manipulations you now have fine-grained control over how space is used inside a segment, which can be exploited to reclaim free space inside a table, reorganize the table rows to make it more compact online, and much more. These facilities help DBAs free themselves from the routine and mundane tasks like table reorganization. The online segment shrink feature is especially helpful in eliminating internal fragmentation and lowering the high water mark of the segment, which can significantly reduce the cost of a full table scan.
Automatic Shared Memory Management
Frustrated by trying to allocate the precise amount of memory required for different pools? Automatic Shared Memory Management makes it possible to allocate memory where it's needed most, automatically.
Whether you're a new or veteran DBA, you've almost certainly seen an error similar to this one at least once:
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool"... ...
or this one:
ORA-04031: unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")
or perhaps this one:
ORA-04031: unable to allocate bytes of shared memory ("shared pool",
"unknown object","joxlod: init h", "JOX: ioc_allocate_pal")
The cause of the first error is obvious: the memory allocated to the shared pool is insufficient for answering the user request. (In some cases the cause may not be the size of the pool itself, but rather the fragmentation that results from excessive parsing due to non-usage of bind variables—a favorite topic of mine; but let's stay focused on the issue at hand right now.) The other errors derive from inadequate space in the large pool and Java pool respectively.
You need to resolve these error conditions without any application-related changes. What are your options? The question is how to divide available memory among all the pools required by the Oracle instance.
How Do You Split the Pie?
The System Global Area (SGA) of an Oracle instance, as you know, comprises several memory areas, including the buffer cache, shared pool, Java pool, large pool, and redo log buffers. These pools occupy fixed amounts of memory in the operating system's memory space; their sizes are specified by the DBA in the initialization parameter file.
The four pools—db block buffer cache, shared pool, Java pool, and large pool—occupy almost all the space inside the SGA. (Relative to the other areas, the redo log buffer does not occupy much space and is inconsequential to our discussion here.) You, as the DBA, must ensure that their respective memory allocations are sufficient.
Suppose you decide that the values of these pools should be 2GB, 1GB, 1GB, and 1GB respectively. You would set the following initialization parameters to mandate the sizes of the pools for the database instance.
db_cache_size = 2g
shared_pool_size = 1g
large_pool_size = 1g
java_pool_size = 1g
Now, take a close look at these parameters. Honestly, are these values accurate?
I'm sure you have your doubts. In real life, no one can specify these pools to an exact science—they depend too heavily on the processing inside the database and the nature of processing changes from time to time.
Here's an example scenario. Say you have a typical, "mostly" OLTP database and have dedicated less memory for the buffer cache than you would have for a purely OLTP one (few of which exist anymore). One day, your users turn loose some very large full table scans for end-of-the-day reporting. Oracle9i Database gives you the ability to change the allocation online, but because the total physical memory available is limited, you decide to pull something away from the large pool and the Java pool:
alter system set db_cache_size = 3g scope=memory;
alter system set large_pool_size = 512m scope=memory;
alter system set java_pool_size = 512m scope=memory;
This solution works fine for a while, but then the nightly RMAN jobs—which use the large pool—begin and the pool immediately falls short. Again, you come to the rescue by supplementing the large pool with some memory from the db cache.
The RMAN jobs complete, but then a batch program that uses Java extensively fires up, and consequently, you start to see Java pool-related errors. So, you reallocate the pools (again) to accommodate the demands on the Java pool and db cache:
alter system set db_cache_size = 2G scope=memory;
alter system set large_pool_size = 512M scope=memory;
alter system set java_pool_size = 1.5G scope=memory;
The next morning, the OLTP jobs come back online and the cycle repeats all over again!
One alternative to this vicious cycle is to set the maximum requirements of each pool permanently. By doing that, however, you may allocate a total SGA more than the available memory—thereby increasing the risk of swapping and paging when the allocation is less than adequate for each pool. The manual reallocation method, although impractical, looks pretty good right now.
Another alternative is to set the values to acceptable minimums. However, when demand goes up and memory is not available, performance will suffer.
Note that in all these examples the total memory allocated to SGA remained the same, while the allocation among the pools changed based on immediate requirements. Wouldn't it be nice if the RDBMS were to automatically sense the demand from users and redistribute memory allocations accordingly?
The Automatic Shared Memory Management feature in Oracle Database 10g does exactly that. You can decide the total size of the SGA and then set a parameter named SGA_TARGET that decides the total size of the SGA. The individual pools within the SGA will be dynamically configured based on the workload. A non-zero value of the parameter SGA_TARGET is all that is needed to enable the automatic memory allocation.
Setting up Automatic Shared Memory Management
Let's see how this works. First, determine the total size of the SGA. You can estimate this value by determining how much memory is allocated right now.
SQL> select sum(value)/1024/1024 from v$sga;
SUM(VALUE)/1024/1024
--------------------
500
The current total size of the SGA right now is approximately 500MB, which will become the value of SGA_TARGET. Next, issue the statement:
alter system set sga_target = 500M scope=both;
This approach obviates the need to set individual values for the pools; thus, you'll need to make their values zero in the parameter file or remove them completely.
shared_pool_size = 0
large_pool_size = 0
java_pool_size = 0
db_cache_size = 0
Recycle the database to make the values take effect.
This manual process can also be implemented via Enterprise Manager 10g. From the database home page, choose the "Administration" tab and then "Memory Parameters." For manually configured memory parameters, the button marked "Enable" will be displayed, along with the values of all manually configured pools. Click the "Enable" button to turn Automatic Shared Memory Management on. Enterprise Manager does the rest.
After the automatic memory allocations are configured, you can check their sizes with the following:
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
340
SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
java pool 4
large pool 4
shared pool 148
As you can see, all the pools were automatically configured from the total target size of 500MB. (See Figure 1.) The buffer cache size is 340MB, Java pool is 4MB, large pool is 4MB, and shared pool is 148MB. Together they total (340+4+4+148=) 496MB, approximately the same size as the target SGA of 500MB.
Now suppose the host memory available to Oracle is reduced from 500MB to 300MB, meaning we have to reduce the size of the total SGA. We can reflect that change by reducing the target SGA size.
alter system set sga_target = 300M scope=both;
Checking the pools now, we can see that:
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
244
SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
java pool 4
large pool 4
shared pool 44
The total size occupied is 240+4+4+44 = 296MB, close to the target of 300MB. Notice how the pools were automatically reallocated when the SGA_TARGET was changed, as shown in Figure 2.
The size of the pools is dynamic. Based on the workload, the pools will expand to accommodate the increase in demand or shrink to accommodate the expansion in another pool. This expansion or contraction occurs automatically without the DBA's intervention, unlike the example in the opening of this article. Returning to that scenario for a moment, assume that after the initial allocation the RMAN job starts, indicating the need for a larger large pool; the large pool will expand from 4MB to 40MB to accommodate the demand. This additional 36MB will be carved out of the db buffers and the db block buffers will shrink, as shown in Figure 3.
The changed sizes of the pools are based on the workload on the system, so the pools needn't be sized for the worst-case scenario—they will automatically adjust to the growth in demand. Furthermore, the total size of the SGA is always within the maximum value specified by SGA_TARGET, so there is no risk of blowing the memory requirement out of proportion (which will lead to paging and swapping). You can dynamically increase the SGA_TARGET to the absolute maximum specified by adjusting the parameter SGA_MAX_SIZE.
Which Pools are Not Affected?
Some pools in SGA are not subject to dynamic resizing, and must be specified explicitly. Notable among them are the buffer pools for nonstandard block sizes and the non-default ones for KEEP or RECYCLE. If your database has a block size of 8K, and you want to configure 2K, 4K, 16K, and 32K block-size pools, you must set them manually. Their sizes will remain constant; they will not shrink or expand based on load. You should consider this factor when using multiple-size buffer, KEEP, and RECYCLE pools. In addition, log buffer is not subject to the memory adjustment—the value set in the parameter log_buffer is constant, regardless of the workload. ( In 10g, a new type of pool can also be defined in the SGA: Streams pool, set with parameter streams_pool_size. This pool is also not subject to automatic memory tuning.)
This gives rise to an interesting question. What if you need a non-default block size pool yet want to manage the other pools automatically?
If you specify any of these non-auto-tunable parameters (such as db_2k_cache_size), their total size is subtracted from the SGA_TARGET value to calculate the automatically tuned parameter values so that the total size of the SGA remains constant . For instance, imagine that the values look like this:
sga_target = 500M
db_2k_cache_size = 50M
and the rest of the pool parameters are unset. The 2KB buffer pool of 50MB leaves 450MB for the auto-tuned pools such as the default block size buffer pool (db_cache_size), shared pool, Java pool, and large pool. When the non-tunable parameter such as the 2KB block size pool is dynamically adjusted in such a way that the tunable portion's size is affected, the tunable portion is readjusted. For example, raising the value of db_2k_cache_size to 100MB from 50MB leaves only 400MB for the tunable parameters. So the tunable pools such as shared, large, Java, and default buffer pools shrink automatically to reduce their total size to 400MB from 450MB, as shown in Figure 4.
But what if you have sufficient memory available or the risks described above may not be that pronounced? If so, you can turn off automatic resizing by not specifying the parameter SGA_TARGET in the parameter file, by setting it to zero in the file, or by changing it to zero dynamically with ALTER SYSTEM. When SGA_TARGET is set to zero, the current values of the pools are automatically set to their parameter.
Using Enterprise Manager
You can also use Enterprise Manager 10g to manipulate these parameters. From the database home page, click the hyperlink "Memory Parameters," which will show you a screen similar to the one in Figure 5.
Note the items circled in red: The database is running in Automatic Shared Memory Management mode and the total size is 564MB, the same value specified in the parameter SGA_TARGET. You can modify it here and click on the Apply button to accept the values; the tunable parameters will automatically adjust.
Specifying a Minimum for Each Pool
Suppose you have set SGA_TARGET to 600MB and the various pools have been allocated automatically:
Pool Size (MB)
Buffer 404
Java 4
Large 4
Shared 148
Looking at the above you might conclude that the Java and large pools are a bit inadequate at 4MB; this value will definitely need to be increased at runtime. Therefore, you may want to make sure the pools at least start with higher values—say, 8MB and 16MB respectively. You can do that by explicitly specifying the value of these pools in the parameter file or dynamically using ALTER SYSTEM as shown below.
alter system set large_pool_size = 16M;
alter system set java_pool_size = 8M;
Checking the pools now, you can see:
SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
java pool 8
large pool 16
shared pool 148
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
388
The reallocation of the pools is shown below:
Pool Size (MB)
Buffer 388
Java 8
Large 16
Shared 148
Note how the Java and large pools have been reconfigured to 8MB and 16MB respectively, and that to keep the total SGA under 600MB, the buffer pool has reduced to 388MB from 404MB. Of course, these pools are still governed by Automatic Shared Memory Management—their sizes will shrink or expand based on demand. The values you have specified explicitly put a lower limit on the pool size; they will never sink below this limit.
Conclusion
The memory requirements of various pools in Oracle SGA are not static—rather, they vary based on the demand on the system. Automatic Shared Memory Management in Oracle Database 10g allows DBAs to manage system memory more efficiently by dynamically reallocating resources to where they are needed most while enforcing a specified maximum to prevent paging and swapping. More efficient memory management also leads to fewer memory requirements, which can make leaner hardware more viable.
ADDM and SQL Tuning Advisor
Get help on SQL tuning from the ultimate authority: the Oracle Database itself! Make a query behave using SQL Profiles and learn how to use ADDM to solve common performance problems quickly and easily.
It has been a quiet day so far: no major problems in the database, no fires to put out. You are almost relaxed; it's a perfect day for catching up on important to-do tasks such as tuning RMAN tuning parameters or multiple block sizes.
Suddenly, a developer appears at your cubicle. His SQL query is taking a long time to run. Could you please, he asks, tune the query ASAP so it will "behave"?
Perhaps you relaxed too soon. Your original agenda was to spend some time making strategic decisions that will make your database better, faster, and more secure—such as ensuring the database is recoverable, enhancing underlying technology, or researching security updates. Instead, you will spend another day focusing on tactical activities such as SQL tuning, leaving little or no time for a strategic agenda.
As a strategic DBA, you want to free yourself from mundane chores and focus more on thought-provoking areas. Wouldn't it be nice to have an assistant DBA do them for you?
With Oracle Database 10g, you have one in the form of Automatic Database Diagnostic Monitor (ADDM), a sort of robotic DBA that tirelessly trolls through database performance statistics to identify bottlenecks, analyze SQL statements, and consequently offer various types of suggestions to improve performance, often in conjunction with other "advisors" such as the SQL Tuning Advisor. In this installment, you will get an overview of how this process works.
Automatic Database Diagnostic Monitor (ADDM)
In Week 6, you learned about Automatic Workload Repository (AWR), which collects detailed performance-related metrics from the database at regular intervals, known as snapshots. After each snapshot is taken, ADDM is invoked to thoroughly analyze the data and metrics deriving from the difference between snapshots, and then recommend necessary actions. As I mentioned earlier, after finding a problem, ADDM might in turn call other advisors (such as the SQL Tuning Advisor) to offer recommendations for improvement.
Instead of explaining this feature in words, let me show you exactly how it works. Suppose you are trying to diagnose an unexplained performance problem. In the example in our introduction, you knew which SQL statements needed to be tuned, or at least that SQL statements were the problem. In real life, however, you probably will not have such helpful information.
To perform a diagnosis in 10g, you would choose the snapshots in the relevant interval for further drill-down analysis. In Enterprise Manager 10g, from the Database home page, you would choose "Advisor Central" and then click on the "ADDM" link, which brings up a page similar to Figure 1.
In this page, you can create tasks to be analyzed by ADDM. You know that the performance problems occurred around 11PM, so choose the snapshots that fall in that range, indicated by "Period Start" and "Period End" values. You can also click on the camera icons to indicate start and stop snapshot intervals, as shown in red ellipses here. After choosing the interval, press the "OK" button, which brings up a page similar to that shown in Figure 2.
Here ADDM identifies two critical and related performance problems in the interval: some SQL statements are consuming significant CPU time, leading to a significant database slowdown. Based on the findings, ADDM recommends SQL tuning for these statements as highlighted in the figure.
If you click on the individual findings, ADDM displays more details. For example, clicking on the problem finding brings up a page similar to the one shown in Figure 3.
Here you can see the specific SQL statement causing this problem. ADDM recommends that you subject this SQL statement to a thorough analysis by SQL Tuning Advisor, as mentioned in the "Action" section. You can immediately run the task by clicking on the button next to it, which will invoke the SQL Tuning Advisor.
In Figure 2, you may have noticed a button named "View Report." In addition to providing the recommendation in individual web pages, ADDM can also create plain-text reports for a quicker one-stop analysis. Listing 1 shows the comprehensive recommendations made in our example plain-text report. Note how the report presents relevant details such as the SQL statement in question, its hash value, and so on. The SQL ID can be used for independent analysis in the SQL Tuning Advisor page in Enterprise Manager or via the command line.
ADDM is invoked after every AWR snapshot is collected, so the recommendations based on the adjacent snapshots are available for viewing. Hence you will not have to create an ADDM task as shown above if the scope of analysis is just two adjacent snapshots. If you want to analyze between two snapshots that are not adjacent, you will need to create the ADDM task.
Keep in mind that this is not all ADDM can do; there are many more analyses and recommendations available for memory management, segment management, redo/undo, and more, as you saw in previous installment. Because it would be impossible to describe the full spectrum of ADDM functionalities in this single brief article, we'll focus only on SQL Tuning Advisor here. Now let's see how it works.
Access Analysis with SQL Tuning Advisor
In a typical runtime optimization, the optimizer generates a set of possible access paths and chooses the least "expensive" among them based on object statistics. At that moment, however, it does not have the time to address whether the statement can be tuned, the statistics are stale, an index can be created, and so on. In contrast, the SQL Tuning Advisor can perform this "expert system" type of thinking. Essentially, the optimizer can answer the question: "Based on what's available, what's the best way to get results?", whereas SQL Tuning Advisor can answer the question, "Based on what the user wants, what else needs to be done to enhance performance?"
As you might expect, this "thinking" consumes resources such as CPU; hence the SQL Tuning Advisor works on SQL statements during a Tuning Mode, which can be run during off-peak times. This mode is indicated by setting the SCOPE and TIME parameters in the function while creating the tuning task. It's a good practice to run Tuning Mode during a low-activity period in the database so that regular users are relatively unaffected, leaving analysis for later.
The concept is best explained through an example. Take the case of the query that the developer brought to your attention, shown below.
select account_no from accounts where old_account_no = 11
This statement is not difficult to tune but for the sake of easier illustration, assume it is. There are two ways to fire up the advisor: using Enterprise Manager or plain command line.
First, let's see how to use it in command line. We invoke the advisor by calling the supplied package dbms_sqltune.
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_sql := 'select account_no from accounts where old_account_no = 11';
dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text => l_sql,
user_name => 'ARUP',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'FOLIO_COUNT'
);
dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/
This package creates and executes a tuning task named FOLIO_COUNT. Next, you will need to see the results of the execution of the task (that is, see the recommendations).
set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
The output is shown is Listing 2. Look at these recommendations carefully; the advisor says you can improve performance by creating an index on the column OLD_ACCOUNT_NO. Even better, the advisor calculated the cost of the query if the index were created, making the potential savings more definable and concrete.
Of course, considering the simplicity of this example, you would have reached the conclusion via manual examination as well. However, imagine how useful this tool would be for more complex queries where a manual examination may not be possible or is impractical.
Intermediate-Level Tuning: Query Restructuring
Suppose the query is a little bit more complex:
select account_no from accounts a
where account_name = 'HARRY'
and sub_account_name not in
( select account_name from accounts
where account_no = a.old_account_no and status is not null);
The advisor recommends the following:
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The optimizer could not unnest the subquery at line ID 1 of the execution
plan.
Recommendation
--------------
Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
either "NOT NULL" constraints or "IS NOT NULL" predicates.
Rationale
---------
A "FILTER" operation can be very expensive because it evaluates the
subquery for each row in the parent query. The subquery, when unnested can
drastically improve the execution time because the "FILTER" operation is
converted into a join. Be aware that "NOT IN" and "NOT EXISTS" might
produce different results for "NULL" values.
This time the advisor did not recommend any structural changes such as indexes, but rather intelligently guessed the right way to tune a query by replacing NOT IN with NOT EXISTS. ecause the two constructs are similar but not identical, the advisor gives the rationale for the change and leaves the decision to the DBA or application developer to decide whether this recommendation is valid for the environment.
Advanced Tuning: SQL Profiles
As you may know, the optimizer decides on a query execution plan by examining the statistics present on the objects referenced in the query and then calculating the least-cost method. If a query involves more than one table, which is typical, the optimizer calculates the least-cost option by examining the statistics of all the referenced objects—but it does not know the relationship among them.
For example, assume that an account with status DELINQUENT will have less than $1,000 as balance. A query that joins the tables ACCOUNTS and BALANCES will report fewer rows if the predicate has a clause filtering for DELINQUENT only. The optimizer does not know this complex relationship—but the advisor does; it "assembles" this relationship from the data and stores it in the form of a SQL Profile. With access to the SQL Profile, the optimizer not only knows the data distribution of tables, but also the data correlations among them. This additional information allows the optimizer to generate a superior execution plan, thereby resulting in a well-tuned query.
SQL Profiles obviate the need for tuning SQL statements by manually adding query hints to the code. Consequently, the SQL Tuning Advisor makes it possible to tune packaged applications without modifying code—a tremendous benefit.
The main point here is that unlike objects statistics, a SQL Profile is mapped to a query, not an object or objects. Another query involving the same two tables—ACCOUNTS and BALANCES—may have a different profile. Using this metadata information on the query, Oracle can improve performance.
If a profile can be created, it is done during the SQL Tuning Advisor session, where the advisor generates the profile and recommends that you "Accept" it. Unless a profile is accepted, it's not tied to a statement. You can accept the profile at any time by issuing a statement such as the following:
begin
dbms_sqltune.accept_sql_profile (
task_name => 'FOLIO_COUNT',
name => 'FOLIO_COUNT_PROFILE'
description => 'Folio Count Profile',
category => 'FOLIO_COUNT');
end;
This command ties the profile named FOLIO_COUNT_PROFILE generated earlier by the advisor to the statement associated with the tuning task named FOLIO_COUNT described in the earlier example. (Note that although only the advisor, not the DBA, can create a SQL Profile, only you can decide when to use it.)
You can see created SQL Profiles in the dictionary view DBA_SQL_PROFILES. The column SQL_TEXT shows the SQL statement the profile was assigned to; the column STATUS indicates if the profile is enabled. (Even if it is already tied to a statement, the profile must be enabled in order to affect the execution plan.)
Using ADDM and SQL Tuning Advisor
In addition to the three cases described above, SQL Tuning Advisor also identifies any objects with missing statistics referenced in the query. Thus, the advisor performs four distinct types of tasks:
Checks if objects have valid, usable statistics for proper optimization
Attempts to rewrite queries for better performance and suggests rewriting
Checks the access path to see if performance could be improved by adding additional structures such as indexes and materialized views
Creates SQL profiles and attaches them to specific queries.
Based on these capabilities, I can think of at least three different scenarios in which ADDM and SQL Tuning Advisor serve as powerful tools:
Reactive Tuning: Your application suddenly starts to perform poorly. Using ADDM, you can drill the problem down to a SQL statement or a set of statements, as shown in the section on ADDM. Following the recommendation of ADDM, you can launch SQL Tuning Advisor and correct the problem.
Proactive Tuning: The application behaves acceptably well; however, you want to make sure that all necessary maintenance tasks are performed and know if queries can be tuned even more. You would fire up SQL Tuning Advisor in the standalone mode to identify possible tuning alternatives.
Development Tuning: While code is being tested in development there are many opportunities to tune the query, as opposed to wthe QA or production phases. You can use the command-line version of the advisor to tune individual SQL statements before they are finalized in development.
Using Enterprise Manager
The previous example was deliberately formulated to illustrate how to use SQL Tuning Advisor in command-line mode, which is very useful for scripting these tasks proactively. In most cases, however, you will need to perform tuning in response to problems reported by an end user. Enterprise Manager 10g comes in handy in those cases.
A few weeks ago (Week 13) you were introduced to the revamped Enterprise Manager interface. Here's how you would use it to diagnose and tune SQL: From the Database home page, click on the link "Advisor Central" at the bottom of the screen, which launches the page containing all the advisors. Next, click on "SQL Tuning Advisor" at the top of the screen as shown in Figure 4.
You have just launched the SQL Tuning Advisor. Choose "Top SQL" from the next page as shown in Figure 5.
This action launches a page similar to the one shown in Figure 6, where a graph containing the various wait classes are traced along a time dimension.
A gray rectangular area within a red ellipse puts the focus on the graph. Reposition the rectangle by mouse-dragging it to a location where the CPU wait is high (as shown in the figure). The lower part of the page will display the relevant SQL statements in that interval, as shown in Figure 7.
As you can see, the SQL statement shown at the top (enclosed by the red ellipse) has the highest activity with maximum CPU consumption. Click on the statement ID to see details of the statement, which will bring up a screen as shown in Figure 8.
In the figure, you can see the exact SQL statement that caused the CPU consumption in that time period. You can click on the button "Run SQL Tuning Advisor" (marked in the figure) to run the advisor. This brings up a screen similar to the one shown in Figure 9.
In the advisor scheduler, you can determine the type of task and how much analysis should be done. For example, in the above figure, I have chosen "comprehensive" analysis and that the advisor is to be run immediately. After the advisor finishes you can see its recommendation, as shown in Figure 10.
This process I just described is similar to what you have seen in the command-line version; however, the flow is more reflective of a real-life scenario in which you have reacted to a problem, drilled down to its cause, and accepted recommendations about how to fix it.
Conclusion
ADDM is a powerful tool that has the "brains" to analyze performance metrics and offer recommendations based on best practices and accepted methodologies professed by seasoned Oracle professionals, all automatically. This functionality can tell the DBA not only what happened and why, but most important, what to do next.
Scheduler
Tired of cumbersome manual management of intervals in dbms_job and need a new scheduling system inside the database? Look no further than the database itself.
Some of you may use the dbms_job package extensively to submit database jobs to be run in the background, control the time or interval of a run, report failures, and much more. However, I have a feeling that most of you don't.
The problem with the package is that it can handle only PL/SQL code segments—just anonymous blocks and stored program units. It cannot handle anything outside the database that is in an operating system command file or executable. To do so, you would have to resort to using an operating system scheduling utility such as cron in Unix or the AT command in Windows. Or, you could use a third-party tool, one that may even extend this functionality by providing a graphical user interface.
Even so, dbms_job has a distinct advantage over these alternatives: it is active only when the database is up and running. If the database is down, the jobs don't run. A tool outside the database must manually check if the database is up—and that can be difficult. Another advantage is that dbms_job is internal to the database; hence you can access it via a database access utility such as SQL*Plus.
The Oracle Database 10g Scheduler feature offers the best of all worlds: a job scheduler utility right inside the database that is sufficiently powerful to handle all types of jobs, not just PL/SQL code segments, and that can help you create jobs either with or without associated programs and/or schedules. Best of all, it comes with the database at no additional cost. In this installment, we'll take a look at how it works.
Creating Jobs Without Programs
Perhaps the concept can be best introduced through examples. Suppose you have created a shell script to move archived log files to a different filesystem as follows:
/home/arup/dbtools/move_arcs.sh
We can specify the OS executable directly without creating it as a program first.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_2',
schedule_name => 'EVERY_30_MINS',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
/
Similarly, you can create a job without a named schedule.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_3',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
/
One advantage of Scheduler over dbms_job is pretty clear from our initial example: the ability to call OS utilities and programs, not just PL/SQL program units. This ability makes it the most comprehensive job management tool for managing Oracle Database and related jobs. However, you may have noted another, equally important advantage: the ability to define intervals in natural language. Note that in the above example we wanted our schedule to run every 30 minutes; hence the parameter REPEAT_INTERVAL is defined with a simple, English-like expression (not a PL/SQL one) :
'FREQ=MINUTELY; INTERVAL=30'
A more complex example may help convey this advantage even better. Suppose your production applications become most active at 7:00AM and 3:00PM. To collect system statistics, you want to run Statspack from Monday to Friday at 7:00AM and 3:00PM only. If you use DBMS_JOB.SUBMIT to create a job, the NEXT_DATE parameter will look something like this:
DECODE
(
SIGN
(
15 - TO_CHAR(SYSDATE,'HH24')
),
1,
TRUNC(SYSDATE)+15/24,
TRUNC
(
SYSDATE +
DECODE
(
TO_CHAR(SYSDATE,'D'), 6, 3, 1
)
)
+7/24
)
Is that code easy to understand? Not really.
Now let's see the equivalent job in DBMS_SCHEDULER. The parameter REPEAT_INTERVAL will be as simple as:
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15'
Furthermore, this parameter value can accept a variety of intervals, some of them very powerful. Here are some more examples:
Last Sunday of every month:
FREQ=MONTHLY; BYDAY=-1SUN
Every third Friday of the month:
FREQ=MONTHLY; BYDAY=3FRI
Every second Friday from the end of the month, not from the beginning:
FREQ=MONTHLY; BYDAY=-2FRI
The minus signs before the numbers indicate counting from the end, instead of the beginning.
What if you wanted to verify if the interval settings are correct? Wouldn't it be nice to see the various dates constructed from the calendar string? Well, you can get a preview of the calculation of next dates using the EVALUATE_CALENDAR_STRING procedure. Using the first example—running Statspack every day from Monday through Friday at 7:00AM and 3:00PM—you can check the accuracy of your interval string as follows:
set serveroutput on size 999999
declare
L_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;
begin
l_start_date := trunc(SYSTIMESTAMP);
l_return_date := l_start_date;
for ctr in 1..10 loop
dbms_scheduler.evaluate_calendar_string(
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',
l_start_date, l_return_date, l_next_date
);
dbms_output.put_line('Next Run on: ' ||
to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
);
l_return_date := l_next_date;
end loop;
end;
/
The output is:
Next Run on: 03/22/2004 07:00:00
Next Run on: 03/22/2004 15:00:00
Next Run on: 03/23/2004 07:00:00
Next Run on: 03/23/2004 15:00:00
Next Run on: 03/24/2004 07:00:00
Next Run on: 03/24/2004 15:00:00
Next Run on: 03/25/2004 07:00:00
Next Run on: 03/25/2004 15:00:00
Next Run on: 03/26/2004 07:00:00
Next Run on: 03/26/2004 15:00:00
This confirms that your settings are correct.
Associating Jobs with Programs
In the above case, you created a job independently of any program. Now let's create one that refers to an operating system utility or program, a schedule to specify how many times something should run, and then join the two to create a job.
First you need to make the database aware that your script is a program to be used in a job. To create this program, you must have the CREATE JOB privilege.
begin
dbms_scheduler.create_program
(
program_name => 'MOVE_ARCS',
program_type => 'EXECUTABLE',
program_action => '/home/arup/dbtools/move_arcs.sh',
enabled => TRUE,
comments => 'Moving Archived Logs to Staging Directory'
);
end;
/
Here you have created a named program unit, specified it as an executable, and noted what the program unit is called.
Next, you will create a named schedule to be run every 30 minutes called EVERY_30_MINS. You would do that with:
begin
dbms_scheduler.create_schedule
(
schedule_name => 'EVERY_30_MINS',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
comments => 'Every 30-mins'
);
end;
/
Now that the program and schedule are created, you will associate the program to the schedule to create a job.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE',
program_name => 'MOVE_ARCS',
schedule_name => 'EVERY_30_MINS',
comments => 'Move Archived Logs to a Different Directory',
enabled => TRUE
);
end;
/
This will create a job to be run every 30 minutes that executes the shell script move_arcs.sh. It will be handled by the Scheduler feature inside the database—no need for cron or the AT utility.
Classes, Plans, and Windows
A good job scheduling system worth its salt must support the ability to prioritize jobs. For instance, the statistics collection job suddenly goes into the OLTP workload window affecting performance there. To ensure the stats collection job doesn't consume resources affecting OLTP, you would use job classes, resource plans, and Scheduler Windows.
For example, while defining a job, you can make it part of a job class, which maps to a resource consumer group for allocation of resources. To do that, first you need to define a resource consumer group called, say, OLTP_GROUP.
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group (
consumer_group => 'oltp_group',
comment => 'OLTP Activity Group'
);
dbms_resource_manager.submit_pending_area();
end;
/
Next, you need to create a resource plan.
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan
('OLTP_PLAN', 'OLTP Database Activity Plan');
dbms_resource_manager.create_plan_directive(
plan => 'OLTP_PLAN',
group_or_subplan => 'OLTP_GROUP',
comment => 'This is the OLTP Plan',
cpu_p1 => 80, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
parallel_degree_limit_p1 => 4,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'OTHER_GROUPS',
switch_time => 10,
switch_estimate => true,
max_est_exec_time => 10,
undo_pool => 500,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_time_in_call => NULL
);
dbms_resource_manager.create_plan_directive(
plan => 'OLTP_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => NULL,
cpu_p1 => 20, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
parallel_degree_limit_p1 => 0,
active_sess_pool_p1 => 0,
queueing_p1 => 0,
switch_group => NULL,
switch_time => NULL,
switch_estimate => false,
max_est_exec_time => 0,
undo_pool => 10,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
end;
/
Finally, you create a job class with the resource consumer group created earlier.
begin
dbms_scheduler.create_job_class(
job_class_name => 'OLTP_JOBS',
logging_level => DBMS_SCHEDULER.LOGGING_FULL,
log_history => 45,
resource_consumer_group => 'OLTP_GROUP',
comments => 'OLTP Related Jobs'
);
end;
/
Let's examine the various parameters in this call. The parameter LOGGING_LEVEL sets how much log data is tracked for the job class. The setting LOGGING_FULL indicates that all activities on jobs in this class—creation, deletion, run, alteration, and so on—will be recorded in the logs. The logs can be seen from the view DBA_SCHEDULER_JOB_LOG and are available for 45 days as specified in the parameter LOG_HISTORY (the default being 30 days). The resource consumer group associated with this class is also specified. The job classes can be seen from the view DBA_SCHEDULER_JOB_CLASSES.
When you create a job, you can optionally associate a class to it. For instance, while creating COLLECT_STATS, a job that collects optimizer statistics by executing a stored procedure collect_opt_stats(), you could have specified:
begin
dbms_scheduler.create_job
(
job_name => 'COLLECT_STATS',
job_type => 'STORED_PROCEDURE',
job_action => 'collect_opt_stats',
job_class => 'OLTP_JOBS',
repeat_interval => 'FREQ=WEEKLY; INTERVAL=1',
enabled => true,
comments => 'Collect Optimizer Stats'
);
end;
/
This command will place the newly created job in the class OLTP_JOBS, which is then governed by the resource plan OLTP_GROUP, which will restrict how much CPU can be allocated to the process, the maximum number of executions before it is switched to a different group, the group to switch to, and so on. Any job defined in this class will be governed by the same resource plan directive. This capability is particularly useful for preventing different types of jobs from taking over the resources of the system.
The Scheduler Window is a time frame with an associated resource plan used for activating that plan-thereby supporting different priorities for the jobs over a time frame. For example, some jobs, such as batch programs to update databases for real-time decision-support, need high priority during the day but become low priority at night (or vice-versa). You can implement this schedule by defining different resource plans and then activating them using Scheduler Windows.
Monitoring
After a job is issued, you can monitor its status from the view DBA_SCHEDULER_JOB_LOG, where the column STATUS shows the current status of the job. If it shows FAILED, you can drill down further to find out the cause from the view DBA_SCHEDULER_JOB_RUN_DETAILS.
Administration
So far, we've discussed how to create several types of objects: programs, schedules, job classes, and jobs. What if you want to modify some of them to adjust to changing needs? Well, you can do that via APIs provided in the DBMS_SCHEDULER package.
From the Database tab of the Enterprise Manager 10g home page, click on the Administration link. This will bring up the Administration Screen, shown in Figure 1. All the Scheduler related tasks are found under the heading "Scheduler" to the bottom right-hand corner, shown inside a red ellipse in the figure.
All the tasks related to scheduler, such as creating, deleting, and maintaining jobs, can be easily accomplished through the hyper-linked task in this page. Let's see a few of these tasks. We created all these tasks earlier, so the clicking on the Jobs tab will show a screen similar to Figure 2.
Clicking on the job COLLECT_STATS allows you to modify its attributes. The screen shown in Figure 3 shows up when you click on "Job Name."
As you can see, you can change parameters of the job as well as the schedule and options by clicking on the appropriate tabs. After all changes are made, you would press the button "Apply" to make the changes permanent. Before doing so, you may want to click the button marked "Show SQL", which shows the exact SQL statement that will be issued—if for no other reason than to see what APIs are called, thereby enabling you to understand the workings behind the scene. You can also store the SQL in a script and execute it later, or store it as a template for the future.
Conclusion
Scheduler in Oracle Database 10g is a giant leap from the original dbms_job interface. For more information on these features and other more advanced ones, see Chapter 25 of the Oracle Database Administrator's Guide.
No comments:
Post a Comment