Capacity reports

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

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

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