I've got an application that has this statement:
CAN-FIND(FIRST tablename WHERE tablename.field1 = "something1" AND tablename.field2 = "something1" AND tablename.field3 <= 03/02/2016)
and it's generating > 100K index reads. CAN-FIND / LAST does a single index reads, and if I have the code USE-INDEX a different index with the same 3 leading fields, that results in a single index read as well.
My first thought was "index corruption" - until an index scan reported otherwise.
Is there a scenario where the index tree could wind up in a state such that 100K index reads would make sense?
> The problem index is primary, unique
I bet 100K+ records were purged in past from the table.
It's the index entry locks. Idxcompact will get rid of them.
Does CAN-FIND use an unique index? Index entry locks?
I would also be interested to know if both indexes are identical regarding ASCending/DESCending columns.
The problem index is primary, unique and the columns are all ascending.
For information sake the primary index is:
char field
char field
date
char field
char field
date
date
The other index which works as expected is not unique and all columns are ascending. Its structure is:
char
char
date
char
The first three fields of both indexes are identical.
> The problem index is primary, unique
I bet 100K+ records were purged in past from the table.
It's the index entry locks. Idxcompact will get rid of them.
That makes sense George. It would also explain why the LAST on the same index produces a different number of reads (different index block search order).
Running an idxanalys before/after the idxcompact would be nice to have.
I've sent the suggestion to the customer - I'll let you know how this turns out.
Here's the before/after the customer put together.
|
Before idxCompact |
After idxCompact |
||||||||||
|
Index Name |
Index Number |
Fields |
Level |
Blocks |
Size |
% Util |
Factor |
Blocks |
Size |
% Util |
Factor |
|
ix1 |
1513 |
4 |
3 |
3,440 |
7.0M |
52.4 |
2 |
2,219 |
6.9M |
79.8 |
1.4 |
|
ix2 |
1512 |
7 |
4 |
208,827 |
449.4M |
55.5 |
1.9 |
135,009 |
442.4M |
84.5 |
1.3 |
|
ix3 |
1514 |
3 |
3 |
49,513 |
100.6M |
52.4 |
2 |
32,430 |
100.0M |
79.5 |
1.4 |
|
ix4 |
1515 |
1 |
3 |
77,969 |
296.8M |
98.1 |
1 |
74,018 |
281.5M |
98 |
1 |
The problem index was "ix2" and the idxcompact seems to've solved the problem as there's now one index read for the CAN-FIND.
One additional question I got was if there any concerns to running idxcompact for a live production database w/OE replication running.
Running idxcompact on modern versions of OE is fine. If they are running an older version make sure to check the KB because there were some bugs in the past.
I have run weekly/monthly idxcompacts on very large (2TB+) databases in the past with no issues. YMMV based on the app but I usually look for indexes with 80% or lower Util with at least a thousand blocks, priority placed on indexes that are heavily used (based on _IndexStat info). Small indexes usually don't compact very well because of skew of data and the index structures.
There are a couple of things to watch out for though...
1) Don't try and compact an index to 100%. The default of 80% is almost always fine. Setting it higher can make the next X updates slower as it splits the index blocks down to a realistic level.
2) If you have a large and very fragmented index the compact can cause quite a bit of activity when it is running. Large is relative to your database and system size. Running the compacts on a regular basis usually prevents this but going from fragmented to "optimal" that first time can be intense.
Keith - thank you for your answer. The customer's on 11.3 so I expect that'll be ok.
To confirm - you run idxcompact on your database while its up and running in production?
> if there any concerns to running idxcompact for a live production database w/OE replication running.
If you need only to fix the issue with the index entry locks then you can run idxcompact with the smallest value of the index utilization parameter - namely 50. As a first step it will get rid of Index Delete Chain. It will not "de-utilize" the index blocks but will remove the obsolete index entry locks.
How many blocks were on Index Delete Chain of index #1512?
I can't speak for Keith but I can confirm that in my world running idxcompact online and in production is perfectly reasonable.
The only idxcompact bug I know of that would impact 11.3 is restricted to AIX and is really a ulimit issue.
Yes I run idxcompact online against production databases with either AI or AI + OE Replication running. Most of the time I run them during the off hours (nights/weekends) unless the indexes are small or it is something that needs to be fixed right now.
> If you have a large and very fragmented index the compact can cause quite a bit of activity when it is running.
And it will cause bi growth if there is a session with long open transaction. Idxcompact itself creates the short transactions.
Ixanalys has enough information to predict the volume of bi activity that idxcompact will generate.
I would suggest that if you have sessions with long running transactions you have a different issue to solve :-)
Re: long running tx - yep!
"Long" is a relative term. 30-60 mins used to be acceptable for the transactions in production environment. But not when the utilities like idxcompact are running!