Indexes gone bad

Ginola · Mar 4, 2018 5:00AM

Hi,

We have a couple of tables that are quite large (~200million rows), and each night we do delete and inserts of about 500k rows.

And after each insert we do rebuild index on all indexes on the table.

A lot of our batch jobs then run against these tables.

But for a couple of days now, the CBO has started doing a weird behaviour. On queries it used to do FTS, it now uses indexes and vice versa. Some of the indexes are obviously not that good to use since we are experiencing exceptional bad performance.

What can have happened? Could the table gone bad? And the indexes then look good for the CBO?

Thanks in advance!

AndrewSayer · Mar 4, 2018 5:25AM

2906705 wrote:Hi,We have a couple of tables that are quite large (~200million rows), and each night we do delete and inserts of about 500k rows.And after each insert we do rebuild index on all indexes on the table.A lot of our batch jobs then run against these tables.But for a couple of days now, the CBO has started doing a weird behaviour. On queries it used to do FTS, it now uses indexes and vice versa. Some of the indexes are obviously not that good to use since we are experiencing exceptional bad performance.What can have happened? Could the table gone bad? And the indexes then look good for the CBO? Thanks in advance!

Rebuilds on a schedule? Yuck! Why do you replace half a million rows each night?

When you build an index you gather stats in it, you do not gather stats on the table at the same time, it’s possible your index has stats that are much bigger in respect to the table stats.

It would be helpful if you shared a simple query that you have seen change plan, include the full execution plan, index definition, old execution plan and the execution plan if you hint for it to use the index (Or vice versa).

Tables going bad? What do you specifically mean by that? If youre Deleting and then appending lots of data constantly then I’d expect the table to be very space inefficient, you’ll have room from all the deleted rows that doesn’t ever get reused. This would make a full scan cost a lot more than you’d expect, but you’d need to have stats gathered on the table (which could happen automatically), and it implies you are using insert append, which you didn’t mention.

Rather than work through a list of guesses and possible reasons, we should take the deterministic approach, this means we start with exactly what is going on (your plans and queries will tell us this) and take it from there.

Ginola · Mar 4, 2018 5:38AM

Hi Andrew!

Thanks for fast response!

I do not know the history why we do delete and insert instead of a merge. But I can look into that.

We gather stats on all tables in the schema everyday as well, so they should look OK compared to the indexes.

What I mean is that it could have gone space inefficient like you said. Is it someway we could fix that? Dropping the table and recreate it?

Here is the explain plan for "bad" plan:

Plan hash value: 3487480137

------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | 244K(100)| |

| 1 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 25 | | 5 (20)| 00:00:01 |

| 2 | BITMAP CONVERSION TO ROWIDS | | | | | | |

| 3 | BITMAP AND | | | | | | |

| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | | |

| 7 | SORT ORDER BY | | | | | | |

| 8 | INDEX RANGE SCAN | UQ_ACCOUNT | 5 | | | 2 (0)| 00:00:01 |

| 9 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 25 | | 5 (20)| 00:00:01 |

| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | |

| 11 | BITMAP AND | | | | | | |

| 14 | BITMAP CONVERSION FROM ROWIDS | | | | | | |

| 15 | SORT ORDER BY | | | | | | |

| 16 | INDEX RANGE SCAN | UQ_ACCOUNT | 5 | | | 2 (0)| 00:00:01 |

| 17 | HASH JOIN RIGHT OUTER | | 93634 | 28M| | 244K (1)| 00:49:00 |

| 18 | VIEW | X_SECURITY_ACCOUNTS_V | 434 | 12152 | | 368 (2)| 00:00:05 |

| 19 | HASH GROUP BY | | 434 | 7812 | | 368 (2)| 00:00:05 |

| 20 | VIEW | | 434 | 7812 | | 367 (2)| 00:00:05 |

| 21 | SORT UNIQUE | | 434 | 101K| | 367 (2)| 00:00:05 |

| 22 | UNION-ALL | | | | | | |

| 23 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | |

| 24 | CONCATENATION | | | | | | |

| 25 | INLIST ITERATOR | | | | | | |

| 26 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 69 | | 7 (0)| 00:00:01 |

| 27 | INDEX RANGE SCAN | UQ_ACCOUNT | 1 | | | 6 (0)| 00:00:01 |

