Chat now with support
Chat with Support

Foglight for Oracle (Cartridge) 6.3.0 - User Guide

Installing and Configuring Agents Using Foglight for Oracle
Viewing the Databases Dashboard Assigning Instances to Users Selecting an Instance to Monitor Foglight for Oracle Overview Dashboard Overview view Advisories view SQL Performance Investigator (SQL PI) Oracle Activity Drilldown Pluggable Databases Drilldown Storage Drilldown Reviewing Configuration Settings Reviewing the Alert Log Reviewing Monitored Data Guard Environments Reviewing ASM Instances Reviewing Exadata-related Information
Administering Foglight for Oracle Reporting Reference Glossary

Viewing the Foglight for Oracle Real-time Summary Page

Viewing the Foglight for Oracle Real-time Summary Page

The following image displays the main elements of the Foglight for Oracle real-time summary page. The table that follows the image identifies each element, and provides a link to display more information, corresponding to the logical dataflow within Foglight for Oracle Instance Homepage.

The main groups (panes) of gathered icons and gauges, which represent the main activity areas in the Oracle connection process, are as follows:

•
Instance identification — used for identifying the instance, its type, and its properties. See Identifying the Instance .
•
Components representing instance data flow — the main activity area in the Foglight for Oracle Instance Homepage includes several panes and flows that represent the data flow in the Oracle operation. The dataflow is represented in a top-down design, that is, from the session to the physical disk storage.
Identifying the Instance

Use the Instance identification indicators to identify the instance, its type and its operation period.

These indicators are:

•
Instance name — identifies the instance.
•
Specified time range — indicates the period of time for which data is being displayed (by default: last 60 minutes).
•
Instance pane — contains the following indicators:
•
DB Type — identifies whether the monitored database’s type is SQL Server, Sybase, DB2 or Oracle.
•
DB Version — identifies the Oracle version number, along with the most recently installed service packs.
•
Up Since — identifies the date and time when the instance was last started.
* 
NOTE: The pane’s initial view only shows the date; to display the time as well, hover the mouse or click the date text.
•
OS Type — identifies the operating system’s version number, along with the most recently installed builds and service packs.
* 
NOTE: The pane’s initial view only shows the OS name; to display the build and service packs as well, hover the mouse or click the OS name’s text.
Monitoring the Instance

The indicators included in the Instance pane provide various details about the instance. All of the indicators display real-time behavior, color-coded for severity.

* 
NOTE: Clicking the Response Time, CPU (%) and Memory (%) indicators displays a popup that shows the metric’s value, plotted over time.
•
Response Time — the time (in milliseconds) that elapses from the moment a query, which is supposed to represent the general workload, is submitted, until the application indicates that the query was executed.
As the response time is usually the starting point for investigation, the Response parameter leads to the SQL Instance Summary panel in the SQL Activity drilldown,
•
CPU (%) and Memory (%) — the average CPU load and memory consumption (percentage), during the specified time range, of all CPU units that host the Oracle instance. This indicator displays the share of Oracle-incurred CPU load and memory consumption within the total figure.
Clicking the number on both icons displays a popup that shows the total CPU usage or memory consumption on the host, plotted over time.
•
Number of CPUs — the number of CPU units on the instance’s host.
•
Total RAM — the total amount (in megabytes) of the host’s physical memory.
•
Free RAM — the total amount (in megabytes) of physical memory available to the applications
Monitoring General Sessions’ Statistics

The Sessions pane monitors all session types, that is: system, user, and Oracle sessions. Use this pane to view the response time compared with the number of sessions and the instance’s level of activity. A high response time value may result from a long queue, that is: an overly high percentage of active users.

Long queues can indicate one of the following issues:

•
A massive workload — the system handles more users than it was initially designed to do.
•
A bottleneck — lack of system resources prevents users from carrying out their transactions, resulting in wait events and an increasing number of sessions that remain active for prolonged periods.

The Sessions pane displays the total number of sessions, distributed according to the following distinctions:

•
Active vs. inactive sessions
•
System (background) vs. User (foreground) sessions

The parameters in this pane lead to the Activity > Sessions panel. For details, see Viewing Detailed Sessions Data .

The Client applications represented graphically in the Sessions pane communicate with Oracle by sending and receiving network packets and by submitting SQL statements for execution by Oracle. The flows, described in the following table, help indicate performance issues if their values are too low.

 
Table 7. Monitoring General Sessions’ Statistics
Flow
Description
Executions

The rate per second of calls (both user and recursive) that executed SQL statements.

Received packets

The rate at which network packets are being received by Oracle from Client applications.

When this icon’s color is yellow, clicking it displays text that describes the deviations that triggered this display, along with a representation of the rate at which Oracle is encountering network packet errors.

Sent packets

