Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

corrupted indexes

ski123Sep 9 2009 — edited Jun 16 2010
Oracle 9.2. We have a job running slow suddenly. Normal run time is 2 hours, now it is 6 hours. The data volume is the same. we are wondering if the indexes is corrupted. How to find corrupted indexes?
Thanks

S.

Comments

JustinCave
Unless there is a pile of information that allowed you to conclude that a slow process was the result of a corrupt index, I would strongly suspect that you don't have a corrupt index. Or if you do, that it is unrelated to your performance problem.

Let's start at the beginning-- why is the process slow?
- Do you have a trace of the session or a statspack report from a good run and from a slow run? What are the differences?
- What is the job doing?
- Did any query plans change?
- What is taking up most of the 6 hours the process is running?

Justin
Anurag Tibrewal
Hi,


Before doing this I would have always checked whether the explain plan it is taking is the best explain plain, and there are no waits for the session which has slowed down.

Then only I would have followed Oracle MetaLink note 122008.1 to rebuild the index
Regards
Anurag Tibrewal
ski123
Thank you very much. I am looking at stats report.
S.
164995
Run AWR and get the last AWR Report when it was good / bad.

Look at top 5 events, identify the sql's during good / bad times. If SQL's are executing at the same speed then problem lies somewhere else.

Such a drastic slowdown is probably external to the Database.

Was there any change environmental or otherwise. Any stats change ?
Richard Foote
Hi Anurag

Interestingly, I mention Metalink Note 122008 a number of times in my Index Seminar as a perfect example of how not+ to determine when to rebuild an index.

Thankfully, Oracle have pulled the error filled note from Metalink and with a bit of luck, it will never resurface again to confuse folk.

Cheers

Richard Foote
http://richardfoote.wordpress.com/
mbobak
Hi Richard,

Speaking of index rebuilds and MetaLink notes, have you seen note #182699.1?

I ask because you are quoted in that note....

-Mark
108476
Hi Richard,
I mention Metalink Note 122008 a number of times in my Index Seminar as a perfect example of how not to determine when to rebuild an index.
Why do you diss the Oracle documentation, and then fail to show the OP a method that does work?

That's not very helpful!

In many cases, evaluating an index for rebuilding is a waste of time.

It can take more reources to evaluate whether an index needs rebuilding than to actually rebuild it! Done properly, index rebuilding is 100% safe, and recommended by Oracle Corporation. . . .

http://www.dba-oracle.com/art_index1.htm

Oracle's index rebuilding guidelines appear in Metalink note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:

“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.

It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”

Thankfully, Oracle have pulled the error filled note from Metalink
Interestingly, Oracle DBA Jim Spath has shown that regular rebuilding of high DML indexes should be scheduled on a regular basis:

https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8750%3Fpage%3Dlast

“This index should be reorganized on a regular basis. . .

What is happening here is the index is becoming unbalanced through constant inserts and deletes.

Over time, the space the index occupies will continue to grow though there may be zero rows at the time the system starts and stops.

Each deleted row continues to occupy space in the index segment until a rebuild is done.”
unknown-698157
Sorry.
Oracle indices are B*-tree indices.
By design they can not become unbalanced. EVER!

--------------
Sybrand Bakker
Senior Oracle DBA
108476
By design they can not become unbalanced. EVER!
It depends on how you define "unbalanced"!

When Oracle does a logical delete of a leaf node, the "logical" structire of the b-tree is out of balance.
Richard Foote
Hi Sybrand

Suggesting that "Each deleted row continues to occupy space in the index segment until a rebuild is done” is of course not correct in most cases either as most deleted space is automatically reclaimed and reused by Oracle.

You can only lead a horse to water ... ;)

Cheers

Richard Foote
http://richardfoote.wordpress.com/
unknown-698157
The structure of the B*-tree is NEVER out of balance.
It is simply impossible!!!

-----------
Sybrand Bakker
Senior Oracle DBA
mbobak
burleson wrote:
By design they can not become unbalanced. EVER!
It depends on how you define "unbalanced"!

When Oracle does a logical delete of a leaf node, the "logical" structire of the b-tree is out of balance.
Don,

