Show TOC

Object documentationPerformance Overview Locate this document in the navigation structure

 

This is the main screen in the SAP/Oracle Database Monitor. It gives you an overview of the performance of your Oracle database.

 

You start the database monitor from the DBA Cockpit by choosing   Performance   Performance Overview  . You see an overview of database performance.

You can right-click a field and choose:

  • Help for more information on the meaning of the field

  • Details to see the values for each instance if you are running an Oracle Real Application Cluster (RAC)

If you are running RAC, you choose in DB Instances whether to display overview information for one RAC instance or for the total of all RAC instances.

Note Note

The appearance of a yellow or red light indicates that the difference in percent for the value of at least one instance from the average of all instances exceeds a certain limit. The limit values are maintained in table ST04N_LIM.

End of the note.

Structure

The fields are grouped as follows:

  • General information, data source is V$INSTANCE

    Field

    Description

    DB instance

    Name of the current database instance

    This is the SAP-SID in non-RAC environments.

    DB node

    Host name of the selected DB node

    This is the database server in non-RAC environments.

    DB release

    Release of the current database

    Day, time

    Current day and time

    Start up at

    Date and time when the current database instance started

    Sec. since start

    Seconds since start of the current database instance

    Sec. btw. snaps.

    Seconds between snapshots

  • Data buffer

    Field

    Description

    Size

    Size of the data buffer in KB

    • Non-RAC or RAC detail: data buffer size of the current instance

    • RAC total: total instance-related database buffers

      Data source: V$SGA

    Quality

    Data buffer quality, calculated as follows:

    100% – ((physicalreads – physicalreads_direct – physicalreads_directlob) / (sess_logicalreads – physicalreads_direct – physicalreads_directlob))

    Non-RAC or RAC detail: data buffer quality of the current instance

    RAC total: average of quality for all instance-related data buffer

    Data source: V$SYSSTAT

    Size default pool

    Size of the default buffer pool in KB, calculated as follows:

    select value from v$parameter where name = 'db_cache_size'

    Data source: V$PARAMETER

    Size keep pool

    Size of the keep buffer pool in KB, calculated as follows:

    select value from v$parameter where name = 'db_keep_cache_size'

    Data source: V$PARAMETER

    Size others

    Sum of the sizes of all other buffer pools in KB, calculated as follows:

    select (a.sum_a - b.sum_b) as buff_others from ( select sum(value) as sum_a from v$parameter where name = 'db_cache_size' or name = 'db_keep_cache_size' or name = 'db_2k_cache_size' or name = 'db_4k_cache_size' or name = 'db_8k_cache_size' or name = 'db_16k_cache_size' or name = 'db_32k_cache_size' or name = 'db_recycle_cache_size' ) a, ( select sum(value) as sum_b from v$parameter where name = 'db_cache_size' or name = 'db_keep_cache_size' ) b

    Data source: V$PARAMETER

    Logical reads

    Number of logical read operations

    • Non-RAC or RAC detail: logical reads of the current instance

    • RAC total: total logical read operations for all instances

      Data source: V$SYSSTAT

    Physical reads

    Number of physical read operations

    • Non-RAC or RAC detail: physical reads of the current instance

    • RAC total: total physical read for all instances

      Data source: V$SYSSTAT

    Physical writes

    Number of physical write operations

    • Non-RAC or RAC detail: physical writes of the current instance

    • RAC total: total physical write operations for all instances

      Data source: V$SYSSTAT

    Buffer busy waits

    Number of buffer busy wait situations

    • Non-RAC or RAC detail: total waitstat counters of the current instance

    • RAC total: total waitstat counters for all instances

      Data source: V$WAITSTAT

    Buffer wait times (s)

    Sum of buffer busy wait times

    • Non-RAC or RAC detail: sum of wait times for all wait counters of the current instance

    • RAC total: sum of wait times for all wait counters for all instances

      Data source: V$WAITSTAT

  • Shared pool

    Field

    Description

    Size (kB)

    Shared pool size in KB

    • Non-RAC or RAC detail: shared pool size of the current instance

    • RAC total: total shared pool size for all instances

      Data source: V$SGA_DYNAMIC_COMPONENTS

    DD-cache quality (%)

    Data dictionary cache quality as percentage, calculated as follows:

    100% – (totalget_misses / totalgets)

    • Non-RAC or RAC detail: data buffer quality of the current instance

    • RAC total: average cache quality for all instances

      Data source: V$ROWCACHE

    SQL area getratio (%)

    Ratio of gethits to gets as a percentage, calculated as follows:

    sum (gethits) / sum (gets) x 100

    • Non-RAC or RAC detail: get ratio of the current instance

    • RAC total: average of all instance-related get ratios

      Data source: V$LIBRARYCACHE

    SQL area pinratio (%)

    Ratio of pinhits to pins as a percentage, calculated as follows:

    sum (pinhits) / sum (pins) x 100

    • Non-RAC or RAC detail: pin ratio of the current instance

    • RAC total: average of all instance-related pin ratios

      Data source: V$LIBRARYCACHE

    SQLA Reloads/pins (%)

    Ratio of reloads to pins as a percentage, calculated as follows:

    sum (reloads) / sum (pins) x 100

    • Non-RAC or RAC detail: reloads per pin ratio of the current instance

    • RAC total: average of all instance-related pin ratios

      Data source: V$LIBRARYCACHE

  • Log buffer

    Field

    Description

    Size (kB)

    Size of the redo log buffer in KB

    • Non-RAC or RAC detail: redo log buffer size of the current instance

    • RAC total: total instance-related redo log buffer sizes

      Data source: V$SGA

    Entries

    Number of redo log buffer entries

    • Non-RAC or RAC detail: redo log buffer entries of the current instance

    • RAC total: sum of all instance-related redo log buffer entries

      Data source: V$SYSSTAT

    Allocation retries

    Number of redo buffer allocation retries

    • Non-RAC or RAC detail: allocation retries of the current instance

    • RAC total: total instance-related log buffer allocation retries

      Data source: V$SYSSTAT

    Alloc fault rate (%)

    Redo buffer allocation retries as a percentage of redo entries

    • Non-RAC or RAC detail: allocation fault rate of the current instance

    • RAC total: average of all instance-related allocation fault rates

      Data source: V$SYSSTAT

    Redo log wait (s)

    Redo log wait in seconds

    • Non-RAC or RAC detail: Redo log wait time of the current instance

    • RAC total: sum of instance-related redo log wait times

      Data source: V$SYSSTAT

    Log files (in use)

    Number of active log files

    • Non-RAC or RAC detail: active log files for the current instance

    • RAC total: total active log files

      The figure in brackets refers to the number of active log files in use.

      Data source: V$LOGFILE

  • Calls

    Field

    Description

    User calls

    Number of user calls

    • Non-RAC or RAC detail: user calls for the current instance

    • RAC total: total user calls for all instances

      Data source: V$SYSSTAT

    User commits

    Number of user commits

    • Non-RAC or RAC detail: user commits for the current instance

    • RAC total: total user commits for all instances

      Data source: V$SYSSTAT

    User rollbacks

    Number of user rollbacks

    • Non-RAC or RAC detail: user rollbacks for the current instance

    • RAC total: total user rollbacks for all instances

      Data source: V$SYSSTAT

  • Time Statistics

    Field

    Description

    Busy wait time (s)

    Busy wait time in seconds, calculated as the sum of the time waited for all non-idle events.

    • Non-RAC or RAC detail: busy wait time for the current instance

    • RAC total: total instance-related busy wait times

      Data source: V$SESSION_EVENT

    CPU time session (s)

    CPU time session in seconds, calculated as sum of CPU time used by this session

    • Non-RAC or RAC detail: total CPU time for the current instance

    • RAC total: total CPU time for all instances

      Data source: V$SYSSTAT

    Time/User call (ms)

    Time for each user call in milliseconds, calculated as follows:

    (busy wait time + CPU time) / user calls

    • Non-RAC or RAC detail: time for the current instance

    • RAC total: average time for all instances

      Data source: V$SESSION_EVENT and V$SYSSTAT

    Sessions busy (%)

    Busy sessions as a percentage, calculated as follows:

    (busy wait time + CPU time) / total wait time

    • Non-RAC or RAC detail: percentage for the current instance

    • RAC total: average percentage for all instances

      Data source: V$SESSION_EVENT and V$SYSSTAT

    CPU usage (%)

    CPU usage as a percentage, calculated as follows:

    CPU time / (elapsed time x CPU count)

    • Non-RAC or RAC detail: percentage for the current instance

    • RAC total: average percentage for all instances

      Data source: V$SYSSTAT, V$INSTANCE, and V$PARAMETER

    Number of CPUs

    Number of CPUs

    • Non-RAC or RAC detail: CPUs for the current instance

    • RAC total: total CPUs for all instances

      Data source: V$PARAMETER

  • Redo Logging

    Field

    Description

    Redo writes

    Number of redo log writes

    • Non-RAC or RAC detail: redo log writes for the current instance

    • RAC total: total redo log writes for all instances

      Data source: V$SYSSTAT

    OS blocks written

    Number of operating system redo blocks written

    • Non-RAC or RAC detail: redo blocks written for the current instance

    • RAC total: sum of written redo blocks for all instances

      Data source: V$SYSSTAT

    Latching time (s)

    Redo writer latching time in seconds

    Non-RAC or RAC detail: redo writer latching time for the current instance

    RAC total: total latching time for all instances

    Data source: V$SYSSTAT

    Redo write time (s)

    Redo write time in seconds

    • Non-RAC or RAC detail: redo write time for the current instance

    • RAC total: total redo write time for all instances

      Data source: V$SYSSTAT

    MB written

    Number of MB written

    • Non-RAC or RAC detail: redo log data written for the current instance

    • RAC total: total redo log data written for all instances

      Data source: V$SYSSTAT

  • Table scans and fetches

    Field

    Description

    Short table scans

    Number of short table scans

    • Non-RAC or RAC detail: short table scans for the current instance

    • RAC total: total short table scans for all instances

      Data source: V$SYSSTAT

    Long table scans

    Number of long table scans

    • Non-RAC or RAC detail: long table scans for the current instance

    • RAC total: total long table scans for all instances

      Data source: V$SYSSTAT

    Table fetch by rowid

    Number of table fetches by row ID

    • Non-RAC or RAC detail: table fetches by row ID for the current instance

    • RAC total: total table fetches by row ID for all instances

      Data source: V$SYSSTAT

    Fetch by contin. row

    Number of fetches by continued row

    • Non-RAC or RAC detail: table fetches by continued row for the current instance

    • RAC total: total table fetches by continued row for all instances

      Data source: V$SYSSTAT

  • Sorts

    Field

    Description

    Sorts (memory)

    Number of sorts in memory

    • Non-RAC or RAC detail: sorts in memory for the current instance

    • RAC total: total sorts in memory for all instances

      Data source: V$SYSSTAT

    Sorts (disk)

    Number of sorts in disk

    • Non-RAC or RAC detail: sorts on disk for the current instance

    • RAC total: total sorts on disk for all instances

      Data source: V$SYSSTAT

    Sorts (rows)

    Number of sorted rows

    • Non-RAC or RAC detail: sorted rows in memory for the current instance

    • RAC total: total rows for all instances

      Data source: V$SYSSTAT

    WA exec. optim. mode

    Number of work area executions in optimal mode

    • Non-RAC or RAC detail: work area executions in optimal mode for the current instance

    • RAC total: total work area executions in optimal mode for all instances

      Data source: V$SYSSTAT

    WA exec. one pass m.

    Number of work area executions in one-pass mode

    • Non-RAC or RAC detail: work area executions in one-pass mode for the current instance

    • RAC total: total work area executions in one-pass mode for all instances

      Data source: V$SYSSTAT

    WA exec. multipass m.

    Number of work area executions below the one-pass memory requirement

    • Non-RAC or RAC detail: work area executions in multipass mode for the current instance

    • RAC total: total work area executions in multipass mode for all instances

      Data source: V$SYSSTAT

  • Instance Efficiency

    Field

    Description

    Soft parse ratio

    Soft parse ratio is calculated as follows:

    1 – (parse count hard / parse count total)

    This shows whether there are many hard parses on the system. The ratio should be compared to the raw statistics to ensure accuracy. For example a soft parse ratio of 0.2 typically indicates a high hard parse rate. However, if the total number of parses is low, you can disregard the ratio.

    • Non-RAC or RAC detail: ratio of the current instance

    • RAC total: average ratio for all instances

      Data source: V$SYSSTAT

    In-memory sort ratio

    In-memory sort ratio is calculated as follows:

    sorts in memory / (sorts in memory + sorts on disk)

    This shows the proportion of sorts that are performed in memory. Optimally, in an operational online transaction processing (OLTP) system, most sorts are small and can be performed solely as in-memory sorts.

    • Non-RAC or RAC detail: ratio of the current instance

    • RAC total: average ratio for all instances

      Source: V$SYSSTAT

    Parse to exec. ratio

    Parse to execute ratio is calculated as follows:

    1 – (parse count total / execute count)

    In an operational environment, optimally a SQL statement should be parsed once and executed many times. Therefore an ideal value is close to 1.

    • Non-RAC or RAC detail: ratio of the current instance

    • RAC total: average ratio of all instances

      Source: V$SYSSTAT

    Parse CPU to total

    Parse-CPU-to-total ratio is calculated as follows:

    1 – (parse time CPU / CPU used by this session)

    This shows how much of the total CPU time used was spent on activities other than parsing. When this ratio is low, the system is performing too many parses.

    • Non-RAC or RAC detail: ratio of the current instance

    • RAC total: average ratio of all instances

      Source: V$SYSSTAT

    PTime CPU / PT elps

    Parse time CPU / parse time elapsed ratio is calculated as follows:

    parse time CPU / parse time elapsed

    This can often indicate latch contention. The ratio indicates whether the time spent parsing is allocated to CPU cycles (that is, productive work) or whether the time spent parsing was not spent on CPU cycles. Time spent parsing not on CPU cycles usually indicates that the time was spent sleeping due to latch contention.

    • Non-RAC or RAC detail: ratio of the current instance

    • RAC total: average ratio of all instances

      Source: V$SYSSTAT