ORACLE8i- P9

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

Thêm vào BST

Báo xấu

53
lượt xem 7
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

ORACLE8i- P9: We want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, information is king, and the speedy, accurate, and reliable retrieval of this information is paramount.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: ORACLE8i- P9

MANAGING DATABASE OBJECTS THAT SUPPORT TABLES AND INDEXES 311 TABLESPACE data DISABLE STORAGE IN ROW INDEX clob_index); Table created. INSERT INTO clob_test_table VALUES (1, ‘This is another test’); 1 row created. SELECT * FROM clob_test_table; ID CLOB_COLUMN PA R T ---------- ------------------------------ II 1 This is another test UPDATE clob_test_table set clob_column=’This is a changed column’ WHERE id=1; Oracle Database Administration 1 row updated. SELECT * FROM clob_test_table; ID CLOB_COLUMN ---------- ------------------------------ 1 This is a changed column DELETE FROM clob_test_table where clob_column=’This is a changed column’; DELETE FROM clob_test_table where clob_column=’This is a changed column’ * ERROR at line 1: ORA-00932: inconsistent datatypes SQL> SELECT * FROM clob_test_table WHERE clob_column LIKE ‘%This%’; SELECT * FROM clob_test_table WHERE clob_column LIKE ‘%This%’ * ERROR at line 1: ORA-00932: inconsistent datatypes Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
312 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Notice that some SQL operations work just fine, but others don’t work correctly. It’s those problematic operations that the DBMS_LOB package is made for! The Oracle- supplied package provides various routines that can be used to manage and manipu- late Oracle internal and external LOBs. Viewing LOB Information Several data dictionary views provide information about LOBs, such as the column name of the LOB, the LOB segment name, and the chunk size. Table 7.2 describes these data dictionary views. TABLE 7.2: DATA DICTIONARY VIEWS FOR LOBS View Description DBA_LOBS Contains various information on LOBs, such as the name of the table that contains the LOB, the name of the LOB column, and so on DBA_LOB_PARTITIONS Contains partition-specific Information on LOBs in table partitions DBA_LOB_SUBPARTITIONS Contains partition-specific Information on LOBs in table subpartitions DBA_PART_LOBS Contains default partition settings for LOBs in table partitions V$TEMPORARY_LOBS Contains Information on temporary LOBs Understanding Database Startup and Shutdown It is important to understand the process of starting up and shutting down the data- base. This is because you will want the database to be in different modes for different types of recoveries (which are discussed in Chapter 10). In this section, we will discuss the various stages of database startup and shutdown. Starting Up the Database When you issue the STARTUP command on an Oracle database, it goes through three distinct stages: NOMOUNT, MOUNT, and OPEN. Several events occur during these stages as the database prepares to open. Database checks, datafile checks, consistency Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
UNDERSTANDING DATABASE STARTUP AND SHUTDOWN 313 checks, and certain types of recoveries occur during the startup process. Another startup mode that is available to the DBA is restricted mode. In executing various tasks, it may (and will likely) be necessary for you to start the database in a mode other than OPEN mode. NOMOUNT Mode NOMOUNT mode, and the NOMOUNT stage of regular startup, is essentially only the startup of the Oracle instance. As Oracle proceeds through the NOMOUNT stage, it performs the following steps: 1. It locates the database parameter file. By default, it will look for the init.ora file in the $ORACLE_HOME/dbs directory on Unix systems or the $ORACLE_HOME\ database directory on Windows NT. PA R T TI P You can use the PFILE parameter in the STARTUP command to designate an alter- II nate location for the parameter file. An example of using the STARTUP command with the PFILE parameter might look something like this: STARTUP NOMOUNT PFILE=d:\oracle\ admin\database\pfile\init(sid).ora. Oracle Database Administration 2. Oracle will open and read the parameter file, and then process and validate the parameters. 3. Oracle will allocate the SGA it will require to crank up the instance. 4. Oracle will proceed to start the processes of the instance, such as DBWR, LGWR, CKPT, SMON, and PMON. Once the instance is started, the NOMOUNT phase of opening the database will have concluded. You will start the database in NOMOUNT mode in just a few cases. First, you will start the instance in this manner when you wish to create a new database (or re-create an old one). Second, you will start the instance in this mode when you need to create a control file using the CREATE CONTROL FILE command (discussed in Chapter 10). To cause the database startup process to proceed only through the NOMOUNT stage and halt, issue the STARTUP NOMOUNT command from the Server Manager or SQL*Plus prompt. You will note that when you start up the instance, the amount of memory allo- cated to the SGA is reported. Generally, the memory reported looks something like this (obviously the sizes of memory allocated will differ): Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
314 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Total System Global Area 92280076 bytes Fixed Size 70924 bytes Variable Size 87937024 bytes Database Buffers 4194304 bytes Redo Buffers 77824 bytes The Fixed Size entry shows the memory allocated to a small portion of the SGA that contains database and instance status information required by background processes. The Variable Size entry shows the overall size of the shared pool area. It is also influenced by the use of the large pool and Java pool sizes. The other entries are self-explanatory. This output can also be retrieved by using the SHOW SGA command or viewed in the V$SGA data dictionary view (which is the source of the output). NOTE The Redo Buffers entry in this output may not match the setting in the init.ora file for your database. This is because there is a minimum setting for the redo log buffer of four times the maximum database block size for the system. MOUNT Mode Once the instance has been started, the next step in opening the database associated with that instance is to mount the database. In MOUNT mode (and during the MOUNT stage), Oracle locates and opens the control file of the database. It is also at the MOUNT stage that database media recovery occurs (if the database needs to be recovered while it is down). To start the database in MOUNT mode, use the STARTUP MOUNT command. You might do this for several reasons: to put the database in ARCHIVELOG mode, to per- form certain recovery operations, or to rename database datafiles. If you wish to open the database in this mode, issue the command ALTER DATABASE OPEN. OPEN Mode During the OPEN stage, Oracle takes the final steps to open the database. The datafiles are opened, and an error will be signaled if a datafile is missing or if Oracle is unable to open it for some other reason. Once the database datafiles are opened, Oracle will check them with the control file to make sure that the database was closed in a con- sistent fashion. If it was, Oracle will open the database for use. If the database was not closed in a normal fashion, Oracle will decide if instance or media recovery is required. If instance recovery is required, Oracle will perform it automatically without any user interaction. During instance recovery, Oracle will apply the redo in the online redo logs to the database, open the database, and roll back any uncommitted transactions Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
UNDERSTANDING DATABASE STARTUP AND SHUTDOWN 315 after the database is open. The process of rolling back transactions while the database is open is known as fast start recovery. Fast start recovery allows the database to be used while instance recovery is ongoing. During fast start recovery, the SMON process will roll back the uncommitted transactions, allowing users access to the database at the same time. In Oracle8i, SMON can actually take advantage of parallel processing while performing these operations. NOTE During fast start recovery, a new feature in Oracle8i allows Oracle user processes to prioritize the recovery of specific, in demand blocks. Thus, user processes will not need to wait for SMON to recover a block, which can take some time during particularly large recovery operations. A user process will determine that the block needs recovery and will take care of that block’s recovery itself. PA R T If Oracle needs to apply media recovery, it will inform the user that media recovery II is required. Oracle will not open the database in this case, and will return to the SQL prompt to allow the DBA to begin media recovery. See Chapter 10 for information about media recovery. Oracle Database Administration Restricted Mode You (as the DBA) can put the database in restricted mode if the database needs to be open for some maintenance operation but you do not want users to be able to con- nect to the database. To start the database in restricted mode, issue the STARTUP RESTRICT command. If the database is already open and you wish to put it in restricted mode, you can issue the command ALTER SYSTEM ENABLE RESTRICTED SESSION. Restricted mode impacts only new logins to the database, so existing users will need to be disconnected to ensure that no one can make changes to the database. Once you have completed your maintenance operations, you can take the database out of restricted mode by issuing the ALTER SYSTEM DISABLE RESTRICTED SESSION command. Also, if you shut down the database and restart it normally, it will no longer be in restricted mode. Shutting Down the Database The normal Oracle database shutdown sequence is much like the startup sequence, just in reverse order. First the database is dismounted. During a normal shutdown, the database waits for all sessions to disconnect (while preventing new sessions from connecting to the database). Once all sessions discon- nect (which has the result of determining the state of all transactions at shutdown Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
316 CHAPTER 7 • ORACLE DATABASE MAINTENANCE time), the database datafiles are checkpointed and then closed. The checkpoint process is very important because it leaves the database datafiles in a consistent state, allowing the database to start up the next time without the database (or the DBA) needing to perform any type of recovery. (See Chapter 5 for more on checkpoints and the consistent state of the database.) The database then proceeds through the dismount phase, in which the control file is closed. Finally, the instance is terminated with the normal shutdown of the Oracle processes and de-allocation of the SGA. Along with the normal SHUTDOWN command, several other commands may be used to shut down the database, as explained in the following sections. SHUTDOWN ABORT A SHUTDOWN ABORT is essentially a crash of the database. At a minimum, this type of shutdown will result in an instance recovery when the database is restarted. The main difference between a SHUTDOWN ABORT and just pulling the plug on the data- base is that the database datafiles are closed normally (although they are not check- pointed in any way) and memory is de-allocated from the system in a somewhat controlled fashion. The normal closing of the database datafiles is perhaps a bit safer than just pulling the plug on a database. SHUTDOWN IMMEDIATE The SHUTDOWN IMMEDIATE command will cause all existing user processes to be terminated, following which a normal shutdown will ensue. All existing uncommit- ted transactions will be rolled back before the database is shut down, which can take some time to complete. Another thing that can slow down a SHUTDOWN IMMEDI- ATE is an ongoing instance recovery process. You can get an idea of how long a SHUTDOWN IMMEDIATE will take by looking at the V$TRANSACTION table’s USED_UBLK or USED_UREC column, which repre- sents the total amount of undo that will need to be reapplied to the database datafiles before the database can shut down. Ongoing instance recovery processes will not show up in V$TRANSACTION. To see those, you can try looking at the V$SESSION_ LONGOPS or V$RECOVERY_PROGRESS view, although neither of these may provide any information, depending on how much recovery Oracle is expecting to do. SHUTDOWN TRANSACTIONAL The SHUTDOWN TRANSACTIONAL command allows the user transaction to com- plete before signing off the user. Thus, if a user application is in the middle of making some change, the database will wait for a commit to occur before disconnecting the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
UNDERSTANDING DATABASE STARTUP AND SHUTDOWN 317 session. This can pose a problem if a transaction is being manually executed and the user doesn’t commit that transaction for a long period of time. When You Have the Choice—SHUTDOWN ABORT or SHUTDOWN IMMEDIATE? When you need to shut down the database for normal maintenance operations, should you use SHUTDOWN ABORT or SHUTDOWN IMMEDIATE? This is another of those ques- tions that everyone has an opinion about. There are those that take the position that a SHUTDOWN ABORT is just fine. It shortens the time for the outage, even though you know that the database will need to perform instance recovery when starting back up, However, there are problems with using SHUTDOWN ABORT to shut down the data- PA R T base for normal maintenance operations. What happens if you use SHUTDOWN ABORT, and then you accidentally remove all the copies of the current online redo log (or per- II haps the system administrator accidentally wipes out the volumes or disks with these critical files on them)? Since the database was not shut down cleanly, it’s going to need to perform instance recovery, which will not be possible without the current online redo Oracle Database log. Since the log file was the current log file, it will not yet have been archived, so there Administration is no copy of it anywhere. Your database is now beyond recovery, unless you have some way to recover those online redo log files that were just removed. Another issue with SHUTDOWN ABORT has to do with some nasty behavior in Oracle (I think it’s a bug) involving the TRUNCATE command. Suppose that you create a table and add a large number of records to it. Now, say that after you have entered all these records (but with no commit), the database is shut down with a SHUTDOWN ABORT command. Now, the database starts up and instance recovery begins. Let’s say that you decide (or perhaps another user does) to truncate the table. Of course, records in this table are being rolled back by the instance recovery process. If you try to truncate the table that is being rolled back during instance recovery, you will crash the database. And we don’t just mean the one user session—the entire database will crash. My point with all of this is that SHUTDOWN ABORT introduces too many variables into the equation. Probably 99.9 percent of the time, everything will be okay, but that one time in a thousand always seems to hit me. We try not to perform SHUTDOWN ABORTs for normal maintenance unless there is some emergency that requires an immediate database termination. If we do decide to do a SHUTDOWN ABORT, we generally try to force a log switch (ALTER SYSTEM SWITCH LOGFILE) on the database and then force a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
318 CHAPTER 7 • ORACLE DATABASE MAINTENANCE checkpoint (ALTER SYSTEM CHECKPOINT) as well, before issuing the SHUTDOWN ABORT command. My personal preference is the SHUTDOWN IMMEDIATE command. This has the impact of killing all user sessions, rolling back all uncommitted transactions, and then closing the database datafiles normally (with a checkpoint to boot!). When this mode is selected, the database will be shut down cleanly. You can lose the online redo log files and still be in good shape. Before performing a SHUTDOWN IMMEDIATE, we do a log switch. After the shutdown, if major system work is about to be performed (particularly if that system work involves disks), we back up all archived redo logs before we allow the work to begin. The downside of this is that the SHUTDOWN IMMEDIATE command (along with my suggestion of a log switch) generally will take longer to complete than the SHUT- DOWN ABORT command will. The solution is to allow sufficient time to shut down the system, and coordinate the shutdown with the user community, so that a minimum number of transactions are occurring on the database when the shutdown occurs. Changing the Database Name If you need to, you can change the name of your database. You probably should not make a habit of this, but there may be times that you really don’t have a choice. Changing the name of your database is a fairly straightforward process: 1. Issue the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command. This will create a trace file in the UDUMP directory that will allow you to re-create the control file, which is required to rename the database. 2. Issue the SHUTDOWN IMMEDIATE or SHUTDOWN command for a consistent database shutdown. 3. Modify the trace file created in step 1. It should just contain the text of the CRE- ATE CONTROL FILE statement. You will need to replace the REUSE statement with SET on the first line of the statement. Also, change the NORESETLOGS com- mand to RESETLOGS. When you are finished, it should look something like this: CREATE CONTROLFILE SET DATABASE “ORA817” RESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 3 MAXDATAFILES 254 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
CHANGING THE DATABASE NAME 319 MAXINSTANCES 1 MAXLOGHISTORY 899 LOGFILE GROUP 1 ‘D:\ORACLE\ORADATA\ORA817\REDO01.LOG’ SIZE 1M, GROUP 2 ‘D:\ORACLE\ORADATA\ORA817\REDO02.LOG’ SIZE 1M, GROUP 3 ‘D:\ORACLE\ORADATA\ORA817\REDO03.LOG’ SIZE 1M DATAFILE ‘D:\ORACLE\ORADATA\ORA817\SYSTEM01.DBF’, ‘D:\ORACLE\ORADATA\ORA817\RBS01.DBF’, ‘D:\ORACLE\ORADATA\ORA817\USERS01.DBF’, ‘D:\ORACLE\ORADATA\ORA817\TEMP01.DBF’, ‘D:\ORACLE\ORADATA\ORA817\TOOLS01.DBF’, ‘D:\ORACLE\ORADATA\ORA817\INDX01.DBF’, ‘D:\ORACLE\ORADATA\ORA817\TEMPTEMP01.DBF’ PA R T CHARACTER SET WE8ISO8859P1; II 4. Back up the physical database control file. (It’s always a good idea to make sure that you have a current backup of the database as well!) Remove the control file after backing it up. Oracle Database 5. If you are using Windows NT, you will need to stop the database service and Administration then edit it to rename the service. Then restart the service. Perform these actions with ORADIM. (See Chapter 3 for more on ORADIM.) 6. You will need to rename the database parameter file and change any database name references inside that file, such as the database name, instance name, and service names. 7. If you are using a password file, you will need to rename the old password file. 8. Change your ORACLE_SID so that it is pointing to the new database name. 9. Issue a STARTUP NOMOUNT command to start the database in NOMOUNT mode. Execute the script you created in step 3. 10. After running the script, issue the RECOVER DATABASE command (if your are in ARCHIVELOG mode and did not shut down the database cleanly). Then issue the ALTER DATABASE OPEN RESETLOGS command. Your database should open normally. 11. Change the global name of your database with the ALTER DATABASE RENAME GLOBAL_NAME command, like this. ALTER DATABASE RENAME GLOBAL_NAME TO newname; 12. Make another backup of your database. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
320 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Managing Database Files After you have your database up and running, you will need to manage various data- base files. Your datafiles may need some attention, and you might want to add a con- trol file. Also, there may be cases where you need to move online redo logs. These topics are covered here. Managing Datafiles Your datafiles are defined with the tablespace that contains them. However, if neces- sary, you can move and resize them. Also, Oracle provides some data dictionary views that provide information about database datafiles. Moving Datafiles To move a datafile with the database up and running, you must first be in ARCHIVELOG mode. To move a datafile that is assigned to a tablespace, follow these steps: 1. Take the datafile offline. 2. Physically move it to the new file system. 3. Alter the datafile location internal to Oracle with the ALTER DATABASE RENAME FILE command. 4. Bring the datafile back online. Listing 7.4 provides an example of moving an existing datafile. Listing 7.4: Moving a Database Datafile -- First, take the datafile offline. ALTER DATABASE DATAFILE ’d:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf’ OFFLINE; -- Now, copy the datafile. Host copy d:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf e:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf -- Now, rename it in the database. ALTER DATABASE RENAME FILE ’d:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf’ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
MANAGING DATABASE FILES 321 TO ’e:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf’; -- Bring the datafile online. ALTER DATABASE DATAFILE ’d:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf’ ONLINE; If you are not running your database in ARCHIVELOG mode, you will need to shut down the database, physically move the datafile again, mount the database, and issue the ALTER DATABASE RENAME command to rename the datafile’s location on the disk. Resizing Datafiles As explained in Chapter 5 (in the discussion of creating tablespaces), the AUTO- PA R T EXTEND clause lets you define how large a datafile is allowed to grow and how large it can grow at any given time. You can also use the ALTER DATABASE DATAFILE com- II mand with the RESIZE clause to resize database datafiles, as in this example: ALTER DATABASE DATAFILE ’d:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf’ Oracle Database RESIZE 10000k; Administration If you are making a datafile smaller, you can only remove free blocks from the end of the datafile. If the datafile has any used blocks, or any free space contained between used blocks, you will not be able to free that space. Viewing Datafile Information Oracle provides many different views for managing database datafiles. Table 7.3 lists the most commonly used views for managing datafiles. TABLE 7.3: COMMON VIEWS FOR DATABASE DATAFILES View Description DBA_DATA_FILES Lists datafiles information, such as the datafile number and asso- ciated tablespace names. DBA_FREE_SPACE Provides free space information for datafiles. Can be used to determine how much free space can be de-allocated from a data- base datafile. V$BACKUP Indicates the backup status of a datafile. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
322 CHAPTER 7 • ORACLE DATABASE MAINTENANCE TABLE 7.3: COMMON VIEWS FOR DATABASE DATAFILES (CONTINUED) View Description V$DATAFILE Provides a large amount of information on database datafiles from the control file. This view is available after the database is mounted. V$RECOVER_FILE Provides information on database datafiles that require media recovery. This view is available after the database is mounted. FET$ Lists datafiles and free blocks (or extents) within the datafiles. UET$ Lists all used extents in a database datafile. DBA_EXTENTS Allows you to locate the datafile in which a specific extent is located. Adding Control Files You should have at least two control file copies at a minimum. If necessary, you can add a control file to your database. Simply follow these steps: 1. Shut down the system. 2. Copy an existing control file for the system to the new name and location of the control file you wish to add. 3. Modify the database parameter file to reflect the addition of the control file. 4. Restart the database. Moving Online Redo Logs Even as a seasoned DBA, it always makes me a bit queasy to even think about touch- ing the online redo logs. This is because the redo logs are critical for database recovery operations, so if something should happen to them, you could end up with an unre- coverable database. To move an online redo log (you might want to do this for I/O distribution pur- poses), you can choose one of two paths. One method is to just delete and re-create the online redo log group, using the ALTER DATABASE DROP LOGFILE and ALTER DATABASE ADD LOGFILE commands (see the “Sizing the Redo Logs” section in Chapter 3 for examples of using these commands). Alternatively, you can manually move the online redo logs and then rename them in the database using the ALTER DATABASE FILE RENAME command. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
ANALYZING DATABASE OBJECTS 323 WARN I NG Remember how important the online redo logs are to Oracle. Act care- fully when manipulating them. To move a database redo log file, follow these steps: 1. Shut down the database and then mount it. 2. Rename the old log file. 3. Physically move the log file to its new location (for example, using the cp or copy command). Create it at the new location with its proper name. 4. To rename the redo log within the database itself, use the ALTER DATABASE RENAME FILE command. 5. Open the database. PA R T Review Listing 7.4 for an example of moving a file in Oracle (redo logs are moved II in the same manner as datafiles). Analyzing Database Objects Oracle Database Administration The ANALYZE command collects a great deal of useful information about tables, indexes, and clusters. The ANALYZE command has the following main purposes: • To collect or delete statistics about tables, indexes, clusters, and scalar object attributes • To validate the structure of tables, indexes, clusters, and REFs • To identify migrated and chained rows of a table or cluster • To create histograms to analyze data distribution Here, we will discuss the basics of gathering statistics and validating objects using the ANALYZE command. The topics of analyzing objects in conjunction with using cost-based optimization (CBO), identifying chained rows, and creating histograms are covered in Chapter 16. Also, analyzing database objects is a prerequisite for some Oracle maintenance and tuning activities, which are discussed in Chapters 15 through 17. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
324 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Gathering Statistics The ANALYZE command provides statistics on tables, such as the number of rows in the table, the number of blocks above the high-water mark of the table, average free space in each block, and a great deal of additional information. When analyzing indexes, you gather statistics on the depth of the index from its root block to its leaf blocks, the number of leaf blocks in the index, the number of distinct index values, and other information. You can either compute the statistics of the object, by using the COMPUTE STATIS- TICS clause of the ANALYZE command, or estimate the statistics of the object, by using the ESTIMATE STATISTICS clause. You can analyze a table, index, or cluster. If you analyze the table, all associated indexes are analyzed by default. However, you can specify that you do not want to analyze the indexes along with the table. You can also choose to analyze only specific columns in a table (all columns is the default), only specific partitions or subpartitions of a partitioned object (Chapter 24 discusses partitioned objects), or only local indexes. WARNING Never analyze an object owned by SYS. These are data dictionary objects and should not be analyzed. Computing Statistics The COMPUTE STATISTICS clause gives you the most accurate statistics possible on the object you are analyzing (short of building histograms, which we’ll discuss in Chapter 16). Here are examples of computing statistics for a table and an index: ANALYZE TABLE employee COMPUTE STATISTICS; ANALYZE INDEX ix_employee COMPUTE STATISTICS; The downside to computing statistics is that you need a significant amount of tem- porary space—enough to hold and sort the entire table. As a benchmark, figure that you need about three times the size of the table as workspace for an analysis using the COMPUTE STATISTICS method. (Note that if indexes are being analyzed, no addi- tional space is required.) Also, a complete analysis of a table takes significantly more processing time than for an analysis using the ESTIMATE STATISTICS clause. This can be particularly noticeable in a warehousing or Decision Support System (DSS) envi- ronment, where you are dealing with very large objects. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
ANALYZING DATABASE OBJECTS 325 Estimating Statistics Computing statistics may not be a realistic option because of the demand on resources. As an alternative, you can use ANALYZE with the ESTIMATE STATISTICS clause. When you estimate a given object’s statistics, Oracle reads through a sample set of rows or blocks, and based on that sample set, generates estimated object statis- tics. You can estimate based on either a specific number of rows or on a percentage of the total number of rows in the object. NOTE If you choose to have the CBO estimate more than 50 percent of the table, it will actually compute the statistics instead. (Of course, Oracle doesn’t bother to tell you that it’s doing so.) PA R T Let’s look at a few examples of the ANALYZE command using the ESTIMATE STA- II TISTICS clause: • To estimate statistics for a table based on the first 2000 rows: ANALYZE TABLE employee ESTIMATE STATISTICS SAMPLE 2000 ROWS; Oracle Database • To estimate statistics for a table based on a sample of 20 percent of the rows: Administration ANALYZE TABLE employee ESTIMATE STATISTICS SAMPLE 20 PERCENT; • To estimate statistics for an index using a sample size of 2000 rows: ANALYZE INDEX ix_employee ESTIMATE STATISTICS SAMPLE 2000 ROWS; If you issue the ANALYZE TABLE ESTIMATE STATISTICS command without defin- ing how many or what percentage of rows to sample, Oracle will, by default, use a 1064-row sample to estimate the statistics. Is there any difference between doing an ANALYZE ESTIMATE using ROWS or an ANALYZE ESTIMATE using PERCENT? Some DBAs have found that using PERCENT generates statistics that more closely align with the actual statistics of the object. We have seen consistently better performance of queries against objects where PERCENT was used, as opposed to ROWS. Thus, we strongly suggest that you use PERCENT when analyzing an Oracle object. NOTE If you attempt to analyze an index that has been marked UNUSABLE (such as a partitioned table and a partition of a global index), the ANALYZE operation will fail. You will need to rebuild the UNUSABLE index before you can analyze it. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
326 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Viewing Statistics The ANALYZE command populates many columns in the DBA_INDEXES, DBA_TABLES, _TAB_COL_STATISTICS, and DBA_TAB_COLUMNS tables. This also applies to the USER and ALL versions of these data dictionary tables. Table 7.4 lists all of the columns populated by the ANALYZE command, and Table 7.5 describes the commonly referenced columns. TABLE 7.4: TABLES POPULATED BY THE ANALYZE COMMAND Table (DBA, USER, and ALL) Columns with Analyze Results _INDEXES BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_ PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED _TABLES NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_ CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, SAMPLE_SIZE, LAST_ANALYZED _TAB_COL_STATISTICS NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, GLOBAL_STATS, USER_STATS, AVG_COL_LEN _TAB_COLUMNS NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE TABLE 7.5: COMMONLY REFERENCED ANALYZE STATISTICS Column Table, Index, Description or Column LAST_ANALYZED All Date the object was last analyzed. SAMPLE_SIZE All Size of the last analyze sample. AVG_COL_LENGTH Column Average length of data in the table column. LOW_VALUE/HIGH_VALUE Column High value and low value for the data points contained in the column. NUM_NULLS Column Number of NULL values currently in the column. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
ANALYZING DATABASE OBJECTS 327 TABLE 7.5: COMMONLY REFERENCED ANALYZE STATISTICS (CONTINUED) Column Table, Index, Description or Column AVG_DATA_BLOCKS_PER_KEY Index Average of how many index blocks a single key resides in. AVG_LEAF_BLOCKS_PER_KEY Index Average of how many index leaf blocks a single key resides in. BLEVEL Index Height of an index—indicates the num- ber of levels from the root block to the node blocks. DISTINCT_KEYS Index Number of distinct keys present in the index. The closer this number is to NUM_ROWS, the more selective the PA R T index. LEAF_BLOCKS Index Number of leaf blocks contained in the II index. AVG_ROW_LEN Table Average length of a row in a table. You can multiply this by NUM_ROWS to dis- Oracle Database cover the total amount of data you are Administration actually storing in a table. AVG_SPACE Table Average amount of free space, in bytes, in a block in the table. BLOCKS Table Number of blocks in the table. CHAIN_CNT Table Number of chained rows in the table. CLUSTERING_FACTOR Table Clustering factor—indicates the ordering of the rows in the table as compared to the order in the index. A lower number is generally better. (See Chapter 16 for details.) EMPTY_BLOCKS Table Number of unused blocks allocated to the table above the high-water mark. NUM_ROWS Table/Index Number of rows in the table. Use this number to keep track of table growth. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
328 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Removing Statistics from an Object Using the DELETE STATISTICS clause of the ANALYZE command, you can remove sta- tistics that have been generated with the ANALYZE command. You can remove statis- tics on a table, cluster, or index. You can also delete statistics for a specific partition of a partitioned table. If you remove statistics from a table, the statistics of any associated index are also removed. Here’s an example of using the ANALYZE DELETE command: ANALYZE TABLE employee DELETE STATISTICS; So, why would you want to use the DELETE STATISTICS command? Perhaps you have decided not to keep existing statistics on a given object because you wish the queries using that table to use rules-based optimization (RBO), rather than CBO. Or, you might have objects that you don’t keep statistics on because you wish them to use RBO, yet you want to have the statistics available for management purposes. In this case, you would analyze the object, copy the statistics somewhere else, and then delete the statistics. For example, Chapter 15 describes how to generate average block usage reports for a table. If you are using Oracle in an RBO environment and you want to actually run these reports, you can analyze the table, run the report, and remove the statistics using the ANALYZE TABLE DELETE STATISTICS command. Validating the Structure of an Object The ANALYZE VALIDATE command allows you to validate the structure of tables, table partitions, indexes, and clusters. You might validate an object if you suspect all or part of it has become corrupted. Also, validating an index is a way to collect statis- tics that are not otherwise collected. Here is a summary of the effects of validating each type of object: Table validation Each row and each data block in the table is validated. If you wish to validate that all of the table’s rows are properly stored in all associ- ated indexes, use the CASCADE clause of the ANALYZE VALIDATE command. When you validate a partitioned table, in addition to the validation routines exe- cuted for normal tables, each row is checked to ensure that it is in the correct par- tition. If an invalid row is discovered, an entry is made in a table called INVALID_ROWS. Index validation Each data block in an index is validated and checked for corruption. However, no cross-checking is done against the table on which the index is built to make sure that all entries in the index are valid. Oracle also col- lects useful statistics that are loaded in tables called INDEX_STATS and INDEX_ HISTOGRAM, which store statistics from only one index at a time. For example, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
DIAGNOSING DATABASE PROBLEMS 329 the INDEX_STATS table includes information about the number of distinct keys in the index, number and total length of deleted leaf rows in the index, how often the most repeated key is repeated, total space allocated and used in the B*Tree, and the height of the B*Tree. Cluster validation The structure of the cluster’s tables is validated. You can use the CASCADE keyword with the ANALYZE VALIDATE command to force vali- dation of the cluster table’s associated indexes. WARN I NG Use caution when validating an object. During the period of the valida- tion, you will not be able to make any changes to the object, and all DML activity on that object will wait for the validation to be completed. PA R T II Diagnosing Database Problems As a DBA, you know that databases occasionally have problems. Perhaps a process Oracle Database Administration keeps failing, or maybe the database keeps shutting down for what appears to be no good reason. Of course, you need to identify the cause of a problem before you can fix it. Oracle provides several tools that you can use for diagnosing and troubleshooting database problems. These include trace files, the alert log, the DBV utility, and the DBMS_REPAIR packages. Using Trace Files and the Alert Log Oracle trace files provides varying levels of information about the database. In partic- ular, they can provide some insightful information about why the database or an associated process has failed. The alert log is created by the database and can be used to monitor the database for ongoing events. Reviewing Process Trace Files Database processes generally create process trace files when they encounter some situation that causes process failure or could indicate some problem condition (for example, encountering a bad block that could be repaired). Sometimes, processes will create informational messages in their trace files. The location of process trace files is controlled by the init.ora parameter BACKGROUND_DUMP_DEST. The file- naming convention differs on different operating systems, but it generally includes Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
330 CHAPTER 7 • ORACLE DATABASE MAINTENANCE the process name in the name of the file. An example of a process trace filename on a Windows NT system is ora816ARC0.log, containing the trace file for the ARC0 archive logging process. Networking processes also create trace files. For example, the listener process cre- ates a listener trace file and stores it in $ORACLE_HOME/network/trace by default. If you have a database crash, be sure to look for trace files written about the time of the crash. Oracle will probably be interested in these as a part of the diagnosis process. N OT E Each ARCH process will create a trace file by default. The parameter LOG_ARCHIVE_TRACE is supposed to prohibit this action. There is a bug (1307166) that causes a trace file to be created, regardless of the setting of LOG_ARCHIVE_TRACE. This bug is corrected in Oracle8i version 8.1.7.0. Reviewing User Trace Files User trace files are generally created at the request of a user session. The trace files generated are created in the directory location specified by the init.ora parameter USER_DUMP_DEST. These trace files generally contain run information from specific sessions that can be analyzed for performance-tuning purposes. User trace files are also used by the system when the DBA issues the ALTER DATA- BASE BACKUP CONTROLFILE TO TRACE command at the SQL prompt. When issued, this command will create a trace file that contains the required SQL commands to re- create the control file of the database. We will discuss this command in more detail in Chapter 10. Finally, the system processes themselves will create trace files as they operate. These trace files can be helpful in cases where the database processes have crashed. Using the Alert Log You can use the alert log to monitor database events such as log switches, startup and shutdown, tablespace coalesce operations, and certain database error conditions. Like the process trace file location, the alert log location is controlled by the init.ora parameter BACKGROUND_DUMP_DEST. In terms of monitoring a production database, you should keep an eye on the alert log. Database problems that will be reported in the alert log include segments that could not extend and certain Oracle database errors. You will also find other impor- tant day-to-day operating messages in the alert log. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com