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

sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote: > > Hi everybody. > Some very general questions on the above topics ( not necessarily > related ) > > > 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 ? The engine uses multiple tempspaces in round robin fashion creating each successive temp table or sort-work file in the next tempspace. This will tend to reduce conflicts between processes using tempspace. The tradeoff is in the behavior of the 7.[12]x ontape. This round robin allocation can cause the temptables used to hold modified pages during archiving to fill up one tempspace and the ontape to fail while there is plenty of room in the other tempspaces. Version 7.3 is to fix this by allocating these temp tables fragmented across all tempspaces and by emptying each temp table as its related dbspace has completed being archived (7.[12]x keeps all of the temp tables around until the archive is finished). So for query performance and reduced inter-query conflict more tempspaces are better while for archive purposes fewer larger tempspaces are needed (at least for now). > 2. How would I know if the size ( configuration ) of my temp dbspace > is causing a performance bottleneck ?? If many processes are creating temp tables, explicit or implicit, then contention can be a problem if few don't worry. If you have queries that need to sort large result sets then the sort can parallelize using a larger number of temp spaces. If all sorted result sets are small then sorting is taking place in memory anyway so... > 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. No Informix does not "rebuild" the index when a row is updated/deleted/inserted but it does maintain the index by updating the nodes possibly splitting nodes as needed. All Informix indexes are treated alike (except for user defined index types defined in Universal Server Datablades where you provide the index update methods). > 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" ? Indexes do not go "out-of-date". You only have to rebuild indexes under a few conditions: 1) Performance has begun to suffer during indexed reads due to index fragmentation or because index page locality (to related data pages) is no longer of benefit (clustering the table on the index can solve this also). Rebuilding can recreate the index with fewer leaves (using a high FILLFACTOR) and with fewer/larger extents (alter table <table> modify next size nnnn). 2) The index has hit the maximum allowable index extents (for detached indexes). Again increase the table's next extent size or detach the index into a dbspace in which it can be contiguous. 3) An index has become damaged during a system crash (should not happen on a logged database but common without logging) or some internal error or maybe gamma rays. > > ONCHECKS: > 1. How often should one oncheck ? Depends on how large you databases are. Ours are in the 100-150GB range with individual tables in the 10-50 million row range. Onchecking these tables is not practical. I do it when I suspect or know about a problem (ie from a log message/event). BTW on ODS NEVER let oncheck rebuild a damaged index on any but tiny tables oncheck does not use any parallel features when it builds indexes. Better to set up for PSORT and drop and rebuild the index in dbaccess. > 2. Is it recommended to do an oncheck -p BEFORE an oncheck -c ? Why ? I could not say. > 3. How do I speed up the performance of an oncheck ( specially the > table and index checks ? ) and what does the performance depend upon You cannot oncheck NEVER uses parallel features. The only thing you can do is to follow the advice above about not letting oncheck build indexes. Art S. Kagel