| 28 | INLIST ITERATOR | | | | | | |

| 29 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 69 | | 7 (0)| 00:00:01 |

| 30 | INDEX RANGE SCAN | UQ_ACCOUNT | 1 | | | 6 (0)| 00:00:01 |

| 31 | NESTED LOOPS | | 36 | 4752 | | 34 (0)| 00:00:01 |

| 32 | CONNECT BY PUMP | | | | | | |

| 33 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 18 | 1368 | | 10 (0)| 00:00:01 |

| 34 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 18 | | | 2 (0)| 00:00:01 |

| 35 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | |

| 36 | CONCATENATION | | | | | | |

| 37 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 69 | | 4 (0)| 00:00:01 |

| 38 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 1 | | | 3 (0)| 00:00:01 |

| 39 | INLIST ITERATOR | | | | | | |

| 40 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 20 | 1380 | | 47 (0)| 00:00:01 |

| 41 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 20 | | | 39 (0)| 00:00:01 |

| 42 | NESTED LOOPS | | 374 | 49368 | | 261 (0)| 00:00:04 |

| 43 | CONNECT BY PUMP | | | | | | |

| 44 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 18 | 1368 | | 10 (0)| 00:00:01 |

| 45 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 18 | | | 2 (0)| 00:00:01 |

| 46 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 26 | | 4 (0)| 00:00:01 |

| 47 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 1 | | | 3 (0)| 00:00:01 |

| 48 | HASH JOIN RIGHT OUTER | | 52211 | 14M| | 244K (1)| 00:48:55 |

| 49 | VIEW | X_SECURITY_OBJECTS_V | 1976 | 55328 | | 3303 (1)| 00:00:40 |

| 50 | HASH GROUP BY | | 1976 | 35568 | | 3303 (1)| 00:00:40 |

| 51 | VIEW | | 1976 | 35568 | | 3302 (1)| 00:00:40 |

| 52 | SORT UNIQUE | | 1976 | 482K| | 3302 (1)| 00:00:40 |

| 53 | UNION-ALL | | | | | | |

| 54 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | |

| 55 | TABLE ACCESS FULL | ORGANIZATION | 1965 | 132K| | 551 (2)| 00:00:07 |

| 56 | HASH JOIN | | 5 | 675 | | 1099 (1)| 00:00:14 |

| 57 | CONNECT BY PUMP | | | | | | |

| 58 | TABLE ACCESS FULL | ORGANIZATION | 76990 | 5939K| | 547 (1)| 00:00:07 |

| 59 | FILTER | | | | | | |

| 60 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | |

| 61 | TABLE ACCESS FULL | ORGANIZATION | 4 | 272 | | 548 (1)| 00:00:07 |

| 62 | HASH JOIN | | 1 | 124 | | 1096 (1)| 00:00:14 |

| 63 | CONNECT BY PUMP | | | | | | |

| 64 | TABLE ACCESS FULL | ORGANIZATION | 76990 | 5112K| | 548 (1)| 00:00:07 |

| 65 | INLIST ITERATOR | | | | | | |

| 66 | TABLE ACCESS BY INDEX ROWID | ORGANIZATION | 1 | 37 | | 5 (0)| 00:00:01 |

| 67 | INDEX RANGE SCAN | UQ_ORGANIZATION_NATURAL | 1 | | | 4 (0)| 00:00:01 |

| 68 | HASH JOIN | | 52211 | 12M| | 241K (1)| 00:48:16 |

| 69 | TABLE ACCESS FULL | CURRENCY | 40 | 2560 | | 3 (0)| 00:00:01 |

| 70 | HASH JOIN | | 52211 | 9993K| 3072K| 241K (1)| 00:48:16 |

| 71 | TABLE ACCESS FULL | ACCOUNT | 45570 | 2536K| | 274 (1)| 00:00:04 |

| 72 | HASH JOIN | | 52211 | 7087K| | 240K (1)| 00:48:06 |

| 73 | TABLE ACCESS FULL | COMPANY | 112 | 1008 | | 3 (0)| 00:00:01 |

| 74 | HASH JOIN RIGHT OUTER | | 52211 | 6628K| | 240K (1)| 00:48:06 |

