Monitoring Oracle 11G Performance: Tips And Tricks

Monitoring the performance of an Oracle 11g database is a crucial task for any database administrator. Oracle Database provides various tools and features to help administrators keep track of database health, identify performance issues, and implement corrective actions. One such tool is the Automatic Database Diagnostic Monitor (ADDM), which periodically collects snapshots of the database state and workload, allowing administrators to analyse and address any problems.

Oracle Database also offers performance views, such as v$osstat, v$sysstat, and v$sqlstat, which provide detailed information about CPU utilisation, RAM utilisation, HDD space, and tablespace usage. Additionally, Oracle Enterprise Manager's Performance page displays real-time database performance data, including average active sessions, throughput charts, and host system utilisation.

Furthermore, database administrators can set up “alerts and notifications” to be promptly informed about any unusual spikes or issues. Third-party tools like dbWatch Control Center and Toad can also aid in monitoring Oracle database performance and provide additional features for query optimisation and issue resolution.

Using Oracle Enterprise Manager to monitor performance

Oracle Enterprise Manager is a powerful tool for monitoring and optimising the performance of Oracle databases. It provides a detailed overview of the database's health and performance, helping administrators identify and address any issues.

Overview of Database Performance:

Oracle Enterprise Manager offers a central location, the Database Home page, to monitor the state and workload of your database. It provides essential information such as database status, uptime, Oracle Database version, host details, and listener details. The page also displays CPU utilisation, active sessions, and other key metrics, helping administrators quickly identify potential bottlenecks or problems.

Alerts and Notifications:

The system generates alerts when specific metric thresholds are exceeded, such as tablespace usage or CPU utilisation. Administrators can set critical and warning threshold values to ensure they are notified when the system enters an undesirable state. Alerts can also be configured to trigger specific actions, such as running a script to address the issue.

Automatic Database Diagnostic Monitor (ADDM):

Oracle Database includes ADDM, a self-diagnostic engine that periodically collects snapshots of the database's state and workload. ADDM analyses these snapshots, identifies performance problems, and recommends solutions. It covers areas such as resource contention, connection management, and lock contention in a multiuser environment.

Performance Statistics and Jobs:

Oracle Enterprise Manager allows administrators to track various performance statistics and provides jobs to monitor specific areas. For example, the "Top Sessions" job identifies the most resource-intensive sessions, waits, and SQL statements. The "Top Consumers" job includes statistics on services, modules, actions, and clients. Additionally, the "Wait Statistics" job is crucial for identifying slow response times and performance degradation.

Customisable Metrics and Thresholds:

Oracle Enterprise Manager supports custom metrics and thresholds, allowing administrators to monitor specific aspects of their database. These can be copied and applied to different instances or databases within the cluster.

Proactive Monitoring and Notifications:

Oracle Enterprise Manager enables proactive monitoring by setting up alerts and notifications for critical performance metrics. For example, administrators can track CPU usage, long-running queries, user memory usage rates, and blocking sessions. Alerts can be configured to send notifications via email or SMS when certain thresholds are reached, helping administrators take prompt action.

Visualisations and Reporting:

The system provides visual representations of performance data, such as charts and histograms, making it easier for administrators to identify trends and anomalies. For example, the Cluster Performance Page displays usage statistics for hosts, while the Cluster Database Performance Page shows charts for run queue length, paging rate, service time, and database throughput.

Support for Different Cluster Levels:

Oracle Enterprise Manager supports monitoring at all levels of a cluster environment, including the cluster itself, the cluster database, and the cluster database instances. It can aggregate and display performance information from multiple cluster databases, eliminating the need to access each database individually.

Security and Availability:

Oracle Enterprise Manager ensures that the monitoring system itself is highly available and adheres to best practices. It provides notification options to alert administrators of critical failures or issues. Additionally, the system offers policy recommendations and best practices to help secure and optimise the database environment.

In summary, Oracle Enterprise Manager is a comprehensive and customisable tool for monitoring Oracle database performance. It provides a wide range of features to help administrators identify and address performance issues, ensuring the optimal operation of their Oracle databases.

Identifying long-running queries

To identify long-running queries in Oracle 11g, you can use the following methods and queries:

Using dbWatch Control Center

