corrupted indexes

Richard Foote · Sep 12, 2009 2:27AM

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 · Sep 14, 2009 5:06AM

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 · Sep 14, 2009 6:17AM

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 · Sep 14, 2009 6:29AM

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 · Sep 14, 2009 8:11AM

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 · Sep 15, 2009 5:22AM

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 · Sep 15, 2009 8:00AM

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 · Sep 19, 2009 11:28AM

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 · Sep 22, 2009 3:47AM

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 · Sep 22, 2009 5:05AM

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

Forum Stats

Quick Links

Categories

corrupted indexes

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

corrupted indexes

Answers