| 75 | VIEW | | 19 | 247 | | 18 (12)| 00:00:01 |

| 76 | FILTER | | | | | | |

| 77 | CONNECT BY WITH FILTERING | | | | | | |

| 78 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 69 | | 3 (0)| 00:00:01 |

| 79 | INDEX RANGE SCAN | UQ_ACCOUNT | 1 | | | 2 (0)| 00:00:01 |

| 80 | NESTED LOOPS | | 18 | 2376 | | 13 (0)| 00:00:01 |

| 81 | CONNECT BY PUMP | | | | | | |

| 82 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 18 | 1368 | | 10 (0)| 00:00:01 |

| 83 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 18 | | | 2 (0)| 00:00:01 |

| 84 | HASH JOIN RIGHT OUTER | | 52211 | 5965K| | 240K (1)| 00:48:06 |

| 85 | VIEW | | 2 | 26 | | 30 (7)| 00:00:01 |

| 86 | FILTER | | | | | | |

| 87 | CONNECT BY WITH FILTERING | | | | | | |

| 88 | TABLE ACCESS BY INDEX ROWID | ORGANIZATION | 1 | 69 | | 12 (0)| 00:00:01 |

| 89 | BITMAP CONVERSION TO ROWIDS | | | | | | |

| 90 | BITMAP AND | | | | | | |

| 92 | BITMAP OR | | | | | | |

| 98 | NESTED LOOPS | | 1 | 135 | | 15 (0)| 00:00:01 |

| 99 | CONNECT BY PUMP | | | | | | |

| 100 | TABLE ACCESS BY INDEX ROWID | ORGANIZATION | 1 | 79 | | 3 (0)| 00:00:01 |

| 101 | INDEX RANGE SCAN | IDX_ORGANIZATION_PARENT | 1 | | | 2 (0)| 00:00:01 |

| 102 | HASH JOIN | | 52211 | 5302K| | 240K (1)| 00:48:06 |

| 103 | TABLE ACCESS FULL | TIME | 9 | 171 | | 3 (0)| 00:00:01 |

| 104 | NESTED LOOPS | | 634K| 51M| | 240K (1)| 00:48:06 |

| 105 | NESTED LOOPS | | 634K| 51M| | 240K (1)| 00:48:06 |

| 106 | NESTED LOOPS | | 5756 | 185K| | 552 (1)| 00:00:07 |

| 107 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |

| 108 | TABLE ACCESS FULL | ORGANIZATION | 5756 | 185K| | 550 (1)| 00:00:07 |

| 109 | INDEX RANGE SCAN | IDX_FACT_PLAN_SUPP_K_COMP_ORG | 65 | | | 19 (0)| 00:00:01 |

| 110 | TABLE ACCESS BY INDEX ROWID | FACT_PLAN_SUPP | 110 | 5720 | | 82 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------------------------------

Here is the "old" better one:

Plan hash value: 631574348

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | 545K(100)| |

| 1 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 25 | | 5 (20)| 00:00:01 |

| 2 | BITMAP CONVERSION TO ROWIDS | | | | | | |

| 3 | BITMAP AND | | | | | | |

| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | | |

| 7 | SORT ORDER BY | | | | | | |

| 8 | INDEX RANGE SCAN | UQ_ACCOUNT | 5 | | | 2 (0)| 00:00:01 |

| 9 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 25 | | 5 (20)| 00:00:01 |

| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | |

| 11 | BITMAP AND | | | | | | |

| 14 | BITMAP CONVERSION FROM ROWIDS | | | | | | |

| 15 | SORT ORDER BY | | | | | | |

| 16 | INDEX RANGE SCAN | UQ_ACCOUNT | 5 | | | 2 (0)| 00:00:01 |

| 17 | HASH JOIN RIGHT OUTER | | 539K| 162M| | 545K (2)| 01:49:03 |

| 18 | VIEW | X_SECURITY_ACCOUNTS_V | 434 | 12152 | | 368 (2)| 00:00:05 |

| 19 | HASH GROUP BY | | 434 | 7812 | | 368 (2)| 00:00:05 |

| 20 | VIEW | | 434 | 7812 | | 367 (1)| 00:00:05 |

| 21 | SORT UNIQUE | | 434 | 101K| | 367 (1)| 00:00:05 |