DbWatch Control Center is a monitoring tool that allows you to track key performance metrics and set up alerts and notifications for potential issues. With dbWatch, you can execute the following command to find long-running queries:

Sql

- Command to find long-running queries in your Oracle database

Additionally, dbWatch offers a "Top user memory usage" job, which helps identify abnormal memory usage spikes and provides details such as session ID, memory usage, and the SQL statement being executed.

Using Oracle Views

You can use various Oracle views to identify long-running queries. Here are some examples:

Sql

- This query shows currently active SQL and related information

SELECT S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text

FROM v$sqltext_with_newlines t, V$SESSION s

WHERE t.address = s.sql_address

AND t.hash_value = s.sql_hash_value

AND s.status = 'ACTIVE'

AND s.username <> 'SYSTEM'

ORDER BY s.sid, t.piece;

Sql

- This query shows locks and blocking sessions

SELECT object_name, object_type, session_id, type,

- Type or system/user lock

Lmode, -- lock mode

- in which session holds lock

Request, block, ctime -- Time since current mode was granted

FROM v$locked_object, all_objects, v$lock

WHERE v$locked_object.object_id = all_objects.object_id

AND v$lock.id1 = all_objects.object_id

AND v$lock.sid = v$locked_object.session_id

ORDER BY session_id, ctime desc, object_name;

Sql

- This query shows long operations (e.g., full table scans)

COLUMN percent FORMAT 999.99

SELECT sid, to_char(start_time, 'hh24:mi:ss') stime, message,

Sofar / totalwork) * 100 percent

FROM v$session_longops

WHERE sofar / totalwork < 1;

Sql

- This query shows queries running for more than 60 seconds

SELECT s.username, s.sid, s.serial#, s.last_call_et / 60 mins_running, q.sql_text

FROM v$session s

JOIN v$sqltext_with_newlines q ON s.sql_address = q.address

WHERE status = 'ACTIVE'

AND TYPE <> 'BACKGROUND'

AND last_call_et > 60

ORDER BY sid, serial#, q.piece;

Sql

- This query shows long-running sessions with details like % completed and remaining time

SELECT SID, SERIAL#, OPNAME, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK;

Using v$sql_monitor View

The v$sql_monitor view can be used to find queries running longer than a specified threshold (e.g., 5 seconds). Note that this view may only be available in Enterprise versions of Oracle. Here's an example query:

Sql

- Identify slow-running queries from a specific service

SELECT to_char(sql_exec_start, 'dd-Mon hh24:mi'),

Elapsed_time / 1000000) run_time,

Cpu_time,

Sql_id,

Sql_text

FROM v$sql_monitor

WHERE service_name = 'TEST_APP'

ORDER BY 1 DESC;

Tracking user memory usage rate

Using SQL Queries

You can use SQL queries to monitor user memory usage in Oracle 11g. Here's an example query to identify the top memory usage rate per session:

Sql

SELECT sess.username AS username,

Sess.sid AS session_id,

Sess.serial# AS session_serial,

Sess.program AS session_program,

Sess.server AS session_mode,

ROUND(stat.value/1024/1024, 2) AS "current_UGA_memory (in MB)"

FROM v$session sess, v$statname name, v$sesstat stat

WHERE sess.sid = stat.sid

AND stat.statistic# = name.statistic#

AND name.name = 'session uga memory'

AND sess.username = 'YOUR_USERNAME';

In this query, we're joining the `v$session`, `v$statname`, and `v$sesstat` views to get the desired information. We're filtering the results to only include the memory usage for a specific username. You can replace `'YOUR_USERNAME'` with the actual username you want to track.

The `ROUND(stat.value/1024/1024, 2)` calculation is used to convert the memory value from bytes to megabytes for easier interpretation.

Using dbWatch Control Center

DbWatch Control Center is a tool that provides advanced performance monitoring features for Oracle databases. It offers a user-friendly interface to track various performance metrics, including user memory usage.

With dbWatch Control Center, you can set up a "Top user memory usage" job to monitor memory usage per session. This job will generate a report that helps you identify abnormal memory usage spikes during the working day. The report also includes information about the specific session ID, memory usage, and the SQL statement being executed. This information can be invaluable for query optimization and troubleshooting performance issues.

