Table Access Monitor 
Definition
This submonitor in the SAP/Oracle Database Monitor lets you view the shared cursor cache from the viewpoint of the tables accessed. This helps you to identify performance problems for a table rather than for a statement, such as a missing index on a table.
Use
You choose Detailed Analyses ® Resource Consumption ® Table Access Monitor.
You cannot view history information in this monitor.
Structure
Entries marked “RAC specific” are only relevant for Oracle Real Application Cluster (RAC). In a non-RAC database, the values in these columns are zero.
· Summary on Table Level
This screen displays the following information:
Column |
Description |
Instance Id |
Database instance ID |
Owner |
Table owner |
Table |
Table name |
Size (kB) |
Table size in kilobytes |
Type |
Type of object (v$object dependency) |
Buffer pool |
Default buffer pool of the object |
Executions |
Total number of executions of all statements accessing this table |
Cache Hit Ratio |
Cache hits calculated with the following formula: 100 x (1– (sum of disk reads / sum of buffer gets)) |
Disk Reads |
Total number of disk reads executed on this table |
Disk Read Ratio |
Sum of (disk reads / physical reads) |
Buffer Gets |
Total number of buffer gets for the table |
Logical Read Ratio |
Sum of buffer gets / (sum of buffer gets + sum of disk reads) |
Rows Proc. |
Total number of rows returned or accessed by the statements on this table |
Rows/Exec |
Number of rows processed / number of executions |
Bufgets/Record |
Number of buffer gets / number of rows processed |
Sorts |
Total number of sorts done by the statements on this table |
CPU Time (ms) |
Total CPU time in milliseconds used by all statements on this table for parsing, executing, or fetching. |
Users Opening |
Total number of users executing the statement |
Opening Version |
Total number of statements related to this table having the child cursor locked |
Loaded Version |
Total number of statements related to this table having the context heap locked |
# Childs |
Maximum number of the child cursor |
Sharable Mem. |
Total amount of shared memory used by the statement in bytes |
Persistent Mem. |
Sum of the fixed amount of memory used for the lifetime of this statement in bytes |
Runtime Mem. |
Sum of the fixed amount of memory required during the execution of this statement in bytes |
# Invalidations |
Total number of times any cursor has been invalidated |
Parse Calls |
Total number of parse calls for all the statements |
ITL Waits |
Number of Interested Transaction List (ITL) waits for this table |
Buffer Busy Waits |
Number of buffer busy waits for this table |
DB Block Changes |
Number of database blocks changed for this table |
Global Cache CR Blocks Served RAC specific |
Number of global cache CR blocks served for this table |
Global Cache Current Blocks Served RAC specific |
Number of global cache current blocks served for this table |
Logical Reads |
Number of logical reads for this table |
Physical Reads |
Number of physical reads for this table |
Physical Reads Direct |
Number of direct logical reads for this table |
Physical Writes |
Number of physical writes for this table |
Physical Writes Direct |
Number of direct physical writes for this table |
Row Lock Waits |
Number of row lock waits for this table |
- Detail of Operations for <Table Name>
...
You can double-click a row on the screen Summary on Table Level to see the details of operations for a table. The only differences from the previous screen are:
¡ The column Operation is new.
¡ The column Table no longer appears.
- Details of Statements for <SQL Statement>
You can double-click a row on the screen Detail of Operations for <Table Name> to display the detail screen with the complete SQL statement in the final column.
From this screen you can select a row and choose:
¡ Execution Plan of SQL statement to display the execution plan
¡ Call Point in ABAP Program to display the ABAP coding, positioned at the calling point of the parsed statement
Integration
This monitor is based on the following views:
- GV$SQL
- GV$OBJECT_DEPENDENCY
- GV$DBA_SEGMENTS
- GV$SYSSTAT
- GV$SEGMENT_STATISTICS