| 22 | UNION-ALL | | | | | | |

| 23 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | |

| 24 | CONCATENATION | | | | | | |

| 25 | INLIST ITERATOR | | | | | | |

| 26 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 69 | | 7 (0)| 00:00:01 |

| 27 | INDEX RANGE SCAN | UQ_ACCOUNT | 1 | | | 6 (0)| 00:00:01 |

| 28 | INLIST ITERATOR | | | | | | |

| 29 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 69 | | 7 (0)| 00:00:01 |

| 30 | INDEX RANGE SCAN | UQ_ACCOUNT | 1 | | | 6 (0)| 00:00:01 |

| 31 | NESTED LOOPS | | 36 | 4752 | | 34 (0)| 00:00:01 |

| 32 | CONNECT BY PUMP | | | | | | |

| 33 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 18 | 1368 | | 10 (0)| 00:00:01 |

| 34 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 18 | | | 2 (0)| 00:00:01 |

| 35 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | |

| 36 | CONCATENATION | | | | | | |

| 37 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 69 | | 4 (0)| 00:00:01 |

| 38 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 1 | | | 3 (0)| 00:00:01 |

| 39 | INLIST ITERATOR | | | | | | |

| 40 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 20 | 1380 | | 47 (0)| 00:00:01 |

| 41 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 20 | | | 39 (0)| 00:00:01 |

| 42 | NESTED LOOPS | | 374 | 49368 | | 261 (0)| 00:00:04 |

| 43 | CONNECT BY PUMP | | | | | | |

| 44 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 18 | 1368 | | 10 (0)| 00:00:01 |

| 45 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 18 | | | 2 (0)| 00:00:01 |

| 46 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 26 | | 4 (0)| 00:00:01 |

| 47 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 1 | | | 3 (0)| 00:00:01 |

| 48 | HASH JOIN RIGHT OUTER | | 300K| 82M| | 544K (2)| 01:48:58 |

| 49 | VIEW | X_SECURITY_OBJECTS_V | 1920 | 53760 | | 3303 (1)| 00:00:40 |

| 50 | HASH GROUP BY | | 1920 | 34560 | | 3303 (1)| 00:00:40 |

| 51 | VIEW | | 1920 | 34560 | | 3302 (1)| 00:00:40 |

| 52 | SORT UNIQUE | | 1920 | 468K| | 3302 (1)| 00:00:40 |

| 53 | UNION-ALL | | | | | | |

| 54 | CONNECT BY WITH FILTERING (UNIQUE) | | | | | | |

| 55 | TABLE ACCESS FULL | ORGANIZATION | 1909 | 128K| | 551 (2)| 00:00:07 |

| 56 | HASH JOIN | | 5 | 675 | | 1099 (1)| 00:00:14 |

| 57 | CONNECT BY PUMP | | | | | | |

| 58 | TABLE ACCESS FULL | ORGANIZATION | 76836 | 5927K| | 547 (1)| 00:00:07 |

| 59 | FILTER | | | | | | |

| 60 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | |

| 61 | TABLE ACCESS FULL | ORGANIZATION | 4 | 272 | | 548 (1)| 00:00:07 |

| 62 | HASH JOIN | | 1 | 124 | | 1096 (1)| 00:00:14 |

| 63 | CONNECT BY PUMP | | | | | | |

| 64 | TABLE ACCESS FULL | ORGANIZATION | 76836 | 5102K| | 548 (1)| 00:00:07 |

| 65 | INLIST ITERATOR | | | | | | |

| 66 | TABLE ACCESS BY INDEX ROWID | ORGANIZATION | 1 | 37 | | 5 (0)| 00:00:01 |

| 67 | INDEX RANGE SCAN | UQ_ORGANIZATION_NATURAL | 1 | | | 4 (0)| 00:00:01 |

| 68 | HASH JOIN | | 300K| 74M| | 541K (2)| 01:48:18 |

| 69 | TABLE ACCESS FULL | CURRENCY | 40 | 2560 | | 3 (0)| 00:00:01 |

| 70 | HASH JOIN RIGHT OUTER | | 300K| 56M| | 541K (2)| 01:48:18 |

| 71 | VIEW | | 19 | 247 | | 18 (12)| 00:00:01 |

