Oracle is slow to delete records with primary key ID

Problem Description:

Oracle deletes 2000 records with primary key ID is slow and takes 12 minutes.

Resolution process:

1. First look at the SQL execution plan, the execution plan is normal, the cost is only 4, the primary key index is used.

2. Review the wait events,

SELECT * from v$session_wait where sid = 507

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

3. See if statistics are working

SELECT * FROM user_tables where table_name = ';

The statistical information is also normal after testing.

4. It is also normal to view the system IO.

5. Unable to find the cause, turn on SQL Trace

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 gets a trace file

Tkprof ORCL_ORA_3708.TRC Myoutput.txt

Cat Myoutput.txt, this time the discovery exception, the file in addition to have the DELETE statement, there are 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          ------------- ------------------------------------------------ ---------- 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 &nbsp fetch    2000      1.84      1.93           0    136000           0         ------------- -------------------------- ---------------------- ---------- total    4001      2.11       2.20          0    136000          0        2000

The two tables were queried 2000 times, can be judged out, is because of this cause the delete very slow, ask the field related personnel, T_table2, T_table3 and t_table1 have what relationship, the field personnel said T_table2 and T_table3 have a foreign key

Dependent on the primary key ID of T_table1, after querying, T_table2 and t_table3 the foreign keys are not created index, so create an index, then execute DELETE statement, this time execution is fast, after SQL Trace, also did not find to query T_table2 and T_ Table3.

See Oracle Feature page for more information on Oracle HTTP://WWW.LINUXIDC.COM/TOPICNEWS.ASPX?TID=12

This article permanently updates the link address: http://www.linuxidc.com/Linux/2014-04/100702.htm

Oracle is slow to delete records with primary key ID