The rate at which network packets are being sent from Oracle to Client applications.

When this icon’s color is yellow, clicking it displays text that describes the deviations that triggered this display, along with a representation of the rate at which Oracle is encountering network packet errors.

Monitoring the Data Guard Service

Use the Data Guard section for monitoring Oracle Data Guard on primary databases. This section displays the gap between the last archive log on the primary database and last applied archive log on the standby database.

Oracle Data Guard can be configured as either physical or logical standby. For details about the preparations required for monitoring each of these setups, see Monitoring the Data Guard physical standby and Working with logical standby (SQL Apply) .

As Oracle Data Guard supports physical standby databases that use Redo Apply technology, use this section to monitor the rate of applying redo log files on standby database instance.

* 
IMPORTANT: The Data Guard section displays data only if the Data Guard service is configured.
* 
NOTE: Starting from Oracle 11g, Oracle provides the Active Data Guard option, which allows the standby to be read-only mode and at the same time to receive changes received from the primary node.
Monitoring the Data Guard physical standby
To prepare the physical standby:
1
Ensure that the Standby Database is in receiving mode.
2
Use sqlplus to connect to the Primary Database as SYS user.
3
Create the standby Foglight schema owner.
4
Run the privileges granting script for granting privileges to the created standby user, still from primary database. This script can be downloaded from the Connection Details screen.
If the Data Guard mode being used is Active Data Guard, proceed to Step 6. Otherwise proceed to the next step.
5
Switch to read-only mode on the standby database.
* 
IMPORTANT: Switching to read-only is only for the standby user availability check; there is no need to stay in that mode after the checkup.
6
Ensure that the Foglight schema owner is available on the standby database.
7
Open a browser and run the Oracle Monitoring Installer wizard.
8
Select Add a New Instance.
9
Specify the standby database credentials and connection details (the user that was created in Step 4).
10
Click Verify Connectivity
After connectivity verification is completed successfully, start monitoring the physical standby database.
Working with logical standby (SQL Apply)
To prepare the logical standby:
1
Use sqlplus to connect to the Logical standby as SYS user.
2
Create the standby Foglight schema owner
3
Open a browser and run the Oracle Monitoring Installer wizard.
4
Continue the Oracle Monitoring Installer process as usual, using the Logical standby user credentials and connection details
After connectivity verification is completed successfully, start monitoring the logical standby database.
Monitoring the Workload

The Workload chart displays the workload of the average active sessions, plotted over time.

The workload can be plotted as either:

•
Baseline — workload plotted over time.
•
Breakdown — the amount of time the database spent waiting for various wait events, plotted over time.

To display the wait events breakdown, click the arrow to the right of the Breakdown link.

Monitoring Process Activity

The Process Activity pane enables the monitoring of the processes ran by the Oracle instance, using performance indicators such as the total number of blocked processes and the rate per second of parses that are carried out during the most recent sample.

The Process Activity pane displays the following parameters.

 
Table 8. Process Activity pane parameters
Parameter
Description
Recent Executions

The number of statements executed during the most recent sample.

Blocked Processes

The average number of blocked processes that were collected during the specified time range.

Blocked processes are processes that are waiting for another process to release a resource that the process is currently locking. Such processes can sometimes result in bottlenecks.

The Blocked Process indicator changes its color when one or more processes become blocked.

Parses

Total number of parse calls. This figure is the total of both hard parse calls (calls that require loading the SQL source code into RAM for parsing) and soft parse calls.

Concurrent Transactions

The number of currently running transactions.

For further details, see Reviewing Instance Usability .

To communicate with the Oracle memory, the Oracle processes use logical reads and soft and hard parses, represented as flows in the homepage.

Monitoring Memory Activity

The Memory Activity session provides a view on the Oracle instance’s memory utilization, as described in the following table.

 
Table 9. Monitoring Memory Activity
Section
Metric
Description
SGA

Total (MB)

The actual size currently allocated to the System Global Area (SGA) memory.

Buffer Cache (MB)

 

The amount of memory currently allocated to the buffer cache. The buffer cache, which is used for caching frequently accessed data blocks, is normally the largest memory cache used by Oracle.

Shared Pool (MB)

The size of the shared pool. The shared pool caches SQL statements, PL/SQL programs, object definitions, and session memory for MTS sessions. Performance can improve when the shared pool is sized properly, thereby avoiding re-parsing and reloading.

Buffer Cache Hit (%)

The percentage of block requests, which found the block in the buffer cache, out of the entire block requests (including requests that required disk reads).

Library Cache Hit (%)

 

The effectiveness of the library cache, computed by comparing the number of queries submitted to Oracle server for execution, which found the parsed execution plan in the library cache (soft parsing), with the entire number of queries submitted during the specified time range (including queries that required the parsing of a new statement, also known as “hard parsing”).