| 72 | FILTER | | | | | | |

| 73 | CONNECT BY WITH FILTERING | | | | | | |

| 74 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 69 | | 3 (0)| 00:00:01 |

| 75 | INDEX RANGE SCAN | UQ_ACCOUNT | 1 | | | 2 (0)| 00:00:01 |

| 76 | NESTED LOOPS | | 18 | 2376 | | 13 (0)| 00:00:01 |

| 77 | CONNECT BY PUMP | | | | | | |

| 78 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 18 | 1368 | | 10 (0)| 00:00:01 |

| 79 | INDEX RANGE SCAN | IDX_ACCOUNT_PARENT | 18 | | | 2 (0)| 00:00:01 |

| 80 | HASH JOIN | | 300K| 52M| 3072K| 541K (2)| 01:48:18 |

| 81 | TABLE ACCESS FULL | ACCOUNT | 45564 | 2536K| | 274 (1)| 00:00:04 |

| 82 | HASH JOIN | | 300K| 36M| 2576K| 539K (2)| 01:47:50 |

| 83 | TABLE ACCESS FULL | ORGANIZATION | 58577 | 1887K| | 550 (1)| 00:00:07 |

| 84 | HASH JOIN | | 6984K| 619M| | 503K (2)| 01:40:44 |

| 85 | TABLE ACCESS FULL | COMPANY | 112 | 1008 | | 3 (0)| 00:00:01 |

| 86 | HASH JOIN RIGHT OUTER | | 6984K| 559M| | 503K (2)| 01:40:44 |

| 87 | VIEW | | 2 | 26 | | 29 (7)| 00:00:01 |

| 88 | FILTER | | | | | | |

| 89 | CONNECT BY WITH FILTERING | | | | | | |

| 90 | TABLE ACCESS BY INDEX ROWID | ORGANIZATION | 1 | 69 | | 12 (0)| 00:00:01 |

| 91 | BITMAP CONVERSION TO ROWIDS | | | | | | |

| 92 | BITMAP AND | | | | | | |

| 94 | BITMAP OR | | | | | | |

| 100 | NESTED LOOPS | | 1 | 135 | | 15 (0)| 00:00:01 |

| 101 | CONNECT BY PUMP | | | | | | |

| 102 | TABLE ACCESS BY INDEX ROWID | ORGANIZATION | 1 | 79 | | 3 (0)| 00:00:01 |

| 103 | INDEX RANGE SCAN | IDX_ORGANIZATION_PARENT | 1 | | | 2 (0)| 00:00:01 |

| 104 | HASH JOIN | | 6984K| 472M| | 503K (2)| 01:40:43 |

| 105 | NESTED LOOPS | | 9 | 171 | | 5 (0)| 00:00:01 |

| 106 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |

| 107 | TABLE ACCESS FULL | TIME | 9 | 171 | | 3 (0)| 00:00:01 |

| 108 | TABLE ACCESS FULL | FACT_PLAN_SUPP | 84M| 4207M| | 503K (2)| 01:40:37 |

-----------------------------------------------------------------------------------------------------------------------------------

William Robertson · Mar 4, 2018 6:04AM

Is partitioning an option? If so, you might be able to define the partitions to match the batch (e.g. partition by business date) and then you can truncate and reload a single partition, or even use partition exchange.

Just to rule it out, are you loading the data via direct path (insert /*+ append */ or sqlldr with direct = true), or just a plain insert?

Ginola · Mar 4, 2018 6:18AM

Maybe it is. I need to investigate how the delete and insert look like in more detail. It sometimes depends on what parameters that are put in.

Plain insert. How does the "append" hint work?

AndrewSayer · Mar 4, 2018 6:39AM

2906705 wrote:Maybe it is. I need to investigate how the delete and insert look like in more detail. It sometimes depends on what parameters that are put in.Plain insert. How does the "append" hint work?

Append hint means that instead of looking for the free space within the blocks that the table already has allocated, allocate some new extents for this data. If you are gathering statistics every day and you are appending your inserts then your stats will know your table is huge when really there's not much data - indexes would be loved and full table scans would be hated. You wouldn't fix this by dropping and recreating the table (and if you can do that, it suggests your data exists elsewhere?), you can just do an alter table move.. , then rebuild ALL the indexes on the table as they would be unusable.

