Home » RDBMS Server » Performance Tuning » Delete taking long due to index maintenance? (Oracle 11.2.0.3 / CentOS)
Delete taking long due to index maintenance? [message #626682] Thu, 30 October 2014 13:05 Go to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I seem to have a pretty straightforward delete that in this case deletes 14,843 rows from a table with 51,312,347 rows, which in this case takes about ~5 Minutes.

The select with the same where clause, including fetching all those rows, takes under a second.

Here is the trace of the delete:

DELETE FROM EKSTAT_ID
WHERE
 DATUM = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      5.20     255.66      46028         77     106634       14843
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      5.20     255.66      46028         77     106634       14843

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 47     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  EKSTAT_ID 
                                    (cr=140 pr=46028 pw=0 time=255656178 us)
     14843      14843      14843   INDEX RANGE SCAN IDX_EKSTAT_DAT 
                                    (cr=67 pr=92 pw=0 time=81738 us 
                                     cost=48 size=405990 card=10410)(object id 53919)


It seems the 0,081 seconds the index range scan takes is not the problem, but the 255 seconds the delete itself takes.

There are no foreign keys from or to the table. There are no triggers on the table.

The wait events during the delete show "db file sequential read" and "db file scattered read" events on database blocks that belong to the eight indexes on the table.

I now enabled monitoring of the indexes, in case there are ones that might be not needed and can be deleted, but I was wondering:

1) Is that much overhead "still normal" for (apparently) index maintenance?

2) In most of these indexes "DATUM" is also present in the index, but most of the time "at the end" of the index. Does this make the index maintenance so expensive, and would it be worth a try moving that field more to the start of the index?

[Updated on: Thu, 30 October 2014 13:06]

Report message to a moderator

