corrupted indexes

Pavan Kumar

Hi Hemanth,

It seems that the process id (PROCESS_INSTANCE) is obtained by another process (the Process Scheduler). Therefore, it is already committed in another transaction before it is passed to this program (which is run by the Process Scheduler). This is the "some_constant_value" that is inserted as the same value in every row at each run (but a new value for a run by another / next session). 

As per your above comments you said that process_id is input came from another process - I think there might some unique table which is accessed across the DB, for maintaining the Unique ness and it is commited on partially in your scripts (Other wise in the case of currency the records might fail).

It can so happen that while one session is still processing with PS_COMB_EXP_TAO4, another session comes along and issues the "DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE > 0". Obviously, there is no Row Lock (TX Enqueue) because the other session cannot see the rows in PS_COMB_EXP_TAO4 that have been inserted by the first (still processing) session. Therefore, the second session issue it's own DELETE statement followed by another INSERT (with a different PROCESS_INSTANCE) value.

As I stated above then, looking to previous commetns or above paragraph, when one process gets the "PROCESS_INSTANCE" from one process - for example 101 and still it is under process and parallely another session comes into picture, then it inturn gets the same "PROCESS_INSTANCE" as 101 - if my understanding is correct.
If it does not get "PROCESS_INSTANCE" as 101, else some thing greater or different - its based on some hashing alogirthm which written or handled or some business procedure - which returns the uniqure session value based on current user.

Since there are multiple transactions concurrently active on the table and each session issuing it's own "delete all rows" (because all rows will always be PROCESS_INSTANCE > 0), ... wouldn't the index grow as deleted entries are not reused ? (deleted entries would be reused if the index was a Unique Index). ?

I don't accept with above clause " Hemanth" - the deleted entreis are re-used - based on the immediate commits happens, by next executing transactions - perphaps the data segments are perfectly fitted in block level with out any row migrations. You should check that the records are splitting the index's in 9-1 ratio or 5-5 ratio.

Let us wait for Jonathan and Richard Comments on this and let go through thread.. in details..some things are missed up.

- Pavan Kumar N

Hemant K Chitale

PROCESS_INSTANCES are incremented and committed, no two sessions get the same PROCESS_INSTANCE value.


Deleted entries that have been committed are reused by the next session that starts after the commit. They are not reused by another session running concurrently with the first session. That is my assumption.

Jonathan Lewis

Hemant K Chitale wrote:

It can so happen that while one session is still processing with PS_COMB_EXP_TAO4, another session comes along and issues the "DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE > 0". Obviously, there is no Row Lock (TX Enqueue) because the other session cannot see the rows in PS_COMB_EXP_TAO4 that have been inserted by the first (still processing) session. Therefore, the second session issue it's own DELETE statement followed by another INSERT (with a different PROCESS_INSTANCE) value.

Scenario 1:_
Assume a session has inserted data for process_instance = 99 and committed.

The next session gets process_instance = 100, issues delete that deletes the rows for process_instance = 99, and starts to insert rows (no commit yet).

The next session gets process_instance = 101, issues a delete that TRIES to delete the rows for process_instance = 99 because read-consistency tells it that those rows still exists. It gets stuck waiting on a TX enqueue until the session using process_instance = 100 commits.

As the session using process_instance 100 commits, the session using process_instance 101 will become free, restart the delete (because it will see the data has changed), then start the insert. It's delete will delete the data for process_instance 100 - but not be able to re-use that bit of space in the index or table, as it has not yet committed, but it will be able to use the space previously filled by the entires for process_instance 99 - moving the free index block from the left hand side of the index to the right hand side.

Consequences of Scenario 1:
Concurrent sessions will serialise on the delete.
The index cannot (in these circumstances) grow unreasonably large - unless you have found a(nother) bug with ASSM


Scenario 2:_

Sessions acquire process IDs 102, 103, 104, 105, 106 and are all queuing up behind session 100 waiting for it to commit.

Assume that the insert statement from the session using process_instance 100 doesn't find any data to insert.

When session 100 commits, session 102 is freed to run. I'll have to check this, but since it has to rollback its attempted delete, it's possible that it frees up all the other waiting sessions at the same time - and since there is no data to delete to introduce another TX enqueue chain, all the sessions will insert their data simultaneously - making the index grow large enough to hold all five sets of data.

Consequences of Scenario 2:
It may take just a few intermittent busy periods when a number of sessions run the job, and occasionally a couple of them don't have any data to insert, to allow your index to grow arbitrarily large. Once the index has grown it will not shrink unless you coalesce (or rebuild) it.

