Monitoring Calls (Oracle)
The total number of calls made to the Oracle kernel since database instance startup is recorded. In a busy production system, the value will be high. Any reduction in the number of calls sent to the kernel will ease the load put on the database system.
The Database Monitor displays the following information on Calls:
Commits is the total number of committed transactions since database instance startup.
Rollbacks indicate the total number of transactions that were rolled back since the database system was started. These rollbacks could be caused by failing programs, application deadlocks or abnormal application termination. If a high number of rollbacks are reported, you should check the database ALERT file (Database Message Log (Oracle)) and the trace files for possible problems.
- The Oracle database ALERT file and trace files for the Oracle background processes are usually found in: /oracle/<SID>/saptrace/background
- Trace files for Oracle user processes are found in: /oracle/<SID>/saptrace/usertrace
Recursive calls occur when Oracle itself must issue a SQL statement in addition to the SQL statement issued by a user process. The most common causes of recursive calls are:
- Misses in the data dictionary cache (Monitoring the Data Buffer (Oracle))
- Dynamic storage extension
- Execution of DDL statements, the enforcement of referential integrity constraints, use of PL/SQL (refer to Oracle documentation for more information)
Recursive calls can impair the performance of the database system and should be minimized when possible.
The recursive call ratio is calculated as Recursive calls/User calls. If the number of Recursive calls is greater than the number of User calls, then you should start a detailed examination. Check the data dictionary cache hit ratio and average parse ratio. Increasing shared_pool_size (SHARED_POOL_SIZE (Oracle)) should help. Ensure that the init<SID>.ora parameter row_cache_cursors (ROW_CACHE_CURSORS (Oracle)) is set to at least the SAP recommended minimum of 100.
Dynamic storage extension occurs when a database object (a table or index) must extend beyond its allocated space (that is, a new extent is allocated). SAP recommends that you always create the original extent (INITIAL parameter) and the following extents (NEXT parameter) large enough to minimize dynamic extent assignment. It is only possible to change the INITIAL storage parameter for an object through a reorganization. You should adjust the NEXT parameter to SAP requirements on a regular basis using the BRCONNECT option next. For more information, see Adapt Next Extents with BRCONNECT.
A table reorganization is generally not necessary. However, an index reorganization can sometimes prove helpful.
As in the case of the cache hit ratios, the value for recursive calls will be high after database instance startup. Since the data dictionary cache is at first empty, all calls needed to load information into working memory will be recursive.
Parses shows the total number of times an SQL statement was parsed (for information on the term "parses", refer to the Oracle documentation). To calculate the average parse ratio, you divide parses by user calls. If this ratio is above 25%, there may be a problem with retaining cursors in the shared cursor cache (SQL Request (Shared SQL Area)). Check the hit rates discussed in the shared SQL area statistics (Monitoring the Shared Pool (Oracle)). It may be necessary to increase shared_pool_size (SHARED_POOL_SIZE (Oracle)).
The value Reads / User calls displays the number of Oracle blocks on average that were read from the data buffer to satisfy a request (call) sent to the database. If this value is greater than 30, this indicates expensive SQL statements. You should, therefore, begin to examine the shared SQL area.
See also:
Monitoring the Shared SQL Area (Oracle)