Re: Delete taking long due to index maintenance? [message #626683 is a reply to message #626682] Thu, 30 October 2014 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65955
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does not seem too weird for me.
Indexes maintenance, given what you said, implies full index scan or index skip scan which is almost the same if the column is at the end of index column list.
The trace should tell you if this scan happens only once or for each row returned by IDX_EKSTAT_DAT.
Re: Delete taking long due to index maintenance? [message #626684 is a reply to message #626683] Thu, 30 October 2014 14:02 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thanks for the reality check, I'll try to optimize the index layout then.

[Updated on: Thu, 30 October 2014 14:02]

Report message to a moderator

Re: Delete taking long due to index maintenance? [message #626702 is a reply to message #626682] Fri, 31 October 2014 06:23 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
Are there a lot of indexes? What is about FK or triggers?
Re: Delete taking long due to index maintenance? [message #626704 is a reply to message #626702] Fri, 31 October 2014 06:29 Go to previous messageGo to next message
gazzag
Messages: 1037
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
There are no foreign keys from or to the table. There are no triggers on the table.
Re: Delete taking long due to index maintenance? [message #626705 is a reply to message #626702] Fri, 31 October 2014 06:29 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
Sorry, I didn't read carefully.
Re: Delete taking long due to index maintenance? [message #626706 is a reply to message #626702] Fri, 31 October 2014 06:34 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A little update:

After I found out that the Wait event was related to read-accesses I upped the SGA from 2G to 4G for the instance. That sped up the whole thing by about 60%

And so far three of the eight monitored indexes have not been used. (I also have a strong hunch that they were crated for a specific application that is no longer in use). I will keep monitoring for a week, and then drop them if they haven't been used in that time.
Re: Delete taking long due to index maintenance? [message #626710 is a reply to message #626706] Fri, 31 October 2014 08:01 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
can you exclude extremely sparse indexes?
Re: Delete taking long due to index maintenance? [message #626715 is a reply to message #626710] Fri, 31 October 2014 10:34 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the only documents I find about sparse indexes seem to be from the pre-Oracle 9 days.

A mentioned MOS Document 122008.1 was also replaced with a "this is no longer relevant" notice.

Seeing that 90% of the rows of that table were never touched since the import from the previous oracle version two years ago I don't see how indexes could have become really sparse, though.

Do you have any recent queries that would show the "sparseness" of indexes?
Re: Delete taking long due to index maintenance? [message #626731 is a reply to message #626715] Sat, 01 November 2014 04:55 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
The first script sparse_norm_idx9i.sql uses optimizer statistics. It is fast but can be not very precise. The second script estimate_sparse_norm_idx10g.sql estimates indexes more accurate but it can take a lot of time. Try firstly with the first one.
Re: Delete taking long due to index maintenance? [message #626732 is a reply to message #626731] Sat, 01 November 2014 04:56 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
the second upload ...
Re: Delete taking long due to index maintenance? [message #626784 is a reply to message #626682] Sun, 02 November 2014 21:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
My understanding of index maintenance on a delete goes like this: Maybe someone who knows better can correct me.

1. the database identifies those rows that need deleting.

2. then it locks and fetches the rows from the underlying table.

at this point the database has access to all columns on the row so that means it can do a simple index lookup on every index to find the index entries that need to be removed.

3. the database accesses each index doing a simple equals access on all columns in the index and modifies the blocks to remove the identified entries.  You can check what is being done by observing recursive SQL that is executed on your behalf to find and remove rows for the indexes.  I have never bothered to do this myself so I could be wrong here.  This is one of those basic assumptions I have just always made.  Maybe it is time for me to some real detective work for myself.  Maybe your should too.

4. it removes rows from the underlying table block.

The above is the simplified strategy given there are no foreign keys defined that point to the table from which you are deleting (which you said is true) and thus no need to worry about creating orphans.

In this scenario, any modern day Oracle database should not take 5 minutes to delete 15 thousand rows UNLESS THERE IS SOMETHING UNUSUAL GOING ON.

Here are my guesses.

1. hardware failures.  You are experiencing some form of hardware failure (network / disk / memory / etc.) that is causing lots of attempts.  You can fix this by having your people look at logs for the appropriate hardware.  Once you identify the bad piece of hardware, replace it.

2. locking.  You are waiting on locks.  Unbeknownst to you, someone is locking and later releasing one or more rows you want to delete.  To fix this figure out who is blocking you and time your deletes to avoid them.

3. bad index statistics.  These are misleading the optimizer into doing index fast full scans in order to locate rows to be deleted.  Try updating index statistics.

That all I can think of at the moment. Good luck. Kevin.

[Updated on: Sun, 02 November 2014 22:17]

Report message to a moderator

Re: Delete taking long due to index maintenance? [message #626817 is a reply to message #626784] Mon, 03 November 2014 07:21 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thanks for the additional feedback.

LNossov: Thanks. Those two scripts don't pick up any indexes in the DB. But was worth a try.

Kevin Meade: Thanks for the pointers. I might take a few days to check things out in that direction.
Re: Delete taking long due to index maintenance? [message #626818 is a reply to message #626817] Mon, 03 November 2014 08:10 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
I assume the same thing happens if you create a duplicate table with the same indexing structure?

Also, 46028 disk events, at let's say ~5ms each (reasonable for spinning ones) is about your wait time.

Are the rows you're deleting spread right most of the table blocks and you're revisting regularly, perhaps?


In any event, I'd mirror the table and experiment with the results of dropping each index.
Re: Delete taking long due to index maintenance? [message #626907 is a reply to message #626818] Wed, 05 November 2014 02:35 Go to previous message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
OK, the Problem seems to be solved, though I'm not 100% sure of the cause.

* deleted one of the indexed that was definitely no longer needed.
* rebuild the rest of the indexes (I know it should "normally not be needed" but I thought it worth a try)
* found an totally unrelated job on another DB that also used high I/O on the shared storage at the same time

Now the delete finished in under one minute max, and most of the time in ~10-20 seconds, so I consider this as solved and as fast as realistically possible.

Thanks again for all the tips and pointers.

Thomas
Previous Topic: Query in Loop causing performance issue
Next Topic: Plan missing from cursor cache
Goto Forum:
  


Current Time: Wed Nov 14 08:15:39 CST 2018