You're absolutely right that we should define what we mean by "balanced". In my view, "balanced" means that the number of "hops" from the root block to any leaf block is always the same. In other words, it's not possible to do an index look up on "Burleson" and go root->branch-leaf containing "Bujrleson" but look up "Foote" and go root->branch->branch->leaf containing "Foote".

Based on that definition of "balanced", Sybrand is correct.

If you have an alternate definition of "balanced", please clarify it.

Thanks,

-Mark
Richard Foote
Hi Mark

Yes, I was asked many years ago by Oracle Support to write a few words to try and clarify why most indexes don't actually need to be rebuilt as Oracle automatically manages things perfectly OK in most scenarios.

Probably a note that could go as well :)

Cheers

Richard Foote
http://richardfoote.wordpress.com/
mbobak
Heh...you know, I sort of had the same thought...."Hmm....nice that Richard wrote that note, he summarizes the issues very succinctly....problem is, I'm guessing people will skim right over them and get on with the script!" :-)

-Mark
108476
The structure of the B*-tree is NEVER out of balance.
Again, it depends on how you define the word "unbalanced"

Personally, I don't care for "word games"; it only serves to confuse . . .

Oracle indexes get unbalanced becaise Oracle uses data blocks as the boundaries for inodes, andf the index internals allow him to re-link blocks onto the freelist.

That's why you cannot specify PCTUSED on an index. . . .

Remember, just because a logically deleted index node MIGHT be re-used, it does not detract from the fact that the b-tree index is indeed unbalanced.

The unbalanced index nodes are called "index browning", like a tree hit by lightening, with brown branches:

http://asktom.oracle.com/pls/ask/f?p=4950:8:444551828551181757::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6601312252730,

"a high percentage of deleted leaf rows to leaf rows (think of leaf rows like leaves on a tree --
dead leafs (deleted row entries) are "brown". If you have lots of dead leaves, the tree looks
brown)"

From the book Oracle9i Performance Tuning: Optimizing Database Productivity, Sam Afyouni he recommnends "Monitor index browning (due to deletions; rebuild as necessary)"

http://www.dbazine.com/oracle/or-articles/jlewis13

"A "human" response to seeing this pattern in an index would, indeed, be to call it "unbalanced." Clearly, the right-hand side of the index is "heavier" than the left."

Again, it's all about how you define "unbalanced". . . .

I think that "word games" are not valuable and let's not parse word meanings here . . .

It's an absolute truth that some high DML Oracle indexes become fragmented and require periiodic rebuilding to "clean-up" the structure and keep high performance (especially for index FFS operations).
108476
Hi Mark,
In my view, "balanced" means that the number of "hops" from the root block to any leaf block is always the same.
Yes, I know, good point.

But as a DBA, I look at indexes from a physical viewpoint, as arrays of pointers residing on data blocks, and from that perspective they become unbalnced when data block are yanked from the tree.
If you have an alternate definition of "balanced", please clarify it.
Maybe we can agree that indexes become "physically" unbalanced, and never "logicaly" unbalanced.
unknown-698157
Burleson,

Can you please only use textbook definitions. In every IT text-book you will learn that B*-tree indices can NOT by design become unbalanced.
And all the rest you post on this is simply incorrect. Really sorry to say so, but you are misleading and misguiding people.

------------------
Sybrand Bakker
Senior Oracle DBA
mbobak
burleson wrote:
Hi Mark,
In my view, "balanced" means that the number of "hops" from the root block to any leaf block is always the same.
Yes, I know, good point.

But as a DBA, I look at indexes from a physical viewpoint, as arrays of pointers residing on data blocks, and from that perspective they become unbalnced when data block are yanked from the tree.
When data blocks are yanked from the tree?? How does this unbalance the index? Note, this seems to me to be a completely different issue than the "brown" leaf blocks, i.e. those with lots of deleted entries in a leaf block that's still part of the index structure. Once a leaf block is completely empty, and is "yanked from the tree" (presumably to be put back on the freelist), this is a good thing, in terms of index self-maintenance.


-Mark
Richard Foote
Hi Sybrand

100% agree with you.

Here's an excellent article by Jonathan Lewis, someone who really knows what they're talking about, that explains the concept of how Oracle indexes always remained "balanced":

