I should delete a table and insert into it frequently on oracle database. I find out that query from the table goes slowly. I guess deleting from a table in frequently manner is responsible. do you have any idea about that?
CodePudding user response：
Lots of possible causes here
Could be a poor execution plan because the database thinks you have "n" whereas you in reality have "m" rows now due to the DML activity
Could be inefficient full scans because the high water mark is higher than it needs to be
Could be delayed block cleanout, where your queries have to clean up "mess" left around by the DML
Could be just overall server load. Queries are fine in isolation, DML is fine in isolation, but the combination of the two is pushing the resources over the top.
Ideally a trace or AWR report on the system would be useful to track down what is happening