Oracle doesn't remove blocks from the left hand side of the index, and since you are always delete with process_instance > 0, you will always have to walk through the entire range of empty blocks from previous deletes before you get to the data that really exists. (If you hinted the delete statement - or gather stats on the index when it had some data in it then Oracle could use a tablescan to delete the data, and this might be quicker - it wouldn't stop the index growing, of course, but it would mitigate the problem caused by the empty space.

Regards
Jonathan Lewis


"Science is more than a body of knowledge; it is a way of thinking".
Carl Sagan

Pavan Kumar

Hi Jonathan,

From last two days, I was waiting for your reply, finally what I expected and though of is expected in Results of Scenario 1, that pretty's nice and know the "Consequences" of Scenario. I hope Hemanth is looking for that scenario too.

Coming to Scenario 2, I am just thinking off, what you pointed of "Consequences". Let us wait what "Hemanth" replies back on that... and still waiting for Richard comments too.!!

Inturn you have elaborated the scenarios very nice Sir.. !! ;-)

- Pavan Kumar N

Hemant K Chitale

Scenario 2

Jonathan Lewis

Hemant K Chitale wrote:
Scenario 2

Hemant,

Does this mean that your think I've probably managed to explain why your index is getting much larger than you were expecting it to be ? If so you may want to run a coalesce command against the index very frequently - perhaps as often as every hour or two (start cautiously, and then reduce the time slowly if the coalesce doesn't seem to cause any undesirable side effects).


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}

{noformat} (lowercase, curly brackets, no spaces) so that the text appears in 

fixed format

.

"Science is more than a body of knowledge; it is a way of thinking" 
Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Santosh Kumar

(start cautiously, and then reduce the time slowly if the coalesce doesn't seem to cause any undesirable side effects).

Sir,

What may be the side effect?
Are you referring to the resource utilization during coalesce process?
Is shrink an option?
What are the trade-offs of using coalesce and shrink?

Regards,
S.K.

Edited by: Santosh Kumar on Sep 26, 2009 11:47 AM(Added two more questions)

Jonathan Lewis

Santosh Kumar wrote:

(start cautiously, and then reduce the time slowly if the coalesce doesn't seem to cause any undesirable side effects).

Sir,

What may be the side effect?
Are you referring to the resource utilization during coalesce process?

That's one thing to consider (reads, writes, and redo generation in particular) - the other main contender is the side effect of trying to do the coalesce and having other processes inserting data at the same time. The potential for buffer busy waits and latch contention climbs. If there's a recognisable pattern of usage, it may be best to do this just a couple of times each day shortly after the table has been busy and has just become less busy.

Is shrink an option?

Could be

What are the trade-offs of using coalesce and shrink?

I haven't investigated the shrink closely enough to make a sensible comment on the trade-offs.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}

{noformat} (lowercase, curly brackets, no spaces) so that the text appears in 

fixed format

.

"Science is more than a body of knowledge; it is a way of thinking" 
Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Hemant K Chitale

The table would be busy during the day as well, outside of the batch cycle, running (smaller) user jobs.

I've scheduled a weekly rebuild.



UPDATE BY HEMANT K CHITALE (the same person !) : 27-Nov-09

I understand that Burleson has quoted this posting of mine, quite out of context at in his article on [Online Index Rebuilding.|http://www.dba-oracle.com/t_scheduling_oracle_index_rebuilding.htm]


NOTE : I do not recommend rebuilding of ALL indexes weekly. This was a specific table (and I have identified only three such likely tables in a database of thousands of tables) that may require : Preferably a COALECE (as adviced by Jonathan Lewis) -- if I can get the code changed !! -- or, as the second best option, a regular REBUILD.

The behaviour described in Oracle Bug#6447841 matches what I have seen. This is [clear later in this same thread|http://forums.oracle.com/forums/thread.jspa?messageID=3792540#3792540]


Here are two postings on my blog where I discuss Rebuilding Indexes -- in general, not relating to the behaviour described in Bug 6447841.

[1. Rebuilding Indexes|http://hemantoracledba.blogspot.com/2008/03/rebuilding-indexes.html]
[2. Rebuilding Indexes - When and Why ?|http://hemantoracledba.blogspot.com/2008/03/rebuild-indexes-when-and-why.html]


Hemant K Chitale
http://hemantoracledba.blogpsot.com

Edited by: Hemant K Chitale on Nov 27, 2009 4:12 PM

108476

Hi Hemant,

I've scheduled a weekly rebuild.

Just curious, please:

- Is your shop concerned about "justifying" index maintenance?

- Will you be measuring the before-and-after effects?

- Why a rebuild over a coalesce?

Please advise. Thanks!