What is the result of

select * from dba_Tables where table_name = '<YOUR TABLE NAME>';

desc <your table name>

And in comparison to the datatype lengths, how large do you think a typical row would be (in bytes/chars)

We can use this to determine what size the table is in comparison to the data it is holding.

Do you have a simpler query we could look at, the plan you posted is pretty long and will have many variables that could be in play? It'll take some patience and caffeine to read through it otherwise (I'm not saying I am strictly opposed to that, but the former would be much easier) . Also, don't forget to share the plans using a fixed width font, in the advanced editor you can chose courier new for this.

Unknown · Mar 4, 2018 11:46AM

We have a couple of tables that are quite large (~200million rows), and each night we do delete and inserts of about 500k rows.

Deletes of WHAT rows? Inserts of WHAT rows? Are you replacing rows you just deleted?

SHOW US the queries/statements you are using to do the deletes and inserts.

And after each insert we do rebuild index on all indexes on the table.

Why? Oracle automatically updates the indexes for deletes and inserts.

Some of the indexes are obviously not that good to use since we are experiencing exceptional bad performance.

What is that statement based on?

It is hardly ever 'obvious' that an index is not good. Oracle wouldn't be using them if it didn't think they were good.

Don't guess. When you suspect a problem, performance or otherwise, follow the STANDARD troubleshooting steps:

1. identify a problem/issue

2. confirm the problem/issue really exists

3. determine the cause of the problem/issue

4. identify possible solutions that will eliminate/mitigate the problem/issue

5. select a small number (2 or 3) solutions for prototyping and testing

6. select the 'best' solution

Although you posted plans you haven't posted the actual queries.

It also appears you have bitmapped indexes on the tables.

John Thorton · Mar 4, 2018 12:03PM

which metric at what value indicates when index goes from Good to Bad?

How to measure the difference between a Good index & a Bad index?

What are specific symptom for when "table goes bad"?

Quantify how a bad table is different from good table.

Ginola · Mar 4, 2018 2:27PM

Deletes of WHAT rows? Inserts of WHAT rows? Are you replacing rows you just deleted?

We do a lot of calculations and aggregations every night, and we delete the data that are in scope for calculations and then insert. So yes, it is rows we just deleted.

SHOW US the queries/statements you are using to do the deletes and inserts.

Why is that important? The inserts are through dynamic sql, creating lots of different queries in one batch run.

And after each insert we do rebuild index on all indexes on the table.

Why? Oracle automatically updates the indexes for deletes and inserts.

It speeds up the deletion and insert, so we toggle them.

The queries that do the insert, are a lot of views on views. I know that the CBO have a higher risk of getting lost, but i'm not looking at redesigning the whole batchflow at this point.

This have run with same performance for many years.

It also appears you have bitmapped indexes on the tables.

Is this an issue? What do you mean?

Ginola · Mar 4, 2018 2:31PM

John Thorton wrote:which metric at what value indicates when index goes from Good to Bad?How to measure the difference between a Good index & a Bad index?What are specific symptom for when "table goes bad"?Quantify how a bad table is different from good table.

Hi John,

I'm discussing from a performance view. Our big 200 million table and index has not been an issue before. But now a query that before took 5-15 minutes to run, now takes 4 hours. And we have not done any changes to code.

All the changes are from delete/insert on that table.

What I mean from "bad" is that it does not give answer on our queries as fast as it did before.

John Thorton · Mar 4, 2018 2:38PM

2906705 wrote:John Thorton wrote:which metric at what value indicates when index goes from Good to Bad?How to measure the difference between a Good index & a Bad index?What are specific symptom for when "table goes bad"?Quantify how a bad table is different from good table.Hi John,I'm discussing from a performance view. Our big 200 million table and index has not been an issue before. But now a query that before took 5-15 minutes to run, now takes 4 hours. And we have not done any changes to code. All the changes are from delete/insert on that table.What I mean from "bad" is that it does not give answer on our queries as fast as it did before.

Does query use Bind Variable(s)?

How many rows deleted?

How many rows inserted?

Are statistics current & valid?

Forum Stats

Quick Links

Categories

Indexes gone bad

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Indexes gone bad

Answers