Diagnose the Hanging problem of the Oraacle database (1)

The following diagnostic steps are discussed:

1) clearly describe the problem
2) Search for specific errors
3) collect operating system-level data
4) Get the dump of systemstate and hanganalyze
5) Get the output report of STATPACK
6) Get the dump of PROCESSSTATE

Note: You may not need to shut down the database to stop hanging. We recommend that you obtain the diagnostic information before closing the database to find out the cause of the error.

The following describes how to diagnose the database Hanging.

Clearly describe the problem:

First, find out the running database version. The complete version number is required, for example, 9.2.0.4.

Are you sure the current database is really hanging or active, but it is very slow? Check whether there is any log switch in the Alert file, and check the current CPU, I/O, and memory usage.

How long does it take to view the start time of the database hanging? Is the database hanging suddenly occurring or is the performance gradually decreasing due to the increase of active transactions? How many connected users are there currently? Is the recent system load increasing?

Is any event set in the initialization parameter file? What type of transactions is the database currently performing? What is the data size of the database?

Is the database running in the cluster environment? If it is a cluster database, close other instances and leave an instance. Does the problem continue? Some of the solutions discussed here apply to cluster databases, but most of them are not suitable. For example, a small buffer cache usually means better performance for cluster databases. Most hanging problems of cluster databases are not discussed here, including PCM locks, pinging, space management, parallel query optimization between nodes, shared disks, or virtual shared disks, network Problems and DLM problems.

Is the database running in the MTS environment? If MTS is canceled, is the problem persists? Are Oracle Applications or tools used? Have you recently upgraded databases, applications, tools, operating systems, and hardware? How often does the problem occur? Can the problem be reproduced?

Is the entire database hanging? 

All instances? All connections? All operations? All nodes?

First, are you sure you can execute the query select * from dual? How often does one switch log files? If an error message related to archiving is found in the Alert Log, you can solve the archive error problem because the database is suspended frequently. For example, if the archive destination space is full or the database is in archive mode, but the ARCH process is stopped. Generally, you can connect to the database with the sysdba permission and execute the archive log list to check whether the database ARCHIVE mode is enabled and whether automatic archiving is enabled. Generally, if automatic archiving is not enabled, it is easy to suspend the database. In this case, the common practice is to change the database to the automatic archiving mode or non-archiving mode.

A specified SQL statement operation?

1) if the database is suspended due to the specified SQL statement, run the TKPROF output report with the timed_statistics parameter and the Execution Plan of the SQL statement. Then, analyze the SQL statement types:
2) if it is a select statement, the SQL statement should be adjusted. If it is a very complex SQL statement, try to interrupt it.
3) For a parallel query statement, you can obtain the execution plan of the parallel query by referring to the script that monitors the running status of the current parallel query. It may be space transaction competition. If a ORA-1575 error occurs in the Alert Log File, set the parameter pct_increase for the temporary tablespace to 0 to prevent the SMON process from joining continuous extents, therefore, the competition for querying slaves is reduced. Meanwhile, data files should be distributed to different disks as much as possible to reduce disk I/O competition. Increasing sort_area_size may reduce the degree of parallelism.
4) if it is a DML statement, it may be caused by a lock. You need to obtain the output information of v $ lock. For details about the lock information, refer to the returned lock information script. Check whether there are limits or triggers on the DML statement objects, which may cause level-based interlocking problems. The index is created on the related foreign key column, which changes the lock behavior on the parent table.
5) a ddl statement may be related to a data dictionary. If it is a create index statement, it may be a space transaction competition problem. Adjusting I/O is a good method. Distributed I/O separates the index and data storage space. parallel execution is a useful method. You can also set the initialization parameter pre_page_sga to true.

Specified database object?

Can I perform any operation on a specified object? Is there a problem with making a select count? If there is a problem with the update object, it may be locked. You can obtain the lock information from the script 3) and 4) above.

Is the space allocated to this object in advance? If yes, it will increase HWM and cause full table scanning, making the database look like "suspended. Full table scan always scans HWM, even if the table contains only a small amount of data. The solution is to avoid pre-allocation of extents unless a large parallel insertion or regular loading is to be executed immediately. Do not pre-allocate extents during direct loading.

If the object is a table, you can try

ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE;

Whether an error is reported. If an error is reported, it means that the index of the table or table has bad blocks. If no error is reported, continue to try the following SQL statement to get the corresponding information:

Block-level space information, a high chain out, may also be part of the problem.

SELECT * FROM sys.dba dba_tables WHERE table_name = '<TABLENAME>';

If you have many update and delete operations, an unsuitable index may also cause problems. The following SQL statement can help you get the relevant index information:

SELECT i.* FROM sys.index_stats i, sys.dba_indexes d WHERE i.name = d.index_name AND d.table_name = '<TABLENAME>';SELECT i.* FROM sys.index_stats i, sys.dba_indexes d WHERE i.name = d.index_name AND d.table_name = '<TABLENAME>';

For a view, you need to view the information of the table where the view is created:

SELECT text FROM sys.dba_views WHERE view_name = '<VIEWNAME>';