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
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.
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';
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.
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:
Clusterwide, hanganalyze can be obtained like so:
oradebug setmypid oradebug setinst all oradebug -g def hanganalyze
|1-2||Only hanganalyze output, no process dump at all|
|3||Level 2 + Dump only processes thought to be in a hang (IN_HANG state)|
|4||Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF, LEAF_NW, IGN_DMP state)|
|5||Level 4 + Dump all processes involved in wait chains (NLEAF state)|
|10||Dump all processes (IGN state)|
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.
|About the book:|
|Oracle Database 10g: Real Applications Clusters Handbook Learn to implement Oracle real application clusters from the ground up. Maximize database availability, scalability, and efficiency. Find RAC concepts, administration, tuning, and troubleshooting information. You'll learn how to prepare and create Oracle RAC databases and servers, and automate administrative tasks. You'll also get full coverage of cutting-edge Oracle RAC diagnostic tools, backup and recovery procedures, performance tweaks and custom application design strategies. Buy this book at McGraw-Hill/Osborne|
|About the author:|
|K Gopalakrishnan is a senior principal consultant with the Advanced Technology Services group at Oracle Corporation, specializing exclusively in performance tuning, high availability, and disaster recovery. He is a recognized expert in Oracle RAC and Database Internals and has used his extensive expertise in solving many vexing performance issues all across the world for telecom giants, banks, financial institutions, and universities.|
This was first published in May 2007