http://www.dbazine.com/oracle/or-articles/jlewis13

Cheers

Richard Foote
http://richardfoote.wordpress.com/
108476
100% agree with you.
We are not talking opinions here, these are facts. . . .

Physically, Oracle indexes become unbalanced . . .

Tell me Richard, is the Oracle segment advisor wrong when it identifies unbalanced indexes for rebuilding?

http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-08-25.8847176467

"The 10G segment advisor identifies segments that have become fragmented as a result of update and delete operations. Oracle describes these objects as being sparsely populated. Not only do sparsely populated objects waste space but they can also contribute to SQL performance problems. "

Also, see right here, on OTN:

http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html
unknown-698157
Yes, these are facts... Quoting from the Jonathan Lewis article Richard posted.

When talking about Balanced B-tree indexes, the term "balanced" means top to bottom, not left to right.
Oracle really does implement a version of "Balanced B-tree indexes," so at any moment, all leaf blocks in an index are exactly the same distance from the root — a distance that can be found in the blevel column of the view user_indexes if the index has been recently analyzed, or as the height (which equals blevel + 1) in the view index_stats immediately after executing a validate index.
Resist the argument that you need to rebuild Indexes regularly because "they become unbalanced." It isn't a valid argument.

And these are the only facts. The rest is myth.

--------------
Sybrand Bakker
Senior Oracle DBA
108476
Hi Sybrand,
When talking about Balanced B-tree indexes, the term "balanced" means top to bottom, not left to right.
If I remember my data structures class from 30 years ago, b-trees follow preorder traversal, left to right:

http://www.cs.usask.ca/content/resources/csconcepts/1998_6/bintree/2-2.html

" pre order traversal prints the contents of a sorted tree, in pre order. In other words, the contents of the root node are printed first, followed by left subtree and finally the right subtree."
Oracle really does implement a version of "Balanced B-tree indexes,"
Yes, but with one critical exception - Oracle uses pbhysical data blocks as logical index nodes!
Resist the argument that you need to rebuild Indexes regularly because "they become unbalanced."
Wether you call it "fragmentatoin" or unbalanced", in many cases of high DML indexes (like SAP and Clintrial) index rebuilding needs to be done on a scheduled basis:

https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8750%3Fpage%3Dlast

"What is happening here is the index is becoming unbalanced through constant inserts and deletes.

Over time, the space the index occupies will continue to grow though there may be zero rows at the time the system starts and stops.

Each deleted row continues to occupy space in the index segment until a rebuild is done.”
It isn't a valid argument.
I beg to differ. I support a SAP database with a key tables and index with 100 freelists defined, with 400 users doing nothing but adding and deleting rows.

The performance gets so bad (for index FFS) that we have to rebuild them nightly!

That's an extreme example, and feel free to call it "rare", but it happens a lot in large OLTP systems with thousands of online users . . .
Richard Foote
burleson wrote:

Tell me Richard, is the Oracle segment advisor wrong when it identifies unbalanced indexes for rebuilding?

http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-08-25.8847176467

"The 10G segment advisor identifies segments that have become fragmented as a result of update and delete operations. Oracle describes these objects as being sparsely populated. Not only do sparsely populated objects waste space but they can also contribute to SQL performance problems. "

Also, see right here, on OTN:

http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html
No, I'm saying you're wrong. You, not the Oracle segment advisor.

The Oracle segment advisor does not identify "unbalanced" indexes. In both the links you've refereneced, the word unbalanced is not mentioned once. Not once.

Richard Foote
http://richardfoote.wordpress.com/
Richard Foote
burleson wrote:

I beg to differ. I support a SAP database with a key tables and index with 100 freelists defined, with 400 users doing nothing but adding and deleting rows.

The performance gets so bad (for index FFS) that we have to rebuild them nightly!

That's an extreme example, and feel free to call it "rare", but it happens a lot in large OLTP systems with thousands of online users . . .
I support a SAP database that has approximately 4,500 users.

Interestingly, we maintain our index structures in a manner such that we don't have to rebuild a single index.

Not one.

Richard Foote
http://richardfoote.wordpress.com/
Jonathan Lewis
Richard Foote wrote:

