The
database error log is the record of database-level errors.
Database
error logs may indicate a database problem that is not reported in other
locations.
1. In the Command field,
enter transaction ST04 and choose Enter (or choose Tools →CCMS, then Control/Monitoring →Performance Menu, then Database →Activity).
2. Check the database log.
A. Choose Goto →Database log.
B. In the Database Messages window,
select Only alerts.
Ø This selection reduces the amount
of text to look through in the first pass.
Ø During a second pass through this
transaction, select All messages.
C. Choose Display.
D.
Scroll down the
log to check for error messages.
3. Check the database statistics
A. Oracle data buffer checks
Ø If there are more than a few million reads,
the buffer quality should be > 98%
§ < 98% could indicate buffer too small or
expensive SQL statements
Ø Buffer waits should be < 5% of reads
§ > 5% could indicate I/O bottleneck
B. Oracle “call statistics” checks
Ø Reads per user call should be < 40
§ > 40 could indicate expensive SQL
Ø User calls / recursive calls should be > 5
§ < 5 could indicate that the Shared Pool
may be too small
C. Oracle “shared Pool statistics” checks
Ø DD cache quality should be > 90%
§ < 90% could indicate that the Shared Pool
is too small
Ø SQL Area getratio, pinratio should be >
90%
§ < 90% could indicate that the Shared Pool
is too small
4. Check database SQL
A. Go to Detailed analysis menu on tool
bar, then to Resource consumption by, SQL request, sort by “Buffer Gets”
B. Look for statements with:
Ø More than 1,000-10,000 bufgets / record (most
optimization potential)
Ø Buffer gets / total reads since start in more
than 5% of the total
Ø More than a few executions
Ø Larger fraction of reads from disk than other
statements
Ø Double click on line to get more information
§ Once displayed, the “Explain” option can be
selected for further analysis
No comments:
Post a Comment