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

In article <344CDF6D.83F@bloomberg.com>, "Art S. Kagel" <kagel@bloomberg.com> writes >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. I would run oncheck -c first to check there is no corruption.. > >> 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 You can run several oncheck -cID <database>.<table> in parallel each one checking a different table... :-> >do is to follow the advice above about not letting oncheck build >indexes. > >Art S. Kagel -- David Williams