Oracle RAC: Database performance issues

Oracle RAC: Database performance issues

RAC databases have more than one instance using the same set of resources, and a resource may be requested by more than one instance. Resource sharing is well managed by Global Cache Services (GCS) and Global Enqueue Services (GES). However, in some cases, the resource management operations could run into a deadlock situation and the entire database may hang because of serialization issues. Sometimes, software bugs also cause database-hang issues, and these situations almost always require the intervention of Oracle Support in the form of a service request.

Database hang issues can be placed in the following categories:

• Hung database
• Hung session(s)
• Overall instance/database performance
• Query performance

We will examine only the hung database, as that is more critical and complex than the others and also related to our point of interest. Detailed texts are available for analyzing the database and query performance.

Hung Database

Oracle Support defines a "true" database hang as "an internal deadlock or a cyclical dependency between two or more processes." When dealing with DML locks (that is, enqueue type TM), Oracle is able to detect this dependency and roll back one of the processes to break the cyclical condition. On the other hand, when this situation occurs with internal kernel-level resources (such as latches or pins), Oracle is usually unable to automatically detect and resolve the deadlock.

If you encounter a database hang situation, you need to take system state dumps so that Oracle Support can begin to diagnose the root cause of the problem. Whenever you take such dumps for a hang, it is important to take at least three of them a few minutes apart, on all instances of your database. That way, evidence shows whether a resource is still being held from one time to the next.

The maxdump file size should be set to unlimited, as this will generate bigger and larger trace files, depending on the size of the System Global Area (SGA), the number of sessions logged in, and the workload on the system. The SYSTEMSTATE dump contains a separate section with information for each process. Normally, you need to take two or three dumps in regular intervals. Whenever you make SYSTEMSTATE dumps repetitively, make sure you reconnect every time so that you get a new process ID and also the new trace files. Expect HUGE trace files!

Starting with Oracle Database 10g, a SYSTEMSTATE dump includes session wait history information. If you are using Oracle 10g or later, you don't need to take multiple system state dumps. The SYSTEMSTATE dump can be taken by any of the following methods.

From SQL *Plus:

alter session set max_dump_file_size = unlimited; alter session set events 'immediate trace name systemstate level 10';

Using oradebug:

REM The select below is to avoid problems on pre 8.0.6 databases select * from dual; oradebug setmypid oradebug unlimit oradebug dump systemstate 10

When the entire database is hung and you cannot connect to SQL *Plus, you can try invoking SQL *Plus with the prelim option if you're using Oracle 10g or later. This attaches the process to the Oracle instance and no SQL commands are run. No login triggers or no pre-processing is done, and no SQL queries are allowed to run. See the change in banner from normal sqlplus and prelim sqlplus.

$sqlplus -prelim SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 11:42:23 2005 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter user-name: / as sysdba SQL>

Alternatively, oradebug allows you to dump the global system state by connecting to one node. The following shows the global SYSTEMSTATE dump from oradebug: oradebug –g all dump systemstate 10

The –g option is used for RAC databases only. This will dump system states for all the instances. The SYSTEMSTATE dump/trace file can be found in the user_dump_dest directory on the instance where the dump was generated.

Hanganalyze Utility

A severe performance problem can be mistaken for a hang. This usually happens when contention is so bad that it seems like the database is completely hung. Usually, a SYSTEMSTATE dump is used to analyze these situations. However, if the instance is large with more than a few gigabytes of SGA and with a heavy workload, a SYSTEMSTATE dump may take an hour or more and often fails to dump the entire SGA and lock structures. Moreover, a SYSTEMSTATE dump has the following limitations when dealing with hang issues:

• Reads the SGA in a "dirty" manner, so it may be inconsistent when the time to dump all the process is long.
• Usually dumps a lot of information (most of which is not needed to determine the source of the hang), which makes it difficult to determine quickly the dependencies between processes.
• Does not identify "interesting" processes on which to perform additional dumps (ERRORSTACK or PROCESS STATE).
• Often very expensive operation in for databases that have large SGAs. A SYSTEMSTATE dump can take hours, and taking few continuous dumps within a few minutes interval is nearly impossible.

To overcome the limitations of the SYSTEMSTATE dump, a new utility called hanganalyze was introduced in Oracle 8i. In Oracle 9i, the hanganalyze command was enhanced to provide clusterwide information in RAC environments on a single shot. This uses the DIAG daemon process in the RAC process to communicate between the instances. Clusterwide, hanganalyze will generate information for all the sessions in the cluster regardless of the instance that issued the command.

Hanganalyze can be invoked from SQL *Plus or through oradebug (which is available when you connect as SYS in the SQL *Plus utility). The following syntax can be used to get a hanganalyze trace when connected to SQL *Plus:

alter session set events 'immediate trace name hanganalyze level ';

Or when logged in as SYS:

oradebug hanganalyze

Clusterwide, hanganalyze can be obtained like so:

oradebug setmypid oradebug setinst all oradebug -g def hanganalyze

The sets the amount of additional information that will be extracted from the processes found by hanganalyze (ERROSTACK dump) based on the STATE of the node. The following table describes the various levels and the trace information emitted when they are set:

Hanganalyze uses internal kernel calls to determine whether a session is waiting for a resource and reports the relationships between blockers and waiters. In addition, it determines which processes are "interesting" to be dumped, and it may perform automatic PROCESS STATE dumps and ERRORSTACKs on those processes, based on the level used while executing hanganalyze

.

NOTE Hanganalyze is not intended to replace a SYSTEMSTATE dump, but it may serve as a road map to interpret a system state while diagnosing complex issues. Performance issues related to row cache objects, enqueues, and latches can be analyzed only with hanganalyze and/ or a SYSTEMSTATE dump. The process state information in these dumps provides an accurate snapshot of the locks/latches held by each process or session. It also tells us which event the process was waiting for and if any TM locks were held for a transaction. Problems associated with DDL statement locks and row cache lock issues can be debugged using only these dumps.

Use the following table of contents to navigate to chapter excerpts or click here to view RAC Troubleshooting in its entirety.

Oracle Database 10g: Real Application Clusters Handbook
  Home: Oracle RAC troubleshooting: Introduction
  1: Oracle RAC: Log directory structure in cluster ready services
  2: Oracle RAC: Log directory structure in Oracle RDBMS
  3: Oracle RAC and the Lamport algorithm
 4: Oracle RAC: ON and OFF
  5: Oracle RAC: Database performance issues
  6: Oracle RAC: Debugging node eviction issues
  7: Oracle RAC: Member voting
  8: Oracle RAC: Cluster reconfiguration steps
  9: Oracle RAC: Debugging CRS and GSD using DTRACING