The Database System Check Monitor (transaction DB16) displays an overview of all the main database functions and statuses.
The Backup Log Monitor (transaction DB12) displays information about the status of database and offline redo log file backups.
The Tables and Indexes Monitor (transaction DB02) displays an overview of the storage behavior of the database and the status of the database objects.
The Database Performance Monitor (transaction ST04) displays an overview of the load and configuration of the database management system and the database.
The DBA Operations Monitor (transaction DB24) provides a central point from which you can access all database monitoring operations, such as database backup logs, optimizer statistic runs, and Oracle database checks.
Check these monitors daily to ensure that:
The cost-based optimizer does not use obsolete information
Too many extents are not created
Freespace problems do not occur
-->The cost-based optimizer uses an SQL statement to determine the most effective strategy for retrieving or manipulating database data. The access strategy used depends on the information in the:
Queried table (or tables, for a view or join)
Fields specified in the WHERE clause of the SQL statement
Indexes defined for the tables queried
The cost-based optimizer computes the cost of several strategies for accessing the tables, and chooses the one that requires the smallest amount of data accesses. To calculate the cost of a strategy, the optimizer requires statistical information about the tables and indexes of the database, such as:
Number of table or index rows and number of blocks allocated for the object
Number of distinct values in each column of the table
The statistical information for a table or index is stored in the data dictionary of the database. To collect the statistical information, use the Oracle SQL command ANALYZE table.
Note that the ANALYZE command is expensive.
Table and index sizes and value distributions can change. If the current number of rows of a table differs greatly from the values determined by the last analyze table run, the optimizer may choose an ineffective strategy and the database access time becomes longer.
You should refresh the optimizer statistics at least once a week.
-->Only up-to-date statistical information can ensure that the Oracle cost-based optimizer chooses the optimal access path. However, gathering optimizer statistics is expensive and reduces system performance.
We recommend the following two-phase strategy:
In the first phase, the SAP tools determine which tables require a statistical update. The command sapdba -checkopt PSAP% determines which database objects contain obsolete statistics, and modifies the control table DBSTATC accordingly.
In the second phase, the statistics of the tables marked TODO in the control table DBSTATC are refreshed using command sapdba -analyze DBSTATCO.
This two-phase strategy ensures:
Up-to-date statistics for all tables
Optimal analysis runtime
Updating Oracle Optimizer Statistics
*******************************
Use the DBA Planning Calendar (transaction DB13) to schedule the two phases of the strategy to run consecutively. The analysis commands are performed by program SAPDBA.
Check optimizer statistics determines the tables requiring an analyze table run.
To determine which database objects require a statistical update, choose the keyword PSAP%: all SAP tablespaces.
Update optimizer statistics at least once a week. Perform the updates during periods of low system activity.
Update optimizer statistics updates the table statistics.
To refresh the statistics of all tables with the TODO flag set in table DBSTATC, choose the keyword DBSTATCO: all tables marked in DBSTATC.
Monitoring Oracle Optimizer Statistics
*******************************
Use the following R/3 database monitors to check the state of the statistics.
To display an overview of the analysis dates, use the Database Tables and Indexes Monitor (transaction DB02) and choose Checks → Dates of table analysis. To display detailed information about the table statistics for tables contained in the control table DBSTATC, choose All tables.
To display the logs of the SAPDBA actions performed to refresh the optimizer statistics, use the DBA Logging Monitor (transaction DB24) and choose DB Optimizer.
To display either the SAPDBA check action logs (function ID opt) or the SAPDBA analyze action logs (function ID aly), choose Function IDs.
Check the return code of the actions performed. Actions that did not end successfully are marked either yellow (warning) or red (errors). To display the details of a SAPDBA log, double-click a SAPDBA action.
Periodic Monitoring: Next Extent Problems
*********************************
To avoid problems with the next extent size, SAPDBA has an automatic extent adjustment (-next). Use the DBA Planning Calendar (transaction DB13) to schedule this adjustment to run at least once a week for critical tablespaces that have high data growth.
To run the SAPDBA extent adjustment from the command prompt, enter: sapdba -next PSAP%.
The results of sapdba -next are written to directory sapcheck in file
To adjust the storage settings for single objects, use SAPDBA, and select d - Reorganization. The value for the NEXT extent should be at least 10% of an object's current size.
To display a list of tables and indexes that have allocated more than 1 extent in the last 4 weeks, use the Tables and Indexes Monitor (transaction DB02) and choose Check → Check next extent size. Look for objects that have allocated too many extents or are nearing the MAXEXTENT limit.
The R/3 Database System Check Monitor (transaction DB16) displays information about objects that exceed a threshold of extents (default 80) or are nearing the MAXEXTENT limit. You can also look at the Database Alert Monitor (transaction RZ20).
The INITIAL EXTENT should be large enough for the expected table or index size. If the extent of a data object becomes full during an insert or update operation, the Oracle storage management system attempts to allocate another extent of the size NEXT extent in the tablespace. An object can allocate extents up to the limit MAXEXTENT (default 300).
If the maximum number of extents per object is reached, the error message ORA-1631 (for a table) or ORA-1632 (for an index) is displayed. If this occurs, increase the parameter MAXEXTENT and check the size of the next extent.
Periodic Monitoring: Freespace Problems
********************************
Use the Tables and Indexes Monitor (transaction DB02) to:
Analyze the tablespaces that are marked as having critical freespace problems
Check the rate of the fill level to determine when a tablespace reaches a critical fill level
Check the Database System Check Monitor (transaction DB16) for freespace problem alerts. Potential bottlenecks in the freespace reserve of a tablespace are displayed in the log of command sapdba -check.
Extend critical tablespaces early. To do this, use the SAPDBA to either add a data file to a tablespace or resize the tablespace. (As of Release 4.5, resize is supported in SAPDBA.)
If you add a data file to extend a tablespace, ensure that the size of the new data file is large enough. If necessary, add a new hard disk and create a sapdata
Check the maximum number of data files allowed in your database. This number is defined in the init
After the structure of the data files have been changed, back up the database, or at least the newly added data file and the control files.
The step-by-step allocation of extents ensures that objects only allocate space when they need it. If there is not enough contiguous freespace to allocate a new extent, error message ORA-1653 (for a table) or ORA-1654 (for an index) is displayed.
Database System Check: sapdba -check
********************************
Use SAPDBA to check the following:
Table and index fragmentation
Tablespace filling
Physical consistency of the database (control files, redo log files, and data files)
Severe error messages in the alert log
init
Database problems specific to the R/3 environment
Schedule SAPDBA to run once a week, using either the DBA Planning Calendar (transaction DB13) or enter the sapdba -check command at the OS level.
Command sapdba -check generates a log file called
Periodic Monitoring: Archive Directory
***********************************
If an archiver stuck situation occurs:
The database system cannot write to the online redo log files
The database cannot perform any modifications to the database data
SAPDBA cannot connect to the database
If the archiver becomes stuck, monitor the database error logs. The archiver can become stuck if:
The database is in archive mode, but the Oracle archiver is not running. If this occurs, SAPDBA displays an error message. Use SAPDBA to start the Oracle archiver.
The archive directory overflows, due to a large amount of data manipulation (such as from upgrades or batch input). If this occurs, free up space in the archive directory.
To make space available in the archive directory, back up the offline redo log files to tape, using SAPDBA or the brarchive -cds. Before you delete offline redo log files, make 2 backups of each file.
If extra space is available, create a temporary (dummy) file in the saparch directory. In the case of an archiver stuck situation, these files can be deleted to free up some space.
Periodic Monitoring: Database Backups
***********************************
The DBA Planning Calendar (transaction DB13) displays the status of all the activities that were performed.
The DB Operations Monitor (transaction DB24) displays information about:
All the backup actions
The status of database and the offline redo log file backups
The log files written by BRBACKUP and BRARCHIVE
To prevent database errors from occurring, perform the following monitoring and maintenance tasks on your Oracle database:
Monitor the state of the optimizer statistics
Update the optimizer statistics
Monitor the database alert log
Check the tablespace fill levels and increase their size when necessary
Monitor the table and index extent growth
Monitor the offline redo log activity
-
No comments:
Post a Comment