PGA

Total (MB)

The actual size currently allocated to the Program Global Area (PGA) memory.

PGA Hit (%)

The percentage PGA cache hit ratio.

When the value of this metric is 100%, it indicates that all work areas executed by the system processed the entire input data using exclusively the PGA memory, thereby making optimal use of this memory. Any lower value indicates that at least one work area required one or more extra passes over the input data.

Monitoring Disk Storage

The Storage pane contains the components listed below, which display storage status. The information shown in this pane is aggregated to the RAC level.

Datafiles-related Indicators
•
Cylinder — displays the total amount of used and free datafile storage.
Click the cylinder to drill down. A popup appears, displaying the total space usage by the database, plotted over time.
This popup can also be used for further investigation by means of the Tablespace Storage drilldown. For further details, see Tablespaces page .
•
Total — displays the total amount of datafile storage available (used and free), in megabytes.
•
Free — displays the amount of free RAC storage, in percent.
•
Autoextensible — indicates if at least one datafile in the database is autoextensible:
•
No — all of the datafiles in the database are not autoextensible.
•
Yes — at least one datafile in the database is autoextensible.
Archive Destinations-related Indicators

If archiving is configured, the following indicators display storage-related information about the most critical archive destination, that is: the archive destination with shortest time to failure.

This section contains the following indicators:

•
Cylinder — shows the amount of used archive destination memory.
•
Time to Failure (hours) — indicates the estimated time to failure in hours, based upon the rate of archiving.
Indicators of Storage-related ASM Information

If storage is managed using Automatic Storage Management (ASM), and the ASM instance is installed and configured, the following indicators display storage-related information about ASM:

•
Cylinder — displays the total amount of allocated and free ASM storage memory.
•
Allocated (megabytes) — the total amount of ASM memory available (allocated and free), in megabytes.
•
Free (percent) — the amount of free RAC ASM storage memory, in percent.
Monitoring Disk Activity

The Disk Activity section displays the following indicators:

•
Unarchived — if archiving is configured, indicates the amount of Redo Logs waiting to be archived, in percent.
The popup that appears when clicking the Unarchived flow can also be used for drilling down further to investigate, using the “Redo Logs” drilldown. For further details, see Redo Logs Panel .
•
ASM I/O — a textual indication of the rate of ASM memory I/O operations. If storage is managed using Automatic Storage Management (ASM), and the ASM instance is installed and configured, this field displays real-time behavior, color-coded for severity.
Tracking Physical I/O Activity

The real-time summary page contains flows and graphic images that are used for tracking physical I/O activity, as listed below.

All of the indicators display real-time behavior, color-coded for severity. Clicking each indicator displays a popup, which shows the metric’s value, plotted over time.

•
Physical Writes — displays the rate per second of physical writes, that is: the rate at which modified blocks are writ ten from the SGA to disk by the DBWR processes.
•
Physical Reads — displays the rate per second at which data blocks are read from disk, when a connection requests a page that does not already exist in the buffer cache.
•
Redo Writes — displays the rate per second of redo writes, that is: the rate at which redo log entries are written to the redo log files by the LGWR processes.

Reviewing Instance Usability

The Instance Usability panel displays aggregated information regarding the instance’s usability and availability.

This panel supports the following tasks:

•
Identifying instances. For details, see The Instance Pane .
•
Viewing usability-related alarms. For details, see The Alarms Pane .
•
Tracking the instance’s availability, plotted over the defined time range. For details, see The Instance Availability Section .
•
Tracking the listener’s availability, plotted over the defined time range. For details, see The Listener Availability Section .
•
Tracking the instance’s connection time, plotted over the defined time range. For details, see The Connection Time Section
•
Tracking the instance’s response time, plotted over the defined time range. For details, see The Response Time Section .
The Instance Pane

The Instance pane contains the following indicators:

•
DB Type — identifies monitored database’s type (for example: SQL Server or Oracle).
•
Oracle Version — identifies the Oracle version number, along with the most recently installed patches.
•
Up Since — identifies the date and time when the instance was last started. If the database is not running this area reads “Database is down”, and a popup is available, displaying the database status.
The Alarms Pane

The Alarms pane displays the usability-related outstanding alarms. These alarms alert the following issues per instance: response time, instance availability, instance connection availability, OS connection availability, instance connection time, and Listener status.

The Instance Availability Section

The Instance Availability section displays the instance availability in percentages, plotted over the time range. If the instance was unavailable once or more during this time, the relevant message describing the last occurrence of unavailability appears in the Error Message box.

The Listener Availability Section

The Listener Availability section displays the availability of all listeners configured for the instance, in percentages, plotted over the time range. If one or more of the listeners were unavailable during this time, a message describing this issue appears under the Listener Errors section.

The Connection Time Section