Using Oracle Enterprise Manager

Oracle Enterprise Manager is another powerful tool provided by Oracle for database administration. It offers a comprehensive set of features for monitoring and managing Oracle databases, including tracking user memory usage.

With Oracle Enterprise Manager, you can set up alerts and notifications to keep track of key performance metrics, such as memory usage. You can configure the system to send you an alarm if a certain memory usage threshold is reached. This proactive monitoring approach helps you stay ahead of potential issues and ensure optimal database performance.

Manual Calculation

If you prefer a more manual approach, you can calculate user memory usage by examining specific memory structures and their utilization. Here are the steps to calculate user memory usage:

• Identify the relevant memory structures: The main memory structures that affect user memory usage are the System Global Area (SGA) and the Program Global Area (PGA). The SGA is a shared memory area that contains data and control information for the database instance. The PGA is a private memory region used by server processes and contains run-time areas for SQL and PL/SQL operations.
• Monitor memory allocation: Use dynamic performance views like `V$SGASTAT` and `V$PROCESS` to monitor memory allocation and usage for the SGA and PGA, respectively. These views provide detailed information about memory allocation, utilization, and statistics.
• Calculate user memory usage: By analyzing the data from the dynamic performance views, you can calculate the user memory usage. This may involve summing up the memory allocations for specific users or sessions and comparing it to the total available memory.

Remember that monitoring user memory usage is just one aspect of performance tuning in Oracle 11g. It's important to consider other metrics and factors as well, such as CPU utilization, disk space usage, and query performance. By combining multiple monitoring techniques and tools, you can effectively optimize your Oracle 11g database's performance.

Detecting blocking sessions

To detect blocking sessions, you can use the following methods and queries:

Using Oracle Views

You can use Oracle views such as `DBA_BLOCKERS` and `V$LOCK` to find blocking locks and identify blocking sessions. Here's an example query using the `V$LOCK` view:

Sql

SELECT * FROM v$lock;

In the query results, focus on the ``BLOCK column. If a session holds a lock that's blocking another session, the value in the `BLOCK` column will be 1. You can also compare the values in `ID1` and `ID2` to determine which session is being blocked. The blocked session will have the same values in `ID1` and `ID2` as the blocking session.

Here's a more detailed query that helps identify the blocking and blocked sessions:

Sql

SELECT l1.inst_id, l1.sid, ' IS BLOCKING ', l2.sid, l1.type, l2.type, l1.lmode, l2.lmode, l2.inst_id

FROM gv$lock l1, gv$lock l2

WHERE l1.block = 1

AND l2.request > 0

AND l1.id1 = l2.id1

AND l1.id2 = l2.id2;

This query selects specific columns from the `gv$lock` view, including the instance ID, session ID, lock type, lock mode, and instance ID of both the blocking and blocked sessions. It filters for cases where `l1.block` is 1 (indicating a blocking session) and `l2.request` is greater than 0 (indicating a blocked session waiting for a resource). The query also ensures that the `id1` and `id2` values match between the blocking and blocked sessions.

Using dbWatch Control Center

DbWatch Control Center is a tool that provides advanced performance monitoring for Oracle databases. It offers a "Blocking detector" job in its monitoring module, which can provide details about blocking sessions, including the username and type of lock. You can configure this job to set a maximum threshold for the blocking session duration and receive notifications via email or SMS if the threshold is reached.

Using Oracle Cloud Infrastructure

If you're using Oracle Cloud Infrastructure, you can utilize the Performance Hub User Guide. It provides a "Blocking Sessions" tab that displays current blocking and waiting sessions in a hierarchical format. This tab allows you to view detailed information about each blocking session, inspect the SQL involved, and perform troubleshooting operations such as terminating sessions to resolve waiting session problems.

Additional Queries

Sql

SELECT 'Instance ' || s1.INST_ID || ' ' || s1.username || '@' || s1.machine ||

' ( SID=' || s1.sid || ',' || s1.serial# || s1.status || ' ) is blocking ' ||

S2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' || s2.sql_id

FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2

WHERE s1.sid = l1.sid

AND s1.inst_id = l1.inst_id

AND s2.sid = l2.sid

AND s2.inst_id = l2.inst_id

AND l1.BLOCK = 1

