Vendor Management Strategies for the CIOOracle 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
Requires Membership to View
To gain access to this and all member only content, please provide the following information:
By submitting your registration information to SearchSystemsChannel.com you agree to receive email communications from the TechTarget network of sites, and/or third party content providers that have relationships with TechTarget, based on your topic interests and activity, including updates on new content, event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile, unsubscribing via email or by contacting us here
- Your use of SearchSystemsChannel.com is governed by our Terms of Use
- We designed our Privacy Policy to provide you with important disclosures about how we collect and use your registration and other information. We encourage you to read the Privacy Policy, and to use it to help make informed decisions.
- If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States.
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
| Level | Trace Information |
| 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