Re: Questions on INDEX(ES), TEMP DBSPACES and ONCHECK(S)

sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote: > TEMPDBS: > 1. what is the difference between having multiple dbspaces ( on > different disks ) and having multiple chunks ( on different disks ) on > one huge temp dbspace ?? Is there a performance issue ? Yes. Having different dbspaces gives the engine a chance to use them in a cyclical fashion, taking advantage of the different disks. If, on the other hand, you just have several chunks assigned to a single tempdbs, the engine will use always the first set of free pages available on the dbspaces; this could easily mean it will always use the first disk and NEVER even touch the rest. BTW, it's generally a good idea to have a one-to-one relationship between dbspaces and chunks, i.e. one and only one chunk per dbspace. It's easier to administer and helps in knowing which disk is holding what. > 2. How would I know if the size ( configuration ) of my temp dbspace > is causing a performance bottleneck ?? > If you're doing mostly DSS queries, you'll notice and increase activity in your tempdbs, and depending on the bandwidth of your disks you should try to keep them working, but not too hard. If you're doing OLTP transacions and your database is well designed in terms of indexes, then you should see little if any activity on your tempdbs. If you do see any activity, you probably need indexes :-) > INDEXES: > 1. Does informix re-build the index everytime a modification happens > to the table ? Or does this depend on the type of index ? I assume > that is does, though I have been answered to the contrary. > 2. What is the need to re-build an index on a table and how often > should we do it ? Is re-building required to organise the index > extents ?? What is meant by an index being "out of date" ? It doesn't rebuild in the sense the index won't be dropped and recreated. It just gets updated to reflect the addition, deletion or update of records. However, this update could mean rebalancing the b-tree; this can be a costly operation if the index is long and wide, therefore you should take care of leaving enough empty room on the index pages the first time you create the index (look at the FILLFACTO in the manual). It's also a good idea to re-create indexes every once in a while if the pages are not fully packed (for me, it's about 85%-90% per page). But most important, you MUST update your statistics every time you make inserts/updates/deletes that modify 10% or more of a given table. BTW, the UPDATE STATISTICS should be done according to Informix' suggestion (check the DSA Performance Guide); and UPDATE STATISTICS HIGH for the whole database, not only will take longer and take a larger amount of disk space, but will have too much information for simpler queries. > ONCHECKS: > 1. How often should one oncheck ? I do oncheck -cr monthly, oncheck -cc every other week and oncheck -cD and oncheck -cI before every level 0 archive. I occasionally do onchecks -cD or -cI when users complaing about a table having slow access. > 2. Is it recommended to do an oncheck -p BEFORE an oncheck -c ? Why ? I only use oncheck -p when doing extent analisys. If someone has a good explanation as to why use it for other purposes, please comment. > 3. How do I speed up the performance of an oncheck ( specially the > table and index checks ? ) and what does the performance depend upon Don't have users working and give all your CPU's to the engine :-) - Ernesto Hern'ndez-Novich - emhn@telcel.net.ve Just another Unix/Perl/Java hacker. One thing is to be the best, and another is to be the most popular. Unix: Live free or die!