The Connection Time section displays the instance’s connection time, in seconds, plotted over the time range.

The Response Time Section

The Response Time section displays the instance’s response time, in milliseconds, plotted over the time range.

Reviewing Cluster Usability

Use the Cluster Usability panel to compare between the various RAC nodes, regarding the following indicators:

•
Availability — measured in percentages
* 
IMPORTANT: The Availability section also displays the listener’s availability during the specified time range.
•
Connection Time — measured in milliseconds
•
Response Time — measured in milliseconds
 

To investigate a single instance, select the requested instance from the list at the top of the panel. The display switches to instance usability. For details, see Reviewing Instance Usability .

Reviewing Locks Activity at the Cluster Level

The Activity Locks panel for a RAC comprises the following panes:

•
Locks Activity
•
Lock Tree (Current)
•
Locks Historical Tree
Locks Activity

The Locks Activity pane displays a breakdown of all lock types and their general properties.

 
Table 10. Locks Activity pane
Name
Description

Lock Name

The name of the lock.

Gets

The total number of enqueue requests or enqueue conversions for this type of enqueue.

Gets Rate

The rate of lock gets.

Gets (percent)

The percentage of lock gets for this type of lock within the total number of locks.

Enqueue Waits

The total number of times an enqueue request or conversion resulted in a wait event.

Waits Rate

The number of wait events per second.

Wait (percent)

The percentage of wait events for this type of lock within the total number of locks.

Lock Wait

The total amount of time spent waiting for the enqueue or enqueue conversion.

Average Lock Duration

The average length of time for a lock, in milliseconds.

Lock Efficiency

The percentage of lock requests that did not escalate to a blocking lock.

Alarms

The number of warning, critical, and fatal alarms that were invoked for this Oracle database instance.

Holding the cursor over each alarm type displays a dwell that shows the most recent alarms invoked for this database instance, sorted by severity.

Click each alarm type to drill down and manage the alarms. See the Foglight Online Help, Monitoring System-Wide Alarms for details on the alarm information.

Lock Tree (Current)

The Lock Tree (Current) pane displays current data, which is available only in real-time mode.

Clicking any locking or locked session table row displays a popup that is used for viewing further details. For information see Session Details Popup .

 
Table 11. Lock Tree (Current)
Name
Description

PDB

In multitenant architectures, the name of the pluggable database where the lock took place.

Session

The identifier for a session that holds or acquires the lock.

Serial

The session’s serial number. The combination of SID and serial number provides a unique identification of the session.

DB User

The database user name.

Object Wanted

The object requested for lock.

Lock Type

The type of user or system lock.

The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:

•
TM — DML enqueue
•
TX — Transaction enqueue
•
UL — User supplied

Lock Mode

The lock mode in which the session holds the lock. This row can have one of the following values:

•
None
•
Null
•
Row-S (SS)
•
Row-X (SX)
•
Share
•
S/Row-X (SSX)
•
Exclusive

Lock Request Mode

The lock mode in which the process requests the lock. This row can have one of the values listed below.

•
None
•
Null
•
Row-S (SS)
•
Row-X (SX)
•
Share
•
S/Row-X (SSX)
•
Exclusive

Lock Duration

The duration of the lock in seconds.

Cross Instance

Defines whether the lock is between different cluster nodes:

•
YES — for global locks
•
NO — for single node locks

Instance

The name of the instance to which the blocking or blocked session belongs.

Kill Session

Used for terminating the selected session, by means of the Kill Session dialog box that is displayed upon clicking this column.

NOTE: Killing a session requires having specific privileges. Please provide an Oracle user with these privileges. Alternatively, select the check box Log-in as SYSDBA to provide the credentials of a SYSDBA user.
Session Details Popup

 

Table 12. Session Details Popup
Name
Description

SID

The session identifier

Serial

The session’s serial number. The combination of SID and serial number provides a unique identification of the session.

Process

The process number

DB User

The database user name

OS

The operating system name

Program

The name of the program that is processing the SQL statements

Client

The Client computer identifier

Host

The host computer identifier

Current SQL

The SQL text of the SQL statement that is currently being processed in this session

Locks Historical Tree

The Locks Historical Tree pane displays the overall locks activity that took place within the selected time range, as well as locks that exceeded a predefined threshold.

This pane comprises the following sections:

•
Lock Wait Events — a chart that displays all of the lock wait events, plotted over time. This chart can be viewed in either of the following views:
•
Baseline — displaying only lock wait events.
•
Breakdown — displaying both lock and non-lock wait events.
•
Locks Exceeding a predefined threshold — displays locks that reached or exceeded the minimal duration, in seconds, which a lock should reach or exceed in order to be collected.
* 
NOTE: The minimal duration’s length is defined using the Locks pane of the Collection Settings view.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating