Statistics On Time

Today I have been reported by development team a problem with a delete process, it was too slow. The delete for 200000 rows in a table that has  272 megs upto the HWM . The table has 597160 rows , is a very small table, but remove 200000 rows takes minutes.

This is the statement;

THOT*ANTO> explain plan for
2 DELETE FROM PROCESS_BILLING A WHERE A.ROWID = :B1
3 ;
————————————————————————————-
| 0  | DELETE STATEMENT  | | 1 | 45 | 1 (0)| 00:00:01 |
| 1  | DELETE                          | PROCESS_BILLING | | | | |
|* 2 | INDEX FULL SCAN      | PROBILL_PK | 1 | 45 | 1 (0)| 00:00:01 |
————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – filter(“A”.ROWID=CHARTOROWID(:B1))

 

Looking at the execution plan of the delete is using the PK index and access with INDEX FULL SCAN, that’s not right. A deleted using the rowid should go straight to the table and not go through any index.

Examining the statistics, the PK is LAST_ANALYZED 07/01/11. We chose as a first step to update the statistics and rebuild the index , since occasionally large deletions are performed. After rebuilding the index , statistics are updated at 15:52:53 19/MAY/2014 , runs again the slow process and it take few seconds now. Examining the execution plan it is accessed with TABLE ACCESS BY USER ROWID.

The execution plan accessed correctly;

PLAN_TABLE_OUTPUT
___________________________________________________________
Plan hash value: 761203433

———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0  | DELETE STATEMENT                        | | 1 | 45 | 1 (0)| 00:00:01 |
| 1  | DELETE | PROCESS_BILLING        | | | | |
|* 2 | TABLE ACCESS BY USER ROWID | PROCESS_BILLING | 1 | 45 | 1 (0)| 00:00:01 |
———————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
2 – access(CHARTOROWID(:B1))

 

Conclusion ,

It is always important to keep up to date statistics , of course, is not always easy or there are limitations that prevent us have updated statistics . Regarding indexes , at least the most modifieds, also should follow a policy of rebuild and always after big loads or deletions.

 

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