In contrast to parameters that must be changed, there are those that should never be changed. Let’s look at a few of them.
_COMPATIBLE_NO_RECOVERY. Usually set to 0.0.0, which defaults to the current version. If you set it to something else and the DB crashes, you may need to do a media recovery instead of just an instance recovery.
_ALLOW_ERROR_SIMULATION. Used by Oracle for internal testing. If you set it, there’s no telling what it will do to your instance.
_DB_BLOCK_CACHE_PROTECT. Will cause a db crash rather than let corruption get to the database. It may result in many ORA-0600 errors and other unpleasant things if set in a regular production database. This is for debugging only!
_IPC_FAIL_NETWORK. Simulates network failure; for testing only.
_IPC_TEST_FAILOVER. Tests transparent cluster network failover; for testing only.
_IPC_TEST_MULT_NETS. Simulates multiple cluster networks; for testing only.
_LOG_BUFFERS_CORRUPT. Corrupts redo buffers after write; for testing only.
_MTS_LOAD_CONSTANTS. A complex set of constants that governs the multithreaded server load balancing. It contains six different values dealing with how the load is balanced across servers and dispatchers.
_CPU_TO_IO. The multiplier for converting CPU cost to I/O cost. Change this and you will directly affect the CBO cost calculation.
_LOG_BUFFERS_CORRUPT. Corrupts redo buffers before write; used only for testing. A sure way to bring your database to its knees is to set this to TRUE.
_SINGLE_PROCESS. Run without detached processes; if you want single-user Oracle, this will give it to you.
_WAIT_FOR_SYNC. Wait for checkpoint sync on commit must always be TRUE; if set to FALSE, will cause mismatch between headers and SCN on DB crash or shutdown abort.
_NO_OBJECTS. Tells Oracle that no objects are being used; set to FALSE. If you set it to TRUE, Oracle will probably crash since the data dictionary uses objects.
_PMON_LOAD_CONSTANTS. As with MTS, these are PMON Server load-balancing constants and directly affect the operation of PMON; don’t mess with them.
This list contains only those parameters that stand out. There are many more that, if you change them, will have a negative effect on how Oracle behaves. When in doubt, don’t touch it!
Recovering Using _ALLOW_RESETLOGS_CORRUPTION
Let’s now look at a detailed example using _allow_resetlogs_corruption to recover a database. Recovery of a database using the undocumented parameter _allow_resetlogs_corruption should be regarded as a last-ditch, emergency recovery scenario only, and should not be attempted until all other avenues of recovery have been exhausted.
Note: Oracle will not support a database that has been recovered using this method unless it is subsequently exported and rebuilt.
Essentially, using _allow_resetlogs_corruption forces the opening of the datafiles even if their SCNs do not match up; then, on the next checkpoint, the old SCN values are overwritten. This could leave the database in an unknown state as far as concurrency.
This type of recovery is usually required when a datafile has been left in hot backup mode through several backup cycles without an intervening shutdown and startup. Upon shutdown and startup, the database will complain that a file (usually file id#1 the SYSTEM tablespace) needs more recovery, and asks for logs past all available archive logs and online logs.
An alternative scenario would be that the database is recovered from a hot backup and the above scenario occurs, or that the database asks for an archive log dated earlier than any that are available (usually for the rollback segment tablespace datafiles.) I have also seen this happen when creating a standby database using a hot backup.
A typical error stack would resemble:
SVRMGR> connect internal
Connected.
SVRMGR> @sycrectl
ORACLE instance started.
Total System Global Area 113344192 bytes
Fixed Size 69312 bytes
Variable Size 92704768 bytes
Database Buffers 20480000 bytes
Redo Buffers 90112 bytes
Statement processed.
ALTER DATABASE OPEN resetlogs
*
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/tstc/dbsyst01.dbf'
Or:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 48 needs more recovery to be consistent
ORA-01110: data file 48: '/vol06/oradata/testdb/ard01.dbf'
If all available archive logs and all available online redo logs are applied, and the error is not corrected, only then should you consider using the parameter _allow_resetlogs_corruption. Make sure a good backup of the database in a closed state (all files) is taken before attempting recovery using this parameter.
Note: It cannot be stressed firmly enough that the database will no longer be supported by Oracle until it is rebuilt after using _allow_resetlogs_corruption for recovery.
Procedure
The following details the recovery process using _allow_resetlogs_corruption:
1. If no recovery attempts have been made, shut down and back up the database (all files) as-is to provide a fallback position should recovery fail.
2. If recovery attempts have been made, recover the database to the state just before any other recovery attempts were made.
3. Use svrmgrl, sqlplus, or appropriate interface to start up the database in a mounted, but not open, condition:
a. STARTUP MOUNT
4. Ensure all datafiles are set to END BACKUP status:
a. SET PAGES 0 FEEDBACK OFF LINES 132
b. SPOOL alter_df.sql
c. SELECT ‘alter database datafile ‘||file_name||’ END BACKUP;’ from v$datafile;
d. SPOOL OFF
e. @alter_df.sql
5. Alter the database into open condition:
a. ALTER DATABASE OPEN;
6. If the database asks for recovery, use an UNTIL CANCEL-type recovery and apply all available archive and online redo logs; then issue the CANCEL and reissue the ALTER DATATBASE OPEN RESETLOGS; commands.
7. If the database asks for logs that are no longer available, or the preceding still resulted in errors, shut down the database.
8. Insert into the initialization file the following line:
a. _allow_resetlogs_corruption=TRUE
9. Use svrmgrl, sqlplus, or appropriate interface to start up the database in a mounted, but not open, condition:
a. STARTUP MOUNT
10. Ensure all datafiles are set to END BACKUP status:
a. SET PAGES 0 FEEDBACK OFF LINES 132
b. SPOOL alter_df.sql
c. SELECT ‘alter database datafile ‘||file_name||’ END BACKUP;’ from v$datafile;
d. SPOOL OFF
e. @alter_df.sql
11. Alter the database into open condition:
a. ALTER DATABASE OPEN;
12. If the database asks for recovery, use an UNTIL CANCEL-type recovery and apply all available archive and online redo logs; then issue the CANCEL and reissue the ALTER DATATBASE OPEN RESETLOGS; commands.
13. Once the database is open, immediately do a full export of the database or an export of the schemas you need to recover.
14. Shut down the database and remove the parameter _allow_resetlogs_corruption.
15. Rebuild the database.
16. Import to finish the recovery.
17. Implement a proper backup plan and procedure.
18. It may be advisable to perform an ANALYZE TABLE…VALIDATE STRUCTURE CASCADE on critical application tables after the recovery and before the export.
Note: Uncommitted records that had been written to disk will possibly be marked as committed by this procedure.
Monday, January 12, 2009
Recovering an Oracle Database with Missing Archived Logs
Introduction
In this paper we will present a method for retrieving data from an Oracle database that cannot be opened normally because one or more datafiles is inconsistent with the others. An example of a scenario where you would find yourself in this situation is as follows:
A disk failed on our server and we lost a datafile. We restored the datafile from a hot backup taken a week ago, but it turns out we are missing a few redo logs archived since then. Oracle is complaining because we can't produce the required archived redo logs, and we can't open the database. The datafile in question contains our most important tables. Is there any way we can salvage our data?
Every DBA should know that there is a problem here. The missing archived redo logs contain transactions which affect the data in the database. So it’s a given that you’re going to lose some data, but the question is, “How much?” Oracle takes a hard-line position and will not let you open the database normally because a data integrity issue exists. However, you may be able to retrieve much of your data if you use non-traditional means to get Oracle to drop its hard-line attitude. Retrieving the data that can be salvaged with the understanding that some data will be lost could be a whole lot better than losing all of the data because a subset of it has been corrupted.
In the next section of this paper, we will look at an overview of how you might go about salvaging data from an inconsistent datafile and get the database back up and running properly again. In the succeeding sections of this paper, we’ll dig into each step of the process in greater detail.
Overview
If you’ve lost a datafile that contained only indexes for heap-organized tables, or other data that is easily recreated, then your best bet might be to drop the tablespace and recreate and repopulate it from scratch. But if you’ve lost a datafile that contained important data not easily recreated, and all you have is an old backup of the file without all of the intervening archived redo logs, then you will want to extract what data you can from the problem tablespace, drop the tablespace, and then recreate and repopulate the tablespace.
Although the exact steps will vary depending on the particular situation, the general steps involved are:
1. Taking a cold backup of what you have now.
2. Restoring the lost datafile from a backup and applying the archived redo logs that you do have.
3. Setting an undocumented instance parameter which will allow you to open the database in its current state.
4. Doing exports and selects to retrieve what data you can from the problem tablespace.
5. Restoring the entire database from the cold backup taken earlier.
6. Taking the damaged datafile offline.
7. Doing exports and selects to retrieve additional data not salvaged in step 4.
8. Restoring again from the cold backup.
9. Dropping the problem tablespace.
10. Recreating the problem tablespace.
11. Rebuilding the data in the problem tablespace with the data extracted in steps 4 and 7.
Some of these steps can be quite tedious and time-consuming. You may choose to skip a few or even several of the steps depending on how much disk space, tedium, and database down time you are willing to pay in exchange for potentially salvaging more of the lost data.
As we discuss each of the steps in greater detail, we will walk through an example case where a datafile called ordtab03.dbf in tablespace ORDTAB was lost due to a disk crash. This datafile contained many extents of the ORDERS table. The datafile was restored from a hot backup taken July 4, 2004, but some of the archived redo logs between July 4 and the present day have been lost.
Step 1: Backup the Database
The first thing you should do is take a cold backup of whatever datafiles, online redo logs, and control files you currently have. If rebuilding everything from existing backups, then make sure you have a place to keep the backup files handy, as you’ll probably want them more than once. It’s best if you have cold backups. If you’ve just lost one or a few datafiles and the database is still open, make a hot backup of each of the remaining datafiles and save this somewhere (and make sure you keep the archived redo logs generated during and after the hot backup).
In a later step we will be using an undocumented and unsupported Oracle feature in order to make the best of a bad situation. Taking a backup of the database now, before we “cross the line” into unsupported territory, allows us to return to this side of the line in a later step.
After you’ve created the backup, it’s time to work on the database. Before you shut the database down, create a backup control file script:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
This will create a script in your user_dump_dest directory to create a new control file. The name of the file will be similar to that of other trace files—something like [instance_name]_ora_[PID].trc. Rename the file to newcontrol.sql.
Now we want to edit the file. Open it with a text editor such as vi or emacs. There will be about 15 lines at the top of the file with version and connection information. We don’t want these messing up our script, so delete all the lines above the first line beginning with a pound sign (#). (Instead of deleting these lines, you could make them comments by putting a pound sign at the beginning of each.)
Then delete the line that begins “RECOVER DATABASE…” toward the end of the file and save the file.
Step 2: Restore the Lost Datafile and Apply Archived Redo Logs
At this point you should restore the lost datafile from a backup and apply what archived redo logs you have in order to roll the datafile contents forward as far as possible. You will have to stop at the first missing archived redo log. In our example, we restored the ordtab03.dbf datafile from the July 4, 2004 hot backup and applied the archived redo logs that we had available.
If you were to try to open the database normally at this point, you would get an ORA-01589 error:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
If you then tried an ALTER DATABASE OPEN RESETLOGS command, you would get an ORA-01195 error:
ORA-01195: online backup of file %s needs more recovery to be consistent
This is where Oracle is taking its hard-line approach. The datafile restored from the backup was not recovered to a point in time consistent with the other datafiles in the database. Therefore data corruption may exist and Oracle will not let you open the database normally.
Step 3: Set an Undocumented Instance Parameter and Open the Database
At this point we leave the world of standard practice and cross the line into unsupported activity. It’s time to edit the database’s init.ora file or spfile. First, you’ll want to set job_queue_processes to 0 if it’s not already, as you don’t need jobs running while you’re extracting data. Then you need to set the parameter:
_allow_resetlogs_corruption=TRUE
This parameter is a “hidden” or undocumented parameter—one of those which you’re never supposed to use unless told to do so by Oracle Support. We probably should have pointed out earlier that you shouldn’t be doing this entire exercise unless you’ve failed with everything Oracle Support has told you to do. The description of this parameter reads “allow resetlogs even if it will cause corruption”. Some of the caveats to be found on Metalink regarding this parameter include:
Steps as mentioned here beneath are only applicable in a situation that no restore/recovery can be performed of the database in question. Applying beneath steps means that data will be exported from a database being in an inconsistent state (no instance recovery can be performed), this is true for user data as well for the data dictionary.
You should NOT use unsupported parameters without the advice from support.
Since you are using these undocumented parameters without specific instruction from Oracle Support, we can't support this database. If someone did instruct you to set these parameters, then you may want to continue discussion on this issue with that individual.
If you are able to startup this database, it would be only to do a full export and recreate it. If you want to pursue this possibility, please log an itar. This issue cannot be handled through this forum.
That said, if you want to continue, after changing the parameter, then move to the directory where you saved your newcontrol.sql script in the first step. (If you didn’t move the script, it will still be in your user_dump_dest directory.) Connect to the database as SYSDBA and run the newcontrol.sql script.
Your database is now open (though NOT consistent). You can run queries and sometimes everything will appear perfectly normal:
SQL> SELECT COUNT(*) FROM OE.orders;
COUNT(*)
----------
403439
The database is in an inconsistent state and you are skating on thin ice. The above query worked fine because Oracle didn’t need to access any data blocks that were corrupt or referenced inconsistent undo entries. The query could have just as easily failed with an ORA-00600 error such as:
SQL> SELECT COUNT(*) FROM OE.orders;
SELECT COUNT(*) FROM OE.orders
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcfrbd_2], [14],
[19081], [8], [1280], [1280], [], []
Step 4: Do Exports and Selects to Retrieve Data
As we saw in the previous step, some queries will work fine and some will fail right away. Still others will return partial results, quitting when an inconsistency is found:
SQL> SELECT order_id FROM OE.orders
2 WHERE entry_date > TO_DATE ('04-JUL-2004');
ORDER_ID
----------
496103
496104
496105
...
511325
511326
511327
511328
ERROR:
ORA-00600: internal error code, arguments: [kcfrbd_2], [14],
[19081], [8], [1280], [1280], [], []
15225 rows selected.
We can use this ability to “pick around” problem data blocks:
SQL> SELECT order_id FROM OE.orders
2 WHERE order_id > 511400;
ORDER_ID
----------
511401
511402
511403
...
513398
513399
513400
513401
2001 rows selected.
Database exports are now possible too, though some errors will occur:
About to export specified users ...
. exporting object type definitions for user OE
EXP-00090: cannot pin type "OE"."ORDER_ELEMENT_TYPE"
EXP-00056: ORACLE error 22303 encountered
OCI-22303: type "OE"."ORDER_ELEMENT_TYPE" not found
...
. about to export OE's tables via Conventional Path ...
. . exporting table BATCH_JOBSS 4382 rows exported
...
. . exporting table CUSTOMER_TYPES
EXP-00056: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [4146], [45144], [45124], [], [], [], [], []
. . exporting table DEFAULT_VALUES 391 rows exported
Even for the tables with errors, some data will likely be extracted and written to the export file. Moreover you can easily determine which tables you’re able to export all rows from, so you won’t have to make further extraction efforts with them.
Step 5: Restore the Database from Backup
This step, along with the next two, is optional. Together these three steps present another approach that may allow you to retrieve more of your data. Restoring the database from a backup at this point effectively undoes any damage caused by the use of the undocumented _allow_resetlogs_corruption instance parameter. This time through, we will not make any attempt to recover the lost datafile.
Step 6: Take the Damaged Datafile Offline
In this step you take the damaged datafile offline. The purpose here is to get the database to a point where everything is completely consistent, and the data that would be inconsistent is simply deemed unavailable.
This is fairly straightforward:
ALTER DATABASE DATAFILE '/u07/oradata/PRD/ordtab03.dbf' OFFLINE;
Step 7: Do Exports and Selects to Retrieve Additional Data
At this point you may be able to retrieve additional data for salvage that you were not able to get at earlier. For example, you may be able to fetch useful data from indexes belonging to tables that are damaged. If you inadvertently try to access the damaged datafile, you’ll get an ORA-00376 error:
ORA-00376: file 39 cannot be read at this time
ORA-01110: data file 39: '/u07/oradata/PRD/ordtab03.dbf'
Step 8: Restore the Database from Backup
Now you restore the database from backup for the last time. This step officially rolls the database back to a point in time before the use of the undocumented instance parameter, and therefore returns the database to a supported state. Note that if you restored the database from backup in step 5 and have not updated any data in the database since then, you may be able to skip this step.
Step 9: Drop the Problem Tablespace
First you’ll need to determine whether there are any referential integrity constraints from tables outside the problem tablespace which refer to primary or unique keys of tables inside the problem tablespace. You can use a query such as the following:
SELECT CR.constraint_name
FROM dba_constraints CR, dba_constraints CP,
dba_tables TP, dba_tables TR
WHERE CR.r_owner = CP.owner
AND CR.r_constraint_name = CP.constraint_name
AND CR.constraint_type = 'R'
AND CP.constraint_type IN ('P', 'U')
AND CP.table_name = TP.table_name
AND CP.owner = TP.owner
AND CR.table_name = TR.table_name
AND CR.owner = TR.owner
AND TR.tablespace_name <> 'ORDTAB'
AND TP.tablespace_name = 'ORDTAB';
If there were any such constraints, you would need to create scripts to recreate them (if you don’t already have them). If you are using export dumps to rebuild the data (in step 11), the constraints can possibly be restored from the export files.
Drop the tablespace containing the damaged datafile with a statement like:
DROP TABLESPACE ordtab INCLUDING CONTENTS CASCADE CONSTRAINTS;
Step 10: Recreate the Problem Tablespace
Here you simply want to recreate the tablespace so that in the next step you can repopulate it with the data you extracted earlier. You could consult an old export file in order to recreate the tablespace exactly as it was, or you could take this opportunity to switch to a locally managed tablespace, enable the autoallocate feature, adjust storage parameters, and so on.
Step 11: Rebuild the Data in the Problem Tablespace
At this time you are ready to reload the tablespace with the data that you salvaged earlier. If you used the export utility to extract data from some or all tables, you can use import to restore the schema objects and data. If export encountered an error while reading a table, import should still be able to recreate the rows that were successfully extracted before the error occurred. If you used queries to extract data into flat files, then you can use SQL*Loader or any number of other tools to put the data back into the database.
Now you will have a functional database with as much data as you were able to extract from the damaged database. If you are lucky you were able to get almost all of your data. If not, at least you were probably able to get some data and get your database working again.
The first thing you should do immediately after repopulating the tablespace is take a backup of this repaired database. The second thing you should do is implement proper procedures for backing up the database, storing the backups and archived redo logs for appropriate retention periods, and testing the backup/recovery process on a regular basis. Your data is too valuable to risk losing, and your time is too valuable to spend going through steps like these to try to recover some of it.
Conclusion
We have discussed a situation that no DBA should ever be in, and hopefully you will never need to use the information presented here. The scenario addressed here results from not having proper (and tested) backup procedures. This paper has been all about making the best of a bad situation.
The Oracle database is very good about protecting the integrity of your data—making sure you’re able to get all of your data in a time-consistent manner. That applies even if you’ve lost datafiles, as long as you have copies of the datafiles (either from cold or hot backups) and all of the archived redo logs from the time of the oldest datafile copy to the current time (or whatever time you want to recover to).
If you don’t have all of the required files, then there is no way of guaranteeing that all of your data is there and consistent with other data. So if you don’t have the necessary files, Oracle won’t allow you to open the database normally. With the _allow_resetlogs_corruption instance parameter, however, Oracle has given us a tool to recover some of the data if possible. This is a tool that you should hope to never have to use—but if you should need it, you’ll be glad it is there and you know something about how to use it.
In this paper we will present a method for retrieving data from an Oracle database that cannot be opened normally because one or more datafiles is inconsistent with the others. An example of a scenario where you would find yourself in this situation is as follows:
A disk failed on our server and we lost a datafile. We restored the datafile from a hot backup taken a week ago, but it turns out we are missing a few redo logs archived since then. Oracle is complaining because we can't produce the required archived redo logs, and we can't open the database. The datafile in question contains our most important tables. Is there any way we can salvage our data?
Every DBA should know that there is a problem here. The missing archived redo logs contain transactions which affect the data in the database. So it’s a given that you’re going to lose some data, but the question is, “How much?” Oracle takes a hard-line position and will not let you open the database normally because a data integrity issue exists. However, you may be able to retrieve much of your data if you use non-traditional means to get Oracle to drop its hard-line attitude. Retrieving the data that can be salvaged with the understanding that some data will be lost could be a whole lot better than losing all of the data because a subset of it has been corrupted.
In the next section of this paper, we will look at an overview of how you might go about salvaging data from an inconsistent datafile and get the database back up and running properly again. In the succeeding sections of this paper, we’ll dig into each step of the process in greater detail.
Overview
If you’ve lost a datafile that contained only indexes for heap-organized tables, or other data that is easily recreated, then your best bet might be to drop the tablespace and recreate and repopulate it from scratch. But if you’ve lost a datafile that contained important data not easily recreated, and all you have is an old backup of the file without all of the intervening archived redo logs, then you will want to extract what data you can from the problem tablespace, drop the tablespace, and then recreate and repopulate the tablespace.
Although the exact steps will vary depending on the particular situation, the general steps involved are:
1. Taking a cold backup of what you have now.
2. Restoring the lost datafile from a backup and applying the archived redo logs that you do have.
3. Setting an undocumented instance parameter which will allow you to open the database in its current state.
4. Doing exports and selects to retrieve what data you can from the problem tablespace.
5. Restoring the entire database from the cold backup taken earlier.
6. Taking the damaged datafile offline.
7. Doing exports and selects to retrieve additional data not salvaged in step 4.
8. Restoring again from the cold backup.
9. Dropping the problem tablespace.
10. Recreating the problem tablespace.
11. Rebuilding the data in the problem tablespace with the data extracted in steps 4 and 7.
Some of these steps can be quite tedious and time-consuming. You may choose to skip a few or even several of the steps depending on how much disk space, tedium, and database down time you are willing to pay in exchange for potentially salvaging more of the lost data.
As we discuss each of the steps in greater detail, we will walk through an example case where a datafile called ordtab03.dbf in tablespace ORDTAB was lost due to a disk crash. This datafile contained many extents of the ORDERS table. The datafile was restored from a hot backup taken July 4, 2004, but some of the archived redo logs between July 4 and the present day have been lost.
Step 1: Backup the Database
The first thing you should do is take a cold backup of whatever datafiles, online redo logs, and control files you currently have. If rebuilding everything from existing backups, then make sure you have a place to keep the backup files handy, as you’ll probably want them more than once. It’s best if you have cold backups. If you’ve just lost one or a few datafiles and the database is still open, make a hot backup of each of the remaining datafiles and save this somewhere (and make sure you keep the archived redo logs generated during and after the hot backup).
In a later step we will be using an undocumented and unsupported Oracle feature in order to make the best of a bad situation. Taking a backup of the database now, before we “cross the line” into unsupported territory, allows us to return to this side of the line in a later step.
After you’ve created the backup, it’s time to work on the database. Before you shut the database down, create a backup control file script:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
This will create a script in your user_dump_dest directory to create a new control file. The name of the file will be similar to that of other trace files—something like [instance_name]_ora_[PID].trc. Rename the file to newcontrol.sql.
Now we want to edit the file. Open it with a text editor such as vi or emacs. There will be about 15 lines at the top of the file with version and connection information. We don’t want these messing up our script, so delete all the lines above the first line beginning with a pound sign (#). (Instead of deleting these lines, you could make them comments by putting a pound sign at the beginning of each.)
Then delete the line that begins “RECOVER DATABASE…” toward the end of the file and save the file.
Step 2: Restore the Lost Datafile and Apply Archived Redo Logs
At this point you should restore the lost datafile from a backup and apply what archived redo logs you have in order to roll the datafile contents forward as far as possible. You will have to stop at the first missing archived redo log. In our example, we restored the ordtab03.dbf datafile from the July 4, 2004 hot backup and applied the archived redo logs that we had available.
If you were to try to open the database normally at this point, you would get an ORA-01589 error:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
If you then tried an ALTER DATABASE OPEN RESETLOGS command, you would get an ORA-01195 error:
ORA-01195: online backup of file %s needs more recovery to be consistent
This is where Oracle is taking its hard-line approach. The datafile restored from the backup was not recovered to a point in time consistent with the other datafiles in the database. Therefore data corruption may exist and Oracle will not let you open the database normally.
Step 3: Set an Undocumented Instance Parameter and Open the Database
At this point we leave the world of standard practice and cross the line into unsupported activity. It’s time to edit the database’s init.ora file or spfile. First, you’ll want to set job_queue_processes to 0 if it’s not already, as you don’t need jobs running while you’re extracting data. Then you need to set the parameter:
_allow_resetlogs_corruption=TRUE
This parameter is a “hidden” or undocumented parameter—one of those which you’re never supposed to use unless told to do so by Oracle Support. We probably should have pointed out earlier that you shouldn’t be doing this entire exercise unless you’ve failed with everything Oracle Support has told you to do. The description of this parameter reads “allow resetlogs even if it will cause corruption”. Some of the caveats to be found on Metalink regarding this parameter include:
Steps as mentioned here beneath are only applicable in a situation that no restore/recovery can be performed of the database in question. Applying beneath steps means that data will be exported from a database being in an inconsistent state (no instance recovery can be performed), this is true for user data as well for the data dictionary.
You should NOT use unsupported parameters without the advice from support.
Since you are using these undocumented parameters without specific instruction from Oracle Support, we can't support this database. If someone did instruct you to set these parameters, then you may want to continue discussion on this issue with that individual.
If you are able to startup this database, it would be only to do a full export and recreate it. If you want to pursue this possibility, please log an itar. This issue cannot be handled through this forum.
That said, if you want to continue, after changing the parameter, then move to the directory where you saved your newcontrol.sql script in the first step. (If you didn’t move the script, it will still be in your user_dump_dest directory.) Connect to the database as SYSDBA and run the newcontrol.sql script.
Your database is now open (though NOT consistent). You can run queries and sometimes everything will appear perfectly normal:
SQL> SELECT COUNT(*) FROM OE.orders;
COUNT(*)
----------
403439
The database is in an inconsistent state and you are skating on thin ice. The above query worked fine because Oracle didn’t need to access any data blocks that were corrupt or referenced inconsistent undo entries. The query could have just as easily failed with an ORA-00600 error such as:
SQL> SELECT COUNT(*) FROM OE.orders;
SELECT COUNT(*) FROM OE.orders
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcfrbd_2], [14],
[19081], [8], [1280], [1280], [], []
Step 4: Do Exports and Selects to Retrieve Data
As we saw in the previous step, some queries will work fine and some will fail right away. Still others will return partial results, quitting when an inconsistency is found:
SQL> SELECT order_id FROM OE.orders
2 WHERE entry_date > TO_DATE ('04-JUL-2004');
ORDER_ID
----------
496103
496104
496105
...
511325
511326
511327
511328
ERROR:
ORA-00600: internal error code, arguments: [kcfrbd_2], [14],
[19081], [8], [1280], [1280], [], []
15225 rows selected.
We can use this ability to “pick around” problem data blocks:
SQL> SELECT order_id FROM OE.orders
2 WHERE order_id > 511400;
ORDER_ID
----------
511401
511402
511403
...
513398
513399
513400
513401
2001 rows selected.
Database exports are now possible too, though some errors will occur:
About to export specified users ...
. exporting object type definitions for user OE
EXP-00090: cannot pin type "OE"."ORDER_ELEMENT_TYPE"
EXP-00056: ORACLE error 22303 encountered
OCI-22303: type "OE"."ORDER_ELEMENT_TYPE" not found
...
. about to export OE's tables via Conventional Path ...
. . exporting table BATCH_JOBSS 4382 rows exported
...
. . exporting table CUSTOMER_TYPES
EXP-00056: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [4146], [45144], [45124], [], [], [], [], []
. . exporting table DEFAULT_VALUES 391 rows exported
Even for the tables with errors, some data will likely be extracted and written to the export file. Moreover you can easily determine which tables you’re able to export all rows from, so you won’t have to make further extraction efforts with them.
Step 5: Restore the Database from Backup
This step, along with the next two, is optional. Together these three steps present another approach that may allow you to retrieve more of your data. Restoring the database from a backup at this point effectively undoes any damage caused by the use of the undocumented _allow_resetlogs_corruption instance parameter. This time through, we will not make any attempt to recover the lost datafile.
Step 6: Take the Damaged Datafile Offline
In this step you take the damaged datafile offline. The purpose here is to get the database to a point where everything is completely consistent, and the data that would be inconsistent is simply deemed unavailable.
This is fairly straightforward:
ALTER DATABASE DATAFILE '/u07/oradata/PRD/ordtab03.dbf' OFFLINE;
Step 7: Do Exports and Selects to Retrieve Additional Data
At this point you may be able to retrieve additional data for salvage that you were not able to get at earlier. For example, you may be able to fetch useful data from indexes belonging to tables that are damaged. If you inadvertently try to access the damaged datafile, you’ll get an ORA-00376 error:
ORA-00376: file 39 cannot be read at this time
ORA-01110: data file 39: '/u07/oradata/PRD/ordtab03.dbf'
Step 8: Restore the Database from Backup
Now you restore the database from backup for the last time. This step officially rolls the database back to a point in time before the use of the undocumented instance parameter, and therefore returns the database to a supported state. Note that if you restored the database from backup in step 5 and have not updated any data in the database since then, you may be able to skip this step.
Step 9: Drop the Problem Tablespace
First you’ll need to determine whether there are any referential integrity constraints from tables outside the problem tablespace which refer to primary or unique keys of tables inside the problem tablespace. You can use a query such as the following:
SELECT CR.constraint_name
FROM dba_constraints CR, dba_constraints CP,
dba_tables TP, dba_tables TR
WHERE CR.r_owner = CP.owner
AND CR.r_constraint_name = CP.constraint_name
AND CR.constraint_type = 'R'
AND CP.constraint_type IN ('P', 'U')
AND CP.table_name = TP.table_name
AND CP.owner = TP.owner
AND CR.table_name = TR.table_name
AND CR.owner = TR.owner
AND TR.tablespace_name <> 'ORDTAB'
AND TP.tablespace_name = 'ORDTAB';
If there were any such constraints, you would need to create scripts to recreate them (if you don’t already have them). If you are using export dumps to rebuild the data (in step 11), the constraints can possibly be restored from the export files.
Drop the tablespace containing the damaged datafile with a statement like:
DROP TABLESPACE ordtab INCLUDING CONTENTS CASCADE CONSTRAINTS;
Step 10: Recreate the Problem Tablespace
Here you simply want to recreate the tablespace so that in the next step you can repopulate it with the data you extracted earlier. You could consult an old export file in order to recreate the tablespace exactly as it was, or you could take this opportunity to switch to a locally managed tablespace, enable the autoallocate feature, adjust storage parameters, and so on.
Step 11: Rebuild the Data in the Problem Tablespace
At this time you are ready to reload the tablespace with the data that you salvaged earlier. If you used the export utility to extract data from some or all tables, you can use import to restore the schema objects and data. If export encountered an error while reading a table, import should still be able to recreate the rows that were successfully extracted before the error occurred. If you used queries to extract data into flat files, then you can use SQL*Loader or any number of other tools to put the data back into the database.
Now you will have a functional database with as much data as you were able to extract from the damaged database. If you are lucky you were able to get almost all of your data. If not, at least you were probably able to get some data and get your database working again.
The first thing you should do immediately after repopulating the tablespace is take a backup of this repaired database. The second thing you should do is implement proper procedures for backing up the database, storing the backups and archived redo logs for appropriate retention periods, and testing the backup/recovery process on a regular basis. Your data is too valuable to risk losing, and your time is too valuable to spend going through steps like these to try to recover some of it.
Conclusion
We have discussed a situation that no DBA should ever be in, and hopefully you will never need to use the information presented here. The scenario addressed here results from not having proper (and tested) backup procedures. This paper has been all about making the best of a bad situation.
The Oracle database is very good about protecting the integrity of your data—making sure you’re able to get all of your data in a time-consistent manner. That applies even if you’ve lost datafiles, as long as you have copies of the datafiles (either from cold or hot backups) and all of the archived redo logs from the time of the oldest datafile copy to the current time (or whatever time you want to recover to).
If you don’t have all of the required files, then there is no way of guaranteeing that all of your data is there and consistent with other data. So if you don’t have the necessary files, Oracle won’t allow you to open the database normally. With the _allow_resetlogs_corruption instance parameter, however, Oracle has given us a tool to recover some of the data if possible. This is a tool that you should hope to never have to use—but if you should need it, you’ll be glad it is there and you know something about how to use it.
Tuesday, November 18, 2008
TUNING AN IMPORT WITH WAIT EVENTS
HISTORY AND IMPLEMENTATION
Previous performance tuning methods used ratio-based tuning to identify contention in the database. Ratio-based statistic gathering is very difficult to use accurately in resolving contention issues. Oracle Corp. recognized this problem and introduced the Wait Event interface to help one rapidly diagnose performance problems with processes in Oracle databases. This started with Oracle 7.0.12 and has been in place ever since. This interface lets a DBA determine exactly what a process is waiting for, and for how long it has been waiting. There is no need to gather various performance ratios and “guess” the cause of the contention. The wait event interface shows you exactly what the process is waiting on.
There are three primary views that you need to use to find out what processes are waiting for. The V$SYSTEM_EVENT view shows what events all processes have been waiting on since instance startup. The V$SESSION_EVENT view shows what events a specific process has been waiting on. The V$SESSION_WAIT gives detailed information about what a process is currently waiting on. The wait events are described in detail in the Oracle Reference guide.
THE PROBLEM
I had an import process that was taking an enormous amount of time to complete. This process took over 60 hours to import the entire schema. Most of this time, 48 hours worth, was spent importing just one table! This table only has two columns, one of which is a LONG datatype. This table also has over 21 million rows of data. It is painfully clear that reducing the amount of time to import just this one table will significantly reduce the overall time to import the entire schema.
THE APPROACH
In order to understand what needs to be tuned, I had to determine where the bottleneck resided. I started an import of just the table in question. After it had been running for a long time, I captured this snapshot of the V$SESSION_EVENT for my import session.
SQL> select event,total_waits,time_waited,average_wait
2 from v$session_event where sid=10
3 order by total_waits desc;
Note: The figures for the PCT_TOT_WAIT column were later calculated by hand and added to the query results. They were not part of the original query.
This query shows me everything that my import process was waiting for, how may times it waited for that event to occur, how long it waited, the average wait for each occurrence of the event. I also showed the percentage of time a specific event was in relation to the total amount of time waiting on all events.
Note: In order for the TIME_WAITED column to be populated with data, you must have TIMED_STATISTICS=TRUE. I suggest that you have this parameter set in your INIT.ORA for all of your databases. The impact on the database to capture timed statistics is negligible especially compared to the benefits!
From this query, it is easy to see that we have three major culprits here! The “SQL*Net message from/to client” events are called idle waits. We generally do not tune these wait events. Waiting on “SQL*Net message from client” means that our process is just waiting for the user to return some data. For most applications, we are waiting on the user to press the ENTER key. We cannot control this wait event in most situations. In our particular case, this event is signaled when Oracle is waiting for the import program to read the next row of data from the dump file. Ideally, if my process has to wait, I’d like to see my process waiting more and more on the import program. As we tune this process, we’ll see the percentage of the total wait for this event near 90% as opposed to the 45% it is currently at.
The “log buffer space” event is the next largest event (in terms of percentage of total wait time). We will begin by tuning this event. According to the Oracle documentation, “Waiting for space in the log buffer because the session is writing data into the log buffer faster than LGWR can write it out. Consider making the log buffer bigger if it is small, or moving the log files to faster disks such as striped disks.” There it is right there in plain English! Make the log buffer bigger if it is too small. Examining my current SGA reveals that my redo log buffer cache is sized with LOG_BUFFER=32000. My log buffer is only 32k! This is way too small. I set my log buffer to 1M and bounced the database. I now repeat the process. With the newly sized log buffer space, I restart my import process. Some time into the import process, I examine the wait events again.
Notice how the “log buffer space” wait event only contributes 1.46% of the total time waited. This is a significant reduction. Also notice my “SQL*Net message from client” event is climbing higher to 76% of the total wait time (up from 45%). The big culprits are now the “db file sequential read”, “write complete waits”, and “log file switch completion”. I now return to the Oracle documentation for description of these wait events.
db file sequential read – “The session waits while a sequential read from the database is performed. This event is also used for rebuilding the controlfile, dumping datafile headers, and getting the database file headers.” This event normally occurs when using an index to access a table.
write complete waits – “The session waits for a buffer to be written. The write is caused by normal aging or by a cross-instance call.”
log file switch completion – “Waiting for a log switch to complete.”
Let’s think about these wait events for a minute. I am importing a very big table. This import fills the buffer cache with dirty blocks as the rows are inserted into the table. This insertion process also generates redo information, which in turn fills up the log files. Since the buffer cache is being filled by these inserted rows, any use of a table that is accessed by an index will unlikely hit the table and index blocks in the buffer cache. Those blocks stand a good chance of being aged out. The “write complete waits” event occurs when dirty buffer blocks need to be flushed to make room for more information in the buffer cache. Taking both the “db file sequential read” and “write complete waits” events into account, it is obvious to me that I have a buffer cache that is too small for my operation. Again examining my SGA, I find that my buffer cache is only 200 database blocks. I double the buffer cache to 400 database blocks, bounce the database, and restart the import process. Once again, I examine my wait events to see what is causing the new bottleneck.
Notice how the “db file sequential read” event went from 8.9% of the total wait time down to 0%! The “write complete waits” event also fell from 3.88% to 2.75%. By tuning DBWR and disk writes, I can get this to fall even more! I can also tune the LGWR and disk writes to get the “log file switch completion” event to be less of a burden on my process. At this point though, I decide that I am finished. I notice that the “SQL*Net message from client” event is over 87% of the total wait time. In fact, the two idle events together comprise almost 90% of my total wait time.
I have gone through two iterations of examining the wait events and adjusting SGA parameters in an attempt to improve my performance. I decide that it is now time to see if my hard work has paid off. I restart the import and time the process. I find that the singular table that previously took 48 hours to import is now imported in only 4 hours! The entire schema, which previously took 60 hours, is now imported in about 16 hours. This is quite the savings considering the process I am trying to tune. For my particular problem, I decide that a 16-hour import window is more than acceptable. I decided to quit right here. With more work and diligence, I could have tuned the other two wait events and cut down my import time even more.
A FINAL NOTE
In the course of tuning this import process with Oracle’s Wait Event interface, I came across a number of wait events that I had never encountered before. The Oracle documentation, specifically the Oracle Reference guide, Appendix A, gives descriptions of each wait event. As you learn to tune processes with these methods, you will undoubtedly run into difficulties deciphering the events and how to resolve them. A thorough and complete understanding of the Oracle RDBMS architecture is necessary to tune with this methodology. This is required so that you will know what to change for a specific event. The Oracle Concepts guide as well as other Oracle documentation is a great place to start understanding the system architecture.
CONCLUSION
Hopefully, this paper has shown how easy it can be to use the Oracle Wait Event interface to diagnose process bottlenecks and how to tune them. This paper is a case study in one of my first attempts at using wait events to tune a specific process. More papers of this topic are sure to follow in the near future.
Previous performance tuning methods used ratio-based tuning to identify contention in the database. Ratio-based statistic gathering is very difficult to use accurately in resolving contention issues. Oracle Corp. recognized this problem and introduced the Wait Event interface to help one rapidly diagnose performance problems with processes in Oracle databases. This started with Oracle 7.0.12 and has been in place ever since. This interface lets a DBA determine exactly what a process is waiting for, and for how long it has been waiting. There is no need to gather various performance ratios and “guess” the cause of the contention. The wait event interface shows you exactly what the process is waiting on.
There are three primary views that you need to use to find out what processes are waiting for. The V$SYSTEM_EVENT view shows what events all processes have been waiting on since instance startup. The V$SESSION_EVENT view shows what events a specific process has been waiting on. The V$SESSION_WAIT gives detailed information about what a process is currently waiting on. The wait events are described in detail in the Oracle Reference guide.
THE PROBLEM
I had an import process that was taking an enormous amount of time to complete. This process took over 60 hours to import the entire schema. Most of this time, 48 hours worth, was spent importing just one table! This table only has two columns, one of which is a LONG datatype. This table also has over 21 million rows of data. It is painfully clear that reducing the amount of time to import just this one table will significantly reduce the overall time to import the entire schema.
THE APPROACH
In order to understand what needs to be tuned, I had to determine where the bottleneck resided. I started an import of just the table in question. After it had been running for a long time, I captured this snapshot of the V$SESSION_EVENT for my import session.
SQL> select event,total_waits,time_waited,average_wait
2 from v$session_event where sid=10
3 order by total_waits desc;
Note: The figures for the PCT_TOT_WAIT column were later calculated by hand and added to the query results. They were not part of the original query.
This query shows me everything that my import process was waiting for, how may times it waited for that event to occur, how long it waited, the average wait for each occurrence of the event. I also showed the percentage of time a specific event was in relation to the total amount of time waiting on all events.
Note: In order for the TIME_WAITED column to be populated with data, you must have TIMED_STATISTICS=TRUE. I suggest that you have this parameter set in your INIT.ORA for all of your databases. The impact on the database to capture timed statistics is negligible especially compared to the benefits!
From this query, it is easy to see that we have three major culprits here! The “SQL*Net message from/to client” events are called idle waits. We generally do not tune these wait events. Waiting on “SQL*Net message from client” means that our process is just waiting for the user to return some data. For most applications, we are waiting on the user to press the ENTER key. We cannot control this wait event in most situations. In our particular case, this event is signaled when Oracle is waiting for the import program to read the next row of data from the dump file. Ideally, if my process has to wait, I’d like to see my process waiting more and more on the import program. As we tune this process, we’ll see the percentage of the total wait for this event near 90% as opposed to the 45% it is currently at.
The “log buffer space” event is the next largest event (in terms of percentage of total wait time). We will begin by tuning this event. According to the Oracle documentation, “Waiting for space in the log buffer because the session is writing data into the log buffer faster than LGWR can write it out. Consider making the log buffer bigger if it is small, or moving the log files to faster disks such as striped disks.” There it is right there in plain English! Make the log buffer bigger if it is too small. Examining my current SGA reveals that my redo log buffer cache is sized with LOG_BUFFER=32000. My log buffer is only 32k! This is way too small. I set my log buffer to 1M and bounced the database. I now repeat the process. With the newly sized log buffer space, I restart my import process. Some time into the import process, I examine the wait events again.
Notice how the “log buffer space” wait event only contributes 1.46% of the total time waited. This is a significant reduction. Also notice my “SQL*Net message from client” event is climbing higher to 76% of the total wait time (up from 45%). The big culprits are now the “db file sequential read”, “write complete waits”, and “log file switch completion”. I now return to the Oracle documentation for description of these wait events.
db file sequential read – “The session waits while a sequential read from the database is performed. This event is also used for rebuilding the controlfile, dumping datafile headers, and getting the database file headers.” This event normally occurs when using an index to access a table.
write complete waits – “The session waits for a buffer to be written. The write is caused by normal aging or by a cross-instance call.”
log file switch completion – “Waiting for a log switch to complete.”
Let’s think about these wait events for a minute. I am importing a very big table. This import fills the buffer cache with dirty blocks as the rows are inserted into the table. This insertion process also generates redo information, which in turn fills up the log files. Since the buffer cache is being filled by these inserted rows, any use of a table that is accessed by an index will unlikely hit the table and index blocks in the buffer cache. Those blocks stand a good chance of being aged out. The “write complete waits” event occurs when dirty buffer blocks need to be flushed to make room for more information in the buffer cache. Taking both the “db file sequential read” and “write complete waits” events into account, it is obvious to me that I have a buffer cache that is too small for my operation. Again examining my SGA, I find that my buffer cache is only 200 database blocks. I double the buffer cache to 400 database blocks, bounce the database, and restart the import process. Once again, I examine my wait events to see what is causing the new bottleneck.
Notice how the “db file sequential read” event went from 8.9% of the total wait time down to 0%! The “write complete waits” event also fell from 3.88% to 2.75%. By tuning DBWR and disk writes, I can get this to fall even more! I can also tune the LGWR and disk writes to get the “log file switch completion” event to be less of a burden on my process. At this point though, I decide that I am finished. I notice that the “SQL*Net message from client” event is over 87% of the total wait time. In fact, the two idle events together comprise almost 90% of my total wait time.
I have gone through two iterations of examining the wait events and adjusting SGA parameters in an attempt to improve my performance. I decide that it is now time to see if my hard work has paid off. I restart the import and time the process. I find that the singular table that previously took 48 hours to import is now imported in only 4 hours! The entire schema, which previously took 60 hours, is now imported in about 16 hours. This is quite the savings considering the process I am trying to tune. For my particular problem, I decide that a 16-hour import window is more than acceptable. I decided to quit right here. With more work and diligence, I could have tuned the other two wait events and cut down my import time even more.
A FINAL NOTE
In the course of tuning this import process with Oracle’s Wait Event interface, I came across a number of wait events that I had never encountered before. The Oracle documentation, specifically the Oracle Reference guide, Appendix A, gives descriptions of each wait event. As you learn to tune processes with these methods, you will undoubtedly run into difficulties deciphering the events and how to resolve them. A thorough and complete understanding of the Oracle RDBMS architecture is necessary to tune with this methodology. This is required so that you will know what to change for a specific event. The Oracle Concepts guide as well as other Oracle documentation is a great place to start understanding the system architecture.
CONCLUSION
Hopefully, this paper has shown how easy it can be to use the Oracle Wait Event interface to diagnose process bottlenecks and how to tune them. This paper is a case study in one of my first attempts at using wait events to tune a specific process. More papers of this topic are sure to follow in the near future.
Monday, November 17, 2008
SGA_MAX_SIZE AND GRANULES
INTRODUCTION
Oracle 9i introduced many new features to make the Database Administrator’s life a little easier. One of these new features includes the capability to dynamically change initialization parameters that make up the System Global Area (SGA) without having to restart the database. Previously, changes to parameters that affected the SGA necessitated a restart of the database contributing to downtime. While much information has been written detailing how to change SGA parameters, there is not much information about the SGA_MAX_SIZE parameter, how it affects dynamic resizing of the SGA and how “granules” affect the process. This paper attempts to shed some light on this subject.
SGA_MAX_SIZE
When I first started playing with Oracle 9i, one of the first things I attempted to do was to dynamically change one of the INIT.ORA parameters associated with the SGA. I immediately ran into a problem as can be seen in the following example:
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 117440512
ORA9I SQL> alter system set shared_pool_size=200M;
alter system set shared_pool_size=200M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
Above, we can see that the SHARED_POOL_SIZE parameter is roughly 100M. When I tried to increase this parameter to 200M, I received the ORA-2097 and ORA-4033 errors. But according to the documentation, I should be able to resize these without any problems! So what is going on here? I have enough memory in the system, so why can’t I increase my Shared Pool? In my tests, I discovered that I could always size things smaller. Let’s look at the next example.
ORA9I SQL> alter system set shared_pool_size=75M;
System altered.
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 83886080
Here, we can see that we could size the SGA memory structure to a smaller value, but we could not size it to a larger value. Why is this the case?
Note: An astute reader may notice that the new Shared Pool size is not 75M. We’ll get into the reasons for this later in the paper.
The reason we could not grow bigger lies with a new parameter called the SGA_MAX_SIZE. This parameter defines the maximum size the SGA can become. The entire SGA must fit into memory determined by this parameter. The default value for this parameter is the “Initial size of SGA at startup”. If I look at the system when I first start the database, I can verify that this is true.
ORA9I SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
sga_max_size big integer 235701300
ORA9I SQL> show sga
Total System Global Area 235701300 bytes
Fixed Size 279604 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 540672 bytes
Here, I can see that the value for SGA_MAX_SIZE is exactly the total size of my SGA! If this is my ceiling, then it is clearly impossible for me to increase any component of the SGA unless I make some other component smaller first! If you try to size any component larger such that the total size of the SGA will become greater than SGA_MAX_SIZE, then you will receive the ORA-2097 and ORA-4033 errors as we’ve already seen. If I want to be able to modify SGA components to a larger value, I must set SGA_MAX_SIZE to a larger value as well. Unfortunately, SGA_MAX_SIZE cannot be changed dynamically. So we’ll have to change it manually and bounce the database.
I was using Oracle 9i’s new SPFILE to hold my parameters. First, I created a PFILE from this SPFILE and then I shutdown the database.
ORA9I SQL> create pfile from spfile;
File created.
ORA9I SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
After this, I removed the old SPFILE with an OS command. I then used a text editor to manually insert the SGA_MAX_SIZE parameter into my INIT.ORA file. The database was then started.
ORA9I SQL> startup
ORACLE instance started.
Total System Global Area 537691548 bytes
Fixed Size 279964 bytes
Variable Size 469762048 bytes
Database Buffers 67108864 bytes
Redo Buffers 540672 bytes
Database mounted.
Database opened.
ORA9I SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
sga_max_size big integer 537691548
Notice now that the SGA_MAX_SIZE is defined. Now let’s test to see if we can increase our Shared Pool.
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 83886080
ORA9I SQL> alter system set shared_pool_size=250M scope=memory;
System altered.
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 268435456
Initially, we can see that the Shared Pool is 80M. I then dynamically changed the Shared Pool to 250M in size. Also notice that I added the clause SCOPE=MEMORY to my ALTER SYSTEM command. This forces the change to happen only to the existing SGA instance and not be changed in the SPFILE for future use.
One other thing to note is the total SGA size after we set SGA_MAX_SIZE.
ORA9I SQL> show sga
Total System Global Area 537691548 bytes
Fixed Size 279964 bytes
Variable Size 469762048 bytes
Database Buffers 67108864 bytes
Redo Buffers 540672 bytes
We can see that our total SGA size is 500M. When you set SGA_MAX_SIZE, Oracle allocates that much memory whether you are using it or not. So I’ve allocated 500M of memory when I initially was only using roughly 250M of it. But this let me dynamically increase any of my SGA components up to 500M if I needed to. It is not a good idea to set SGA_MAX_SIZE to 1GB “just in case”. If you never use all 1GB of allocated memory, then you are just wasting memory space. So set SGA_MAX_SIZE to a value that you may need to use in the future. For databases that support 24x7 operations, it may be more advantageous to waste this space to have the ability to increase SGA components in the future. There is a trade-off, which must be weighed when deciding how much memory above current usage to allocate to SGA_MAX_SIZE.
GRANULES
In order to help manage the ability to dynamically change SGA parameters, Oracle introduced the concept of a granule. A granule is “ a unit of contiguous virtual memory”. SGA components in Oracle 9i are now based on the granule size. If your total SGA is less than 128 MB in size, the granule size is 4MB per granule. Otherwise, the granule size is 16MB in size.
Oracle uses these granules similar to the way that Locally Managed Tablespaces now use uniform extent sizes to reduce free space fragmentation. Without these granules, it is possible that dynamic resizing of SGA components would result in fragmented free space in the SGA, where each fragment is too small to be used.
Let’s look at a granule in use. We’ll dynamically change our Shared Pool and examine the results.
ORA9I SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
sga_max_size big integer 537691548
ORA9I SQL> alter system set shared_pool_size=195M scope=memory;
System altered.
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 218103808
ORA9I SQL> select 218103808/1024/1024 as s_pool_megs from dual;
S_POOL_MEGS
-----------
208
ORA9I SQL> select 208/16 as num_granules from dual;
NUM_GRANULES
------------
13
We can see that SGA_MAX_SIZE is 500MB. Since this is larger than 128MB, the granule size for our instance will be 16MB. Then, I purposely changed the Shared Pool to be 195MB, which is less than an integer multiple of the granule size. I queried the database to determine the “real” shared pool size. Dividing by 1,024 twice yields 208MB for the Shared Pool. This is higher than the 195MB I specified. Why? This is because Oracle has to round up to the nearest granule boundary. You can see from the final query that 208MB is an integer multiple of the 16MB granule size.
CONCLUSION
Hopefully, after reading this paper, you’ll have a better understanding of how to use SGA_MAX_SIZE to adequately size your SGA for dynamic reallocation of components. You should also have a better understanding how Oracle uses granules to stop fragmentation of free space in the SGA and how dynamically changing SGA component parameters uses the granule size to determine the new component size. Using these two concepts, you will be able to understand how to configure your Oracle 9i database for dynamic SGA growth and how Oracle internally manages that growth.
Oracle 9i introduced many new features to make the Database Administrator’s life a little easier. One of these new features includes the capability to dynamically change initialization parameters that make up the System Global Area (SGA) without having to restart the database. Previously, changes to parameters that affected the SGA necessitated a restart of the database contributing to downtime. While much information has been written detailing how to change SGA parameters, there is not much information about the SGA_MAX_SIZE parameter, how it affects dynamic resizing of the SGA and how “granules” affect the process. This paper attempts to shed some light on this subject.
SGA_MAX_SIZE
When I first started playing with Oracle 9i, one of the first things I attempted to do was to dynamically change one of the INIT.ORA parameters associated with the SGA. I immediately ran into a problem as can be seen in the following example:
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 117440512
ORA9I SQL> alter system set shared_pool_size=200M;
alter system set shared_pool_size=200M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
Above, we can see that the SHARED_POOL_SIZE parameter is roughly 100M. When I tried to increase this parameter to 200M, I received the ORA-2097 and ORA-4033 errors. But according to the documentation, I should be able to resize these without any problems! So what is going on here? I have enough memory in the system, so why can’t I increase my Shared Pool? In my tests, I discovered that I could always size things smaller. Let’s look at the next example.
ORA9I SQL> alter system set shared_pool_size=75M;
System altered.
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 83886080
Here, we can see that we could size the SGA memory structure to a smaller value, but we could not size it to a larger value. Why is this the case?
Note: An astute reader may notice that the new Shared Pool size is not 75M. We’ll get into the reasons for this later in the paper.
The reason we could not grow bigger lies with a new parameter called the SGA_MAX_SIZE. This parameter defines the maximum size the SGA can become. The entire SGA must fit into memory determined by this parameter. The default value for this parameter is the “Initial size of SGA at startup”. If I look at the system when I first start the database, I can verify that this is true.
ORA9I SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
sga_max_size big integer 235701300
ORA9I SQL> show sga
Total System Global Area 235701300 bytes
Fixed Size 279604 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 540672 bytes
Here, I can see that the value for SGA_MAX_SIZE is exactly the total size of my SGA! If this is my ceiling, then it is clearly impossible for me to increase any component of the SGA unless I make some other component smaller first! If you try to size any component larger such that the total size of the SGA will become greater than SGA_MAX_SIZE, then you will receive the ORA-2097 and ORA-4033 errors as we’ve already seen. If I want to be able to modify SGA components to a larger value, I must set SGA_MAX_SIZE to a larger value as well. Unfortunately, SGA_MAX_SIZE cannot be changed dynamically. So we’ll have to change it manually and bounce the database.
I was using Oracle 9i’s new SPFILE to hold my parameters. First, I created a PFILE from this SPFILE and then I shutdown the database.
ORA9I SQL> create pfile from spfile;
File created.
ORA9I SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
After this, I removed the old SPFILE with an OS command. I then used a text editor to manually insert the SGA_MAX_SIZE parameter into my INIT.ORA file. The database was then started.
ORA9I SQL> startup
ORACLE instance started.
Total System Global Area 537691548 bytes
Fixed Size 279964 bytes
Variable Size 469762048 bytes
Database Buffers 67108864 bytes
Redo Buffers 540672 bytes
Database mounted.
Database opened.
ORA9I SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
sga_max_size big integer 537691548
Notice now that the SGA_MAX_SIZE is defined. Now let’s test to see if we can increase our Shared Pool.
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 83886080
ORA9I SQL> alter system set shared_pool_size=250M scope=memory;
System altered.
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 268435456
Initially, we can see that the Shared Pool is 80M. I then dynamically changed the Shared Pool to 250M in size. Also notice that I added the clause SCOPE=MEMORY to my ALTER SYSTEM command. This forces the change to happen only to the existing SGA instance and not be changed in the SPFILE for future use.
One other thing to note is the total SGA size after we set SGA_MAX_SIZE.
ORA9I SQL> show sga
Total System Global Area 537691548 bytes
Fixed Size 279964 bytes
Variable Size 469762048 bytes
Database Buffers 67108864 bytes
Redo Buffers 540672 bytes
We can see that our total SGA size is 500M. When you set SGA_MAX_SIZE, Oracle allocates that much memory whether you are using it or not. So I’ve allocated 500M of memory when I initially was only using roughly 250M of it. But this let me dynamically increase any of my SGA components up to 500M if I needed to. It is not a good idea to set SGA_MAX_SIZE to 1GB “just in case”. If you never use all 1GB of allocated memory, then you are just wasting memory space. So set SGA_MAX_SIZE to a value that you may need to use in the future. For databases that support 24x7 operations, it may be more advantageous to waste this space to have the ability to increase SGA components in the future. There is a trade-off, which must be weighed when deciding how much memory above current usage to allocate to SGA_MAX_SIZE.
GRANULES
In order to help manage the ability to dynamically change SGA parameters, Oracle introduced the concept of a granule. A granule is “ a unit of contiguous virtual memory”. SGA components in Oracle 9i are now based on the granule size. If your total SGA is less than 128 MB in size, the granule size is 4MB per granule. Otherwise, the granule size is 16MB in size.
Oracle uses these granules similar to the way that Locally Managed Tablespaces now use uniform extent sizes to reduce free space fragmentation. Without these granules, it is possible that dynamic resizing of SGA components would result in fragmented free space in the SGA, where each fragment is too small to be used.
Let’s look at a granule in use. We’ll dynamically change our Shared Pool and examine the results.
ORA9I SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
sga_max_size big integer 537691548
ORA9I SQL> alter system set shared_pool_size=195M scope=memory;
System altered.
ORA9I SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 218103808
ORA9I SQL> select 218103808/1024/1024 as s_pool_megs from dual;
S_POOL_MEGS
-----------
208
ORA9I SQL> select 208/16 as num_granules from dual;
NUM_GRANULES
------------
13
We can see that SGA_MAX_SIZE is 500MB. Since this is larger than 128MB, the granule size for our instance will be 16MB. Then, I purposely changed the Shared Pool to be 195MB, which is less than an integer multiple of the granule size. I queried the database to determine the “real” shared pool size. Dividing by 1,024 twice yields 208MB for the Shared Pool. This is higher than the 195MB I specified. Why? This is because Oracle has to round up to the nearest granule boundary. You can see from the final query that 208MB is an integer multiple of the 16MB granule size.
CONCLUSION
Hopefully, after reading this paper, you’ll have a better understanding of how to use SGA_MAX_SIZE to adequately size your SGA for dynamic reallocation of components. You should also have a better understanding how Oracle uses granules to stop fragmentation of free space in the SGA and how dynamically changing SGA component parameters uses the granule size to determine the new component size. Using these two concepts, you will be able to understand how to configure your Oracle 9i database for dynamic SGA growth and how Oracle internally manages that growth.
ORACLE BASIC FEATURES
TOPICS
1. Miscellaneous
2. Oracle-Managed Files
3. Deprecation of SVRMGR and CONNECT INTERNAL
4. Quiescing the database
5. Suspending and Resuming the Database
6. UNDO Tablespaces
7. Automatic Segment Space Management
8. Default Temporary Tablespaces
9. Automatically Delete Datafiles
10. Dynamic SGA Sizing
11. Index Skip Scans
12. Multi-table Inserts
13. Merge SQL Statement
14. Resumable Transactions
15. Grouping Sets
16. External Tables
17. ANSI/ISO SQL99 Compliant Joins
18. Flashback Query
19. Multiple Block Sizes
20. Cached Execution Plans
21. Cache Advice
22. Server Parameter File
23. Monitoring Index Usage
24. Log Miner
25. DBMS_METADATA
26. DBMS_REDEFINITION
27. Time Data
28. List Partitioning
29. PGA Working Memory Management
30. RMAN Improvements
31. ANSI CASE Support
32. Resource Manager Improvements
33. Automatically Gathering Statistics
34. Export/Import New Features
MISCELLANEOUS
Why did this section come first? It makes more sense for a “catch-all” section to appear at the end of this document. I only put this section first just for formatting reasons. That’s it! The material in this section does not require reading of other sections. So feel free to read on.
MORE ARCHIVE DESTINATIONS
The number of archive destinations, controlled by LOG_ARCHIVE_DEST_n parameter will now let you specify up to 10 destinations (up from 5 in 8i).
TRANSACTION NAMING
Beginning with Oracle 9i, you can now supply a name for your transactions. This is really helpful with resolving in-doubt, distributed transactions as the transaction name will appear in error messages and the alert log.
ORA9I SQL> set transaction name 'This is my first transaction' ;
Transaction set.
DBCA CHANGES
The Oracle Database Configuration Assistant (DBCA) has changed in Oracle 9i. You can now specify which tablespaces to create, where to individually locate all datafiles, use and create templates for database creation, run custom scripts after database creation, and modify user passwords at database creation.
CONTROLLING ARCHIVE LAG
An online redo log switches when it becomes full. In certain environments this may not be desirable. This is especially true with Standby Databases. We want to periodically apply redo to the standby database. And we want to control that period. If the online redo log has not filled up in the past 30 minutes, we may still want to apply that redo to the standby database to keep it as current as possible. The ARCHIVE_LOG_TARGET initialization parameter defines how many seconds pass before a log switch is forced. If there has been redo and the defined number of seconds has passed, then a log switch takes place. The default is zero which turns off this feature.
REAL APPLICATION CLUSTERS
Oracle 9i has a product called Real Application Clusters, which is the replacement for Oracle Parallel Server. This product provides out-of-the-box, high-availability, linear-scaling performance for Oracle databases. This is done without redesigning the current application. Real Application Clusters makes big use of Cache Fusion, the ability to share the buffer cache between instances.
DATA GUARD
Oracle 9i introduces Oracle Data Guard for standby databases. Oracle Data Guard is a suite of components designed to make standby databases easier to manage, more automated, and provide better stability of data through Zero Data Loss using a physical standby database. Delayed mode lets a DBA intervene before data is applied to the standby database in the event that the user has logically corrupted data.
COMPLETE ONLINE INDEX OPERATIONS
With Oracle 9i, one can now perform any index operations with the ONLINE keyword. This improves availability of schema objects during database maintenance.
ORACLE-MANAGED FILES
In previous Oracle versions, the only time the RDBMS software attempted to manage any datafiles was on initial creation of tablespaces, online redo logs, control files, etc. If a tablespace was dropped, it was up to the DBA to manually remove the tablespace’s datafiles with OS commands. On occasion, a DBA creating a tablespace could attempt to overwrite an existing file in error. This would result in an ORA-01537 error. While not damaging, it could be very frustrating. Now, Oracle has database-managed files. If you create a tablespace, Oracle can automatically generate a filename for you. If you drop an Oracle-managed online redo log, Oracle will automatically remove the file for you if you choose. Oracle touts this feature as a great time saver. In my opinion, it can save time, but not that much. And the side effect of Oracle-generated file names may not be appealing to you. So let’s take a quick look at Oracle-managed files.
ORA9I SQL> create tablespace test;
Tablespace created.
ORA9I SQL> select file_name,bytes from dba_data_files
2 where tablespace_name = 'TEST';
FILE_NAME BYTES
-------------------------------------------------- ----------------
/edcsns14/oradata3/ora9i/ora_test_xog67sr8.dbf 104,857,600
ORA9I SQL> host ls -l /edcsns14/oradata3/ora9i
total 1490224
-rw-r----- 1 oracle dba 104858112 Jul 5 13:26 ora_1_xmnmvyfx.log
-rw-r----- 1 oracle dba 104858112 Jul 5 13:26 ora_1_xmnmwdlw.log
-rw-r----- 1 oracle dba 104858112 Jul 19 12:55 ora_2_xmnmwxx4.log
-rw-r----- 1 oracle dba 104858112 Jul 19 12:55 ora_2_xmnmxcqc.log
-rw-r----- 1 oracle dba 10493952 Jun 27 17:22 ora_dflt_ts_xmnmznby.tmp
-rw-r----- 1 oracle dba 112205824 Jul 19 12:26 ora_system_xmnmxw0r.dbf
-rw-r----- 1 oracle dba 104865792 Jul 19 12:55 ora_test_xog67sr8.dbf
-rw-r----- 1 oracle dba 119545856 Jul 19 12:52 ora_undo_ts_xmnmzgf2.dbf
-rw-r----- 1 oracle dba 2121728 Jul 19 12:55 ora_xmnmvxr6.ctl
-rw-r----- 1 oracle dba 2121728 Jul 19 12:55 ora_xmnmvy2n.ctl
ORA9I SQL> drop tablespace test;
Tablespace dropped.
ORA9I SQL> host ls -l /edcsns14/oradata3/ora9i
total 1285280
-rw-r----- 1 oracle dba 104858112 Jul 5 13:26 ora_1_xmnmvyfx.log
-rw-r----- 1 oracle dba 104858112 Jul 5 13:26 ora_1_xmnmwdlw.log
-rw-r----- 1 oracle dba 104858112 Jul 19 12:55 ora_2_xmnmwxx4.log
-rw-r----- 1 oracle dba 104858112 Jul 19 12:55 ora_2_xmnmxcqc.log
-rw-r----- 1 oracle dba 10493952 Jun 27 17:22 ora_dflt_ts_xmnmznby.tmp
-rw-r----- 1 oracle dba 112205824 Jul 19 12:26 ora_system_xmnmxw0r.dbf
-rw-r----- 1 oracle dba 119545856 Jul 19 12:52 ora_undo_ts_xmnmzgf2.dbf
-rw-r----- 1 oracle dba 2121728 Jul 19 12:55 ora_xmnmvxr6.ctl
-rw-r----- 1 oracle dba 2121728 Jul 19 12:55 ora_xmnmvy2n.ctl
In this short example, we created a tablespace. Notice that we never provided a file name, file size, or any other attributes. The CREATE TABLESPACE command was short and sweet. Looking at the DBA_DATA_FILES, we see that a file name has been automatically generated for us and that the file was created with a default size of 100 Mbytes. As a final step, the tablespace was dropped. We can see clearly that the file was deleted when the tablespace was dropped.
FILENAMES
It is pretty easy to see the cryptic generated filenames. For example, when we created the TEST tablespace, the ora_test_xog67sr8.dbf file was generated for us. Filenames are generated based on the following table:
Table 1. Generated Filenames
Where %t is the tablespace name (up to 8 characters), %g is the online redo log group number, and %u is an eight-character string to guarantee uniqueness among filenames. To me, it is this number that leads to the most confusion when looking at filenames.
SETTING UP
Before one can start using Oracle-managed files, the database must be configured to support this feature. As an aside, new features of Oracle 9i will not be available to you unless you set COMPATIBLE = 9.0 in the INIT.ORA file at a bare minimum! This is true for other sections of this paper as well, but I only mention it here.
How did my previous example know which directory to place the Oracle-managed files? Simple, I told it where to place the files with an INIT.ORA parameter. There are really only two INIT.ORA parameters that you need to configure. They are shown in the following table:
Table 2. Oracle-managed file INIT.ORA parameters
If no file specification is given, the DB_CREATE_FILE_DEST parameter tells Oracle where to place the datafile. This directory must exist before Oracle can try to create a file there. Oracle will not create the directory for you. If the INIT.ORA parameter DB_CREATE_
ONLINE_LOG_DEST_n is specified (n is between 1 and 5), then members of online redo log files and control files will be placed in these directories. If three directories are specified (n = 1, 2, and 3), then three redo log members (or control files) will be created for each created group. If this parameter is omitted, then online redo log files and control files will be created in the directory specified by the DB_CREATE_FILE_DEST parameter.
FILE SIZES
Oracle-managed files are created with the following default sizes if no size is specified.
Table 3. Oracle-managed default file sizes
Control files do not have a default size since their size is determined by how much information they need to hold. Control files grow automatically and their size cannot easily be controlled.
MULTIPLE DESTINATIONS?
As you have seen, there is only one parameter that controls the placement of Oracle-managed datafiles, DB_CREATE_FILE_DEST. And as I’m sure you are aware, placing all your datafiles in one directory is a sure fire way to introduce performance problems in a high-volume database. So how do you place Oracle-managed files in other directories? The DB_CREATE_FILE_DEST is a session and system modifiable parameter. To create an Oracle-managed file in a different directory, see the following example:
ORA9I SQL> alter session set db_create_file_dest = '/edcsns14/oradata2/ora9i';
Session altered.
ORA9I SQL> create tablespace newtest;
Tablespace created.
ORA9I SQL> select file_name from dba_data_files where tablespace_name='NEWTEST';
FILE_NAME
--------------------------------------------------
/edcsns14/oradata2/ora9i/ora_newtest_xogf5g27.dbf
With this method, the destination was changed and the new file was created in that new destination.
WEIGHING THE OPTION
So now you know what an Oracle-managed file is and how it works. When is a good time to use Oracle-managed files? Oracle-managed files are great for quick and easy, low-end or test databases. Due to the seemingly cryptic filenames one would not want to use this option in a high-end production environment where recognizing filenames can be of utmost importance to a DBA. They are also good for systems that employ a Logical Volume Manager to administer RAID environments.
DEPRECATION OF SVRMGR AND CONNECT INTERNAL
Oracle 9i now sees the disappearance of the old SVRMGR utility and the familiar CONNECT INTERNAL method of starting and stopping databases. Now you must connect as SYSDBA (or SYSOPER) before you can perform privileged commands. Out of the box, SYS is granted SYSDBA privileges.
edcsns14% sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Thu Jul 19 14:44:28 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
Notice that SQL*Plus is started with the /NOLOG option. This starts SQL*Plus without it prompting you for a userid and password similar to the default behavior of the old SVRMGR. After that, you CONNECT AS a SYSDBA (or SYSOPER). The SYS user is not the only one who can be granted this privilege. To grant SYSDBA or SYSOPER to another user, log on as SYS and issue:
GRANT sysdba TO other_user;
One other point to note is that you can no longer connect to SYS without specifying SYSDBA or SYSOPER.
edcsns14% sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Wed Oct 3 14:06:27 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect sys
Enter password:
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
QUIESCING THE DATABASE
At times, it is desirable to put the database in a state where only “DBA actions” are allowed, i.e. no user transactions. Prior to Oracle 9i, shutting down the database and then starting the database in restricted mode accomplished this. But this meant that the database must be brought down first. While Oracle Corporation states in their press releases that this “is much less of a restriction because it eliminates the disruption to users and downtime associated with shutting down and restarting the database.” The only real benefit to queiscing the database is that user transactions cease, database administration functions take place, and then users can continue their work. In starting and stopping the database, users must sign back on to the database to continue after it has been opened. To quiesce a database, issue the following command:
ALTER SYSTEM QUIESCE RESTRICTED;
In order to quiesce a database, you must have the Database Resource Manager enabled and it must have been running since instance startup. To resume normal operations, issue the following command:
ALTER SYSTEM UNQUIESCE;
One can determine the state of the database by querying the ACTIVE_STATE column of V$INSTANCE. The states are NORMAL, QUIESCING, QUIESCED and are self-explanatory.
SUSPENDING AND RESUMING THE DATABASE
Suspending a database halts all input and output to datafiles and control files. This is “normally” used to backup a database without interference and without having to bring it down. When the database is suspended, any current I/O operations will complete, but new ones will not be allowed to start. After administration work has been performed, the database is resumed. The following shows how to suspend and resume a database:
SQL> alter system suspend;
System altered.
SQL> select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS from
2> v$instance;
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS
---------------- ---------- ------- -----------------
ora9i edcsns14 OPEN SUSPENDED
SQL> alter system resume;
System altered.
Notice that after the database was suspended, the database status changed.
UNDO TABLESPACES
In previous Oracle versions, undoing transactions meant rollback segments. Most DBA’s set up rollback segments in a tablespace (or two) designed to specifically hold rollback segments. It used to be part of a DBA’s duties to manage storage for these rollback segments within the rollback segment tablespace. With an UNDO tablespace, the system takes control over managing space allocations and rollback segment administration. According to the Oracle documentation, you cannot use both UNDO tablespaces and the traditional rollback segments.
CONFIGURATION
Before you can use UNDO tablespaces, you must first configure the database to handle this function for you. This is done with the INIT.ORA parameters on the following page.
Table 4. UNDO TS INIT.ORA Parameters
Before the database will manage your UNDO segments, you must set UNDO_MANAGEMENT = AUTO. The default is MANUAL. The UNDO_TABLESPACE parameter specifies which tablespace to use for undo segments. Only one tablespace can be the UNDO tablespace. The UNDO_RETENTION parameter specifies how many seconds to retain UNDO (default=900 seconds). The number of online rollback segments to automatically start is determined by the ROLLBACK_SEGMENTS, MAX_ROLLBACK_SEGMENTS, TRANSACTIONS, and TRANSACTIONS_PER_ROLLBACK_SEGMENT parameters. Oracle handles all of this for you.
UNDO TABLESPACES
The UNDO tablespace is not like other tablespaces. It must be created explicitly for UNDO. This can be accomplished with the CREATE UNDO TABLESPACE command.
SQL> create undo tablespace undo2;
Tablespace created.
SQL> alter system set undo_tablespace=undo2;
System altered.
You can switch between multiple undo tablespaces by modifying the system-modifiable parameter as seen above.
UNDO RETENTION
As seen previously, the UNDO_RETENTION parameter controls how long UNDO is kept in the UNDO tablespace for consistent reads. The real nice thing about this is that retained undo can survive instance crashes and shutdowns. And undo retention is also important in Flashback Queries (discussed later in this paper).
MONITORING UNDO
One thing to note is the system generated rollback segment names when using automatic UNDO management. See the following query for an example:
SQL> select usn,name from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 rows selected.
The SYSTEM rollback segment was created at database creation and resides in the SYSTEM tablespace. The other 10 rollback segments were created automatically for me.
In addition, the V$UNDOSTAT performance view has been introduced to help you determine how much overall space the UNDO tablespace requires. Each row in the view corresponds to a ten-minute interval. For example:
SQL> select to_char(begin_time,'MM/DD/YY HH24:MI:SS') as start_time,
2 to_char(end_time,'MM/DD/YY HH24:MI:SS') as end_time,
3 undoblks,txncount from v$undostat;
START_TIME END_TIME UNDOBLKS TXNCOUNT
----------------- ----------------- ---------- ----------
07/19/01 17:07:57 07/19/01 17:08:03 0 0
07/19/01 16:57:57 07/19/01 17:07:57 0 2
07/19/01 16:47:57 07/19/01 16:57:57 0 2
07/19/01 16:37:57 07/19/01 16:47:57 2 2
07/19/01 16:27:57 07/19/01 16:37:57 6 36
07/19/01 16:17:57 07/19/01 16:27:57 0 2
07/19/01 16:07:57 07/19/01 16:17:57 0 2
07/19/01 15:57:57 07/19/01 16:07:57 0 2
07/19/01 15:47:57 07/19/01 15:57:57 0 2
07/19/01 15:37:57 07/19/01 15:47:57 0 2
07/19/01 15:27:57 07/19/01 15:37:57 1 2
START_TIME END_TIME UNDOBLKS TXNCOUNT
----------------- ----------------- ---------- ----------
07/19/01 15:17:57 07/19/01 15:27:57 0 2
07/19/01 15:07:57 07/19/01 15:17:57 0 2
07/19/01 14:57:57 07/19/01 15:07:57 0 2
07/19/01 14:47:57 07/19/01 14:57:57 0 2
07/19/01 14:37:57 07/19/01 14:47:57 1 3
07/19/01 14:27:57 07/19/01 14:37:57 0 3
07/19/01 14:17:57 07/19/01 14:27:57 2 9
18 rows selected.
This view shows the start and end times of the interval with the number of undo database blocks required and the transaction count.
AUTOMATIC SEGMENT SPACE MANAGEMENT
In an effort to simplify database management, Oracle 9i has introduced automatic segment space management. In previous versions of Oracle, there was considerable time spent in determining space management parameters for database segments. These parameters are PCTUSED, FREELISTS, FREELIST GROUPS. With automatic segment space management, the DBA is freed from configuring these parameters. Automatic segment space management is performed by bitmaps to track the space utilization of each database block. The database now has a more accurate picture of space usage in the block. DML operations will perform better since free space lookups can be performed faster. This feature is only available in permanent, Locally Managed Tablespaces. To create a tablespace that uses automatic segment space management, issue a command like the following:
ORA9I SQL> create tablespace users datafile
2 '/edcsns14/oradata3/ora9i/users01.dbf' size 100m
3 extent management local
4 uniform size 40k
5 segment space management auto;
Tablespace created.
Notice line 5 in the above example. This clause is all that is required for automatic segment space management. The default is for MANUAL segment space management. If the extent management is UNIFORM size, then this size must be at least five database blocks. If the extent management size is AUTOALLOCATE and if the database block size is 16K or greater, then the minimum extent size is 1M. When using automatic segment space management, any specification of PCTUSED, FREELISTS, or FREELIST GROUPS is ignored.
The DBA_TABLESPACES view has been modified to give information on automatic segment space management. See below for an example:
ORA9I SQL> select tablespace_name,segment_space_management
2 from dba_tablespaces;
TABLESPACE_NAME SEGMEN
------------------------------ ------
SYSTEM MANUAL
UNDO_TS MANUAL
DFLT_TS MANUAL
UNDO2 MANUAL
WORKING MANUAL
USERS AUTO
6 rows selected.
Notice how the tablespace we just created is set up for automatic segment space management.
DEFAULT TEMPORARY TABLESPACES
In previous versions of Oracle, a newly created user was assigned the SYSTEM tablespace for the user’s temporary tablespace by default. This meant that a DBA had to remember to change the user’s temporary tablespace either when creating the user or with the ALTER USER command. Forgetting this crucial step could have significant implications. Any big sort imposed by the user could hog up critical space in the SYSTEM tablespace. Oracle 9i now lets the DBA define a tablespace as the default temporary tablespace. This does not tie all users to that temporary tablespace. A DBA can assign a different temporary tablespace to any user at any time. But if the DBA “forgets” to assign a temporary tablespace, the user defaults to a “good” temporary tablespace, not the SYSTEM tablespace.
create database ora9i
datafile size 100M autoextend on next 1M maxsize 300M
DEFAULT TEMPORARY TABLESPACE dflt_ts TEMPFILE SIZE 10M
UNDO TABLESPACE undo_ts DATAFILE SIZE 10M
maxlogfiles 32
maxlogmembers 3
maxdatafiles 100
maxinstances 3
character set "US7ASCII"
noarchivelog;
This code shows how to define the default temporary tablespace at database creation time. The default temporary tablespace must be a locally managed tablespace. If the above clause is omitted, then the default temporary tablespace defaults to the SYSTEM tablespace as in previous releases. You cannot specify the SYSTEM tablespace in this clause. The ALTER DATABASE command can be used to change the default temporary tablespace.
ORA9I SQL> create temporary tablespace temp tempfile
2 '/edcsns14/oradata3/ora9i/temp01.dbf' size 10m
3 extent management local uniform size 64k;
Tablespace created.
ORA9I SQL> alter database default temporary tablespace temp;
Database altered.
Here, we created a new temporary tablespace. We then changed the default temporary tablespace to this new tablespace. To determine the current default temporary tablespace, issue the following command:
ORA9I SQL> select property_value
2 from database_properties
3 where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------
TEMP
AUTOMATICALLY DELETE DATAFILES
We’ve already seen that Oracle managed files will drop a datafile when the tablespace is dropped. But what if the file is not Oracle-managed? We can still drop the datafile when we drop the tablespace.
ORA9I SQL> create tablespace dummy_ts datafile
2 '/edcsns14/oradata3/ora9i/dummy_ts01.dbf' size 1m;
Tablespace created.
ORA9I SQL> host ls /edcsns14/oradata3/ora9i
dummy_ts01.dbf ora_system_xmnmxw0r.dbf ora_xmnmvy2n.ctl
ora_1_xmnmvyfx.log ora_undo2_xognrkkg.dbf temp01.dbf
ora_1_xmnmwdlw.log ora_undo_ts_xmnmzgf2.dbf users01.dbf
ora_2_xmnmwxx4.log ora_working_xp30c10o.dbf
ora_2_xmnmxcqc.log ora_xmnmvxr6.ctl
ORA9I SQL> drop tablespace dummy_ts including datafiles;
drop tablespace dummy_ts including datafiles
*
ERROR at line 1:
ORA-01911: CONTENTS keyword expected
ORA9I SQL> drop tablespace dummy_ts including contents and datafiles;
Tablespace dropped.
ORA9I SQL> host ls /edcsns14/oradata3/ora9i
ora_1_xmnmvyfx.log ora_system_xmnmxw0r.dbf ora_xmnmvxr6.ctl
ora_1_xmnmwdlw.log ora_undo2_xognrkkg.dbf ora_xmnmvy2n.ctl
ora_2_xmnmwxx4.log ora_undo_ts_xmnmzgf2.dbf temp01.dbf
ora_2_xmnmxcqc.log ora_working_xp30c10o.dbf users01.dbf
In this example, we first create a tablespace that we will drop. We can verify that the datafile for the tablespace exists. We then try to drop the database and its datafile(s). An error is flagged. You can see that I had to drop the tablespace, its contents and then its datafiles. Even though the tablespace was completely empty, you must specify the INCLUDING CONTENTS to be able to delete the datafiles at the same time. Notice that after the command was executed, the datafile was deleted as well.
DYNAMIC SGA SIZING
There are numerous INIT.ORA parameters that control the size of the System Global Area (SGA) and its components. For instance, to control the size of the Shared Pool, one would modify the SHARED_POOL_SIZE parameter. This was simply done by editing the INIT.ORA file with the new parameter size. But the change in size did not take effect until the database was bounced. Even taking the database down for a moment to resize SGA parameters can be harmful to 24x7 operations. Oracle 9i introduced the ability to dynamically resize the SGA without having to bounce the database. All INIT.ORA parameters that determine the size of the SGA can be dynamically altered while the instance is running.
SQL> alter system set shared_pool_size=10000000;
System altered.
Any SGA parameter can now be modified without having to bounce the database. This can help ensure a good 24x7-operating environment. One note: in order for me to get this functionality to work correctly, I had to use a SPFILE. For more information, see the section further in this document.
INDEX SKIP SCANS
For some time (longer than I’ve been around), Oracle has allowed a composite index. A composite index is an index on a table based on multiple columns of that table. The composite index is only used if the leading columns of the index participate in the WHERE clause. Oracle 9i introduced the capability to use an Index Skip Scan to take advantage of the index even when the leading columns of the index were not used. To show you what I mean I will create a test table in both an Oracle 8i and 9i database. I will then create an index on that table. I will then show you how an index skip scan will improve performance in the 9i database by using an Index Skip Scan where the performance was not improved in the 8i database. In both databases, I issued the following:
SQL> create table test as select object_id,object_name from dba_objects;
Table created.
SQL> create index test_idx on test(object_id,object_name);
Index created.
SQL> execute dbms_stats.gather_schema_stats(NULL);
PL/SQL procedure successfully completed.
This created a test table, populated it with data, and then created an index on two columns of that table. After the tables and indexes were created, I generated statistics using a PL/SQL supplied package. This helps the Cost Based Optimizer determine the best execution path. I will now execute two queries against the Oracle 8i database.
ORA8I SQL> column object_name format a30
ORA8I SQL> set autotrace on
ORA8I SQL> select * from test where object_id=100;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
100 I_IDL_UB11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card
=1 Bytes=20)
This first query used the index on the table since the leading column of the index (OBJECT_TYPE) was present in the WHERE clause. Now, let’s exclude this leading column from our query and query on just the second column of the index.
RS14 SQL> select * from test where object_name='I_IDL_UB11';
OBJECT_ID OBJECT_NAME
---------- ------------------------------
100 I_IDL_UB11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
It is obvious that the index was not used in this example. Now let’s look at this same query on a 9i database.
ORA9I SQL> column object_name format a30
ORA9I SQL> set autotrace on
ORA9I SQL> select * from test where object_id=100;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
100 TRUSTED_LIST$
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card
=1 Bytes=20)
Here, we used the leading column of the index. As before, an Index Range Scan was performed. Now, let’s query on the other column.
ORA9I SQL> select * from test where object_name='TRUSTED_LIST$';
OBJECT_ID OBJECT_NAME
---------- ------------------------------
100 TRUSTED_LIST$
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=20)
1 0 INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=4
Card=1 Bytes=20)
Here, you can see that the TEST_IDX index was still used even though we did not use the leading column of the index. The Index Skip Scan method was used on this index. By using this feature, one does not need to create an additional index on the OBJECT_NAME column like they had to previous to Oracle 9i.
MULTI-TABLE INSERTS
In previous Oracle versions, the INSERT statement would only insert into one table at a time. Beginning with Oracle 9i, an INSERT statement can insert into more than one table at a time. This operation should be much faster since the source data is only scanned once.
ORA9I SQL> create table test1 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table test2 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> set timing on
ORA9I SQL> set echo on
ORA9I SQL> @insert_two_stmts.sql
ORA9I SQL> INSERT INTO test1 SELECT * FROM dba_objects;
4800 rows created.
Elapsed: 00:00:02.15
ORA9I SQL> INSERT INTO test2 SELECT * FROM dba_objects;
4800 rows created.
Elapsed: 00:00:02.28
In this example, you can see that I created two test tables (the ‘0=1’ condition helps create a table with no rows). As you can clearly see, I read from one view, dba_objects, and inserted data into these two test tables. The total time to perform these two inserts was over 4.5 seconds.
Note: In my example, these test tables are identical, but do not need to be. They can be different so long as the INSERT statement is syntactically correct.
Now let’s see how a multi-table insert will look and perform.
ORA9I SQL> INSERT ALL
2 INTO test1
3 INTO test2
4 SELECT * FROM dba_objects;
9600 rows created.
Elapsed: 00:00:03.81
Here, I used one INSERT command to insert data into both tables. Notice that the overall execution time was an improvement over executing this as two separate INSERT commands. For very large tables, the improvement will be much more noticeable. This type of multi-table insert is called an “Unconditional ALL INSERT”.
There is a “Conditional ALL INSERT” as well. This can give you some control over which table will receive the data. For instance, look at the following example:
ORA9I SQL> create table db_tables as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table db_views as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> insert all
2 when object_type='TABLE'
3 then into db_tables
4 when object_type='VIEW'
5 then into db_views
6 select * from dba_objects;
2025 rows created.
ORA9I SQL> select count(*) from db_tables;
COUNT(*)
----------
384
ORA9I SQL> select count(*) from dba_objects
2 where object_type='TABLE';
COUNT(*)
----------
384
ORA9I SQL> select count(*) from db_views;
COUNT(*)
----------
1641
ORA9I SQL> select count(*) from dba_objects
2 where object_type='VIEW';
COUNT(*)
----------
1641
Here, I created two tables. One will hold all database tables (db_tables) and the other will hold all database views (db_views). I then used a conditional insert statement, selecting from all database objects and inserting into these two tables based on a condition. When the INSERT statement is complete, the number of records in db_tables should be equivalent to the total number of tables in the database. Similarly, this should hold for the views and db_views. This was shown with the ‘count’ statements. One thing to note for this type of INSERT statement is that for any rows returned by the SELECT clause, those rows will be inserted into all tables that match the WHEN clause.
There is one more type of multi-table INSERT statement that I’d like to discuss. This is the ‘Conditional FIRST INSERT’ statement. In the previous statement, any row is capable of being inserted into all the tables that match the WHEN clause. But sometimes, it is preferable to insert the row into the first match in the WHEN clause. The following is an example:
ORA9I SQL> create table db_objects_1000 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table db_objects_2000 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table db_objects_3000 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table db_objects_rest as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> insert first
2 when object_id <=1000 then into db_objects_1000
3 when object_id <=2000 then into db_objects_2000
4 when object_id <=3000 then into db_objects_3000
5 else into db_objects_rest
6 select * from dba_objects;
4802 rows created.
ORA9I SQL> select count(*) from db_objects_rest;
COUNT(*)
----------
1822
ORA9I SQL> select count(*) from db_objects_2000;
COUNT(*)
----------
1000
This example also introduced the ELSE clause.
MERGE SQL STATEMENT
The MERGE statement lets a single SQL statement either conditionally insert or update a table by selecting rows from another table. If the row already exists, an update is performed. Otherwise, an insert is performed. For our example, let’s look at two sample tables:
ORA9I SQL> select * from category;
ID CATEGORY
---------- ------------------------------
1 Logs
2 Writes
3 Reads
4 Updates
5 Deletes
6 Selects
6 rows selected.
ORA9I SQL> select * from new_category;
ID CATEGORY
---------- ------------------------------
1 Bad
2 Good
When trying to insert data into new_category that is selected from category, a constraint violation occurs.
ORA9I SQL> insert into new_category select * from category;
insert into new_category select * from category
*
ERROR at line 1:
ORA-00001: unique constraint (PEASLAND.NEW_CAT_PK) violated
This exception occurs because the primary key constraint was violated. There are duplicate ID entries as we have already seen. Wouldn’t it be nice if we could insert only those records that do not violate the primary key condition? And at the same time, let’s update the records that do match!
ORA9I SQL> merge into new_category n
2 using category c
3 on (n.id = c.id)
4 when matched then
5 update set n.category = n.category || ' ' || c.category
6 when not matched then
7 insert (n.id,n.category) values (c.id,'NEW ' || c.category);
6 rows merged.
ORA9I SQL> select * from new_category;
ID CATEGORY
---------- ------------------------------
1 Bad Logs
2 Good Writes
5 NEW Deletes
6 NEW Selects
4 NEW Updates
3 NEW Reads
6 rows selected.
The INTO clause specifies the target table. The USING clause specifies the source table. The ON clause specifies the condition where an update or an insert is performed. When the condition is MATCHED, then the update command is performed. When the condition is NOT MATCHED, then the insert is performed. Take particular note of how the columns were modified here.
RESUMABLE TRANSACTIONS
Pre-9i, if a transaction ran out of space, an exception was raised and the transaction aborted. The DBA had to fix the space problem and then the transaction had to be restarted. Often times, this was a detriment because the long running transaction had to be restarted from the beginning. In 9i, a transaction can be suspended until the DBA resolves the space issue. Once the space issue has been resolved, the transaction can be resumed. In order for a transaction to be resumable, the user must have been given the RESUMABLE privileges.
ORA9I SQL> grant resumable to peasland;
Grant succeeded.
Then the user must start a resumable session and then start their transaction.
ORA9I SQL> alter session enable resumable;
Session altered.
ORA9I SQL> insert into my_db_objects select * from db_objects;
At this point, the user’s session hangs. They are not given any warning messages. The DBA can verify that this resumable transaction is suspended by querying the DBA_RESUMABLE view.
ORA9I SQL> select user_id,status,start_time,suspend_time,error_number,
2> error_msg from dba_resumable;
USER_ID STATUS START_TIME SUSPEND_TIME ERROR_NUMBER
---------- --------- -------------------- ----------------- ------------
ERROR_MSG
------------------------------------------------------------------------
18 SUSPENDED 09/12/01 17:14:34 09/12/01 17:14:38 1631
ORA-01631: max # extents (100) reached in table PEASLAND.MY_DB_OBJECTS
Here, the DBA can see that a transaction has been suspended. The reason for the suspension is clearly obvious here. This is also logged in the alert log as such:
Wed Sep 12 17:14:38 2001
statement in resumable session 'User PEASLAND(18), Session 8, Instance 1' was suspended due to
ORA-01631: max # extents (100) reached in table PEASLAND.MY_DB_OBJECTS
The DBA takes measures to resolve the problem. The alert log shows that the session was resumed:
Wed Sep 12 17:18:55 2001
statement in resumable session 'User PEASLAND(18), Session 8, Instance 1' was resumed
And the DBA_RESUMABLE view shows that operation is NORMAL:
ORA9I SQL> select user_id,status,start_time,suspend_time,error_number,
2 error_msg from dba_resumable;
USER_ID STATUS START_TIME SUSPEND_TIME ERROR_NUMBER
---------- --------- -------------------- ----------------- ------------
ERROR_MSG
------------------------------------------------------------------------
18 NORMAL 09/12/01 17:14:34 0
Notice here that the error number is zero, indicating no error.
GROUPING SETS
Oracle 8i introduced new aggregate functions CUBE and ROLLUP. Oracle 9i extends the aggregate functions even further with grouping sets and concatenated grouping sets. Prior to 9i, a GROUP BY aggregate query could like:
ORA9I SQL> select deptno,job,sum(sal) as total_salary
2 from emp
3 group by deptno,job;
DEPTNO JOB TOTAL_SALARY
---------- --------- ------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
9 rows selected.
We can now group by sets as seen below.
ORA9I SQL> select deptno,job,sum(sal) as total_salary
EXTERNAL TABLES
Oracle 9i introduces tables that can be stored outside of the database in a flat file. These external tables are read-only tables to the database. This functionality was primarily introduced to assist data warehouses. By creating an external table that can be queried easily with SQL commands, data can be quickly transformed and loaded into existing data warehouse tables.
In my example, I have a simple flat file that looks like the following:
edcsns14% cat people.txt
Joe Smith,123-45-6789,(605)123-4567
Julie Smith,234-56-7890,(605)234-5678
Jane Doe,012-34-5678,(123)456-7890
John Doe,012-34-5679,(123)456-7891
As you can see, this file holds names in one column with their respective SSN’s and phone numbers. We will create an external table for this simple flat file. Before we can do that, we need to create a directory in the database.
ORA9I SQL> create directory external_tab_dir
2 as '/edcsns14/oradata3/ora9i/ext_tab';
Directory created.
This directory is the directory on the server that holds the flat file. Once this is in place, we will use the Oracle directory name in our CREATE TABLE command.
ORA9I SQL> create table people_external (
2 name varchar2(13),
3 ssn varchar2(11),
4 phone varchar2(13))
5 organization external
6 ( type oracle_loader
7 default directory external_tab_dir
8 access parameters
9 ( records delimited by newline
10 badfile 'bad_ext_records'
11 logfile 'log_ext_records'
12 fields terminated by ','
13 missing field values are null
14 (name,ssn,phone)
15 )
16 location ('people.txt')
17 )
18* reject limit unlimited;
Table created.
We have now created an external table! This external table’s columns are defined in lines 2-4 just like a normal table. Line 5 signifies that this table is an external table. How does the data become available to our queries? Line 6 shows that SQL*Loader is used to get the information out of the flat file. Line 12 is a directive to SQL*Loader that the comma is the delimiter. Line 14 maps the columns in the flat file to the columns of the table. Line 16 indicates which file contains the data.
We can now use the external table in our queries.
ORA9I SQL> select * from people_external;
NAME SSN PHONE
------------- ----------- -------------
Joe Smith 123-45-6789 (605)123-4567
Julie Smith 234-56-7890 (605)234-5678
Jane Doe 012-34-5678 (123)456-7890
John Doe 012-34-5679 (123)456-7891
Here, you can see that we can query the contents of this table just as if the table existed inside the database. Remember that this table is read-only. What happens if we try to update the contents of this table?
ORA9I SQL> update people_external set ssn='098-76-5432'
2 where name='Joe Smith';
update people_external set ssn='098-76-5432'
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
An exception is raised.
2 from emp
3 group by
4 grouping sets ((deptno,job),(job));
DEPTNO JOB TOTAL_SALARY
---------- --------- ------------
20 ANALYST 6000
ANALYST 6000
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
MANAGER 8275
10 PRESIDENT 5000
PRESIDENT 5000
30 SALESMAN 5600
SALESMAN 5600
14 rows selected.
Here, we group by two sets of information. The first grouping set is based on the deptno,job columns. After that, we further aggregate on the job column. Another example follows:
ORA9I SQL> select deptno,job,mgr,avg(sal)
2 from emp
3 group by
4* grouping sets ((deptno,job,mgr),(deptno,job));
DEPTNO JOB MGR AVG(SAL)
---------- --------- ---------- ----------
10 CLERK 7782 1300
10 CLERK 1300
10 MANAGER 7839 2450
10 MANAGER 2450
10 PRESIDENT 5000
10 PRESIDENT 5000
20 ANALYST 7566 3000
20 ANALYST 3000
20 CLERK 7788 1100
20 CLERK 7902 800
20 CLERK 950
20 MANAGER 7839 2975
20 MANAGER 2975
30 CLERK 7698 950
30 CLERK 950
30 MANAGER 7839 2850
30 MANAGER 2850
30 SALESMAN 7698 1400
30 SALESMAN 1400
19 rows selected.
In this example, we have two grouping sets again. To get an idea of how this data is grouped by sets, let’s look at department 20. Notice that we have two groups based on the deptno,job,mgr columns. The clerks for manager 7788 average 1100 for their salary. The clerks for manager 7902 average 800 for their salary. Below that line, is the grouping set result for the depot,job columns. And the average salary for that grouping set is shown, i.e. 950.
These grouping set queries are more powerful than CUBE and UNION ALL queries. They generate unnecessary groupings or extra queries that lead to inefficient processing. The grouping sets are much more efficient.
Now let’s look at a concatenated grouping set.
ORA9I SQL> select deptno,job,mgr,sum(sal) as total_salary
2 from emp
3 group by
4 grouping sets (deptno,job),
5 grouping sets (mgr);
DEPTNO JOB MGR TOTAL_SALARY
---------- --------- ---------- ------------
10 7782 1300
10 7839 2450
10 5000
20 7566 6000
20 7788 1100
20 7839 2975
20 7902 800
30 7698 6550
30 7839 2850
ANALYST 7566 6000
CLERK 7698 950
CLERK 7782 1300
CLERK 7788 1100
CLERK 7902 800
MANAGER 7839 8275
PRESIDENT 5000
SALESMAN 7698 5600
17 rows selected.
Notice here that there are two distinct grouping sets unlike our first examples. The first grouping set is on the deptno,job columns. The second grouping set is only on the mgr column. The results are the possible permutations between the two sets. In our case, we are grouped on the deptno,mgr and job,mgr columns.
ANSI/ISO SQL99 COMPLIANT JOINS
One of the things that I’ve often heard is that Oracle is not compliant with the ANSI SQL standards, especially for joins. All major RDBMS vendors claim to be ANSI compliant when it comes to SQL. But each vendor can have different SQL syntax. If they are ANSI compliant, then how can they have different SQL syntax? What some people don’t realize is that there are four levels to the ANSI SQL standards. In order to put the “ANSI Compliant” tag on their product, they only need to comply with the first level of the ANSI standards. All major RDBMS vendors comply with at least the first level. After that, they pick and choose which standards to employ in their database engine. So outside of the first level of the ANSI SQL standards, databases can differ in their SQL syntax and functionality.
People who come from another RDBMS vendor to Oracle often try to port over their old SQL statements to Oracle. This can lead to confusion when dealing with joins. Beginning with Oracle 9i, the database is now compliant to ANSI SQL99 standards.
In the past, a join such as this would fail:
RS14 SQL> select o.owner,u.user_id,o.object_name
2 from (dba_objects o inner join dba_users u on o.owner=u.username)
3 where o.owner='PEASLAND';
from (dba_objets o inner join dba_users u on o.owner=u.username)
*
ERROR at line 2:
ORA-00928: missing SELECT keyword
But Oracle 9i let’s us use ANSI SQL99 keywords such as INNER, NATURAL, and OUTER JOINS in our SQL statements:
ORA9I SQL> select o.owner,u.user_id,o.object_name
2 from (dba_objects o inner join dba_users u on o.owner=u.username)
3* where o.owner='PEASLAND';
OWNER USER_ID OBJECT_NAME
---------- ---------- ---------------
PEASLAND 18 DB_OBJECTS
PEASLAND 18 DB_OBJECTS_IDX
PEASLAND 18 DB_USERS
PEASLAND 18 OBJECTS
PEASLAND 18 PEOPLE_EXTERNAL
PEASLAND 18 PLAN_TABLE
PEASLAND 18 TABLESPACES
PEASLAND 18 USERS
8 rows selected.
Feel free to start using this ANSI SQL99 syntax in your code!
FLASHBACK QUERY
Flashback Query, new for Oracle 9i, lets a user go back in time to perform their queries. For instance, lets say that our database holds account information for a bank. During the day, people deposit and withdraw money from their bank account. The manager of the bank wants to run a report on these bank accounts in the afternoon, but doesn’t want today’s transactions to be included in the report. Before Oracle 9i, the account information had to be drawn off before the start of the business day so that the manager could run reports against this duplicated data. With Flashback Query, the manager can tell the database that the reports will run against only those transactions that were completed before 9:00 am today. Flashback Query has other uses as well. It can be used to recover data in case someone accidentally deleted it. Just query the data in the past and generate SQL statements to insert the deleted records. (LogMiner offers this functionality as well).
Flashback Queries can perform queries against the database back to a specific point in time, or a specific SCN in the database. While one is performing a Flashback Query, other DML operations are allowed to take place in real time. Flashback Query does not undo any changes to the database. It is a query-only mechanism.
In order to use Flashback Query, the transactions must be using Oracle’s new UNDO tablespace. Flashback Query cannot use old-fashioned rollback segments. It is the UNDO tablespace that lets the Flashback Query rollback any changes before a point in time. How far back in time can one query? That is determined by the UNDO_RETENTION parameter specified in the INIT.ORA file. The farther back one will need to go will determine how this parameter is set. The DBA will also need to set UNDO_MANAGEMENT=AUTO in the INIT.ORA file.
ORA9I SQL> select to_char(sysdate,'HH24:MI:SS') as curr_time from dual;
CURR_TIM
--------
09:40:13
ORA9I SQL> select * from users;
USERNAME USER_ID CREATED
---------- ---------- ---------
OUTLN 11 27-JUN-01
DBSNMP 17 27-JUN-01
PEASLAND 18 24-AUG-01
jim 9876 28-SEP-01
susan 9874 28-SEP-01
jack 9873 29-SEP-01
jack 9873 29-SEP-01
7 rows selected.
ORA9I SQL> delete from users where username='jack';
2 rows deleted.
ORA9I SQL> commit;
Above, we can see that we deleted records from a table after 9:40 am. Those records no longer exist in the table since the transaction was committed. Let’s use the DBMS_FLASHBACK package to change our query’s point in time. The ENABLE_AT_TIME procedure sets the time of our Flashback Query.
ORA9I SQL> execute dbms_flashback.enable_at_time( -
> to_date('10/02/2001 09:30:00','MM/DD/YYYY HH24:MI:SS'));
PL/SQL procedure successfully completed.
ORA9I SQL> select * from users;
USERNAME USER_ID CREATED
---------- ---------- -------------------
OUTLN 11 06/27/2001 16:28:10
DBSNMP 17 06/27/2001 16:57:06
PEASLAND 18 08/24/2001 08:35:20
jim 9876 09/28/2001 13:19:56
susan 9874 09/28/2001 13:23:03
jack 9873 09/29/2001 13:29:26
jack 9873 09/29/2001 13:30:33
7 rows selected.
ORA9I SQL> execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
ORA9I SQL> select * from users;
USERNAME USER_ID CREATED
---------- ---------- -------------------
OUTLN 11 06/27/2001 16:28:10
DBSNMP 17 06/27/2001 16:57:06
PEASLAND 18 08/24/2001 08:35:20
jim 9876 09/28/2001 13:19:56
susan 9874 09/28/2001 13:23:03
We changed our reference time for the Flashback Query. On examining the table, we see that the records are there! We then disable the Flashback Query. Examining the table once again shows that the records are in fact gone.
MULTIPLE BLOCK SIZES
The smallest unit of I/O in Oracle is defined by the block size. Before Oracle 9i, each database could only have one block size for the entire database. For most systems, this restriction was not an impediment to a successful implementation. But some DBA’s desired to have the ability to use different block sizes in the database at the same time. There are two major reasons for this. One, different tablespaces hold different types of data. Some tablespaces perform better with smaller block sizes while other tablespaces (which potentially hold large objects) would benefit from a larger block size. Two, when using transportable tablespaces (introduced in Oracle 8i), there is a restriction that the source database and target database have the same block size. Allowing different block sizes in the database would remove this restriction. One database would not be dependant on another. To address these issues, Oracle introduced support for multiple block sizes within the same database.
CONFIGURATION
Before you can use multiple block sizes in your database, you must first configure the database.
In my database, I still have a default block size specified by the db_block_size parameter.
ORA9I SQL> show parameter db_block_size
NAME TYPE VALUE
---------------------------------- ----------- -------------------------
db_block_size integer 8192
As you can see, my default block size is 8K. When blocks are read by the database, the information is placed into the buffer cache. The initialization parameter db_block_buffers is being deprecated in favor of the db_cache_size parameter that we’ll talk about more in the next paragraph.
We can configure up to five non-standard block sizes for use in our database. In order to use multiple block sizes in the database, we have to configure different sub-caches in the buffer cache. All tablespaces which are created with a 2K block size will use the 2K sub-cache. There are six new parameters that control these caches. The db_cache_size parameter sizes the default buffer cache used by the default block size. The db_nk_cache_size parameter controls the sizes of these sub-caches where n=2,4,6,16 or 32. Please note that your default block size is not available for use as a sub-cache. So in my example, I could not set the db_8k_cache_size parameter since my default block size is 8K.
ORA9I SQL> show parameter db_block_buffers
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
db_block_buffers integer 0
ORA9I SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
db_cache_size big integer 12582912
ORA9I SQL> show parameter k_cache_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 4194304
db_32k_cache_size big integer 0
db_4k_cache_size big integer 4194304
db_8k_cache_size big integer 0
From above, we can see that we modified the db_cache_size parameter and did not set the db_block_buffers parameter. You cannot have both of these set, it is one or the other. I also modified my INIT.ORA file to create two alternate sub-caches, 2K and 4K. Now that these sub-caches have been defined, I can create a tablespace with an alternate block size.
CREATING TABLESPACES
The SYSTEM tablespace in the database will always be created with the default block size. But I can create other tablespaces with different block sizes as follows:
ORA9I SQL> create tablespace ts_2k
2 datafile '/edcsns14/oradata3/ora9i/ts_2k01.dbf'size 10m
3 extent management local uniform size 64k
4 blocksize 2k;
Tablespace created.
Here, I created a tablespace with a 2K block size. Any data read from this tablespace will be placed in the 2K sub-cache.
As can be expected, the DBA_TABLESPACES has changed so that you can find out the block size of the tablespaces.
ORA9I SQL> select tablespace_name,block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 8192
UNDO_TS 8192
UNDO2 8192
WORKING 8192
USERS 8192
TEMP 8192
INDEXES 8192
PEASLAND 8192
PEASLAND_IDX 8192
TS_2K 2048
10 rows selected.
You can clearly see the different block size on the tablespace we created above.
CACHED EXECUTION PLANS
Oracle 9i now lets a DBA more easily diagnose query problems by letting you see the execution plan information for each cached cursor in the Shared Pool of the SGA. There is a new view that has been added for this functionality.
ORA9I SQL> select address,hash_value,sql_text
2 from v$sqlarea
3 where sql_text like 'select count%';
ADDRESS HASH_VALUE SQL_TEXT
-------- ---------- ----------------------------------------
805AF35C 428662531 select count(*) from dba_objects
Here, I’ve visited the familiar V$SQLAREA view to see a cursor that is cached in the Shared Pool. I need the ADDRESS and HASH_VALUE for further analysis.
The V$SQL_PLAN view will show you the execution plan that the database used to execute a specific cached query. For our query above, we’ll see the execution plan used below:
ORA9I SQL> select lpad(' ',2*(depth-1)) || operation || ' ' || options || ' ' ||
2 object_name as "Query Plan"
3 from v$sql_plan
4 where address='805AF35C' and hash_value=428662531;
Query Plan
------------------------------------------------------------------------
SELECT STATEMENT
SORT AGGREGATE
VIEW
UNION-ALL
FILTER
TABLE ACCESS BY INDEX ROWID OBJ$
NESTED LOOPS
TABLE ACCESS FULL USER$
INDEX RANGE SCAN I_OBJ2
TABLE ACCESS BY INDEX ROWID IND$
INDEX UNIQUE SCAN I_IND1
NESTED LOOPS
TABLE ACCESS FULL USER$
INDEX RANGE SCAN I_LINK1
14 rows selected.
This is the execution plan that Oracle used when executing our SQL statement. This saves the DBA the trouble from generating an Explain Plan on this statement. It is also important since environmental factors can contribute to different plans. So now you know exactly which plan was used to execute this query.
CACHE ADVICE
Before Oracle 9i, determining the optimal setting for the buffer cache size was a process of calculating hit ratios and adjusting accordingly. In Oracle 7.3, you could even determine the effect of raising or lowering db_block_buffers by setting db_block_lru_extended_
statistics to TRUE and examining your findings in X$KCBRBH and X$KCBCBH. Oracle 9i now includes the V$DB_CACHE_ADVICE view to determine this. Before you can use this view, you need to configure your system to provide the advice. This is done with the db_cache_advice parameter. This parameter may incur undue overhead to your system so be cautious.
ORA9I SQL> alter system set db_cache_advice=on;
System altered.
Before we can examine the above view, we need to determine which buffer cache we are looking at.
ORA9I SQL> select id,name,block_size,current_size,buffers
2> from v$buffer_pool;
ID NAME BLOCK_SIZE CURRENT_SIZE BUFFERS
---------- ---------- ---------- ------------ ----------
3 DEFAULT 8192 12 1503
4 DEFAULT 2048 4 1892
5 DEFAULT 4096 4 983
In my case, I’m going to be looking at the default buffer cache for my default block size (8K). So I’m interested in ID=3. Let’s see what V$DB_ADVICE_CACHE indicates for this buffer cache.
ORA9I SQL> set headsep !
ORA9I SQL> column buffers_for_estimate heading "Buffers For!Estimate"
ORA9I SQL> column size_for_estimate heading "Size For!Estimate"
ORA9I SQL> column estd_physical_reads heading "Estimated!Phys Reads"
ORA9I SQL> select name,size_for_estimate,buffers_for_estimate,
2 estd_physical_read_factor as read_factor,estd_physical_reads
3 from v$db_cache_advice
4 where id=3;
Size For Buffers For Estimated
NAME Estimate Estimate READ_FACTOR Phys Reads
---------- ---------- ----------- ----------- ----------
DEFAULT 1.1719 150 2.0064 4
DEFAULT 2.3438 300 1.234 2
DEFAULT 3.5156 450 1.0021 2
DEFAULT 4.6875 600 1 2
DEFAULT 5.8594 750 1 2
DEFAULT 7.0313 900 1 2
DEFAULT 8.2031 1050 1 2
DEFAULT 9.375 1200 1 2
DEFAULT 10.5469 1350 1 2
DEFAULT 11.7188 1500 1 2
DEFAULT 12.8906 1650 1 2
DEFAULT 14.0625 1800 1 2
DEFAULT 15.2344 1950 1 2
DEFAULT 16.4063 2100 1 2
DEFAULT 17.5781 2250 1 2
DEFAULT 18.75 2400 1 2
DEFAULT 19.9219 2550 1 2
DEFAULT 21.0938 2700 1 2
DEFAULT 22.2656 2850 1 2
DEFAULT 23.4375 3000 1 2
20 rows selected.
The SIZE_FOR_ESTIMATE column is the size in Megabytes for the prediction. The BUFFERS_FOR_ESTIMATE column is the number of block buffers for the estimate. The ESTD_PHYSICAL_READ_FACTOR column (READ_FACTOR) is a ratio comparing the estimated number of physical reads to the number of reads in the real cache. In my example above, this means that if I chose 150 block buffers, then I’d have to perform 2 physical reads for every one of my reads. This would be terrible performance. According to my output above, I should have at least 600 block buffers for optimal performance under this load condition.
SERVER PARAMETER FILE
Before Oracle 9i, the DBA would modify the INIT.ORA parameter file stored on the same server as the database. Remote tools such as Oracle Enterprise Manager (OEM) could use a remote parameter file stored on the machine that ran OEM. If this scenario was deployed, it meant that the DBA had to keep the server-side INIT.ORA file in sync with all the remote parameter files. To alleviate this problem, Oracle created a Server Parameter File (SPFILE). Initialization parameters in this file are persistent. Any changes made to this parameter file will the instance is up and running will be modified automatically in this file.
Before you can use a SPFILE, you must create one. The created SPFILE will be a binary file that cannot be modified through a text editor. This is a major change from the normal INIT.ORA files. The SPFILE must be initially created from an INIT.ORA file with the CREATE SPFILE command.
ORA9I SQL> connect sys as sysdba
Enter password:
Connected.
ORA9I SQL> create spfile
2 from pfile='/edcsns14/pkg/oracle/admin/ora9i/pfile/initora9i.ora';
File created.
You need to connect as SYSDBA to perform this operation. The server parameter file is always created on the machine running the database server. While there are options to specify exactly where you want the SPFILE to reside and what you want to call it, it is best to let it default in name and location. After you’ve created the SPFILE, remove the INIT.ORA file. Otherwise, Oracle will get confused and issue an ORA-3113 error.
edcsns14% sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Tue Oct 2 14:44:46 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 33924028 bytes
Fixed Size 279484 bytes
Variable Size 12582912 bytes
Database Buffers 20971520 bytes
Redo Buffers 90112 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
spfile string ?/dbs/spfile@.ora
The database has been restarted and it is now using the new SPFILE. Let’s change a parameter and see that it persists across database shutdown.
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 4194304
SQL> alter system set shared_pool_size=10000000;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 42312676 bytes
Fixed Size 279524 bytes
Variable Size 20971520 bytes
Database Buffers 20971520 bytes
Redo Buffers 90112 bytes
Database mounted.
Database opened.
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 12582912
Here, we can see that our Shared Pool was 4M. We modified the Shared Pool size dynamically to 10M. The system was shutdown and then restarted. Notice that the parameter change persisted!
You can go back as well. This way, you can create a readable, text-modifiable form for your use.
SQL> create pfile from spfile;
File created.
One final note: In my studies, I had difficulty dynamically setting the SGA parameters as previously discussed in this paper. Once I went to an SPFILE, I was able to do as advertised.
MONITORING INDEX USAGE
I’ve heard the question asked time and time again. “How do I know if my index is being used?” There has never been an easy answer. An unused index wastes disk space and slows down DML operations that need to update the index. So why keep an index that is barely being used, or worse, not being used at all? With Oracle 9i, one can monitor the index usage by querying the V$OBJECT_USAGE view.
ORA9I SQL> select index_name from user_indexes
2 where table_name='DB_USERS';
INDEX_NAME
------------------------------
DB_USERS_ACCOUNT_STATUS_IDX
DB_USERS_USERID_IDX
DB_USERS_USERNAME_IDX
Here you can see that I have three indexes on this table in my schema. I now need to tell the system to monitor the usage on these three indexes.
ORA9I SQL> alter index DB_USERS_ACCOUNT_STATUS_IDX monitoring usage;
Index altered.
ORA9I SQL> alter index DB_USERS_USERID_IDX monitoring usage;
Index altered.
ORA9I SQL> alter index DB_USERS_USERNAME_IDX monitoring usage;
Index altered.
Here, I’ve used the ALTER INDEX command to begin monitoring the usage of the three indexes on my table. After time, I can get an idea of which indexes were used and which was not.
ORA9I SQL> select index_name,monitoring,used,start_monitoring
2> from v$object_usage;
INDEX_NAME MON USE START_MONITORING
------------------------------ --- --- -------------------
DB_USERS_ACCOUNT_STATUS_IDX YES YES 10/02/2001 15:43:30
DB_USERS_USERID_IDX YES NO 10/02/2001 15:43:39
DB_USERS_USERNAME_IDX YES YES 10/02/2001 15:43:45
Here, I can see that the DB_USERS_USERID_IDX was never used since monitoring began. It is very easy to turn off monitoring.
ORA9I SQL> select index_name,monitoring,used,
2 end_monitoring from v$object_usage;
INDEX_NAME MON USE END_MONITORING
----------------------------- --- --- -------------------
DB_USERS_ACCOUNT_STATUS_IDX NO YES 10/02/2001 15:50:03
DB_USERS_USERID_IDX NO NO 10/02/2001 15:50:12
DB_USERS_USERNAME_IDX NO YES 10/02/2001 15:50:19
Notice by the second column that these indexes are no longer being monitored.
LOG MINER
Log Miner, originally introduced in Oracle 8i, has been enhanced in Oracle 9i. For starters, Log Miner now supports the following objects:
• Clustered tables
• Chained and migrated rows
• LOB and LONG datatypes
• Direct loads
• Scalar object types
• Data definition statements (DDL)
Log Miner now also has a new GUI interface, a component of Oracle Enterprise Manager. Previously, one had to run a supplied PL/SQL package that loaded a table (accessed through V$LOGMNR_CONTENTS). Log Miner also supports queries on the logs based on the content of changes (for example, show all changes to an employee named Smith).
DBMS_METADATA
If you’ve worked with Oracle for any amount of time, you’ve undoubtedly run across various scripts to generate DDL statements to recreate database objects. Often times, these are called reverse-engineering scripts. You may have created your own reverse-engineering scripts as well. For instance, you’ve probably run across a script to reverse-engineer a table. The problem with these scripts is that they need to be modified to take into account the new features of new database releases. With Oracle 9i, there is now a supplied PL/SQL package that will perform this functionality for you, DBMS_METADATA. It’s pretty easy to use as you can see from the following examples:
ORA9I SQL> select dbms_metadata.get_ddl('TABLE','DB_USERS','PEASLAND')
2> as ddl from dual;
DDL
------------------------------------------------------------------------
CREATE TABLE "PEASLAND"."DB_USERS"
( "USERNAME" VARCHAR2(30) NOT NULL ENABLE,
"USER_ID" NUMBER NOT NULL ENABLE,
"PASSWORD" VARCHAR2(30),
"ACCOUNT_STATUS" VARCHAR2(32) NOT NULL ENABLE,
"LOCK_DATE" DATE,
"EXPIRY_DATE" DATE,
"DEFAULT_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,
"TEMPORARY_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,
"CREATED" DATE NOT NULL ENABLE,
"PROFILE" VARCHAR2(30) NOT NULL ENABLE,
"INITIAL_RSRC_CONSUMER_GROUP" VARCHAR2(30),
"EXTERNAL_NAME" VARCHAR2(4000)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
The above example shows how easy it is to reverse engineer a table. The parameters to the GET_DDL procedure are the object_type, object_name, and owner. Below is an example of extracting the DDL for an index:
ORA9I SQL> select dbms_metadata.get_ddl('INDEX',
2 'DB_OBJECTS_IDX','PEASLAND') as ddl from dual;
DDL
------------------------------------------------------------------------
CREATE INDEX "PEASLAND"."DB_OBJECTS_IDX" ON "PEASLAND"."DB_OBJECTS" ("OBJECT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PEASLAND_IDX"
There is also a GET_XML procedure, which will extract the schema object in XML format.
DBMS_REDEFINITION
The DBMS_REDEFINITION supplied package allows for online reorganization of tables. This helps maintain high availability by letting a DBA perform maintenance against a table while not interrupting DML operations against the table. This is achieved by behind-the-scenes, incremental Materialized Views. While maintenance is being performed against the table, DML operations are logged. After the maintenance is complete, the logged DML operations are reapplied against the redefined table. During the reorganization, the table is only locked for a very short duration to update the data dictionary at the beginning and end of the redefinition.
There are a few restrictions to using this package. They are as follows:
• Tables with no primary keys cannot be reorganized online.
• Tables that have materialized views and materialized view logs defined on them cannot be reorganized online.
• Tables that are materialized view container tables and AQ tables cannot be reorganized online.
• The overflow table of an IOT table cannot be reorganized online.
Before we can begin online redefinition of a table, we need to determine if it can be redefined. We use the CAN_REDEF_TABLE procedure of the package to determine if the table is a candidate for online redefinition. If the table cannot be redefined, then an exception is raised.
ORA9I SQL> execute dbms_redefinition.can_redef_table('PEASLAND','OBJECTS');
BEGIN dbms_redefinition.can_redef_table('PEASLAND','OBJECTS'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "PEASLAND"."OBJECTS" with no primary
key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 236
ORA-06512: at line 1
This table cannot be redefined online because it has no primary key.
ORA9I SQL> alter table objects
2 add (constraint objects_pk primary key (object_id));
Table altered.
ORA9I SQL> execute dbms_redefinition.can_redef_table('PEASLAND','OBJECTS');
PL/SQL procedure successfully completed.
This table can now be redefined online. To do that, we need to create an interim table (in the same schema) that will have our new definition. In our example, we will be dropping columns from the existing table. The new table will only have 4 columns in it. The others will be dropped.
ORA9I SQL> create table new_objects (
2 owner varchar2(30),
3 object_name varchar2(128),
4 object_id number,
5 object_type varchar2(18));
Table created.
We have now created our interim table. We could have changed anything here including the table’s tablespace and storage parameters. We now need to initiate the reorganization process.
ORA9I SQL> exec dbms_redefinition.start_redef_table( -
> 'PEASLAND','OBJECTS','NEW_OBJECTS', -
> 'OWNER OWNER, OBJECT_NAME OBJECT_NAME, OBJECT_ID OBJECT_ID,OBJECT_TYPE OBJECT_TYPE');
PL/SQL procedure successfully completed.
We have started the redefinition process. The first parameter is the schema owner. The second parameter is the original table name. The third parameter is the interim table name. The fourth parameter is the column mappings. I’m not changing the column names here, but I could. If this fourth parameter is NULL, then it is assumed that all columns will be mapped by their original names.
At this point, we could create any indexes or constraints on the interim table.
ORA9I SQL> create index objects_name_idx
2> on new_objects(object_name);
Index created.
At this point, all redefinitions are now complete. Should the redefinitions take a long time, you can synchronize the interim table with the following command:
ORA9I SQL> exec dbms_redefinition.sync_interim_table( -
> 'PEASLAND','OBJECTS','NEW_OBJECTS');
PL/SQL procedure successfully completed.
This step is optional. Synchronization will take place once the redefinition is complete. If you redefinitions take a long time, this can speed up the end phase. Finally, end the redefinition.
ORA9I SQL> exec dbms_redefinition.finish_redef_table( -
> 'PEASLAND','OBJECTS','NEW_OBJECTS');
PL/SQL procedure successfully completed.
To show that the table has been redefined, we’ll describe the table:
ORA9I SQL> desc objects
Name Null? Type
----------------------------------------- -------- --------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
As you can see, this table now only has four columns. The interim table can now be dropped.
TIME DATA
Before Oracle 9i, the only datatype to hold time and date information was the DATE datatype. This datatype would hold the century, year, month, day, hours, minutes, and seconds. Oracle 9i introduced the TIMESTAMP datatype. With the TIMESTAMP datatype, you can store fractional seconds.
ORA9I SQL> create table timings (
2 id number,
3 time timestamp);
Table created.
This table has a column with the new TIMESTAMP datatype. Let’s now look at an example of using fractional seconds.
ORA9I SQL> insert into timings values (1,systimestamp);
1 row created.
ORA9I SQL> insert into timings values (2,systimestamp);
1 row created.
ORA9I SQL> select * from timings;
ID TIME
---------- ------------------------------
1 03-OCT-01 01.35.25.508883 PM
2 03-OCT-01 01.35.34.185915 PM
ORA9I SQL> select b.time-a.time as difference
2 from (select time from timings where id=1) a,
3 (select time from timings where id=2) b;
DIFFERENCE
------------------------------------------------------------------------
+000000000 00:00:08.677032
Here, we used the SYSTIMESTAMP function when inserting values into the table. The SYSTIMESTAMP function is similar to SYSDATE except it returns fractional seconds. Just like the DATE datatype, we can perform arithmetic on the values. Above, we found that there was a difference of 8.677 seconds between the insert statements.
In addition to the TIMESTAMP datatype, there is the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes. There are also a number of functions for these new datatypes including TO_TIMESTAMP and TO_TIMESTAMP_TZ. Also, the database can be created with a time zone in the CREATE DATABASE statement. Additional functions are shown below:
ORA9I SQL> select current_date from dual;
CURRENT_D
---------
03-OCT-01
ORA9I SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
------------------------------------------------------------------------
03-OCT-01 01.44.36.053026 PM -05:00
ORA9I SQL> select dbtimezone from dual;
DBTIME
------
-05:00
The first query shows the current date. The second query shows the current timestamp similar to the SYSTIMESTAMP call we made earlier. The third query shows the database time zone.
LIST PARTITIONING
Before 9i, we could partition a table by a range of values or by a hash on the values. Oracle 8i introduced composite partitioning where both a range and a hash were used to partition the rows in a table. Oracle 9i introduces List Partitioning. This type of partitioning splits rows depending on their values compared to a list of values. This option gives the DBA even more flexibility when designing partitioned tables.
There are some restrictions to list partitioning.
• You can specify only one partitioning key in the column list, and it cannot be a LOB column.
• If the partitioning key is an object type column, you can partition on only one attribute of the column type.
• You cannot specify MAXVALUE in the VALUES clause.
• Each partition value in the VALUES clause must be unique among all partitions of the table.
• You cannot list partition an index-organized table
ORA9I SQL> create table list_part (
2 object_type varchar2(50),
3 object_name varchar2(128))
4 partition by list (object_type) (
5 partition list_part_idx values ('INDEX'),
6 partition list_part_tab values ('TABLE'),
7 partition list_part_others values ('VIEW','SYNONYM'));
Table created.
Here, we have created a list-partitioned table. This table has three partitions. Those rows where the OBJECT_TYPE is INDEX will be placed in the LIST_PART_IDX partition. Those rows where the OBJECT_TYPE is TABLE will be placed in the LIST_PART_TAB partition. Those rows where the OBJECT_TYPE is VIEW or SYNONYM will be placed in the LIST_PART_OTHERS partition. Let’s insert data into this table and see how it works!
ORA9I SQL> insert into list_part
2 select object_type,object_name from dba_objects
3 where object_type in ('INDEX','TABLE','VIEW','SYNONYM');
3774 rows created.
ORA9I SQL> select count(*) from list_part partition (list_part_idx);
COUNT(*)
----------
412
ORA9I SQL> column object_type format a10
ORA9I SQL> column object_name format a20
ORA9I SQL> select * from list_part partition (list_part_idx)
2 where rownum <=10;
OBJECT_TYP OBJECT_NAME
---------- --------------------
INDEX I_COBJ#
INDEX I_CON2
INDEX I_CON1
INDEX I_OBJ1
INDEX I_OBJ#
INDEX I_CCOL2
INDEX I_COL2
INDEX I_UNDO1
INDEX I_FILE1
INDEX I_ICOL1
10 rows selected.
Here, we inserted data into the table. We then verified that 412 rows of the 3,774 rows inserted fell into the LIST_PART_IDX partition. Examining the first 10 rows of this partition revealed that the OBJECT_TYPE is INDEX as advertised.
PGA WORKING MEMORY MANAGEMENT
When a user starts a session, the user allocates a Program Global Area (PGA) for that dedicated session. Any work that needs to be performed in memory for that user is performed in the user’s PGA. For instance, sort operations, hash-joins, and bitmap creations all need a working area in order to complete the task. Before Oracle 9i, there were many INIT.ORA parameters that controlled the size of each user’s PGA. These parameters include SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_
AREA_SIZE. Collectively, these parameters are referred to as the *_AREA_SIZE parameters. The user’s PGA is basically, the sum of the *_AREA_SIZE parameters. It is ideal that certain user’s tasks be performed entirely in memory. If there is not enough memory to perform one of these tasks, then the operation must use sort space on disk, typically in a TEMP tablespace.
The hard part is accurately determining optimal values of the *_AREA_SIZE parameters. If you allocate too much to these parameters, then you waste valuable and limited server memory. If you do not allocate enough, then the process must perform costly I/O to the TEMP tablespace in order to complete the task. Show how do you adequately size these? And aside from SORT_AREA_SIZE, the other parameters allocate space that may never be used by the majority of processes running in the system, especially OLTP systems. Oracle 9i introduces a new, dynamic way to let the database automatically control these different working areas for each process. The portion of the PGA allocated to these working areas can now be adjusted based on the overall target set by the DBA. Keep in mind, this functionality is only for dedicated server processes. Shared server processes with Oracle’s Multi-Threaded Server (MTS) will not benefit from this new functionality.
The PGA_AGGREGATE_TARGET initialization parameter defines the total amount of work area space for all dedicated sessions. When set, the *_AREA_SIZE parameters are ignored for dedicated server processes. The value of PGA_AGGREGATE_TARGET should be determined by the total available physical memory in the system. Oracle Corp. recommends setting this parameter to 16% of the total physical memory for OLTP databases and 40% of the total physical memory for data warehouses. This parameter is system modifiable.
ORA9I SQL> alter system set pga_aggregate_target=160M;
System altered.
ORA9I SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
pga_aggregate_target big integer 167772160
There is some help in tuning this parameter. There are three modes of execution in these work areas. There are optimal modes, one-pass modes, and multi-pass modes. Optimal and one-pass modes are ideal. V$SYSSTAT can show us how many different times operations were performed in each of these three modes since instance startup.
ORA9I SQL> select name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
2 from (select name, value cnt, (sum(value) over ()) total
3 from v$sysstat
4* where name like 'workarea exec%')
ORA9I SQL> /
PROFILE CNT PERCENTAGE
---------------------------------------- ---------- ----------
workarea executions - optimal 7 100
workarea executions - onepass 0 0
workarea executions - multipass 0 0
If the multi-pass operation count is not zero, the DBA may consider raising the PGA_AGGREGATE_TARGET setting. Also, if there are a high number of one-pass executions compared to optimal executions, raise this parameter. The V$SQL_WORKAREA_ACTIVE view shows all active work areas in the database.
ORA9I SQL> select sid, operation_type,
2> trunc(work_area_size/1024) wsize,
3> trunc(expected_size/1024) esize,
4> trunc(actual_mem_used/1024) mem,
5> trunc(max_mem_used/1024) max_mem,
6> number_passes pass
7> from v$sql_workarea_active;
SID OPERATION_TYPE WSIZE ESIZE MEM MAX_MEM PASS
--- --------------------- ----- --------- --------- --------- ----
27 GROUP BY (SORT) 73 73 64 64 0
44 HASH-JOIN 3148 3147 2437 2437 0
71 HASH-JOIN 13241 19200 12884 34684 1
This view shows that SID 71 is currently using a 13MB working area size. But in the past, it has used up to 34 MB of working area memory. If a lot of sessions appear this way, the DBA may want to consider raising the PGA_AGGREGATE_TARGET parameter.
RMAN IMPROVEMENTS
RMAN has been improved in Oracle 9i. This section gives an overview of the RMAN improvements in Oracle 9i.
• RMAN now supports a recovery window (sometimes called a retention policy). This controls when backups expire. This reduces administration time by automation.
• RMAN can now recover individual blocks while leaving the datafile online.
• RMAN can now maintain persistent configuration changes. A number of features can be modified for the session and those changes will endure through future sessions.
• RMAN can now make a mandatory and automatic controlfile backup. This lets RMAN restore even if the backup catalog is not available.
• RMAN can now restart a backup should that backup fail for some reason.
• RMAN now supports the multiple block sizes for tablespaces.
• RMAN can now be configured to skip specified tablespaces.
ANSI CASE SUPPORT
Oracle 9i now supports the ANSI style CASE statement and expression. An example appears below:
ORA9I SQL> DECLARE
2 category CHAR(1);
3 value NUMBER;
4 BEGIN
5 value := 52;
6 DBMS_OUTPUT.PUT_LINE('The value is: ' || value);
7
8 category :=
9 CASE
10 WHEN value BETWEEN 0 AND 9 THEN 'A'
11 WHEN value BETWEEN 10 AND 19 THEN 'B'
12 WHEN value BETWEEN 20 AND 29 THEN 'C'
13 WHEN value BETWEEN 30 AND 39 THEN 'D'
14 WHEN value BETWEEN 40 AND 49 THEN 'E'
15 WHEN value BETWEEN 50 AND 59 THEN 'F'
16 WHEN value BETWEEN 60 AND 69 THEN 'G'
17 WHEN value BETWEEN 70 AND 79 THEN 'H'
18 WHEN value BETWEEN 80 AND 89 THEN 'I'
19 WHEN value BETWEEN 90 AND 99 THEN 'J'
20 ELSE 'Z'
21 END;
22
23 DBMS_OUTPUT.PUT_LINE('The CATEGORY is: ' || category);
24
25 END;
26 /
The value is: 52
The CATEGORY is: F
PL/SQL procedure successfully completed.
In this example, you can clearly see the CASE statement at work. It’s pretty self-explanatory if you’ve used CASE statements in other programming languages.
RESOURCE MANAGER IMPROVEMENTS
Oracle 8i introduced the database Resource Manager. With this component, the DBA can manage system workload by assigning a user to a Consumer Group. Each consumer group is defined to only consume a specific amount of CPU resources. This let’s some users become more “important” than other users in the database. For instance, the DBA may want users who run batch jobs to get less CPU from the system than online users. Oracle 9i has improved the Resource Manager as follows:
• Automatic Consumer Group Switching – Before 9i, the DBA could switch a user from one consumer group to another manually. In 9i, the DBA can have this happen automatically. This is done with new consumer group plan keywords. They are as follows:
o SWITCH_GROUP – A user is switched to this consumer group when the parameters that follow are envoked.
o SWITCH_TIME – If a session is active for this amount of time, it is switched. This let’s users who sit on the machine for too long be switched to a less resource intensive group. Once the user logs off and then back on, they will be in their original group.
o SWITCH_ESTIMATE – If set to TRUE, then the Resource Manager will estimate the execution time to decide if to switch to a new group before the operation starts.
• Operation Queueing – It is possible for too many sessions to become active in the database at one time. For instance, what is stopping a user from starting 20 sessions to run batch jobs all at the same time? Oracle 9i let’s the Resource Manager determine the maximum number of active sessions allowable for a certain group. Any new sessions will be queued until other sessions become inactive or complete. An optional timeout period can specify how long a session waits on the queue before timing out.
• Maximum Estimated Execution Time – A new directive, MAX_ESTIMATED_EXEC_
TIME states how long of an operation is allowed to run. The Resource Manager first estimates how long an operation will take to complete. If the operation is longer than this directive, then the operation will trip an exception and an error will be raised.
• UNDO Quota – The UNDO_POOL directive let’s the DBA control a quota for the amount of UNDO space a user in the consumer group can use. The default value is UNLIMITED.
AUTOMATICALLY GATHERING STATISTICS
Long before Oracle 9i, the Cost Based Optimizer (CBO) was introduced. This SQL optimization method relies heavily on up-to-date statistics. The problem is that these statistics need to be updated on a regular basis. This either involves having someone manually update the statistics or create a DBMS_JOB or cron job to update the statistics.
With Oracle 9i, you can specify the MONITORING clause for a table with the CREATE TABLE or ALTER TABLE commands. This enables DBMS_STATS to automatically gather statistics for you. Then through an automated job, that invokes the DBMS_STATS.GATHER_TABLE_STATS procedure, you can use the GATHER STALE option. This option will only gather statistics on those tables that have experienced a number of DML operations.
EXPORT/IMPORT NEW FEATURES
The Export and Import utilities have a few new features for Oracle 9i as well. The following is a list of those new features.
• Pre-calculated statistics – In previous versions, one could export with a STATISTICS parameter. This meant that the export file contained a statement for the table to analyze the table and compute or estimate statistics. This statement in the export file (if it exists) could be overridden with the STATISTICS parameter in the import utility. Now, the export utility will place the pre-calculated statistics in the export file. This saves the time and trouble of having to re-compute them on import. On import, there are parameters to control whether to use these pre-computed statistics or not. They are:
o ALWAYS – Always import the statistics, even if they are questionable
o SAFE – Only import the statistics if they are safe.
o RECALCULATE – Do not import the statistics. Recompute them instead.
o NONE – Do not import statistics and do not compute them.
• There are new parameters for export and import. They are as follows:
o Export new parameters:
FLASHBACK_SCN - Specifies the system change number (SCN) that Export will use to enable flashback. The export operation is performed with data consistent as of this specified SCN.
FLASHBACK_TIME – Just like above, but uses the time, not the SCN.
RESUMABLE – Used to enable or disable resumable space allocation.
RESUMABLE_NAME – Used to identify the statement that is resumable. This parameter is only used if RESUMABLE=Y.
RESUMABLE_TIMEOUT – Specifies the time period in which the resumable operation must be fixed. Again, this parameter is only used if RESUMABLE=Y.
o Import new parameters
RESUMABLE – As above.
RESUMABLE_NAME – As above.
RESUMABLE_TIMEOUT – As above.
• Some of the parameters for export and import have been revised. They are as follows:
o Export modified parameters
TABLES – This parameter now supports the ‘%’ wildcard. Any tables that match the pattern dictated by the wildcard in that schema will be exported.
TABLESPACES – This parameter does not have to be used in conjunction with the TRANSPORT_TABLESPACE parameter anymore. This means that all tables in that tablespace will be exported.
o Import modified parameters
TABLES – Supports the ‘%’ wildcard to only import tables matching the pattern.
1. Miscellaneous
2. Oracle-Managed Files
3. Deprecation of SVRMGR and CONNECT INTERNAL
4. Quiescing the database
5. Suspending and Resuming the Database
6. UNDO Tablespaces
7. Automatic Segment Space Management
8. Default Temporary Tablespaces
9. Automatically Delete Datafiles
10. Dynamic SGA Sizing
11. Index Skip Scans
12. Multi-table Inserts
13. Merge SQL Statement
14. Resumable Transactions
15. Grouping Sets
16. External Tables
17. ANSI/ISO SQL99 Compliant Joins
18. Flashback Query
19. Multiple Block Sizes
20. Cached Execution Plans
21. Cache Advice
22. Server Parameter File
23. Monitoring Index Usage
24. Log Miner
25. DBMS_METADATA
26. DBMS_REDEFINITION
27. Time Data
28. List Partitioning
29. PGA Working Memory Management
30. RMAN Improvements
31. ANSI CASE Support
32. Resource Manager Improvements
33. Automatically Gathering Statistics
34. Export/Import New Features
MISCELLANEOUS
Why did this section come first? It makes more sense for a “catch-all” section to appear at the end of this document. I only put this section first just for formatting reasons. That’s it! The material in this section does not require reading of other sections. So feel free to read on.
MORE ARCHIVE DESTINATIONS
The number of archive destinations, controlled by LOG_ARCHIVE_DEST_n parameter will now let you specify up to 10 destinations (up from 5 in 8i).
TRANSACTION NAMING
Beginning with Oracle 9i, you can now supply a name for your transactions. This is really helpful with resolving in-doubt, distributed transactions as the transaction name will appear in error messages and the alert log.
ORA9I SQL> set transaction name 'This is my first transaction' ;
Transaction set.
DBCA CHANGES
The Oracle Database Configuration Assistant (DBCA) has changed in Oracle 9i. You can now specify which tablespaces to create, where to individually locate all datafiles, use and create templates for database creation, run custom scripts after database creation, and modify user passwords at database creation.
CONTROLLING ARCHIVE LAG
An online redo log switches when it becomes full. In certain environments this may not be desirable. This is especially true with Standby Databases. We want to periodically apply redo to the standby database. And we want to control that period. If the online redo log has not filled up in the past 30 minutes, we may still want to apply that redo to the standby database to keep it as current as possible. The ARCHIVE_LOG_TARGET initialization parameter defines how many seconds pass before a log switch is forced. If there has been redo and the defined number of seconds has passed, then a log switch takes place. The default is zero which turns off this feature.
REAL APPLICATION CLUSTERS
Oracle 9i has a product called Real Application Clusters, which is the replacement for Oracle Parallel Server. This product provides out-of-the-box, high-availability, linear-scaling performance for Oracle databases. This is done without redesigning the current application. Real Application Clusters makes big use of Cache Fusion, the ability to share the buffer cache between instances.
DATA GUARD
Oracle 9i introduces Oracle Data Guard for standby databases. Oracle Data Guard is a suite of components designed to make standby databases easier to manage, more automated, and provide better stability of data through Zero Data Loss using a physical standby database. Delayed mode lets a DBA intervene before data is applied to the standby database in the event that the user has logically corrupted data.
COMPLETE ONLINE INDEX OPERATIONS
With Oracle 9i, one can now perform any index operations with the ONLINE keyword. This improves availability of schema objects during database maintenance.
ORACLE-MANAGED FILES
In previous Oracle versions, the only time the RDBMS software attempted to manage any datafiles was on initial creation of tablespaces, online redo logs, control files, etc. If a tablespace was dropped, it was up to the DBA to manually remove the tablespace’s datafiles with OS commands. On occasion, a DBA creating a tablespace could attempt to overwrite an existing file in error. This would result in an ORA-01537 error. While not damaging, it could be very frustrating. Now, Oracle has database-managed files. If you create a tablespace, Oracle can automatically generate a filename for you. If you drop an Oracle-managed online redo log, Oracle will automatically remove the file for you if you choose. Oracle touts this feature as a great time saver. In my opinion, it can save time, but not that much. And the side effect of Oracle-generated file names may not be appealing to you. So let’s take a quick look at Oracle-managed files.
ORA9I SQL> create tablespace test;
Tablespace created.
ORA9I SQL> select file_name,bytes from dba_data_files
2 where tablespace_name = 'TEST';
FILE_NAME BYTES
-------------------------------------------------- ----------------
/edcsns14/oradata3/ora9i/ora_test_xog67sr8.dbf 104,857,600
ORA9I SQL> host ls -l /edcsns14/oradata3/ora9i
total 1490224
-rw-r----- 1 oracle dba 104858112 Jul 5 13:26 ora_1_xmnmvyfx.log
-rw-r----- 1 oracle dba 104858112 Jul 5 13:26 ora_1_xmnmwdlw.log
-rw-r----- 1 oracle dba 104858112 Jul 19 12:55 ora_2_xmnmwxx4.log
-rw-r----- 1 oracle dba 104858112 Jul 19 12:55 ora_2_xmnmxcqc.log
-rw-r----- 1 oracle dba 10493952 Jun 27 17:22 ora_dflt_ts_xmnmznby.tmp
-rw-r----- 1 oracle dba 112205824 Jul 19 12:26 ora_system_xmnmxw0r.dbf
-rw-r----- 1 oracle dba 104865792 Jul 19 12:55 ora_test_xog67sr8.dbf
-rw-r----- 1 oracle dba 119545856 Jul 19 12:52 ora_undo_ts_xmnmzgf2.dbf
-rw-r----- 1 oracle dba 2121728 Jul 19 12:55 ora_xmnmvxr6.ctl
-rw-r----- 1 oracle dba 2121728 Jul 19 12:55 ora_xmnmvy2n.ctl
ORA9I SQL> drop tablespace test;
Tablespace dropped.
ORA9I SQL> host ls -l /edcsns14/oradata3/ora9i
total 1285280
-rw-r----- 1 oracle dba 104858112 Jul 5 13:26 ora_1_xmnmvyfx.log
-rw-r----- 1 oracle dba 104858112 Jul 5 13:26 ora_1_xmnmwdlw.log
-rw-r----- 1 oracle dba 104858112 Jul 19 12:55 ora_2_xmnmwxx4.log
-rw-r----- 1 oracle dba 104858112 Jul 19 12:55 ora_2_xmnmxcqc.log
-rw-r----- 1 oracle dba 10493952 Jun 27 17:22 ora_dflt_ts_xmnmznby.tmp
-rw-r----- 1 oracle dba 112205824 Jul 19 12:26 ora_system_xmnmxw0r.dbf
-rw-r----- 1 oracle dba 119545856 Jul 19 12:52 ora_undo_ts_xmnmzgf2.dbf
-rw-r----- 1 oracle dba 2121728 Jul 19 12:55 ora_xmnmvxr6.ctl
-rw-r----- 1 oracle dba 2121728 Jul 19 12:55 ora_xmnmvy2n.ctl
In this short example, we created a tablespace. Notice that we never provided a file name, file size, or any other attributes. The CREATE TABLESPACE command was short and sweet. Looking at the DBA_DATA_FILES, we see that a file name has been automatically generated for us and that the file was created with a default size of 100 Mbytes. As a final step, the tablespace was dropped. We can see clearly that the file was deleted when the tablespace was dropped.
FILENAMES
It is pretty easy to see the cryptic generated filenames. For example, when we created the TEST tablespace, the ora_test_xog67sr8.dbf file was generated for us. Filenames are generated based on the following table:
Table 1. Generated Filenames
Where %t is the tablespace name (up to 8 characters), %g is the online redo log group number, and %u is an eight-character string to guarantee uniqueness among filenames. To me, it is this number that leads to the most confusion when looking at filenames.
SETTING UP
Before one can start using Oracle-managed files, the database must be configured to support this feature. As an aside, new features of Oracle 9i will not be available to you unless you set COMPATIBLE = 9.0 in the INIT.ORA file at a bare minimum! This is true for other sections of this paper as well, but I only mention it here.
How did my previous example know which directory to place the Oracle-managed files? Simple, I told it where to place the files with an INIT.ORA parameter. There are really only two INIT.ORA parameters that you need to configure. They are shown in the following table:
Table 2. Oracle-managed file INIT.ORA parameters
If no file specification is given, the DB_CREATE_FILE_DEST parameter tells Oracle where to place the datafile. This directory must exist before Oracle can try to create a file there. Oracle will not create the directory for you. If the INIT.ORA parameter DB_CREATE_
ONLINE_LOG_DEST_n is specified (n is between 1 and 5), then members of online redo log files and control files will be placed in these directories. If three directories are specified (n = 1, 2, and 3), then three redo log members (or control files) will be created for each created group. If this parameter is omitted, then online redo log files and control files will be created in the directory specified by the DB_CREATE_FILE_DEST parameter.
FILE SIZES
Oracle-managed files are created with the following default sizes if no size is specified.
Table 3. Oracle-managed default file sizes
Control files do not have a default size since their size is determined by how much information they need to hold. Control files grow automatically and their size cannot easily be controlled.
MULTIPLE DESTINATIONS?
As you have seen, there is only one parameter that controls the placement of Oracle-managed datafiles, DB_CREATE_FILE_DEST. And as I’m sure you are aware, placing all your datafiles in one directory is a sure fire way to introduce performance problems in a high-volume database. So how do you place Oracle-managed files in other directories? The DB_CREATE_FILE_DEST is a session and system modifiable parameter. To create an Oracle-managed file in a different directory, see the following example:
ORA9I SQL> alter session set db_create_file_dest = '/edcsns14/oradata2/ora9i';
Session altered.
ORA9I SQL> create tablespace newtest;
Tablespace created.
ORA9I SQL> select file_name from dba_data_files where tablespace_name='NEWTEST';
FILE_NAME
--------------------------------------------------
/edcsns14/oradata2/ora9i/ora_newtest_xogf5g27.dbf
With this method, the destination was changed and the new file was created in that new destination.
WEIGHING THE OPTION
So now you know what an Oracle-managed file is and how it works. When is a good time to use Oracle-managed files? Oracle-managed files are great for quick and easy, low-end or test databases. Due to the seemingly cryptic filenames one would not want to use this option in a high-end production environment where recognizing filenames can be of utmost importance to a DBA. They are also good for systems that employ a Logical Volume Manager to administer RAID environments.
DEPRECATION OF SVRMGR AND CONNECT INTERNAL
Oracle 9i now sees the disappearance of the old SVRMGR utility and the familiar CONNECT INTERNAL method of starting and stopping databases. Now you must connect as SYSDBA (or SYSOPER) before you can perform privileged commands. Out of the box, SYS is granted SYSDBA privileges.
edcsns14% sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Thu Jul 19 14:44:28 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
Notice that SQL*Plus is started with the /NOLOG option. This starts SQL*Plus without it prompting you for a userid and password similar to the default behavior of the old SVRMGR. After that, you CONNECT AS a SYSDBA (or SYSOPER). The SYS user is not the only one who can be granted this privilege. To grant SYSDBA or SYSOPER to another user, log on as SYS and issue:
GRANT sysdba TO other_user;
One other point to note is that you can no longer connect to SYS without specifying SYSDBA or SYSOPER.
edcsns14% sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Wed Oct 3 14:06:27 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect sys
Enter password:
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
QUIESCING THE DATABASE
At times, it is desirable to put the database in a state where only “DBA actions” are allowed, i.e. no user transactions. Prior to Oracle 9i, shutting down the database and then starting the database in restricted mode accomplished this. But this meant that the database must be brought down first. While Oracle Corporation states in their press releases that this “is much less of a restriction because it eliminates the disruption to users and downtime associated with shutting down and restarting the database.” The only real benefit to queiscing the database is that user transactions cease, database administration functions take place, and then users can continue their work. In starting and stopping the database, users must sign back on to the database to continue after it has been opened. To quiesce a database, issue the following command:
ALTER SYSTEM QUIESCE RESTRICTED;
In order to quiesce a database, you must have the Database Resource Manager enabled and it must have been running since instance startup. To resume normal operations, issue the following command:
ALTER SYSTEM UNQUIESCE;
One can determine the state of the database by querying the ACTIVE_STATE column of V$INSTANCE. The states are NORMAL, QUIESCING, QUIESCED and are self-explanatory.
SUSPENDING AND RESUMING THE DATABASE
Suspending a database halts all input and output to datafiles and control files. This is “normally” used to backup a database without interference and without having to bring it down. When the database is suspended, any current I/O operations will complete, but new ones will not be allowed to start. After administration work has been performed, the database is resumed. The following shows how to suspend and resume a database:
SQL> alter system suspend;
System altered.
SQL> select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS from
2> v$instance;
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS
---------------- ---------- ------- -----------------
ora9i edcsns14 OPEN SUSPENDED
SQL> alter system resume;
System altered.
Notice that after the database was suspended, the database status changed.
UNDO TABLESPACES
In previous Oracle versions, undoing transactions meant rollback segments. Most DBA’s set up rollback segments in a tablespace (or two) designed to specifically hold rollback segments. It used to be part of a DBA’s duties to manage storage for these rollback segments within the rollback segment tablespace. With an UNDO tablespace, the system takes control over managing space allocations and rollback segment administration. According to the Oracle documentation, you cannot use both UNDO tablespaces and the traditional rollback segments.
CONFIGURATION
Before you can use UNDO tablespaces, you must first configure the database to handle this function for you. This is done with the INIT.ORA parameters on the following page.
Table 4. UNDO TS INIT.ORA Parameters
Before the database will manage your UNDO segments, you must set UNDO_MANAGEMENT = AUTO. The default is MANUAL. The UNDO_TABLESPACE parameter specifies which tablespace to use for undo segments. Only one tablespace can be the UNDO tablespace. The UNDO_RETENTION parameter specifies how many seconds to retain UNDO (default=900 seconds). The number of online rollback segments to automatically start is determined by the ROLLBACK_SEGMENTS, MAX_ROLLBACK_SEGMENTS, TRANSACTIONS, and TRANSACTIONS_PER_ROLLBACK_SEGMENT parameters. Oracle handles all of this for you.
UNDO TABLESPACES
The UNDO tablespace is not like other tablespaces. It must be created explicitly for UNDO. This can be accomplished with the CREATE UNDO TABLESPACE command.
SQL> create undo tablespace undo2;
Tablespace created.
SQL> alter system set undo_tablespace=undo2;
System altered.
You can switch between multiple undo tablespaces by modifying the system-modifiable parameter as seen above.
UNDO RETENTION
As seen previously, the UNDO_RETENTION parameter controls how long UNDO is kept in the UNDO tablespace for consistent reads. The real nice thing about this is that retained undo can survive instance crashes and shutdowns. And undo retention is also important in Flashback Queries (discussed later in this paper).
MONITORING UNDO
One thing to note is the system generated rollback segment names when using automatic UNDO management. See the following query for an example:
SQL> select usn,name from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 rows selected.
The SYSTEM rollback segment was created at database creation and resides in the SYSTEM tablespace. The other 10 rollback segments were created automatically for me.
In addition, the V$UNDOSTAT performance view has been introduced to help you determine how much overall space the UNDO tablespace requires. Each row in the view corresponds to a ten-minute interval. For example:
SQL> select to_char(begin_time,'MM/DD/YY HH24:MI:SS') as start_time,
2 to_char(end_time,'MM/DD/YY HH24:MI:SS') as end_time,
3 undoblks,txncount from v$undostat;
START_TIME END_TIME UNDOBLKS TXNCOUNT
----------------- ----------------- ---------- ----------
07/19/01 17:07:57 07/19/01 17:08:03 0 0
07/19/01 16:57:57 07/19/01 17:07:57 0 2
07/19/01 16:47:57 07/19/01 16:57:57 0 2
07/19/01 16:37:57 07/19/01 16:47:57 2 2
07/19/01 16:27:57 07/19/01 16:37:57 6 36
07/19/01 16:17:57 07/19/01 16:27:57 0 2
07/19/01 16:07:57 07/19/01 16:17:57 0 2
07/19/01 15:57:57 07/19/01 16:07:57 0 2
07/19/01 15:47:57 07/19/01 15:57:57 0 2
07/19/01 15:37:57 07/19/01 15:47:57 0 2
07/19/01 15:27:57 07/19/01 15:37:57 1 2
START_TIME END_TIME UNDOBLKS TXNCOUNT
----------------- ----------------- ---------- ----------
07/19/01 15:17:57 07/19/01 15:27:57 0 2
07/19/01 15:07:57 07/19/01 15:17:57 0 2
07/19/01 14:57:57 07/19/01 15:07:57 0 2
07/19/01 14:47:57 07/19/01 14:57:57 0 2
07/19/01 14:37:57 07/19/01 14:47:57 1 3
07/19/01 14:27:57 07/19/01 14:37:57 0 3
07/19/01 14:17:57 07/19/01 14:27:57 2 9
18 rows selected.
This view shows the start and end times of the interval with the number of undo database blocks required and the transaction count.
AUTOMATIC SEGMENT SPACE MANAGEMENT
In an effort to simplify database management, Oracle 9i has introduced automatic segment space management. In previous versions of Oracle, there was considerable time spent in determining space management parameters for database segments. These parameters are PCTUSED, FREELISTS, FREELIST GROUPS. With automatic segment space management, the DBA is freed from configuring these parameters. Automatic segment space management is performed by bitmaps to track the space utilization of each database block. The database now has a more accurate picture of space usage in the block. DML operations will perform better since free space lookups can be performed faster. This feature is only available in permanent, Locally Managed Tablespaces. To create a tablespace that uses automatic segment space management, issue a command like the following:
ORA9I SQL> create tablespace users datafile
2 '/edcsns14/oradata3/ora9i/users01.dbf' size 100m
3 extent management local
4 uniform size 40k
5 segment space management auto;
Tablespace created.
Notice line 5 in the above example. This clause is all that is required for automatic segment space management. The default is for MANUAL segment space management. If the extent management is UNIFORM size, then this size must be at least five database blocks. If the extent management size is AUTOALLOCATE and if the database block size is 16K or greater, then the minimum extent size is 1M. When using automatic segment space management, any specification of PCTUSED, FREELISTS, or FREELIST GROUPS is ignored.
The DBA_TABLESPACES view has been modified to give information on automatic segment space management. See below for an example:
ORA9I SQL> select tablespace_name,segment_space_management
2 from dba_tablespaces;
TABLESPACE_NAME SEGMEN
------------------------------ ------
SYSTEM MANUAL
UNDO_TS MANUAL
DFLT_TS MANUAL
UNDO2 MANUAL
WORKING MANUAL
USERS AUTO
6 rows selected.
Notice how the tablespace we just created is set up for automatic segment space management.
DEFAULT TEMPORARY TABLESPACES
In previous versions of Oracle, a newly created user was assigned the SYSTEM tablespace for the user’s temporary tablespace by default. This meant that a DBA had to remember to change the user’s temporary tablespace either when creating the user or with the ALTER USER command. Forgetting this crucial step could have significant implications. Any big sort imposed by the user could hog up critical space in the SYSTEM tablespace. Oracle 9i now lets the DBA define a tablespace as the default temporary tablespace. This does not tie all users to that temporary tablespace. A DBA can assign a different temporary tablespace to any user at any time. But if the DBA “forgets” to assign a temporary tablespace, the user defaults to a “good” temporary tablespace, not the SYSTEM tablespace.
create database ora9i
datafile size 100M autoextend on next 1M maxsize 300M
DEFAULT TEMPORARY TABLESPACE dflt_ts TEMPFILE SIZE 10M
UNDO TABLESPACE undo_ts DATAFILE SIZE 10M
maxlogfiles 32
maxlogmembers 3
maxdatafiles 100
maxinstances 3
character set "US7ASCII"
noarchivelog;
This code shows how to define the default temporary tablespace at database creation time. The default temporary tablespace must be a locally managed tablespace. If the above clause is omitted, then the default temporary tablespace defaults to the SYSTEM tablespace as in previous releases. You cannot specify the SYSTEM tablespace in this clause. The ALTER DATABASE command can be used to change the default temporary tablespace.
ORA9I SQL> create temporary tablespace temp tempfile
2 '/edcsns14/oradata3/ora9i/temp01.dbf' size 10m
3 extent management local uniform size 64k;
Tablespace created.
ORA9I SQL> alter database default temporary tablespace temp;
Database altered.
Here, we created a new temporary tablespace. We then changed the default temporary tablespace to this new tablespace. To determine the current default temporary tablespace, issue the following command:
ORA9I SQL> select property_value
2 from database_properties
3 where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------
TEMP
AUTOMATICALLY DELETE DATAFILES
We’ve already seen that Oracle managed files will drop a datafile when the tablespace is dropped. But what if the file is not Oracle-managed? We can still drop the datafile when we drop the tablespace.
ORA9I SQL> create tablespace dummy_ts datafile
2 '/edcsns14/oradata3/ora9i/dummy_ts01.dbf' size 1m;
Tablespace created.
ORA9I SQL> host ls /edcsns14/oradata3/ora9i
dummy_ts01.dbf ora_system_xmnmxw0r.dbf ora_xmnmvy2n.ctl
ora_1_xmnmvyfx.log ora_undo2_xognrkkg.dbf temp01.dbf
ora_1_xmnmwdlw.log ora_undo_ts_xmnmzgf2.dbf users01.dbf
ora_2_xmnmwxx4.log ora_working_xp30c10o.dbf
ora_2_xmnmxcqc.log ora_xmnmvxr6.ctl
ORA9I SQL> drop tablespace dummy_ts including datafiles;
drop tablespace dummy_ts including datafiles
*
ERROR at line 1:
ORA-01911: CONTENTS keyword expected
ORA9I SQL> drop tablespace dummy_ts including contents and datafiles;
Tablespace dropped.
ORA9I SQL> host ls /edcsns14/oradata3/ora9i
ora_1_xmnmvyfx.log ora_system_xmnmxw0r.dbf ora_xmnmvxr6.ctl
ora_1_xmnmwdlw.log ora_undo2_xognrkkg.dbf ora_xmnmvy2n.ctl
ora_2_xmnmwxx4.log ora_undo_ts_xmnmzgf2.dbf temp01.dbf
ora_2_xmnmxcqc.log ora_working_xp30c10o.dbf users01.dbf
In this example, we first create a tablespace that we will drop. We can verify that the datafile for the tablespace exists. We then try to drop the database and its datafile(s). An error is flagged. You can see that I had to drop the tablespace, its contents and then its datafiles. Even though the tablespace was completely empty, you must specify the INCLUDING CONTENTS to be able to delete the datafiles at the same time. Notice that after the command was executed, the datafile was deleted as well.
DYNAMIC SGA SIZING
There are numerous INIT.ORA parameters that control the size of the System Global Area (SGA) and its components. For instance, to control the size of the Shared Pool, one would modify the SHARED_POOL_SIZE parameter. This was simply done by editing the INIT.ORA file with the new parameter size. But the change in size did not take effect until the database was bounced. Even taking the database down for a moment to resize SGA parameters can be harmful to 24x7 operations. Oracle 9i introduced the ability to dynamically resize the SGA without having to bounce the database. All INIT.ORA parameters that determine the size of the SGA can be dynamically altered while the instance is running.
SQL> alter system set shared_pool_size=10000000;
System altered.
Any SGA parameter can now be modified without having to bounce the database. This can help ensure a good 24x7-operating environment. One note: in order for me to get this functionality to work correctly, I had to use a SPFILE. For more information, see the section further in this document.
INDEX SKIP SCANS
For some time (longer than I’ve been around), Oracle has allowed a composite index. A composite index is an index on a table based on multiple columns of that table. The composite index is only used if the leading columns of the index participate in the WHERE clause. Oracle 9i introduced the capability to use an Index Skip Scan to take advantage of the index even when the leading columns of the index were not used. To show you what I mean I will create a test table in both an Oracle 8i and 9i database. I will then create an index on that table. I will then show you how an index skip scan will improve performance in the 9i database by using an Index Skip Scan where the performance was not improved in the 8i database. In both databases, I issued the following:
SQL> create table test as select object_id,object_name from dba_objects;
Table created.
SQL> create index test_idx on test(object_id,object_name);
Index created.
SQL> execute dbms_stats.gather_schema_stats(NULL);
PL/SQL procedure successfully completed.
This created a test table, populated it with data, and then created an index on two columns of that table. After the tables and indexes were created, I generated statistics using a PL/SQL supplied package. This helps the Cost Based Optimizer determine the best execution path. I will now execute two queries against the Oracle 8i database.
ORA8I SQL> column object_name format a30
ORA8I SQL> set autotrace on
ORA8I SQL> select * from test where object_id=100;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
100 I_IDL_UB11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card
=1 Bytes=20)
This first query used the index on the table since the leading column of the index (OBJECT_TYPE) was present in the WHERE clause. Now, let’s exclude this leading column from our query and query on just the second column of the index.
RS14 SQL> select * from test where object_name='I_IDL_UB11';
OBJECT_ID OBJECT_NAME
---------- ------------------------------
100 I_IDL_UB11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
It is obvious that the index was not used in this example. Now let’s look at this same query on a 9i database.
ORA9I SQL> column object_name format a30
ORA9I SQL> set autotrace on
ORA9I SQL> select * from test where object_id=100;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
100 TRUSTED_LIST$
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card
=1 Bytes=20)
Here, we used the leading column of the index. As before, an Index Range Scan was performed. Now, let’s query on the other column.
ORA9I SQL> select * from test where object_name='TRUSTED_LIST$';
OBJECT_ID OBJECT_NAME
---------- ------------------------------
100 TRUSTED_LIST$
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=20)
1 0 INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=4
Card=1 Bytes=20)
Here, you can see that the TEST_IDX index was still used even though we did not use the leading column of the index. The Index Skip Scan method was used on this index. By using this feature, one does not need to create an additional index on the OBJECT_NAME column like they had to previous to Oracle 9i.
MULTI-TABLE INSERTS
In previous Oracle versions, the INSERT statement would only insert into one table at a time. Beginning with Oracle 9i, an INSERT statement can insert into more than one table at a time. This operation should be much faster since the source data is only scanned once.
ORA9I SQL> create table test1 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table test2 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> set timing on
ORA9I SQL> set echo on
ORA9I SQL> @insert_two_stmts.sql
ORA9I SQL> INSERT INTO test1 SELECT * FROM dba_objects;
4800 rows created.
Elapsed: 00:00:02.15
ORA9I SQL> INSERT INTO test2 SELECT * FROM dba_objects;
4800 rows created.
Elapsed: 00:00:02.28
In this example, you can see that I created two test tables (the ‘0=1’ condition helps create a table with no rows). As you can clearly see, I read from one view, dba_objects, and inserted data into these two test tables. The total time to perform these two inserts was over 4.5 seconds.
Note: In my example, these test tables are identical, but do not need to be. They can be different so long as the INSERT statement is syntactically correct.
Now let’s see how a multi-table insert will look and perform.
ORA9I SQL> INSERT ALL
2 INTO test1
3 INTO test2
4 SELECT * FROM dba_objects;
9600 rows created.
Elapsed: 00:00:03.81
Here, I used one INSERT command to insert data into both tables. Notice that the overall execution time was an improvement over executing this as two separate INSERT commands. For very large tables, the improvement will be much more noticeable. This type of multi-table insert is called an “Unconditional ALL INSERT”.
There is a “Conditional ALL INSERT” as well. This can give you some control over which table will receive the data. For instance, look at the following example:
ORA9I SQL> create table db_tables as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table db_views as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> insert all
2 when object_type='TABLE'
3 then into db_tables
4 when object_type='VIEW'
5 then into db_views
6 select * from dba_objects;
2025 rows created.
ORA9I SQL> select count(*) from db_tables;
COUNT(*)
----------
384
ORA9I SQL> select count(*) from dba_objects
2 where object_type='TABLE';
COUNT(*)
----------
384
ORA9I SQL> select count(*) from db_views;
COUNT(*)
----------
1641
ORA9I SQL> select count(*) from dba_objects
2 where object_type='VIEW';
COUNT(*)
----------
1641
Here, I created two tables. One will hold all database tables (db_tables) and the other will hold all database views (db_views). I then used a conditional insert statement, selecting from all database objects and inserting into these two tables based on a condition. When the INSERT statement is complete, the number of records in db_tables should be equivalent to the total number of tables in the database. Similarly, this should hold for the views and db_views. This was shown with the ‘count’ statements. One thing to note for this type of INSERT statement is that for any rows returned by the SELECT clause, those rows will be inserted into all tables that match the WHEN clause.
There is one more type of multi-table INSERT statement that I’d like to discuss. This is the ‘Conditional FIRST INSERT’ statement. In the previous statement, any row is capable of being inserted into all the tables that match the WHEN clause. But sometimes, it is preferable to insert the row into the first match in the WHEN clause. The following is an example:
ORA9I SQL> create table db_objects_1000 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table db_objects_2000 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table db_objects_3000 as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> create table db_objects_rest as
2 select * from dba_objects where 0=1;
Table created.
ORA9I SQL> insert first
2 when object_id <=1000 then into db_objects_1000
3 when object_id <=2000 then into db_objects_2000
4 when object_id <=3000 then into db_objects_3000
5 else into db_objects_rest
6 select * from dba_objects;
4802 rows created.
ORA9I SQL> select count(*) from db_objects_rest;
COUNT(*)
----------
1822
ORA9I SQL> select count(*) from db_objects_2000;
COUNT(*)
----------
1000
This example also introduced the ELSE clause.
MERGE SQL STATEMENT
The MERGE statement lets a single SQL statement either conditionally insert or update a table by selecting rows from another table. If the row already exists, an update is performed. Otherwise, an insert is performed. For our example, let’s look at two sample tables:
ORA9I SQL> select * from category;
ID CATEGORY
---------- ------------------------------
1 Logs
2 Writes
3 Reads
4 Updates
5 Deletes
6 Selects
6 rows selected.
ORA9I SQL> select * from new_category;
ID CATEGORY
---------- ------------------------------
1 Bad
2 Good
When trying to insert data into new_category that is selected from category, a constraint violation occurs.
ORA9I SQL> insert into new_category select * from category;
insert into new_category select * from category
*
ERROR at line 1:
ORA-00001: unique constraint (PEASLAND.NEW_CAT_PK) violated
This exception occurs because the primary key constraint was violated. There are duplicate ID entries as we have already seen. Wouldn’t it be nice if we could insert only those records that do not violate the primary key condition? And at the same time, let’s update the records that do match!
ORA9I SQL> merge into new_category n
2 using category c
3 on (n.id = c.id)
4 when matched then
5 update set n.category = n.category || ' ' || c.category
6 when not matched then
7 insert (n.id,n.category) values (c.id,'NEW ' || c.category);
6 rows merged.
ORA9I SQL> select * from new_category;
ID CATEGORY
---------- ------------------------------
1 Bad Logs
2 Good Writes
5 NEW Deletes
6 NEW Selects
4 NEW Updates
3 NEW Reads
6 rows selected.
The INTO clause specifies the target table. The USING clause specifies the source table. The ON clause specifies the condition where an update or an insert is performed. When the condition is MATCHED, then the update command is performed. When the condition is NOT MATCHED, then the insert is performed. Take particular note of how the columns were modified here.
RESUMABLE TRANSACTIONS
Pre-9i, if a transaction ran out of space, an exception was raised and the transaction aborted. The DBA had to fix the space problem and then the transaction had to be restarted. Often times, this was a detriment because the long running transaction had to be restarted from the beginning. In 9i, a transaction can be suspended until the DBA resolves the space issue. Once the space issue has been resolved, the transaction can be resumed. In order for a transaction to be resumable, the user must have been given the RESUMABLE privileges.
ORA9I SQL> grant resumable to peasland;
Grant succeeded.
Then the user must start a resumable session and then start their transaction.
ORA9I SQL> alter session enable resumable;
Session altered.
ORA9I SQL> insert into my_db_objects select * from db_objects;
At this point, the user’s session hangs. They are not given any warning messages. The DBA can verify that this resumable transaction is suspended by querying the DBA_RESUMABLE view.
ORA9I SQL> select user_id,status,start_time,suspend_time,error_number,
2> error_msg from dba_resumable;
USER_ID STATUS START_TIME SUSPEND_TIME ERROR_NUMBER
---------- --------- -------------------- ----------------- ------------
ERROR_MSG
------------------------------------------------------------------------
18 SUSPENDED 09/12/01 17:14:34 09/12/01 17:14:38 1631
ORA-01631: max # extents (100) reached in table PEASLAND.MY_DB_OBJECTS
Here, the DBA can see that a transaction has been suspended. The reason for the suspension is clearly obvious here. This is also logged in the alert log as such:
Wed Sep 12 17:14:38 2001
statement in resumable session 'User PEASLAND(18), Session 8, Instance 1' was suspended due to
ORA-01631: max # extents (100) reached in table PEASLAND.MY_DB_OBJECTS
The DBA takes measures to resolve the problem. The alert log shows that the session was resumed:
Wed Sep 12 17:18:55 2001
statement in resumable session 'User PEASLAND(18), Session 8, Instance 1' was resumed
And the DBA_RESUMABLE view shows that operation is NORMAL:
ORA9I SQL> select user_id,status,start_time,suspend_time,error_number,
2 error_msg from dba_resumable;
USER_ID STATUS START_TIME SUSPEND_TIME ERROR_NUMBER
---------- --------- -------------------- ----------------- ------------
ERROR_MSG
------------------------------------------------------------------------
18 NORMAL 09/12/01 17:14:34 0
Notice here that the error number is zero, indicating no error.
GROUPING SETS
Oracle 8i introduced new aggregate functions CUBE and ROLLUP. Oracle 9i extends the aggregate functions even further with grouping sets and concatenated grouping sets. Prior to 9i, a GROUP BY aggregate query could like:
ORA9I SQL> select deptno,job,sum(sal) as total_salary
2 from emp
3 group by deptno,job;
DEPTNO JOB TOTAL_SALARY
---------- --------- ------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
9 rows selected.
We can now group by sets as seen below.
ORA9I SQL> select deptno,job,sum(sal) as total_salary
EXTERNAL TABLES
Oracle 9i introduces tables that can be stored outside of the database in a flat file. These external tables are read-only tables to the database. This functionality was primarily introduced to assist data warehouses. By creating an external table that can be queried easily with SQL commands, data can be quickly transformed and loaded into existing data warehouse tables.
In my example, I have a simple flat file that looks like the following:
edcsns14% cat people.txt
Joe Smith,123-45-6789,(605)123-4567
Julie Smith,234-56-7890,(605)234-5678
Jane Doe,012-34-5678,(123)456-7890
John Doe,012-34-5679,(123)456-7891
As you can see, this file holds names in one column with their respective SSN’s and phone numbers. We will create an external table for this simple flat file. Before we can do that, we need to create a directory in the database.
ORA9I SQL> create directory external_tab_dir
2 as '/edcsns14/oradata3/ora9i/ext_tab';
Directory created.
This directory is the directory on the server that holds the flat file. Once this is in place, we will use the Oracle directory name in our CREATE TABLE command.
ORA9I SQL> create table people_external (
2 name varchar2(13),
3 ssn varchar2(11),
4 phone varchar2(13))
5 organization external
6 ( type oracle_loader
7 default directory external_tab_dir
8 access parameters
9 ( records delimited by newline
10 badfile 'bad_ext_records'
11 logfile 'log_ext_records'
12 fields terminated by ','
13 missing field values are null
14 (name,ssn,phone)
15 )
16 location ('people.txt')
17 )
18* reject limit unlimited;
Table created.
We have now created an external table! This external table’s columns are defined in lines 2-4 just like a normal table. Line 5 signifies that this table is an external table. How does the data become available to our queries? Line 6 shows that SQL*Loader is used to get the information out of the flat file. Line 12 is a directive to SQL*Loader that the comma is the delimiter. Line 14 maps the columns in the flat file to the columns of the table. Line 16 indicates which file contains the data.
We can now use the external table in our queries.
ORA9I SQL> select * from people_external;
NAME SSN PHONE
------------- ----------- -------------
Joe Smith 123-45-6789 (605)123-4567
Julie Smith 234-56-7890 (605)234-5678
Jane Doe 012-34-5678 (123)456-7890
John Doe 012-34-5679 (123)456-7891
Here, you can see that we can query the contents of this table just as if the table existed inside the database. Remember that this table is read-only. What happens if we try to update the contents of this table?
ORA9I SQL> update people_external set ssn='098-76-5432'
2 where name='Joe Smith';
update people_external set ssn='098-76-5432'
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
An exception is raised.
2 from emp
3 group by
4 grouping sets ((deptno,job),(job));
DEPTNO JOB TOTAL_SALARY
---------- --------- ------------
20 ANALYST 6000
ANALYST 6000
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
MANAGER 8275
10 PRESIDENT 5000
PRESIDENT 5000
30 SALESMAN 5600
SALESMAN 5600
14 rows selected.
Here, we group by two sets of information. The first grouping set is based on the deptno,job columns. After that, we further aggregate on the job column. Another example follows:
ORA9I SQL> select deptno,job,mgr,avg(sal)
2 from emp
3 group by
4* grouping sets ((deptno,job,mgr),(deptno,job));
DEPTNO JOB MGR AVG(SAL)
---------- --------- ---------- ----------
10 CLERK 7782 1300
10 CLERK 1300
10 MANAGER 7839 2450
10 MANAGER 2450
10 PRESIDENT 5000
10 PRESIDENT 5000
20 ANALYST 7566 3000
20 ANALYST 3000
20 CLERK 7788 1100
20 CLERK 7902 800
20 CLERK 950
20 MANAGER 7839 2975
20 MANAGER 2975
30 CLERK 7698 950
30 CLERK 950
30 MANAGER 7839 2850
30 MANAGER 2850
30 SALESMAN 7698 1400
30 SALESMAN 1400
19 rows selected.
In this example, we have two grouping sets again. To get an idea of how this data is grouped by sets, let’s look at department 20. Notice that we have two groups based on the deptno,job,mgr columns. The clerks for manager 7788 average 1100 for their salary. The clerks for manager 7902 average 800 for their salary. Below that line, is the grouping set result for the depot,job columns. And the average salary for that grouping set is shown, i.e. 950.
These grouping set queries are more powerful than CUBE and UNION ALL queries. They generate unnecessary groupings or extra queries that lead to inefficient processing. The grouping sets are much more efficient.
Now let’s look at a concatenated grouping set.
ORA9I SQL> select deptno,job,mgr,sum(sal) as total_salary
2 from emp
3 group by
4 grouping sets (deptno,job),
5 grouping sets (mgr);
DEPTNO JOB MGR TOTAL_SALARY
---------- --------- ---------- ------------
10 7782 1300
10 7839 2450
10 5000
20 7566 6000
20 7788 1100
20 7839 2975
20 7902 800
30 7698 6550
30 7839 2850
ANALYST 7566 6000
CLERK 7698 950
CLERK 7782 1300
CLERK 7788 1100
CLERK 7902 800
MANAGER 7839 8275
PRESIDENT 5000
SALESMAN 7698 5600
17 rows selected.
Notice here that there are two distinct grouping sets unlike our first examples. The first grouping set is on the deptno,job columns. The second grouping set is only on the mgr column. The results are the possible permutations between the two sets. In our case, we are grouped on the deptno,mgr and job,mgr columns.
ANSI/ISO SQL99 COMPLIANT JOINS
One of the things that I’ve often heard is that Oracle is not compliant with the ANSI SQL standards, especially for joins. All major RDBMS vendors claim to be ANSI compliant when it comes to SQL. But each vendor can have different SQL syntax. If they are ANSI compliant, then how can they have different SQL syntax? What some people don’t realize is that there are four levels to the ANSI SQL standards. In order to put the “ANSI Compliant” tag on their product, they only need to comply with the first level of the ANSI standards. All major RDBMS vendors comply with at least the first level. After that, they pick and choose which standards to employ in their database engine. So outside of the first level of the ANSI SQL standards, databases can differ in their SQL syntax and functionality.
People who come from another RDBMS vendor to Oracle often try to port over their old SQL statements to Oracle. This can lead to confusion when dealing with joins. Beginning with Oracle 9i, the database is now compliant to ANSI SQL99 standards.
In the past, a join such as this would fail:
RS14 SQL> select o.owner,u.user_id,o.object_name
2 from (dba_objects o inner join dba_users u on o.owner=u.username)
3 where o.owner='PEASLAND';
from (dba_objets o inner join dba_users u on o.owner=u.username)
*
ERROR at line 2:
ORA-00928: missing SELECT keyword
But Oracle 9i let’s us use ANSI SQL99 keywords such as INNER, NATURAL, and OUTER JOINS in our SQL statements:
ORA9I SQL> select o.owner,u.user_id,o.object_name
2 from (dba_objects o inner join dba_users u on o.owner=u.username)
3* where o.owner='PEASLAND';
OWNER USER_ID OBJECT_NAME
---------- ---------- ---------------
PEASLAND 18 DB_OBJECTS
PEASLAND 18 DB_OBJECTS_IDX
PEASLAND 18 DB_USERS
PEASLAND 18 OBJECTS
PEASLAND 18 PEOPLE_EXTERNAL
PEASLAND 18 PLAN_TABLE
PEASLAND 18 TABLESPACES
PEASLAND 18 USERS
8 rows selected.
Feel free to start using this ANSI SQL99 syntax in your code!
FLASHBACK QUERY
Flashback Query, new for Oracle 9i, lets a user go back in time to perform their queries. For instance, lets say that our database holds account information for a bank. During the day, people deposit and withdraw money from their bank account. The manager of the bank wants to run a report on these bank accounts in the afternoon, but doesn’t want today’s transactions to be included in the report. Before Oracle 9i, the account information had to be drawn off before the start of the business day so that the manager could run reports against this duplicated data. With Flashback Query, the manager can tell the database that the reports will run against only those transactions that were completed before 9:00 am today. Flashback Query has other uses as well. It can be used to recover data in case someone accidentally deleted it. Just query the data in the past and generate SQL statements to insert the deleted records. (LogMiner offers this functionality as well).
Flashback Queries can perform queries against the database back to a specific point in time, or a specific SCN in the database. While one is performing a Flashback Query, other DML operations are allowed to take place in real time. Flashback Query does not undo any changes to the database. It is a query-only mechanism.
In order to use Flashback Query, the transactions must be using Oracle’s new UNDO tablespace. Flashback Query cannot use old-fashioned rollback segments. It is the UNDO tablespace that lets the Flashback Query rollback any changes before a point in time. How far back in time can one query? That is determined by the UNDO_RETENTION parameter specified in the INIT.ORA file. The farther back one will need to go will determine how this parameter is set. The DBA will also need to set UNDO_MANAGEMENT=AUTO in the INIT.ORA file.
ORA9I SQL> select to_char(sysdate,'HH24:MI:SS') as curr_time from dual;
CURR_TIM
--------
09:40:13
ORA9I SQL> select * from users;
USERNAME USER_ID CREATED
---------- ---------- ---------
OUTLN 11 27-JUN-01
DBSNMP 17 27-JUN-01
PEASLAND 18 24-AUG-01
jim 9876 28-SEP-01
susan 9874 28-SEP-01
jack 9873 29-SEP-01
jack 9873 29-SEP-01
7 rows selected.
ORA9I SQL> delete from users where username='jack';
2 rows deleted.
ORA9I SQL> commit;
Above, we can see that we deleted records from a table after 9:40 am. Those records no longer exist in the table since the transaction was committed. Let’s use the DBMS_FLASHBACK package to change our query’s point in time. The ENABLE_AT_TIME procedure sets the time of our Flashback Query.
ORA9I SQL> execute dbms_flashback.enable_at_time( -
> to_date('10/02/2001 09:30:00','MM/DD/YYYY HH24:MI:SS'));
PL/SQL procedure successfully completed.
ORA9I SQL> select * from users;
USERNAME USER_ID CREATED
---------- ---------- -------------------
OUTLN 11 06/27/2001 16:28:10
DBSNMP 17 06/27/2001 16:57:06
PEASLAND 18 08/24/2001 08:35:20
jim 9876 09/28/2001 13:19:56
susan 9874 09/28/2001 13:23:03
jack 9873 09/29/2001 13:29:26
jack 9873 09/29/2001 13:30:33
7 rows selected.
ORA9I SQL> execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
ORA9I SQL> select * from users;
USERNAME USER_ID CREATED
---------- ---------- -------------------
OUTLN 11 06/27/2001 16:28:10
DBSNMP 17 06/27/2001 16:57:06
PEASLAND 18 08/24/2001 08:35:20
jim 9876 09/28/2001 13:19:56
susan 9874 09/28/2001 13:23:03
We changed our reference time for the Flashback Query. On examining the table, we see that the records are there! We then disable the Flashback Query. Examining the table once again shows that the records are in fact gone.
MULTIPLE BLOCK SIZES
The smallest unit of I/O in Oracle is defined by the block size. Before Oracle 9i, each database could only have one block size for the entire database. For most systems, this restriction was not an impediment to a successful implementation. But some DBA’s desired to have the ability to use different block sizes in the database at the same time. There are two major reasons for this. One, different tablespaces hold different types of data. Some tablespaces perform better with smaller block sizes while other tablespaces (which potentially hold large objects) would benefit from a larger block size. Two, when using transportable tablespaces (introduced in Oracle 8i), there is a restriction that the source database and target database have the same block size. Allowing different block sizes in the database would remove this restriction. One database would not be dependant on another. To address these issues, Oracle introduced support for multiple block sizes within the same database.
CONFIGURATION
Before you can use multiple block sizes in your database, you must first configure the database.
In my database, I still have a default block size specified by the db_block_size parameter.
ORA9I SQL> show parameter db_block_size
NAME TYPE VALUE
---------------------------------- ----------- -------------------------
db_block_size integer 8192
As you can see, my default block size is 8K. When blocks are read by the database, the information is placed into the buffer cache. The initialization parameter db_block_buffers is being deprecated in favor of the db_cache_size parameter that we’ll talk about more in the next paragraph.
We can configure up to five non-standard block sizes for use in our database. In order to use multiple block sizes in the database, we have to configure different sub-caches in the buffer cache. All tablespaces which are created with a 2K block size will use the 2K sub-cache. There are six new parameters that control these caches. The db_cache_size parameter sizes the default buffer cache used by the default block size. The db_nk_cache_size parameter controls the sizes of these sub-caches where n=2,4,6,16 or 32. Please note that your default block size is not available for use as a sub-cache. So in my example, I could not set the db_8k_cache_size parameter since my default block size is 8K.
ORA9I SQL> show parameter db_block_buffers
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
db_block_buffers integer 0
ORA9I SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
db_cache_size big integer 12582912
ORA9I SQL> show parameter k_cache_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 4194304
db_32k_cache_size big integer 0
db_4k_cache_size big integer 4194304
db_8k_cache_size big integer 0
From above, we can see that we modified the db_cache_size parameter and did not set the db_block_buffers parameter. You cannot have both of these set, it is one or the other. I also modified my INIT.ORA file to create two alternate sub-caches, 2K and 4K. Now that these sub-caches have been defined, I can create a tablespace with an alternate block size.
CREATING TABLESPACES
The SYSTEM tablespace in the database will always be created with the default block size. But I can create other tablespaces with different block sizes as follows:
ORA9I SQL> create tablespace ts_2k
2 datafile '/edcsns14/oradata3/ora9i/ts_2k01.dbf'size 10m
3 extent management local uniform size 64k
4 blocksize 2k;
Tablespace created.
Here, I created a tablespace with a 2K block size. Any data read from this tablespace will be placed in the 2K sub-cache.
As can be expected, the DBA_TABLESPACES has changed so that you can find out the block size of the tablespaces.
ORA9I SQL> select tablespace_name,block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 8192
UNDO_TS 8192
UNDO2 8192
WORKING 8192
USERS 8192
TEMP 8192
INDEXES 8192
PEASLAND 8192
PEASLAND_IDX 8192
TS_2K 2048
10 rows selected.
You can clearly see the different block size on the tablespace we created above.
CACHED EXECUTION PLANS
Oracle 9i now lets a DBA more easily diagnose query problems by letting you see the execution plan information for each cached cursor in the Shared Pool of the SGA. There is a new view that has been added for this functionality.
ORA9I SQL> select address,hash_value,sql_text
2 from v$sqlarea
3 where sql_text like 'select count%';
ADDRESS HASH_VALUE SQL_TEXT
-------- ---------- ----------------------------------------
805AF35C 428662531 select count(*) from dba_objects
Here, I’ve visited the familiar V$SQLAREA view to see a cursor that is cached in the Shared Pool. I need the ADDRESS and HASH_VALUE for further analysis.
The V$SQL_PLAN view will show you the execution plan that the database used to execute a specific cached query. For our query above, we’ll see the execution plan used below:
ORA9I SQL> select lpad(' ',2*(depth-1)) || operation || ' ' || options || ' ' ||
2 object_name as "Query Plan"
3 from v$sql_plan
4 where address='805AF35C' and hash_value=428662531;
Query Plan
------------------------------------------------------------------------
SELECT STATEMENT
SORT AGGREGATE
VIEW
UNION-ALL
FILTER
TABLE ACCESS BY INDEX ROWID OBJ$
NESTED LOOPS
TABLE ACCESS FULL USER$
INDEX RANGE SCAN I_OBJ2
TABLE ACCESS BY INDEX ROWID IND$
INDEX UNIQUE SCAN I_IND1
NESTED LOOPS
TABLE ACCESS FULL USER$
INDEX RANGE SCAN I_LINK1
14 rows selected.
This is the execution plan that Oracle used when executing our SQL statement. This saves the DBA the trouble from generating an Explain Plan on this statement. It is also important since environmental factors can contribute to different plans. So now you know exactly which plan was used to execute this query.
CACHE ADVICE
Before Oracle 9i, determining the optimal setting for the buffer cache size was a process of calculating hit ratios and adjusting accordingly. In Oracle 7.3, you could even determine the effect of raising or lowering db_block_buffers by setting db_block_lru_extended_
statistics to TRUE and examining your findings in X$KCBRBH and X$KCBCBH. Oracle 9i now includes the V$DB_CACHE_ADVICE view to determine this. Before you can use this view, you need to configure your system to provide the advice. This is done with the db_cache_advice parameter. This parameter may incur undue overhead to your system so be cautious.
ORA9I SQL> alter system set db_cache_advice=on;
System altered.
Before we can examine the above view, we need to determine which buffer cache we are looking at.
ORA9I SQL> select id,name,block_size,current_size,buffers
2> from v$buffer_pool;
ID NAME BLOCK_SIZE CURRENT_SIZE BUFFERS
---------- ---------- ---------- ------------ ----------
3 DEFAULT 8192 12 1503
4 DEFAULT 2048 4 1892
5 DEFAULT 4096 4 983
In my case, I’m going to be looking at the default buffer cache for my default block size (8K). So I’m interested in ID=3. Let’s see what V$DB_ADVICE_CACHE indicates for this buffer cache.
ORA9I SQL> set headsep !
ORA9I SQL> column buffers_for_estimate heading "Buffers For!Estimate"
ORA9I SQL> column size_for_estimate heading "Size For!Estimate"
ORA9I SQL> column estd_physical_reads heading "Estimated!Phys Reads"
ORA9I SQL> select name,size_for_estimate,buffers_for_estimate,
2 estd_physical_read_factor as read_factor,estd_physical_reads
3 from v$db_cache_advice
4 where id=3;
Size For Buffers For Estimated
NAME Estimate Estimate READ_FACTOR Phys Reads
---------- ---------- ----------- ----------- ----------
DEFAULT 1.1719 150 2.0064 4
DEFAULT 2.3438 300 1.234 2
DEFAULT 3.5156 450 1.0021 2
DEFAULT 4.6875 600 1 2
DEFAULT 5.8594 750 1 2
DEFAULT 7.0313 900 1 2
DEFAULT 8.2031 1050 1 2
DEFAULT 9.375 1200 1 2
DEFAULT 10.5469 1350 1 2
DEFAULT 11.7188 1500 1 2
DEFAULT 12.8906 1650 1 2
DEFAULT 14.0625 1800 1 2
DEFAULT 15.2344 1950 1 2
DEFAULT 16.4063 2100 1 2
DEFAULT 17.5781 2250 1 2
DEFAULT 18.75 2400 1 2
DEFAULT 19.9219 2550 1 2
DEFAULT 21.0938 2700 1 2
DEFAULT 22.2656 2850 1 2
DEFAULT 23.4375 3000 1 2
20 rows selected.
The SIZE_FOR_ESTIMATE column is the size in Megabytes for the prediction. The BUFFERS_FOR_ESTIMATE column is the number of block buffers for the estimate. The ESTD_PHYSICAL_READ_FACTOR column (READ_FACTOR) is a ratio comparing the estimated number of physical reads to the number of reads in the real cache. In my example above, this means that if I chose 150 block buffers, then I’d have to perform 2 physical reads for every one of my reads. This would be terrible performance. According to my output above, I should have at least 600 block buffers for optimal performance under this load condition.
SERVER PARAMETER FILE
Before Oracle 9i, the DBA would modify the INIT.ORA parameter file stored on the same server as the database. Remote tools such as Oracle Enterprise Manager (OEM) could use a remote parameter file stored on the machine that ran OEM. If this scenario was deployed, it meant that the DBA had to keep the server-side INIT.ORA file in sync with all the remote parameter files. To alleviate this problem, Oracle created a Server Parameter File (SPFILE). Initialization parameters in this file are persistent. Any changes made to this parameter file will the instance is up and running will be modified automatically in this file.
Before you can use a SPFILE, you must create one. The created SPFILE will be a binary file that cannot be modified through a text editor. This is a major change from the normal INIT.ORA files. The SPFILE must be initially created from an INIT.ORA file with the CREATE SPFILE command.
ORA9I SQL> connect sys as sysdba
Enter password:
Connected.
ORA9I SQL> create spfile
2 from pfile='/edcsns14/pkg/oracle/admin/ora9i/pfile/initora9i.ora';
File created.
You need to connect as SYSDBA to perform this operation. The server parameter file is always created on the machine running the database server. While there are options to specify exactly where you want the SPFILE to reside and what you want to call it, it is best to let it default in name and location. After you’ve created the SPFILE, remove the INIT.ORA file. Otherwise, Oracle will get confused and issue an ORA-3113 error.
edcsns14% sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Tue Oct 2 14:44:46 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 33924028 bytes
Fixed Size 279484 bytes
Variable Size 12582912 bytes
Database Buffers 20971520 bytes
Redo Buffers 90112 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
spfile string ?/dbs/spfile@.ora
The database has been restarted and it is now using the new SPFILE. Let’s change a parameter and see that it persists across database shutdown.
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 4194304
SQL> alter system set shared_pool_size=10000000;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 42312676 bytes
Fixed Size 279524 bytes
Variable Size 20971520 bytes
Database Buffers 20971520 bytes
Redo Buffers 90112 bytes
Database mounted.
Database opened.
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
shared_pool_size big integer 12582912
Here, we can see that our Shared Pool was 4M. We modified the Shared Pool size dynamically to 10M. The system was shutdown and then restarted. Notice that the parameter change persisted!
You can go back as well. This way, you can create a readable, text-modifiable form for your use.
SQL> create pfile from spfile;
File created.
One final note: In my studies, I had difficulty dynamically setting the SGA parameters as previously discussed in this paper. Once I went to an SPFILE, I was able to do as advertised.
MONITORING INDEX USAGE
I’ve heard the question asked time and time again. “How do I know if my index is being used?” There has never been an easy answer. An unused index wastes disk space and slows down DML operations that need to update the index. So why keep an index that is barely being used, or worse, not being used at all? With Oracle 9i, one can monitor the index usage by querying the V$OBJECT_USAGE view.
ORA9I SQL> select index_name from user_indexes
2 where table_name='DB_USERS';
INDEX_NAME
------------------------------
DB_USERS_ACCOUNT_STATUS_IDX
DB_USERS_USERID_IDX
DB_USERS_USERNAME_IDX
Here you can see that I have three indexes on this table in my schema. I now need to tell the system to monitor the usage on these three indexes.
ORA9I SQL> alter index DB_USERS_ACCOUNT_STATUS_IDX monitoring usage;
Index altered.
ORA9I SQL> alter index DB_USERS_USERID_IDX monitoring usage;
Index altered.
ORA9I SQL> alter index DB_USERS_USERNAME_IDX monitoring usage;
Index altered.
Here, I’ve used the ALTER INDEX command to begin monitoring the usage of the three indexes on my table. After time, I can get an idea of which indexes were used and which was not.
ORA9I SQL> select index_name,monitoring,used,start_monitoring
2> from v$object_usage;
INDEX_NAME MON USE START_MONITORING
------------------------------ --- --- -------------------
DB_USERS_ACCOUNT_STATUS_IDX YES YES 10/02/2001 15:43:30
DB_USERS_USERID_IDX YES NO 10/02/2001 15:43:39
DB_USERS_USERNAME_IDX YES YES 10/02/2001 15:43:45
Here, I can see that the DB_USERS_USERID_IDX was never used since monitoring began. It is very easy to turn off monitoring.
ORA9I SQL> select index_name,monitoring,used,
2 end_monitoring from v$object_usage;
INDEX_NAME MON USE END_MONITORING
----------------------------- --- --- -------------------
DB_USERS_ACCOUNT_STATUS_IDX NO YES 10/02/2001 15:50:03
DB_USERS_USERID_IDX NO NO 10/02/2001 15:50:12
DB_USERS_USERNAME_IDX NO YES 10/02/2001 15:50:19
Notice by the second column that these indexes are no longer being monitored.
LOG MINER
Log Miner, originally introduced in Oracle 8i, has been enhanced in Oracle 9i. For starters, Log Miner now supports the following objects:
• Clustered tables
• Chained and migrated rows
• LOB and LONG datatypes
• Direct loads
• Scalar object types
• Data definition statements (DDL)
Log Miner now also has a new GUI interface, a component of Oracle Enterprise Manager. Previously, one had to run a supplied PL/SQL package that loaded a table (accessed through V$LOGMNR_CONTENTS). Log Miner also supports queries on the logs based on the content of changes (for example, show all changes to an employee named Smith).
DBMS_METADATA
If you’ve worked with Oracle for any amount of time, you’ve undoubtedly run across various scripts to generate DDL statements to recreate database objects. Often times, these are called reverse-engineering scripts. You may have created your own reverse-engineering scripts as well. For instance, you’ve probably run across a script to reverse-engineer a table. The problem with these scripts is that they need to be modified to take into account the new features of new database releases. With Oracle 9i, there is now a supplied PL/SQL package that will perform this functionality for you, DBMS_METADATA. It’s pretty easy to use as you can see from the following examples:
ORA9I SQL> select dbms_metadata.get_ddl('TABLE','DB_USERS','PEASLAND')
2> as ddl from dual;
DDL
------------------------------------------------------------------------
CREATE TABLE "PEASLAND"."DB_USERS"
( "USERNAME" VARCHAR2(30) NOT NULL ENABLE,
"USER_ID" NUMBER NOT NULL ENABLE,
"PASSWORD" VARCHAR2(30),
"ACCOUNT_STATUS" VARCHAR2(32) NOT NULL ENABLE,
"LOCK_DATE" DATE,
"EXPIRY_DATE" DATE,
"DEFAULT_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,
"TEMPORARY_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,
"CREATED" DATE NOT NULL ENABLE,
"PROFILE" VARCHAR2(30) NOT NULL ENABLE,
"INITIAL_RSRC_CONSUMER_GROUP" VARCHAR2(30),
"EXTERNAL_NAME" VARCHAR2(4000)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
The above example shows how easy it is to reverse engineer a table. The parameters to the GET_DDL procedure are the object_type, object_name, and owner. Below is an example of extracting the DDL for an index:
ORA9I SQL> select dbms_metadata.get_ddl('INDEX',
2 'DB_OBJECTS_IDX','PEASLAND') as ddl from dual;
DDL
------------------------------------------------------------------------
CREATE INDEX "PEASLAND"."DB_OBJECTS_IDX" ON "PEASLAND"."DB_OBJECTS" ("OBJECT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PEASLAND_IDX"
There is also a GET_XML procedure, which will extract the schema object in XML format.
DBMS_REDEFINITION
The DBMS_REDEFINITION supplied package allows for online reorganization of tables. This helps maintain high availability by letting a DBA perform maintenance against a table while not interrupting DML operations against the table. This is achieved by behind-the-scenes, incremental Materialized Views. While maintenance is being performed against the table, DML operations are logged. After the maintenance is complete, the logged DML operations are reapplied against the redefined table. During the reorganization, the table is only locked for a very short duration to update the data dictionary at the beginning and end of the redefinition.
There are a few restrictions to using this package. They are as follows:
• Tables with no primary keys cannot be reorganized online.
• Tables that have materialized views and materialized view logs defined on them cannot be reorganized online.
• Tables that are materialized view container tables and AQ tables cannot be reorganized online.
• The overflow table of an IOT table cannot be reorganized online.
Before we can begin online redefinition of a table, we need to determine if it can be redefined. We use the CAN_REDEF_TABLE procedure of the package to determine if the table is a candidate for online redefinition. If the table cannot be redefined, then an exception is raised.
ORA9I SQL> execute dbms_redefinition.can_redef_table('PEASLAND','OBJECTS');
BEGIN dbms_redefinition.can_redef_table('PEASLAND','OBJECTS'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "PEASLAND"."OBJECTS" with no primary
key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 236
ORA-06512: at line 1
This table cannot be redefined online because it has no primary key.
ORA9I SQL> alter table objects
2 add (constraint objects_pk primary key (object_id));
Table altered.
ORA9I SQL> execute dbms_redefinition.can_redef_table('PEASLAND','OBJECTS');
PL/SQL procedure successfully completed.
This table can now be redefined online. To do that, we need to create an interim table (in the same schema) that will have our new definition. In our example, we will be dropping columns from the existing table. The new table will only have 4 columns in it. The others will be dropped.
ORA9I SQL> create table new_objects (
2 owner varchar2(30),
3 object_name varchar2(128),
4 object_id number,
5 object_type varchar2(18));
Table created.
We have now created our interim table. We could have changed anything here including the table’s tablespace and storage parameters. We now need to initiate the reorganization process.
ORA9I SQL> exec dbms_redefinition.start_redef_table( -
> 'PEASLAND','OBJECTS','NEW_OBJECTS', -
> 'OWNER OWNER, OBJECT_NAME OBJECT_NAME, OBJECT_ID OBJECT_ID,OBJECT_TYPE OBJECT_TYPE');
PL/SQL procedure successfully completed.
We have started the redefinition process. The first parameter is the schema owner. The second parameter is the original table name. The third parameter is the interim table name. The fourth parameter is the column mappings. I’m not changing the column names here, but I could. If this fourth parameter is NULL, then it is assumed that all columns will be mapped by their original names.
At this point, we could create any indexes or constraints on the interim table.
ORA9I SQL> create index objects_name_idx
2> on new_objects(object_name);
Index created.
At this point, all redefinitions are now complete. Should the redefinitions take a long time, you can synchronize the interim table with the following command:
ORA9I SQL> exec dbms_redefinition.sync_interim_table( -
> 'PEASLAND','OBJECTS','NEW_OBJECTS');
PL/SQL procedure successfully completed.
This step is optional. Synchronization will take place once the redefinition is complete. If you redefinitions take a long time, this can speed up the end phase. Finally, end the redefinition.
ORA9I SQL> exec dbms_redefinition.finish_redef_table( -
> 'PEASLAND','OBJECTS','NEW_OBJECTS');
PL/SQL procedure successfully completed.
To show that the table has been redefined, we’ll describe the table:
ORA9I SQL> desc objects
Name Null? Type
----------------------------------------- -------- --------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
As you can see, this table now only has four columns. The interim table can now be dropped.
TIME DATA
Before Oracle 9i, the only datatype to hold time and date information was the DATE datatype. This datatype would hold the century, year, month, day, hours, minutes, and seconds. Oracle 9i introduced the TIMESTAMP datatype. With the TIMESTAMP datatype, you can store fractional seconds.
ORA9I SQL> create table timings (
2 id number,
3 time timestamp);
Table created.
This table has a column with the new TIMESTAMP datatype. Let’s now look at an example of using fractional seconds.
ORA9I SQL> insert into timings values (1,systimestamp);
1 row created.
ORA9I SQL> insert into timings values (2,systimestamp);
1 row created.
ORA9I SQL> select * from timings;
ID TIME
---------- ------------------------------
1 03-OCT-01 01.35.25.508883 PM
2 03-OCT-01 01.35.34.185915 PM
ORA9I SQL> select b.time-a.time as difference
2 from (select time from timings where id=1) a,
3 (select time from timings where id=2) b;
DIFFERENCE
------------------------------------------------------------------------
+000000000 00:00:08.677032
Here, we used the SYSTIMESTAMP function when inserting values into the table. The SYSTIMESTAMP function is similar to SYSDATE except it returns fractional seconds. Just like the DATE datatype, we can perform arithmetic on the values. Above, we found that there was a difference of 8.677 seconds between the insert statements.
In addition to the TIMESTAMP datatype, there is the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes. There are also a number of functions for these new datatypes including TO_TIMESTAMP and TO_TIMESTAMP_TZ. Also, the database can be created with a time zone in the CREATE DATABASE statement. Additional functions are shown below:
ORA9I SQL> select current_date from dual;
CURRENT_D
---------
03-OCT-01
ORA9I SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
------------------------------------------------------------------------
03-OCT-01 01.44.36.053026 PM -05:00
ORA9I SQL> select dbtimezone from dual;
DBTIME
------
-05:00
The first query shows the current date. The second query shows the current timestamp similar to the SYSTIMESTAMP call we made earlier. The third query shows the database time zone.
LIST PARTITIONING
Before 9i, we could partition a table by a range of values or by a hash on the values. Oracle 8i introduced composite partitioning where both a range and a hash were used to partition the rows in a table. Oracle 9i introduces List Partitioning. This type of partitioning splits rows depending on their values compared to a list of values. This option gives the DBA even more flexibility when designing partitioned tables.
There are some restrictions to list partitioning.
• You can specify only one partitioning key in the column list, and it cannot be a LOB column.
• If the partitioning key is an object type column, you can partition on only one attribute of the column type.
• You cannot specify MAXVALUE in the VALUES clause.
• Each partition value in the VALUES clause must be unique among all partitions of the table.
• You cannot list partition an index-organized table
ORA9I SQL> create table list_part (
2 object_type varchar2(50),
3 object_name varchar2(128))
4 partition by list (object_type) (
5 partition list_part_idx values ('INDEX'),
6 partition list_part_tab values ('TABLE'),
7 partition list_part_others values ('VIEW','SYNONYM'));
Table created.
Here, we have created a list-partitioned table. This table has three partitions. Those rows where the OBJECT_TYPE is INDEX will be placed in the LIST_PART_IDX partition. Those rows where the OBJECT_TYPE is TABLE will be placed in the LIST_PART_TAB partition. Those rows where the OBJECT_TYPE is VIEW or SYNONYM will be placed in the LIST_PART_OTHERS partition. Let’s insert data into this table and see how it works!
ORA9I SQL> insert into list_part
2 select object_type,object_name from dba_objects
3 where object_type in ('INDEX','TABLE','VIEW','SYNONYM');
3774 rows created.
ORA9I SQL> select count(*) from list_part partition (list_part_idx);
COUNT(*)
----------
412
ORA9I SQL> column object_type format a10
ORA9I SQL> column object_name format a20
ORA9I SQL> select * from list_part partition (list_part_idx)
2 where rownum <=10;
OBJECT_TYP OBJECT_NAME
---------- --------------------
INDEX I_COBJ#
INDEX I_CON2
INDEX I_CON1
INDEX I_OBJ1
INDEX I_OBJ#
INDEX I_CCOL2
INDEX I_COL2
INDEX I_UNDO1
INDEX I_FILE1
INDEX I_ICOL1
10 rows selected.
Here, we inserted data into the table. We then verified that 412 rows of the 3,774 rows inserted fell into the LIST_PART_IDX partition. Examining the first 10 rows of this partition revealed that the OBJECT_TYPE is INDEX as advertised.
PGA WORKING MEMORY MANAGEMENT
When a user starts a session, the user allocates a Program Global Area (PGA) for that dedicated session. Any work that needs to be performed in memory for that user is performed in the user’s PGA. For instance, sort operations, hash-joins, and bitmap creations all need a working area in order to complete the task. Before Oracle 9i, there were many INIT.ORA parameters that controlled the size of each user’s PGA. These parameters include SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_
AREA_SIZE. Collectively, these parameters are referred to as the *_AREA_SIZE parameters. The user’s PGA is basically, the sum of the *_AREA_SIZE parameters. It is ideal that certain user’s tasks be performed entirely in memory. If there is not enough memory to perform one of these tasks, then the operation must use sort space on disk, typically in a TEMP tablespace.
The hard part is accurately determining optimal values of the *_AREA_SIZE parameters. If you allocate too much to these parameters, then you waste valuable and limited server memory. If you do not allocate enough, then the process must perform costly I/O to the TEMP tablespace in order to complete the task. Show how do you adequately size these? And aside from SORT_AREA_SIZE, the other parameters allocate space that may never be used by the majority of processes running in the system, especially OLTP systems. Oracle 9i introduces a new, dynamic way to let the database automatically control these different working areas for each process. The portion of the PGA allocated to these working areas can now be adjusted based on the overall target set by the DBA. Keep in mind, this functionality is only for dedicated server processes. Shared server processes with Oracle’s Multi-Threaded Server (MTS) will not benefit from this new functionality.
The PGA_AGGREGATE_TARGET initialization parameter defines the total amount of work area space for all dedicated sessions. When set, the *_AREA_SIZE parameters are ignored for dedicated server processes. The value of PGA_AGGREGATE_TARGET should be determined by the total available physical memory in the system. Oracle Corp. recommends setting this parameter to 16% of the total physical memory for OLTP databases and 40% of the total physical memory for data warehouses. This parameter is system modifiable.
ORA9I SQL> alter system set pga_aggregate_target=160M;
System altered.
ORA9I SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
pga_aggregate_target big integer 167772160
There is some help in tuning this parameter. There are three modes of execution in these work areas. There are optimal modes, one-pass modes, and multi-pass modes. Optimal and one-pass modes are ideal. V$SYSSTAT can show us how many different times operations were performed in each of these three modes since instance startup.
ORA9I SQL> select name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
2 from (select name, value cnt, (sum(value) over ()) total
3 from v$sysstat
4* where name like 'workarea exec%')
ORA9I SQL> /
PROFILE CNT PERCENTAGE
---------------------------------------- ---------- ----------
workarea executions - optimal 7 100
workarea executions - onepass 0 0
workarea executions - multipass 0 0
If the multi-pass operation count is not zero, the DBA may consider raising the PGA_AGGREGATE_TARGET setting. Also, if there are a high number of one-pass executions compared to optimal executions, raise this parameter. The V$SQL_WORKAREA_ACTIVE view shows all active work areas in the database.
ORA9I SQL> select sid, operation_type,
2> trunc(work_area_size/1024) wsize,
3> trunc(expected_size/1024) esize,
4> trunc(actual_mem_used/1024) mem,
5> trunc(max_mem_used/1024) max_mem,
6> number_passes pass
7> from v$sql_workarea_active;
SID OPERATION_TYPE WSIZE ESIZE MEM MAX_MEM PASS
--- --------------------- ----- --------- --------- --------- ----
27 GROUP BY (SORT) 73 73 64 64 0
44 HASH-JOIN 3148 3147 2437 2437 0
71 HASH-JOIN 13241 19200 12884 34684 1
This view shows that SID 71 is currently using a 13MB working area size. But in the past, it has used up to 34 MB of working area memory. If a lot of sessions appear this way, the DBA may want to consider raising the PGA_AGGREGATE_TARGET parameter.
RMAN IMPROVEMENTS
RMAN has been improved in Oracle 9i. This section gives an overview of the RMAN improvements in Oracle 9i.
• RMAN now supports a recovery window (sometimes called a retention policy). This controls when backups expire. This reduces administration time by automation.
• RMAN can now recover individual blocks while leaving the datafile online.
• RMAN can now maintain persistent configuration changes. A number of features can be modified for the session and those changes will endure through future sessions.
• RMAN can now make a mandatory and automatic controlfile backup. This lets RMAN restore even if the backup catalog is not available.
• RMAN can now restart a backup should that backup fail for some reason.
• RMAN now supports the multiple block sizes for tablespaces.
• RMAN can now be configured to skip specified tablespaces.
ANSI CASE SUPPORT
Oracle 9i now supports the ANSI style CASE statement and expression. An example appears below:
ORA9I SQL> DECLARE
2 category CHAR(1);
3 value NUMBER;
4 BEGIN
5 value := 52;
6 DBMS_OUTPUT.PUT_LINE('The value is: ' || value);
7
8 category :=
9 CASE
10 WHEN value BETWEEN 0 AND 9 THEN 'A'
11 WHEN value BETWEEN 10 AND 19 THEN 'B'
12 WHEN value BETWEEN 20 AND 29 THEN 'C'
13 WHEN value BETWEEN 30 AND 39 THEN 'D'
14 WHEN value BETWEEN 40 AND 49 THEN 'E'
15 WHEN value BETWEEN 50 AND 59 THEN 'F'
16 WHEN value BETWEEN 60 AND 69 THEN 'G'
17 WHEN value BETWEEN 70 AND 79 THEN 'H'
18 WHEN value BETWEEN 80 AND 89 THEN 'I'
19 WHEN value BETWEEN 90 AND 99 THEN 'J'
20 ELSE 'Z'
21 END;
22
23 DBMS_OUTPUT.PUT_LINE('The CATEGORY is: ' || category);
24
25 END;
26 /
The value is: 52
The CATEGORY is: F
PL/SQL procedure successfully completed.
In this example, you can clearly see the CASE statement at work. It’s pretty self-explanatory if you’ve used CASE statements in other programming languages.
RESOURCE MANAGER IMPROVEMENTS
Oracle 8i introduced the database Resource Manager. With this component, the DBA can manage system workload by assigning a user to a Consumer Group. Each consumer group is defined to only consume a specific amount of CPU resources. This let’s some users become more “important” than other users in the database. For instance, the DBA may want users who run batch jobs to get less CPU from the system than online users. Oracle 9i has improved the Resource Manager as follows:
• Automatic Consumer Group Switching – Before 9i, the DBA could switch a user from one consumer group to another manually. In 9i, the DBA can have this happen automatically. This is done with new consumer group plan keywords. They are as follows:
o SWITCH_GROUP – A user is switched to this consumer group when the parameters that follow are envoked.
o SWITCH_TIME – If a session is active for this amount of time, it is switched. This let’s users who sit on the machine for too long be switched to a less resource intensive group. Once the user logs off and then back on, they will be in their original group.
o SWITCH_ESTIMATE – If set to TRUE, then the Resource Manager will estimate the execution time to decide if to switch to a new group before the operation starts.
• Operation Queueing – It is possible for too many sessions to become active in the database at one time. For instance, what is stopping a user from starting 20 sessions to run batch jobs all at the same time? Oracle 9i let’s the Resource Manager determine the maximum number of active sessions allowable for a certain group. Any new sessions will be queued until other sessions become inactive or complete. An optional timeout period can specify how long a session waits on the queue before timing out.
• Maximum Estimated Execution Time – A new directive, MAX_ESTIMATED_EXEC_
TIME states how long of an operation is allowed to run. The Resource Manager first estimates how long an operation will take to complete. If the operation is longer than this directive, then the operation will trip an exception and an error will be raised.
• UNDO Quota – The UNDO_POOL directive let’s the DBA control a quota for the amount of UNDO space a user in the consumer group can use. The default value is UNLIMITED.
AUTOMATICALLY GATHERING STATISTICS
Long before Oracle 9i, the Cost Based Optimizer (CBO) was introduced. This SQL optimization method relies heavily on up-to-date statistics. The problem is that these statistics need to be updated on a regular basis. This either involves having someone manually update the statistics or create a DBMS_JOB or cron job to update the statistics.
With Oracle 9i, you can specify the MONITORING clause for a table with the CREATE TABLE or ALTER TABLE commands. This enables DBMS_STATS to automatically gather statistics for you. Then through an automated job, that invokes the DBMS_STATS.GATHER_TABLE_STATS procedure, you can use the GATHER STALE option. This option will only gather statistics on those tables that have experienced a number of DML operations.
EXPORT/IMPORT NEW FEATURES
The Export and Import utilities have a few new features for Oracle 9i as well. The following is a list of those new features.
• Pre-calculated statistics – In previous versions, one could export with a STATISTICS parameter. This meant that the export file contained a statement for the table to analyze the table and compute or estimate statistics. This statement in the export file (if it exists) could be overridden with the STATISTICS parameter in the import utility. Now, the export utility will place the pre-calculated statistics in the export file. This saves the time and trouble of having to re-compute them on import. On import, there are parameters to control whether to use these pre-computed statistics or not. They are:
o ALWAYS – Always import the statistics, even if they are questionable
o SAFE – Only import the statistics if they are safe.
o RECALCULATE – Do not import the statistics. Recompute them instead.
o NONE – Do not import statistics and do not compute them.
• There are new parameters for export and import. They are as follows:
o Export new parameters:
FLASHBACK_SCN - Specifies the system change number (SCN) that Export will use to enable flashback. The export operation is performed with data consistent as of this specified SCN.
FLASHBACK_TIME – Just like above, but uses the time, not the SCN.
RESUMABLE – Used to enable or disable resumable space allocation.
RESUMABLE_NAME – Used to identify the statement that is resumable. This parameter is only used if RESUMABLE=Y.
RESUMABLE_TIMEOUT – Specifies the time period in which the resumable operation must be fixed. Again, this parameter is only used if RESUMABLE=Y.
o Import new parameters
RESUMABLE – As above.
RESUMABLE_NAME – As above.
RESUMABLE_TIMEOUT – As above.
• Some of the parameters for export and import have been revised. They are as follows:
o Export modified parameters
TABLES – This parameter now supports the ‘%’ wildcard. Any tables that match the pattern dictated by the wildcard in that schema will be exported.
TABLESPACES – This parameter does not have to be used in conjunction with the TRANSPORT_TABLESPACE parameter anymore. This means that all tables in that tablespace will be exported.
o Import modified parameters
TABLES – Supports the ‘%’ wildcard to only import tables matching the pattern.
Subscribe to:
Posts (Atom)