I support a SAP database that has approximately 4,500 users.

Interestingly, we maintain our index structures in a manner such that we don't have to rebuild a single index.
burleson wrote:I beg to differ. I support a SAP database with a key tables and index with 100 freelists defined, with 400 users doing nothing but adding and deleting rows.

The performance gets so bad (for index FFS) that we have to rebuild them nightly!

That's an extreme example, and feel free to call it "rare", but it happens a lot in large OLTP systems with thousands of online users . . .
That's very convenient - lots of people run SAP so there's no question of giving away secret information by supplying a table name, or index name, or a few numbers about an object in the SAP schema. I'm sure the owners of the systems would give permission for such non-identifying information to be published.

So could we ask Mr. Burleson to identify the index (table_name, index_name, index columns and order, freelist information, block size, etc.) and give us some numbers that show the index causes a performance problem unless rebuilt every night on his 400 user system.

Then Mr. Foote can show us the same index definition and statistics from his system and explain what he's done to the same index so that he doesn't have to rebuild it every night on his 4,500 user system.

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

"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan
Hemant K Chitale
Jonathan,

Yes, a comparison between the two SAP databases (supported by Don and Richard respectively) -- for that index specifically and indexes generally -- would be useful !

Index "size" (or explosion) can also depend on the nature of operations against the index and how the index was first set up. Your "Index Explosions" notes also indicate some cases when indexes may grow large.

