Sometimes, I certainly refer to very few cases, we will feel that our library "hang", the meaning of adding homes is sometimes hang, there are not because of performance Problem. I have encountered several haang's situations, combined with some online articles, put possible reasons, and some of the operations we should do, there is a summary of the following, you can send Mail to me: qiuyb@21cn.com.
I. Possible phenomenon when database hang
1. The most intuitive is that most of your business operations, such as a query uses a long time, or it will not return results at all. This is different from the simple lock form.
2. Use the HP-UNIX with HP-UNIX with Glance, AIX with NMON and use SAR to monitor the system idle illusion, and the surface looks very idle, and the system has already hang.
3, check V $ session_wait will have a lot of "Latch Free", "Enqueue", "Free Buffer Waits" and so on, sometimes there will be a lot of .trc files in the background, you need to watch $ oracle_home / rdbms / log Location, there are some TRACE files to generate here.
Second, some useful operations and queries when HANG in Oracle library
1. If you want to seek Oracle's technical support, we need DUMP Oracle's SystemState to operate the following SQL> conn / as sysdba; sql> alter session set events' immediate trace name systemstate level; need to wait a few minutes, this This .TRC file can be found in the location of the user_dump_dest identified in INIT
2, capture status values for some views sql> conn / as sysdba; SQL> Set linesize 500sql> set pagesize 0sql> spool v_views.txtsql> SELECT * from V $ Parameter; SQL> SELECT CLASS, VALUE, NAME FROM V $ sysstat; SQL> SID, ID1, ID2, TYPE, LMODE, REQUEST from V $ LOCK; SQL> Select L.Latch #, N.NAME, H.PID, L.GETS, L.MISS, 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; - Repeated 3 times in a few seconds. SQL> Spool off
3. If you have condition, use Statspack to generate a Report if you are not familiar with StatsPach, you can refer to http://www.itpub.net/showthread.php?s=&threadid=144448 this link.
Third, several causes of hang possible
1. In the case of the invasive file, the file system in which the archive position is located, then the LGWR will wait for the completion of the archive process, and DML can't write the log, all in the waiting state.
2, when the HP system is aware of IO, Mlock permissions are not set for the group of DBA. Normally, Oracle's startup will be reported, but sometimes it is like Oracle8.1.6 version does not report an error, but at this time, the asynchronous IO is problematic, carefully view you will see it in $ oracle_home / rdbms / log A lot of .tc files. I have encountered hang in this case. 3. Due to the abnormal process, what I am talking about is a particularly large process that takes up system resources (CPU, Memory), which generally takes up 90% of the CPU, and relatively outstanding relative to other processes. Monitor can be monitored by tools such as HP-UNIX Glance, TOPAS, IBM NMOD, TOPAS, PS.
4. Because of the bottleneck of the host system, the bottleneck of the array, in practice, the most common should be IO problem, such as the IO, there is a bottleneck, then DBWR, LGWR will lead "Free Buffer Waits", "Log Buffer Space "Waiting for a series of waiting incidents, at this time, it is a performance problem, it feels like hang.
5, there is also a bug factor, my libraries are the latest patches, this problem has not been encountered.
Fourth, some processing suggestions When you get Oracle's Kuqi slow or HANG, it is important to keep a calm mind, you can perform some processing as follows:
1. First go to the operating system, take a look at the tools such as Glance, look at the busyness of the CPU, memory, switched area, and disk and usually look at the usual process. Sometimes when these processes are caused by V $ Process and V $ sessions, two views of this Oracle process are found, serial #, use ALTER SYSTEM KILL Session 'SID, Serial #'; kill it. If you usually have a busyness of your disk 95%, while it is 20%, of course, it is assumed, indicating that the Oracle library does not really hang.
2, the time, the statement in "II" is necessary, so you can submit this information to Oracle's Supporter, let you analyze you, check the reason.
3, if your library is archived, there is a time when there is a HANG, you must see if the file system of the archive log is full, and it is necessary to make a complete network management system.
4. When opening asynchronous IO on the HP system, you must not forget that this group of mlock is the permissions of mlock, I have eaten this loss.
5, often watching V $ session_wait or use Statspack to watch waiting event, general IO-related events, open asynchronous Io for Oracle, more write processes and Raw Device use for Oracle data files It is necessary to bring it. Exchange with the developer of the application is also very important. It is not allowed to have a new application to the special IO or special CPU resource.
6. Try to use a relatively stable Oracle version, such as Oracle8.1.7.4 and ORACEL9.2.0.4 (the latest 9.2.0.5) is very stable, you can have a lot of trouble.
