It is slow for Oracle to delete records by using primary key id

Problem description:

It takes 12 minutes for Oracle to delete 2000 records by using the primary key id.

Solution Process:

1. First, check the SQL Execution Plan. The execution plan is normal, and the cost is only 4. The primary key index is used.

2. View wait events,

Select * from v $ session_wait where sid = 507

The event displayed is db file sequential read, and there is no exception.

3. Check whether the statistics are normal

Select * from user_tables where table_name = '';

After detection, the statistics are normal.

4. Check the system IO.

5. If no cause is found, enable SQL tracking.

Alter session set events = '10046 trace name context forever, level 12 ';

Delete from t_table1 where id> = xxx

Alter session set events = '10046 trace name context off ';

SQL trace to get a trace file

Tkprof orcl_ora_3708.trc myoutput.txt

Cat myoutput.txt: The exception was found this time. Besides the delete statement, the file contains two select statements:

Select/* + all_rows */count (1)
From
"Xxx". "T_TABLE2" where "FRESHMANID" =: 1
 
Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.23 0.22 0 0 0 0
Fetch 2000 720.58 740.36 842 61038000 0 2000
-----------------------------------------------------------------------
Total 4001 720.82 740.59 842 61038000 0 2000

Select/* + all_rows */count (1)
From
"Xxx". "T_TABLE3" where "FRESHMANID" =: 1
 

Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.27 0.27 0 0 0 0
Fetch 2000 1.84 1.93 0 136000 0 2000
-----------------------------------------------------------------------
Total 4001 2.11 2.20 0 136000 0 2000

The two tables are queried for 2000 times each time. It can be determined that the delete operation is very slow due to this reason. Ask the related personnel at the site. What is the relationship between t_table2, t_table3, and t_table1, the onsite staff said that t_table2 and t_table3 had a foreign key.

The ID of the primary key that depends on t_table1. After query, no index is created on the Foreign keys of t_table2 and t_table3. Therefore, the index is created and then the delete statement is executed. This execution is fast and has been tracked by SQL, no query is found for t_table2 and t_table3.