[This Peoplesoft discussion thread|http://tech.groups.yahoo.com/group/psftdba/message/2533] also has found issues with some indexes -- I've also seen indexes grow extraordinarily large, impacting not only Index Scans but, possibly, even DML against the table.

[On asktom|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396006300346456969#1563326300346627527], (towards the end of that thread) too, I presented a similar issue. A rebuild or coalesce returns index size and performance of batch jobs to "normal".


However, I am not talking of indexes being "unbalanced". But, rather, that, due to some peculiarities, (e.g concurrent delete+insert without intervening commit, also with new key value for each batch of rows) some indexes do need a periodic rebuild or coalesce (to "shrink" them, not to "re-balance" them).

Don needs to identify why his index does need a daily rebuild. His performance gains probably come from the index shrinking at the beginning of the day (not from a perceived "re-balancing").



Hemant K Chitale
http://hemantoracledba.blogspot.com
Richard Foote
Hi Jonathan

Mr Foote thinks it's a great idea :)

I would love to know exactly why such an index needs to be rebuilt every day in SAP. Once I have the specific details (including this Fast Full Index Scan query), more than happy to compare differences.

Cheers

Richard Foote
http://richardfoote.wordpress.com/
108476
I'm saying you're wrong.
No, you are just playing "word games".

I have a real hard time believing that you don't understand the fundamental nature of Oracle indexes on the data blocks.

You do understand; you just want to argue about proper word usage.

FACT - After a large delete, Oracle will re-link an index data block back onto the freelists, where the block might get index entries from other places in the tree.

FACT - When massive deletes occur, Oracle DOES NOT re-balance the index nodes . . .
the word unbalanced is not mentioned once.
You can call it "bad", "exploded", unbalanced, fragmented, call it whatever you like . . .

As to the SAP issues, there are several OSS notes specifically related to rebuilding of Oracle indexes, especially when they are defined with multiple freelist groups.

https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8750%3Fpage%3Dlast

+"For index quality, we should talk about fragmentation, or balancing, which relate to how much extra space is in the index, leading to longer access times, as well as the side effect of pushing good data out of our caches such as the DB_BLOCK_BUFFER (DB_CACHE in recent Oracle versions)."+

Also, don't forget that SAPDBA has a utility specifically designed to identify "bad" Oracle indexes for rebuilding.

http://www.sap-basis-abap.com/bc/rebuilding-indexes-in-sap.htm

As to specific indexes, VBAP___0 comes to mind . . .
635471
burleson wrote:
You can call it "bad", "exploded", unbalanced, fragmented, call it whatever you like . . .
A 1:1 relationship between words and meanings in this technical context would be my own preference. Everyone knows that m:m relationships cause problems, surely.

I wouldn't be taking database terminology lessons from SAP.
Jonathan Lewis
Hemant K Chitale wrote:

Index "size" (or explosion) can also depend on the nature of operations against the index and how the index was first set up. Your *[Index Explosions|http://jonathanlewis.wordpress.com/2009/07/28/index-explosion/]* notes also indicate some cases when indexes may grow large.
Not so much "large" as "two or three times larger than expected" - the problem is bounded, not constantly increasing
[This Peoplesoft discussion thread|http://tech.groups.yahoo.com/group/psftdba/message/2533] also has found issues with some indexes -- I've also seen indexes grow extraordinarily large, impacting not only Index Scans but, possibly, even DML against the table.
I would treat that example with a little caution, and not one to quote as dependable evidence of a real problem, for two reasons:

<ul>
first - the originator makes a comment about "indexes not re-using space unless the exact value is re-inserted", thus demonstrating a basic misunderstanding of Oracle's implementation. Based on that, I have to assume that he may have failed to investigate the problem properly, and may not have understood any evidence he observed.

secondly - when David Kurtz (one of the world's specialists in trouble-shooting Peoplesoft) asked him if he has made sure the index was not a bitmap index, he gave no reply. The faiure to respond (and, indeed, David's feeling that it was a necessary question) make me wonder if the issue was something very basic that the originator had not realised was relevant.
</ul>
[On asktom|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396006300346456969#1563326300346627527], (towards the end of that thread) too, I presented a similar issue. A rebuild or coalesce returns index size and performance of batch jobs to "normal".
A couple of points about your Peoplesoft example:
<ul>
a) The table has 10 columns
b) Both indexes are built on the same 9 out of 10, and both start with the same four in the same order
c) The concurrent processes insert data with "process_id = {my_id}" but start by deleting data for all process_ids.
</ul>

This does not look like a mechanism designed by someone who understands Oracle, or indexing, let alone Oracle's indexing (or even concurrency).

I can think of a couple of reasons (including hypothetical bugs / edge cases) why the Peoplesoft code might result in index space getting lost - particularly space that should be put onto freelists but doesn't get there, or doesn't get re-used. I've already built a test case emulating this behaviour by applying an unsuitable (but not insanely stupid) design error.

If you care to extract the definitions of the objects using dbms_metadata.get_ddl() and post them here, I'll spend a little time seeing if I can make the problem appear with your object definitions.

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
Richard Foote
burleson wrote:

You do understand; you just want to argue about proper word usage.
Strangely, proper word usage is important. You don't say "small red car" for example when you're talking about big pink elephants ...

>
FACT - After a large delete, Oracle will re-link an index data block back onto the freelists, where the block might get index entries from other places in the tree.
Not sure what you're trying to say here. After a large delete, an index block is placed back on the freelist only if all it's index entries have been deleted. In which case, once a new block is required due to a subsequent block split, this empty index block gets logically unlinked from it's current location within the index structure and is recycled and relinked elsewhere. But this of course is a good thing as it means all the deleted entries are removed without the need for an index rebuild or coalesce.

>
FACT - When massive deletes occur, Oracle DOES NOT re-balance the index nodes . . .
Again, not sure what you're trying to say here as it's unclear what you mean by "re-balance" and by "index nodes". After a massive delete, all index leaf blocks are all the exact same distance from the root block (blevel) and the index remains perfectly "balanced" so indeed there is no need to "re-balance" the index after such a delete. Again, this is a good thing as it means the index structure is still efficient and as the deleted index entries may all get subsequently reused and cleaned out automatically by Oracle by subsequent inserts, may again mean there's no need for an index rebuild or coalesce.

>
the word unbalanced is not mentioned once.
You can call it "bad", "exploded", unbalanced, fragmented, call it whatever you like . . .
I like to call something by the correct terminology, not by whatever I like, as the above four terms have different meanings ...

>
As to the SAP issues, there are several OSS notes specifically related to rebuilding of Oracle indexes, especially when they are defined with multiple freelist groups.

https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8750%3Fpage%3Dlast

+"For index quality, we should talk about fragmentation, or balancing, which relate to how much extra space is in the index, leading to longer access times, as well as the side effect of pushing good data out of our caches such as the DB_BLOCK_BUFFER (DB_CACHE in recent Oracle versions)."+
LOL !! If you actually read the linked blog entry and see the comments below, you'll find that other SAP folk disagree with his opinions. You might even notice I get mentioned a number of times when explaining why he is wrong :)

>
Also, don't forget that SAPDBA has a utility specifically designed to identify "bad" Oracle indexes for rebuilding.

http://www.sap-basis-abap.com/bc/rebuilding-indexes-in-sap.htm
A number of years ago, I attended a SAP on Oracle Admin course in Sydney, run by SAP, which was just full of errors and misinformation about how Oracle works and should be maintained. I was asked by the instructor to talk to the class one afternoon and go quickly through all the incorrect material and explain why it was all wrong, which the class and instructor appreciated very much.

Note BTW, the author in the link above doesn't sound particularly confident on what he's talking about ("Don't think its quite the same as a proper drop index ..."). It dates to at least Oracle 8.1.7 and recommends stopping the database and running the rebuild in restricted mode ...

LOL, do you really want SAP to tell you how to administer an Oracle database in this way, really ?
As to specific indexes, VBAP___0 comes to mind . . .
So are you saying you rebuild indexes on the VBAP table each and every day ?


Richard Foote
http://richardfoote.wordpress.com/
Hemant K Chitale
Jonathan,

Table and Index DDL : 
SQL> select dbms_metadata.get_ddl('TABLE','PS_COMB_EXP_TAO4','SYSADM') from dual;

DBMS_METADATA.GET_DDL('TABLE','PS_COMB_EXP_TAO4','SYSADM')
--------------------------------------------------------------------------------

  CREATE TABLE "SYSADM"."PS_COMB_EXP_TAO4"
   (    "PROCESS_INSTANCE" NUMBER(10,0) NOT NULL ENABLE,
        "SETID" VARCHAR2(5) NOT NULL ENABLE,
        "TREE_EFFDT" DATE,
        "PROCESS_GROUP" VARCHAR2(10) NOT NULL ENABLE,
        "COMBINATION" VARCHAR2(10) NOT NULL ENABLE,
        "SEQUENCE_NBR_6" NUMBER(*,0) NOT NULL ENABLE,
        "CHARTFIELD" VARCHAR2(18) NOT NULL ENABLE,
        "RANGE_FROM_30" VARCHAR2(30) NOT NULL ENABLE,
        "EFFDT_FROM" DATE,
        "EFFDT_TO" DATE
   ) PCTFREE 10 PCTUSED 80 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "GLAPP"



SQL>  select dbms_metadata.get_ddl('INDEX','PSACOMB_EXP_TAO4','SYSADM') from dual;

DBMS_METADATA.GET_DDL('INDEX','PSACOMB_EXP_TAO4','SYSADM')
--------------------------------------------------------------------------------

  CREATE INDEX "SYSADM"."PSACOMB_EXP_TAO4" ON "SYSADM"."PS_COMB_EXP_TAO4" 
("PROCESS_INSTANCE", "SETID", "PROCESS_GROUP", "CHARTFIELD", "RANGE_FROM_30", 
"EFFDT_FROM", "EFFDT_TO", "COMBINATION", "SEQUENCE_NBR_6")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 40960 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSINDEX"



SQL>  select dbms_metadata.get_ddl('INDEX','PSBCOMB_EXP_TAO4','SYSADM') from dual;

DBMS_METADATA.GET_DDL('INDEX','PSBCOMB_EXP_TAO4','SYSADM')
--------------------------------------------------------------------------------

  CREATE INDEX "SYSADM"."PSBCOMB_EXP_TAO4" ON "SYSADM"."PS_COMB_EXP_TAO4" 
("PROCESS_INSTANCE", "SETID", "PROCESS_GROUP", "CHARTFIELD", "COMBINATION", 
"SEQUENCE_NBR_6", "RANGE_FROM_30", "EFFDT_FROM", "EFFDT_TO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 40960 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSINDEX"



SQL>
DELETE statement issued by different (at times concurrent) sessions : 
DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0;
(and we know that PROCESS_INSTANCE is never NULL, 0 or negative !)

The DELETE statement appears as the Top SQL by Elapsed_Time (5.56sec each), CPU_Time (5.43sec each), Buffer_Gets(327,234 gets each) and Physical_Reads (320,762 blocks each execute)


The INSERT statement is : 
INSERT INTO PS_COMB_EXP_TAO4 (PROCESS_INSTANCE, SETID, TREE_EFFDT, PROCESS_GROUP, COMBINATION, SEQUENCE_NBR_6, CHARTFIELD, RANGE_FROM_30, EFFDT_FROM, EFFDT_TO) SELECT DISTINCT some_constant_number .....
from multiple tables joined
Therefore, each INSERT inserts N rows (a few to a few ten's of thousands) with the same PROCESS_INSTANCE. However, every new INSERT statement, being issued by a separate "job" has a new PROCESS_INSTANCE.

The table is also referenced in many queries.
Unfortunately, it is the DELETE that is the biggest pain.
327thousand gets at every DELETE is a very high count.

Hemant K Chitale

BTW: This is supposedly from standard Peoplesoft code.

Edited by: Hemant K Chitale on Sep 14, 2009 5:13 PM
Jonathan Lewis
Hemant,

Your notes on asktom suggest that a process does the following:
DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0;
INSERT INTO PS_COMB_EXP_TAO4 ... SELECT DISTINCT some_constant_number .....
commit;
Your comments above don't sound like exactly the same description - they give the impression that there may be processes that delete but don't insert.

Questions:
Is a given value for +"some_constant_number"+ reused over time, or is it a value that never gets used for two inserts ?

If it is a constantly changing value, is it always increasing, or is it effectively random.

Within a given process_instance, how does the setid (second column in index) behave.

Which version of Oracle are you on and has the behaviour changed between versions. Upgrading from 9i to 10g, for example, could have caused the insert to change from an ordered insert (from a sort distinct) to a randomised insert (from a hash distinct).


Thoughts:
I can understand why the delete should use a lot of resources, but I don't understand why you aren't seeing lots of time lost waiting on TX locks. If my interpretation of your description on AskTom is correct: since each delete can see the committed data of all the other inserts, every session will be trying to delete the same data - which means each has to wait for the one ahead to complete - and when a session starts its delete it has to use a lot of undo to check what data it's supposed to see and whether it's supposed to delete it.


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


"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan
Hemant K Chitale
1. No, I wasn't waiting on TX locks. The DELETEs take much longer than the INSERTs. The time on the DELETEs s high Buffer Gets and CPU.

2. The sessions run DELETE and INSERT statements.

3. Each new INSERT batch is for a new PROCESS_INSTANCE. This is an Increasing number -- but Peoplesoft doesn't use a Sequence, only does a current_value+1 on a lookup table. The SETID can be different, but within a very small set (there would be about 4 to 8 different SETIDs in this database) at each run.

4. Release 10.2.0.2 with ASSM

Edited by: Hemant K Chitale on Sep 14, 2009 6:29 PM
Jonathan Lewis
Hemant K Chitale wrote:
1. No, I wasn't waiting on TX locks. The DELETEs take much longer than the INSERTs. The time on the DELETEs s high Buffer Gets and CPU.
The comments about timing don't surprise me given your description to date. The absence of TX lock, though, means you are only running one process at a time - or these processes are waiting somewhere else in the code.

2. The sessions run DELETE and INSERT statements.
So no other sessions run a delete, and if a session runs an insert it must first run a delete, and does the commit take place after the insert.

3. Each new INSERT batch is for a new PROCESS_INSTANCE. This is an Increasing number -- but Peoplesoft doesn't use a Sequence, only does a current_value+1 on a lookup table. The SETID can be different, but within a very small set (there would be about 4 to 8 different SETIDs in this database) at each run.
Presumably the code to get the next value does a select for update, update to value+1. Does this code run before of after the delete; if it runs before the delete does it commit, or is covered by the final commit. How many processes are likely to be active concurrently ?

"different setids in each run". So it's a very small number of setids in each run, and a very small number in total.
4. Release 10.2.0.2 with ASSM
Can you check whether the select statement is using a hash distinct or a sort distinct.


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


"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan
Hemant K Chitale
I currently have more than 1,500 "INSERT INTO PS_COMB_EXP_TAO4" statements in the Shared_Pool. As each one inserts a different PROCESS_INSTANCE.
There is only one "DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0" statement.

In the busiest databases, (perversely ?), there would only be 1 or 2 SETIDs.

I am not sure which SELECT you refer to in "Can you check whether the select statement is using a hash distinct or a sort distinct.".

(Note : gbyhash_aggregation_enabled is FALSE).
Santosh Kumar
This thread is becoming an advanced topic on index. :)

I hope this will continue, so that we will get a lot of information from you guys.

With great regards to all,
S.K.
Jonathan Lewis
Hemant K Chitale wrote:
I am not sure which SELECT you refer to in "Can you check whether the select statement is using a hash distinct or a sort distinct.".
You gave me the following "insert as select ...." - that's why I wanted to know about the "distinct" behaviour, as it might make a difference to the way the index was loaded.
INSERT INTO PS_COMB_EXP_TAO4 ... SELECT DISTINCT some_constant_number .....
But your comment here gives me the answer I need (probably).
(Note : gbyhash_aggregation_enabled is FALSE).
If you are still interested in trying to work out what's going on, you're going to have to figure out what happens as a process goes through the entire sequence of steps that ends in the final commit. From what I can tell so far a process will:

<ul>
select its "process id" from a table
update the table with the next value for the "process id".

-- is there a commit at this point ?
-- what is the actual select / update code used for the process id ?

delete all visible rows from the ps_comb_exp_ta04 table (where process_id > 0)
insert a number of rows into the ps_comb_exp_ta04 using the recently acquired process_id
commit;

</ul>

But there are some details missing from this set of steps, because as described there has to be at least one point where processes will queue on TX enqueues waiting for rows, and you say that this doesn't happen.

As a background question - is the number of executions of the delete statement the same (to within 1 or 2) as the number of sum(executions of all the copies of the insert statement) ?

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
Jonathan Lewis
Jonathan Lewis wrote:
Richard Foote wrote:

I support a SAP database that has approximately 4,500 users.

Interestingly, we maintain our index structures in a manner such that we don't have to rebuild a single index.
burleson wrote:I beg to differ. I support a SAP database with a key tables and index with 100 freelists defined, with 400 users doing nothing but adding and deleting rows.

The performance gets so bad (for index FFS) that we have to rebuild them nightly!

That's an extreme example, and feel free to call it "rare", but it happens a lot in large OLTP systems with thousands of online users . . .
That's very convenient - lots of people run SAP so there's no question of giving away secret information by supplying a table name, or index name, or a few numbers about an object in the SAP schema. I'm sure the owners of the systems would give permission for such non-identifying information to be published.

So could we ask Mr. Burleson to identify the index (table_name, index_name, index columns and order, freelist information, block size, etc.) and give us some numbers that show the index causes a performance problem unless rebuilt every night on his 400 user system.

Then Mr. Foote can show us the same index definition and statistics from his system and explain what he's done to the same index so that he doesn't have to rebuild it every night on his 4,500 user system.
We've waited 10 days now for information from Mr. Burleson about some tables and indexes with freelists 100, and why the indexes need to be rebuilt every night. To date all we have is this comment:

>
As to specific indexes, VBAP___0 comes to mind . . .
But we haven't seen any technical information to suggest that it is necessary to rebuild indexes on this table. In fact we haven't even been told anything about the index definitions, let alone performance characteristics. We don't even have a response to Richard Foote's question:

>
So are you saying you rebuild indexes on the VBAP table each and every day ?
I think we can only assume at the moment that Mr. Burleson is currently unable to provide any evidence in support of the need to rebuild indexes.

I would ask Mr. Foote to explain why he doesn't need to rebuild the indexes on VBAP___0 on his system - but that's a bit like asking someone to prove that BigFoot or Unicorns don't exist. He won't know what it is that he's supposed to show isn't+ happening (or isn't relevant) until Mr. Burleson shows what is+ happening that makes him want to rebuild the indexes.

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
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).

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.

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). ?

Hemant K Chitale
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!
1 - 50 Next
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 25 2009
Added on Sep 9 2009
#general-database-discussions
92 comments
23,075 views