Shulou(Shulou.com)06/01 Report--

I. failure phenomenon

A CASE was received at the weekend, and the fault description was that the business produced a large number of TX locks at that time. Because DBA did not know much about the principle of locks and blind KILL processes, it was unable to locate the problem as soon as possible. In order not to affect the business, restart the database, resulting in a large number of user complaints.

Fri Mar 13 15:05:17 2020

Opiodr aborting process unknown ospid (33292328) as a result of ORA-28

Fri Mar 13 15:05:59 2020

Opiodr aborting process unknown ospid (34668796) as a result of ORA-28

Fri Mar 13 15:06:11 2020

Opiodr aborting process unknown ospid (33554618) as a result of ORA-28

Fri Mar 13 15:06:23 2020

Opiodr aborting process unknown ospid (34471986) as a result of ORA-28

Fri Mar 13 15:06:45 2020

2. Analysis process 1. From the point of view of awrtop waiting events, a large amount of TX is generated.

2awr top sql

3 ASH analysis

From the awr point of view, the blocking should be caused by the insert statement, and the awr execution statement is relatively long.

While UPDATA and DELETE are in the first place in awr. There is no way to continue to look at some of the contents of ash. It is found that the following table is relatively high.

In awr, there are no statements related to this table

From the above analysis, we can see that there is an insert statement locked, so it is suspected that there is no index for the foreign key.

View the structure of the table.

As follows:

Caused by DELETE FROM bTT WHERE id_=:1, and there is no index on the task_id field

III. Conclusion

After adding the table index, the problem was solved, and there were no more enq locks.

IV. Summary

In the ORACLE database, when you define a foreign key constraint, ORACLE does not automatically create the corresponding index, but must manually create the index on the column related to the foreign key constraint.

Foreign key missing index impact

The lack of indexes on foreign key columns can cause three problems, limiting concurrency, affecting performance, and possibly causing deadlocks. So for most scenarios, we should try to consider creating indexes on foreign keys.

Affect performance. If the child table foreign key does not create an index, the child table will be scanned fully when the parent table queries the associated child table. Affects how tables are joined.

Affect concurrency. Whether you update the primary key of the parent table or delete a parent record, a table lock is added to the child table (no changes to the child table are allowed until this statement is complete). This unnecessarily locks more rows and affects concurrency

3. In special cases, deadlocks can also be caused.

There is no need for foreign keys to build an index, and the following conditions are met:

1. Rows in the parent table are not deleted.

two。 Intentionally or unintentionally, the unique / primary key field value of the parent table is not updated.

3. It is not joined from the parent table to the child table, or, more generally, the foreign key column does not support an important access path to the child table, and you do not use these foreign keys in the predicate to select data from the child table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.