Evaluating Options

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s