AND l2.request > 0

AND l1.id1 = l2.id1

AND l2.id2 = l2.id2;

```

This query provides detailed information about the blocking and blocked sessions, including instance ID, username, machine, session ID, and SQL ID.

Sql

SELECT /*+ RULE */

S.sid,

S.serial#,

P.spid "OS SID",

S.sql_hash_value "HASH VALUE",

S.username "ORA USER",

S.status,

S.osuser "OS USER",

S.machine,

S.terminal,

S.type,

S.program,

S.logon_time,

S.last_call_et,

S.sql_id,

L.id1,

L.id2,

DECODE(l.block, 0, 'WAITING', 1, 'BLOCKING') block,

DECODE(l.LMODE,

1, 'No Lock',

2, 'Row Share',

3, 'Row Exclusive',

4, 'Share',

5, 'Share Row Exclusive',

6, 'Exclusive', null) lmode,

DECODE(l.REQUEST,

1, 'No Lock',

2, 'Row Share',

3, 'Row Exclusive',

4, 'Share',

5, 'Share Row Exclusive',

6, 'Exclusive', null) request,

ROUND(l.ctime / 60, 2) "MIN WAITING",

L.type

FROM v$process p, v$session s, v$Lock l

WHERE p.addr = s.paddr

AND s.sid = l.sid

AND (l.id1, l.id2, l.type) IN (

SELECT l2.id1, l2.id2, l2.type

FROM V$LOCK l2

WHERE l2.request <> 0

Understanding wait statistics

Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing. Wait event data reveals various symptoms of problems that might be impacting performance, such as latch contention, buffer contention, and I/O contention.

Oracle Database includes a self-diagnostic engine called the Automatic Database Diagnostic Monitor (ADDM), which can be used to diagnose performance and determine how any identified problems can be resolved. ADDM periodically collects snapshots of the database state and workload, which are stored in the Automatic Workload Repository (AWR) and used for performance comparisons.

Wait events are grouped into classes, including Administrative, Application, Cluster, Commit, Concurrency, Configuration, Idle, Network, Other, Scheduler, System I/O, and User I/O.

The V$SESSION, V$SESSION_WAIT, V$SESSION_HISTORY, V$SESSION_EVENT, and V$SYSTEM_EVENT views provide information on what resources were waited for and, if the configuration parameter TIMED_STATISTICS is set to true, how long each resource was waited for.

• Buffer busy waits: Indicates that multiple processes are attempting to access buffers in the buffer cache concurrently.
• Db file scattered read: Signifies that the user process is reading buffers into the SGA buffer cache and waiting for a physical I/O call to return.
• Direct path read and direct path read temp: Occurs when a session is reading buffers directly from disk into the Program Global Area (PGA) rather than the SGA.
• Enqueue (enq:) waits: Enqueues are locks that coordinate access to database resources. This event indicates that a session is waiting for a lock held by another session.
• Free buffer waits: Indicates that a server process was unable to find a free buffer and has requested the database writer to make buffers available by writing out dirty buffers.
• Log file parallel write: Involves writing redo records to the redo log files from the log buffer.
• Library cache pin: Manages library cache concurrency and is acquired when a client wants to modify or examine an object.
• Library cache lock: Controls the concurrency between clients of the library cache and is obtained when a client wants to maintain a dependency for a long time.
• Log buffer space: Occurs when server processes are waiting for free space in the log buffer because redo is being generated faster than LGWR can write it out.
• Log file sync: When a user session commits or rolls back, the session's redo information must be flushed to the redo log file, and the server process performing this action waits under this event for the write to complete.
• SQLNet events: Signify that the database process is waiting for acknowledgment from a database link or client process.

It is important to note that idle wait events, such as "SQL*Net message from client", usually imply that if there is a bottleneck, it is not due to database resources.

To identify the cause of wait events, it is essential to examine related data and cross-reference it with other statistics. For example, for buffer busy waits, you can query V$WAITSTAT to see which block type has the highest wait count and wait time. Additionally, you can use segment-level statistics to identify specific tables or indexes causing performance problems.

By analyzing wait statistics and related timing data, you can gain insights into the performance bottlenecks in your Oracle database and take appropriate actions to optimize its performance.

Frequently asked questions