ST04 - DB Performance Analysis

Transaction code ST04 is used to shows detail of the database behave & usage, both history and current time.

• Data buffer cache size & quality must be above 95% [meaning less physical read from disk]

• User/Recursive Call > 2 [Recursive call will get more over time]

• Read/User Call < 30 [> 30 means expensive SQL statements]

• Time/User Call < 15ms

• Busy time & CPU time ratio 60:40 ratio. Higher ratio means tuning required

• Sort sections. It should be less than 0.1% of total sorts.

• Shared pool statistics. DD (data dictionary) cache quality should be more than 99%, similarly the SQL area get ratio.

• Instance performance. Soft parse ratio max value is 1 which is not possible because at least once the SQL is hard parsed and then soft parsed in its next executions. But this must be as close as possible to 1 for a healthy system. Similarly there is another fact which is in-memory sort ratio; this is for a healthy system should have higher values. In fact the less the disk sorts the better.

How to understand the information in ST04 screens

Data Buffer

It is a memory area to store database data/blocks so future access on the same data can be fulfilled from memory. Accessing data in memory is much faster than accessing data in the disk.

Data buffer is to reduce physical I/O as much as possible.

Size- display configured memory size.

Quality- number of memory reads/reads × 100%.

Reads- display the sum of number of disk access and memory access since start of database server.

Physical reads-total number of disk access.

Buffer busy wait- when a session wants to access a database block in memory but it cannot because the buffer is busy. (Related to concurrent accessing on the same database table)

Shared pool- it is a memory area to store executable SQL version of SQL codes & database objects. Shared pool is to promote reusing of parsed SQL & avoid disc access for database objects.

DD-Cache Quality- the Data Dictionary cache stores information on objects in the Oracle database, such as their name, definition & access to them.

SQL Area GetRatio & PinRatio- Stores the processing SQL statement to drive an access path to the data within the database.

Log buffer- it is a circular memory area to store every database change before they are written to log files. Every database change would create one “redo” entry in log buffer.

Change in log buffer can be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, all entries in the log buffer would be written to a redo log file by Oracle LGWR process, even though some redo record may not be committed. If necessary, the database can roll back these change.

The log buffer is flushed to persistent storage based on Oracle design:

- Every 3 seconds

- Every commit

- When 1/3 full

-when 1 meg full

Size- memory size of the log buffer

Entries- total number of redo entry since database was started.

Allocation Retries & Allocation fault rate- the number of failed attempts to allocate free space in the redo log buffer and the ratio.

Redo Log wait & Log files- display wait situation and number of log files used for recovery. Log file sync waits are a wait on lgwr process to complete a write to disk.

Call- display important statistics on database operation. Total number of user calls since database was started.

Commit- total number of committed transactions since database was started.

Rollbacks- total number of rollback transactions since database was started.

Recursive calls- display the number of recursive calls.

Recursive calls/user calls- display the number of recursive calls and user calls. SAP mentions total number of recursive calls should be smaller than the number of user calls by a factor of 5 in a productive environment.

Parses- display the total number of parsed SQL statement. Parses to User Calls should be under 25%.

Reads/User calls- display the number of Oracle block read on average from the data buffer to answer user queries. If this number is greater than 30, this point to “expensive” SQL statements

Time Statistics- display summery of database wait situation.

Busy wait time- show the cumulative time that is used up, because the database system had to wait for a resource that was not available at the time of the request.

If this value is high, you must perform a detailed analysis via Oracle wait event analysis.

CPU time- total CPU time since database was started.

CPU usage- average CPU usage since database was started.

Redo Logging- information here is related to redo log output performance.

Table scans & Fetches- display summery of sequential read as well as total number related to chain row/continued row or read via index.

Sort tables- display the total number of sequential read operations on small tables (tables with less than 5 Oracle data block) since database was started.

Long tables- display the total number of sequential read operations on small tables (tables with 5 or more Oracle data block) since database was started.

Fetch By rowed- displays total number of table rows chosen by index or by a unique ID (row ID, ROWID) in the SQL statement since database was started.

Continued Row- displays number of chained Rows fetched since database was started.

Sorts- sorts operations performed in the main memory and on the disk.