Oracle Database hang

Oracle Database hang I. Possible phenomena in database hang
1. The most intuitive thing is that most of your business operations, for example, a query takes a long time, or no results are returned at all. This is different from the simple lock table.
2. Using HP-UNIX with glance, Aix with nmon, and SAR for monitoring in the operating system may lead to the illusion that the system is idle. On the surface, the system looks idle. In fact, the system has hang.
3. Check v $ session_wait for a large number of wait events such as "latch free", "enqueue", and "free buffer waits". Sometimes a large number of wait events occur in the background. TRC file. You also need to check the location of $ ORACLE_HOME/rdbms/log. Sometimes the trace file will be generated here.

Ii. Some useful operations and queries for Oracle Database hang
1. If you want to seek technical support from Oracle, dump the systemstate of Oracle as follows:
SQL> Conn/As sysdba;
SQL> alter session set events 'immediate trace name systemstate level 10 ';
Wait a few minutes. In this case, the. TRC file can be found at the location identified by user_dump_dest set in init <Sid>. ora, which is generally large.
2. Capture Status values of some views
SQL> Conn/As sysdba;
SQL> set linesize 500
SQL> set pagesize 0
SQL> spool v_views.txt
SQL> select * from V $ parameter;
SQL> select class, value, name from V $ sysstat;
SQL> select Sid, id1, Id2, type, lmode, request from V $ lock;
SQL> select L. latch #, N. name, H. PID, L. gets, L. misses, L. immediate_gets, L. immediate_misses, L. sleeps from V $ latchname N, V $ latchholder H, V $ latch L where L. latch # = n. latch # and L. ADDR = H. laddr (+ );

SQL> select * from V $ session_wait order by SID; -- repeats three times every several seconds.
SQL> spool off
3, conditional words with statspack generated a report, if you are not familiar with statspach, you can refer to the http://www.itpub.net/showthread.php? S = & threadid = 144448.

Iii. Possible reasons for hang
1. When archiving is enabled, when the file system at the archiving location is full, lgwr will wait for the archiving process to complete, and DML will not be able to write logs and will be in the waiting state.
2. When the HP system enables asynchronous Io, The mlock permission is not set for the DBA group. Normally, Oracle startup reports an error, but sometimes, for example, oracle8.1.6 does not report an error, but asynchronous Io is faulty at this time, you will see a lot of data in $ ORACLE_HOME/rdbms/log. TRC file. In this case, I have encountered hang.

3. Due to abnormal processes, I am talking about processes that occupy a very large amount of system resources (CPU and memory). These processes generally occupy more than 90% of the CPU, it is more prominent than other processes. It can be monitored through tools such as HP-Unix glance, topas, IBM nmod, topas, and PS.

4. Due to bottlenecks in host systems and arrays, I/O problems are the most common problems in practice, such as I/O bottlenecks, dbwr and lgwr will introduce a series of wait events, such as "free buffer waits" and "log buffer space". This is a performance issue and feels like hang.

5. There are also bugs. My libraries are all the latest patches, and I have never encountered any problems in this regard.
Iv. Suggestions
It is important to maintain a calm mind when Oracle's database is slow or hang. You can perform the following operations:
1. First, go to the operating system and use glance and other tools to check whether the CPU, memory, swap zone, and disk are busy or not, at the same time, let's see if there are any processes that occupy a very large amount of system resources. Sometimes these processes are caused by these processes. Find the SID of the Oracle process through the V $ process and V $ session Views, serial #, use alter system kill session 'sid, serial # '; To kill the session. If your disk is 95% busy in the same period of time and 20% busy in some cases, it is assumed that the Oracle database may be hang.

2. If the time is too late, it is necessary to execute the statements in "2". In this way, you can submit the information to the Oracle supporter, so that you can analyze the information and check the cause.
3. If your database is archived, you must check whether the file system for archiving logs is full when hang occurs. It is necessary to build a complete network management system.
4. Do not forget to add the mlock permission to the DBA group when enabling asynchronous Io on the HP system. I have suffered this loss.
5. Pay attention to V $ session_wait or use statspack to wait for events. Generally, there are many io-related events, it is necessary to enable asynchronous Io for Oracle, add more write processes, and use strip for raw devices used by Oracle data files. It is also important to communicate with application developers. A new application may consume I/O or CPU resources.

6. Try to use stable Oracle versions. For example, oracle8.1.7.4 and cancel9.2.0.4 (the latest version is 9.2.0.5) are stable and can reduce the trouble.