Capacity reports


Warning

This feature is no longer supported by any of the KM's supported versions. From version 9.7.11.01, the feature is no longer available. For more information see, 9.7.11.01: Fix pack 1 for PATROL for Oracle Database.

Instance capacity is defined by the Oracle resources that are used by the users, processes, and transactions on the instance. The following reports give information about the availability of the instance:

  • Access Statistics
  • Locks Outstanding
  • Resources Used
  • Trace Files Analysis
  • Wait State Analysis

Before you can manage the job queue, you must activate the CAPACITY application by choosing KM Admin > Monitor Settings Modify from the INSTANCE menu, and enabling the CAPACITY category.

The CollCapacity parameter starts collecting information about the next polling cycle, and then populates the consumer parameters. The CAPACITY icon should appear within two polling cycles.

Determining capacity use on an instance

Use the reports in this section to determine how much of the Oracle resources have been used by the users, transactions, and processes on the instance. You can also determine the statistics on the DB object cache and the shared SQL area.

Accessing statistics for the instance

The Access Statistics report lists the numbers and types of access on the database objects.

Choose Reports > Capacity > Access Statistics from the INSTANCE menu, or Reports > Access Statistics from the CAPACITY menu for the instance. The report appears in a text window.

The following table shows the information that the report provides.



Access Statistics report

Item

Description

Owner

owner of the object

Name

name of the object

Type

type of database object

Namespace

library cache namespace of the object:

  • TABLE/PROCEDURE
  • BODY
  • TRIGGER
  • INDEX
  • CLUSTER
  • OBJECT

Memory

amount of memory the object uses

Loads

number of times the object has been loaded This count also increases when an object has been invalidated

Executions

number of executions on the object

Locks

number of users currently locking this object

Pins

number of users currently pinning this object

Buffers

number of buffers on the object


Viewing resources used on the instance

The Resources Used report lists the current values of the following parameters compared with the maximum value defined in each init.ora file:

  • OpenCursors
  • LocksUsed
  • ProcsUsed
  • SessionsUsed
  • TransactionsUsed
  • CursoresUsed
  • CursorsLeft

Choose Reports > Capacity > Resources Used from the INSTANCE menu, or Reports > Resources Used from the CAPACITY menu.

The report is displayed in a text window.

Tracing files space analysis for the instance

Use this report to determine if trace files are taking up too much space on the instance.

Choose Reports > Capacity > Trace Files Space Analysis from the INSTANCE menu, or Reports > Trace Files Space Analysis from the CAPACITY menu.

The following values are displayed in the report:

  • Background dump space left
  • Background dump space used
    BACKGROUND_DUMP_DEST specifies the path name for a directory where debugging trace files for the background processes (LGWR, DBWR, and so on) are written during Oracle operations.
  • Core dump space left
  • Core dump space used
    CORE_DUMP_DEST specifies the directory where core files are dumped.
  • User dump space left
  • User dump space used
    USER_DUMP_DEST specifies the pathname for the directory where the server writes debugging trace files on behalf of a user process.

    The AlertLogSize parameter under the ORACLE_CAPACITY application class and the Alerts parameters under the ORACLE_AVAILABILITY application class support monitoring locations of Oracle 11 g (DIAGNOSTIC_DEST) and conventions other than BACKGROUND_DUMP_DEST.
  • For BACKGROUND_DUMP_DEST,
    • For versions earlier than Oracle11 g, PATROL for Oracle looks for Alert_SID.log in the directory defined by the Oracle parameter, background_dump_dest.
    • For Oracle version 11 g and later, PATROL for Oracle looks for Alert_SID.log in the directory, diagnostic_dest/diag/rdbms/dbname/instanceName/trace. For example, /data2/oracle/diag/rdbms/ora112db/ora112db/trace.
  • For CORE_DUMP_DEST,
    • For versions earlier than Oracle11 g, PATROL for Oracle looks for directory defined by the Oracle parameter, core_dump_dest.
    • For Oracle version 11 g and later, PATROL for Oracle looks for directory in the diagnostic_dest/diag/rdbms/dbname/instanceName/cdump directory.
  • For USER_DUMP_DEST,
    • For versions earlier than Oracle11 g, PATROL for Oracle looks for directory defined by the Oracle parameter, user_dump_dest.
    • For Oracle version 11 g and later, PATROL for Oracle looks for directory in the diagnostic_dest/diag/rdbms/dbname/instanceName/trace directory.

      where diagnostic_dest is the Oracle parameter, which might contain $ORACLE_BASE as the value.

      For each PATROL Agent you can set only one value for $ORACLE_BASE. To resolve the actual value of the $ORACLE_BASE environment variable, set the path in the /OracleConfig. InstanceName.OracleBase pconfig variable to correct ORACLE_BASE.

Wait State Analysis report

The Wait State Analysis report shows information about wait events in the database.

Choose Reports > Capacity > Wait State Analysis from the INSTANCE menu, or Reports > Wait State Analysis from the CAPACITY menu for the instance.

The following table shows the information that the report provides.



Wait State Analysis report

Item

Description

User Name

name of the user session

Command

command in progress, or the last statement parsed

Schema Name

schema user name

OS User

operating system client user name

OS PID

operating system client process ID

Wait Event

resource or event for which the session is waiting.

SQL Statement

column containing one piece of the SQL text


Determining lock problems

Use the Locks Outstanding report to determine if there are any outstanding locks on the instance.

  1. Choose Reports > Capacity > Locks Outstanding from the INSTANCE menu, or Reports > Locks Outstanding from the CAPACITY menu.
  2. Select one of the following options, and click Accept.
    • For all user sessions, select List all user sessions sorted by, and select the sort order to use.
    • For an individual user session, select the user session from the User Name list.
      The locks for specific users, session, and objects are listed. The locks that are waiting are also listed.The following table shows the information that the report provides.



      Locks Outstanding report

      Item

      Description

      User Name

      name of the user with a lock that is outstanding

      Session ID

      SID of the user with the outstanding lock

      Lock Object

      object that the outstanding lock is for

Was this page helpful? Yes No Submitting... Thank you

Comments