Today I’ve been notified an issue about a delete is very slow. This is the statement
DELETE FROM WHERE SIEBELA.S_EVT_ACT_FSX PAR_ROW_ID =: B1;
Examining the execution plan, the delete is using a FTS but there is an index on PAR_ROW_ID and don’t use it. Search differents values used by the bind variable I observe that there are no values that avoid the use of the index.
Basically there are three options for solve this problem;
i) analyze statistics
ii) hint / force plan outline with outline
iii) generating profile
Let’s look at each in detail
i) It should be natural and automatic and default, statistics are updated (if enabled) to night by default it is; Weekday windows (Monday through Friday) are configured to be open (active) for 4 hours starting at 10:00 p.m. Weekend windows (Saturday and Sunday) begin at 6:00 a.m. and remain open for 20 hours.
ii) this option is the more usual, especially when statistics are inefficient. The problem here is that This application is a Siebel (third party application) and can not touch the code, so you would have to create a outline.
iii) This option is to use SQL TUNE ADVISOR and see if we’re lucky and it generate a profile good enough, however, this is perhaps the worst option, since the profile not guaranteed the plan. From documentation ” SQL Profile do not guarantee the same plan each time the statement is parsed.”
The first option was chosen. Analyze the table immediately fix the problem, executing a index access.
HTH – Antonio NAVARRO