Checking indexes after large deletes
bozon
number of deletes have been done to a table? I searched here and didn't
see anything. Is there an oncheck command or sysmaster table to look
at? If so what values should I look for? I have to make a case for
rebuilding indexes and having hard data would make my case stronger.
Thanks.
Adam Tauno Williams
I don't know if it is what you are looking for but I use these three
queries to look at index effectiveness:
Index key uniqueness:
select tabname, idxname, nrows, nunique from systables t, sysindexes i
where t.tabid = i.tabid and t.tabid > 99 and nrows > 0 and nunique >0;
Index depth:
select idxname, levels from sysindexes order by 2 desc;
How many times the engine has resorted to a sequential scan:
select tabname, sum(seqscans) tot_scans from sysmaster:sysptprof where
seqscans > 0 and dbsname not like "sys%" group by 1;
Marco Greco
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Why rebuild the index after you have gone through the effort of marking all
those keys as deleted? drop the index first.
Caveat: if you are using the index to drive the delete, that might not be a
good idea...
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
bozon
this process is part of a job that removes customer data for customers
that no longer use our product. It is part of a monthly cleanup and it
happens while the instance is on line.
So is there a way to know if the cleaner is keeping up and
restructuring the indexes (by removing the keys that are marked deleted
and then reorganizing the nodes to rebalance the B-Tree?
Art S. Kagel
> Good idea to drop the index first. In general this is what I do, but
> this process is part of a job that removes customer data for customers
> that no longer use our product. It is part of a monthly cleanup and it
> happens while the instance is on line.
>
> So is there a way to know if the cleaner is keeping up and
> restructuring the indexes (by removing the keys that are marked deleted
> and then reorganizing the nodes to rebalance the B-Tree?
onstat -g btc
Will show you what index pages have been marked for cleaning. If you see a
preponderence of pages for a single partnum (index or index fragment) that
would be a candidate for a rebuild if you cannot wait for the Btree Cleaners
to catch up.
Art S. Kagel
Hamilton, Jerry
"Onstat -g btc" must be a V10 feature, I don't have it available on my
V9.4 instance.
bozon
10.0 FC4.
Adam Tauno Williams
> 10.0 FC4.
It doesn't do anything on my "10.00.UC4" box.
There is "btree cleaner info", with "onstat -C". Is that the same
thing?
Doug McAllister@Fidelity Investments
it onstat gives me the help listing. When I try it on my 9.40
instances, I get nothing at all.
TBP
onstat -C
Use the -C option to print the file information about the B-tree scanner
subsystem and each B-tree scanner thread. The following options are
available with the onstat -C command:
prof
Prints the profile information for the system and each B-tree
scanner thread
hot
Prints the hot list index key in the order to be cleaned
part
Prints all partitions with index statistics
clean
Prints information about all the partitions that were cleaned or
need to be cleaned.
range
Prints the savings in pages processes by using index range scanning
all
Prints all onstat -C options
Darren...@carmax.com
the b-tree scanner. Anyone recall who it was? Jerry, weren't you
interested in this too?
Art, was it you by chance?
peace
TBP
<TheBigPotato@Not
Here.Co.Uk> To
Sent by: inform...@iiug.org
informix-list-bou cc
nc...@iiug.org
Subject
Re: Checking indexes after large
06/07/2006 02:30 deletes
PM
onstat -C
Hamilton, Jerry
Yes I was interested in this too.
I think a blue person came on here and said, "If you put this line in
your onconfig, all your problems with the b-tree scanner will be
forgotten". So I did and I forgot all about my b-tree scanner. But I
still don't know what the heck it's doing.
BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000
-----Original Message-----
From: informix-l...@iiug.org
[mailto:informix-l...@iiug.org] On Behalf Of
Gentsch, Sam
I received an interesting email concerning BTSCANNERS
from a kind man at IBM Tech support....kind of a question and answer
document.
Would I be hung and quartered for sharing it with folks that ask me?
Also Neil has some very good information on the subject...on his web
site.
--
Sam Gentsch
Neil Truby
message news:1149703655.7...@u72g2000cwu.googlegroups.com...
> hmmm, btc is not a valid option in my 10.00.FC4 instance and when I try
> it onstat gives me the help listing. When I try it on my 9.40
> instances, I get nothing at all.
Nor on my 10.0FC5 one ...
[informix@orbistest01 ~]$ onstat -g btc
IBM Informix Dynamic Server Version 10.00.UC5 -- On-Line -- Up
04:56:48 -- 28048 Kbytes
usage: onstat [ -abcdfghklmpstuxzBCDFRX ] [ -i ] [ -r [<seconds>] ]
[ -o [<outfile>] ] [ <infile> ]
-a Print all info
-b Print buffers
-c Print configuration file
-d [update]
Print spaces and chunks
....
Hamilton, Jerry
Please share it.
John Miller
Here is what this paramater means:
num= the number of btree scanner threads to start when the engine starts up
priority= low | high
low means run the btree scanner below a normal user thread and only
give the btree scanner extra CPU cycles
high means run the btree scanner thread equal to that of a normal users
threshold=
This is the number of times a user must encounter an index item which
has been marked for deletion, but has not been removed. Examples If you
delete 20,000 rows the delete will generate a count of 20,000 for this
index. If you then run a select that scans the index encountering 1/2
of the index items you just deleted then this will count as 10,000 for
this index. So each index item encountered when select, inserts,
updates, delete encounter an items marked for deletion in an index but
still consuming space will count as 1 hit on this index. When this
index exceed this count the index will be compressed and rebalanced.
rangesize=
Prior to 10.00.UC5 there was only two methods for cleaning an index,
leaf scanning (i.e. walking all leaf pages) and range scanning (i.e
walking a range of pages physically adjacent). The later is much, much
faster. Range scanning does a light scan on the index to read the
pages, avoiding saturating the buffer pool with unwanted index pages.
For small indexes they are already in the buffer pool so we will want to
user leaf scanning. This paramater determines the size cut off for
using the two different scanning methods.
If more detail on this topic is required I would be happy to do a talk
on this at the Information on Demand Conference in October, or a Chat
with the lab talk.
let me know.
John Miller
Darren...@carmax.com
Don't forget Lester's call for presentation for the DC User's Forum! Chat
w/Lab sounds great too.
peace
John Miller
<jmiller@jfmiii.c
om> To
Sent by: inform...@iiug.org
informix-list-bou cc
nc...@iiug.org
Subject
Re: Checking indexes after large
06/09/2006 03:09 deletes
AM
Kernoal
This would be a great topic for a "Chat with the lab".
Thanks
Kernoal
Neil Truby
From: Gentsch, Sam [mailto:sgen...@intercall.com]
Sent: Wednesday, June 07, 2006 4:11 PM
To: Hamilton, Jerry; inform...@iiug.org
Subject: RE: Checking indexes after large deletes
>> I received an interesting email concerning BTSCANNERS from a kind man at
>> IBM Tech support....kind of a question and answer document ... Also Neil
>> has some very good information on the subject...on his web site.
http://www.informix-support.co.uk/btscanner.htm
Gentsch, Sam
Paul put it up for us.
Try www.oninit.com/btscanner.html and you should get a 'pretty' version
--
Sam
>-----Original Message-----
>From: Hamilton, Jerry [mailto:hami...@fleishman.com]
>Please share it.
Neil Truby
news:mailman.335.115081041...@iiug.org...
Paul put it up for us.
Try www.oninit.com/btscanner.html and you should get a 'pretty' version
http://www.informix-support.co.uk/btscanner.htm is better!