system catalogues and extents

I know that it's recommended that the number of extents for a table be kept at 8 or less. However I notice that many of the system catalogue tables have 12 or more. Is there anything I should/could do about this? Sent via Deja.com http://www.deja.com/ Before you buy.

jblatz@worldnet.att.net wrote: > > I know that it's recommended that the number of extents for a table be > kept at 8 or less. However I notice that many of the system catalogue > tables have 12 or more. Is there anything I should/could do about this? First, the recommendation to have fewer than 8 extents was an OL5.xx problem. OL5.xx cached exactly 8 extent pointers per table so having more meant thrashing that cache. IDS NO LONGER HAS THIS PROBLEM as the table's entire extent list is part of the Data Dictionary Cache (see below)! Having said that, you still do not want to have TOO many extents as large queries will tend to jump around the drives and head movement is expensive. So, what constitutes "too many extents"? It depends on the size of the table, the size of each extent and the size and nature of a normal query. For a large table multiple extents are unavoidable since extents cannot span multiple chunks. If all extents are at least 100MB and the table is rarely deleted from and most queries are only interested in recent data then most queries will only access the one or two most recently allocated extents anyway. Similarly any query that performs a table scan will not be adversely affected by multiple extents since the head movement is always sequential. So the answer is it depends. As usual us DBAs have to know our data and our applications. Second, and more specific to the question, the system catalogs. This is another OL5.xx problem solved in IDS 7/8/9.xx. You need not be concerned about system catalogs acquiring too many extents because the entire system catalog entry for active tables are cached in the Data Dictionary Cache (onstat -g dic) which by default holds all of the system catalog info for up to 310 tables. If you have more than 310 active tables the undocumented tunables DD_HASHMAX and DD_HASHSIZE can be adjusted in the ONCONFIG file. DD_HASHSIZE is the number of hash buckets and must be a prime number (default 31 see the onstat -g dic output) and DD_HASHMAX (default 10) is the number of table entries in each bucket when tables hash to the same bucket. DD_HASHMAX is a small integer. I use either 127 & 20 giving 2540 tables or 521 & 20 giving 10420 tables in the cache. These parameters just allocate space for the hash table headers so a large table is not costly, memory for the dictionary entries themselves is allocated out of the Virtual segment as tables are added to the cache. Anyway there is nothing you can do about catalog extent growth. Art S. Kagel

In article <382043EE.19130C6D@bloomberg.net>, kagel@bloomberg.net wrote: > jblatz@worldnet.att.net wrote: > > > > I know that it's recommended that the number of extents for a table be > > kept at 8 or less. However I notice that many of the system catalogue > > tables have 12 or more. Is there anything I should/could do about this? > [snip] > Anyway there is nothing you can do about catalog extent growth. > > Art S. Kagel > Well.. Art, I have to disagree with you on this one. There definitely is somthing that can be done. The system catalogue tables can be altered to increase the extent size so that they don't get so fragmented. Actually this should be done on databases that have a large number of objects to be created, like all the major ERP apps, before any tables or other objects are created. There also is a problem caused by allowing your systems catalogue tables to extent many times. The system catalogues may span across every chunk in the dbspace that you created the database in. If you have a large database this could be very serious, because those chunks are solely dedicated to that dbspace and cannot be moved unless a full dbexport and dbimport are performed. You can empty a dbspace, but if a system catalogue table has an extent in it, you will not be able to drop it. You also cannot reorganize or move the system catalogue tables. It is a very good idea to increase the next extent size of your system catalogue table to prevent this from happening. Matur Suksema Sent via Deja.com http://www.deja.com/ Before you buy.

matur_suksema@my-deja.com wrote: > > In article <382043EE.19130C6D@bloomberg.net>, > kagel@bloomberg.net wrote: > > jblatz@worldnet.att.net wrote: > > > > > > I know that it's recommended that the number of extents for a table > be > > > kept at 8 or less. However I notice that many of the system > catalogue > > > tables have 12 or more. Is there anything I should/could do about > this? > > > [snip] > > Anyway there is nothing you can do about catalog extent growth. > > > > Art S. Kagel > > > Well.. Art, I have to disagree with you on this one. There definitely > is somthing that can be done. The system catalogue tables can be > altered to increase the extent size so that they don't get so Well, the next size anyway. That is correct. I'll accept the comment. Also you make a good point below about the extents spanning the chunks. This is not a performance issue, which is where my head was, but then the original post said nothing about performance, did it? Good catch Matur. > fragmented. Actually this should be done on databases that have a large > number of objects to be created, like all the major ERP apps, before any > tables or other objects are created. > > There also is a problem caused by allowing your systems catalogue tables > to extent many times. The system catalogues may span across every > chunk in the dbspace that you created the database in. If you have a > large database this could be very serious, because those chunks are > solely dedicated to that dbspace and cannot be moved unless a full > dbexport and dbimport are performed. You can empty a dbspace, but if a > system catalogue table has an extent in it, you will not be able to drop > it. You also cannot reorganize or move the system catalogue tables. It > is a very good idea to increase the next extent size of your system > catalogue table to prevent this from happening. > > Matur Suksema > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Well - thanks for the advice. When I called Informix Tech Support they told me that they do not 'support' any changes to the system catalogue tables because they are not real tables. I was told that the only way to correct the problem would be to export and import the database (no thanks). He did hint, however, that if I did decide to change the next size I had to be sure there were no users on the system. Have either of you actually done this? We're running Dynamic Server ver 7.24 UC3 Jane In article <3820ABD4.7670A758@bloomberg.net>, kagel@bloomberg.net wrote: > matur_suksema@my-deja.com wrote: > > > > In article <382043EE.19130C6D@bloomberg.net>, > > kagel@bloomberg.net wrote: > > > jblatz@worldnet.att.net wrote: > > > > > > > > I know that it's recommended that the number of extents for a table > > be > > > > kept at 8 or less. However I notice that many of the system > > catalogue > > > > tables have 12 or more. Is there anything I should/could do about > > this? > > > > > [snip] > > > Anyway there is nothing you can do about catalog extent growth. > > > > > > Art S. Kagel > > > > > Well.. Art, I have to disagree with you on this one. There definitely > > is somthing that can be done. The system catalogue tables can be > > altered to increase the extent size so that they don't get so > > Well, the next size anyway. That is correct. I'll accept the comment. > Also you make a good point below about the extents spanning the chunks. > This is not a performance issue, which is where my head was, but then the > original post said nothing about performance, did it? Good catch Matur. > > > fragmented. Actually this should be done on databases that have a large > > number of objects to be created, like all the major ERP apps, before any > > tables or other objects are created. > > > > There also is a problem caused by allowing your systems catalogue tables > > to extent many times. The system catalogues may span across every > > chunk in the dbspace that you created the database in. If you have a > > large database this could be very serious, because those chunks are > > solely dedicated to that dbspace and cannot be moved unless a full > > dbexport and dbimport are performed. You can empty a dbspace, but if a > > system catalogue table has an extent in it, you will not be able to drop > > it. You also cannot reorganize or move the system catalogue tables. It > > is a very good idea to increase the next extent size of your system > > catalogue table to prevent this from happening. > > > > Matur Suksema > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

jblatz@worldnet.att.net wrote: > > Well - thanks for the advice. When I called Informix Tech Support they > told me that they do not 'support' any changes to the system catalogue You talked to a first level engineer in November, guaranteed you got some recent college grad just out of two months of Informix school on his/ her third call (and still keeping track). The system catalogues are indeed "REAL" tables. It is the sysmaster database that is not REAL, except for the system catalog tables (tabid <100) of course! Anyway NOONE suggested mucking with the catalog tables. To change the next size of a catalogue table you just have to: ALTER TABLE systables NEXT SIZE 128; ALTER TABLE syscolumns NEXT SIZE 128; Simple, safe and it not only changes the nextsize column in systables but it also changes the entry in the table's tablespace tablespace entry which is echoed in the SMI pseudotable sysmaster:sysptnhdr where it is stored in pages rather than KB. Art S. Kagel > tables because they are not real tables. I was told that the only way > to correct the problem would be to export and import the database (no > thanks). He did hint, however, that if I did decide to change the next > size I had to be sure there were no users on the system. > > Have either of you actually done this? We're running Dynamic Server ver > 7.24 UC3 > > Jane > > In article <3820ABD4.7670A758@bloomberg.net>, > kagel@bloomberg.net wrote: > > matur_suksema@my-deja.com wrote: > > > > > > In article <382043EE.19130C6D@bloomberg.net>, > > > kagel@bloomberg.net wrote: > > > > jblatz@worldnet.att.net wrote: > > > > > > > > > > I know that it's recommended that the number of extents for a > table > > > be > > > > > kept at 8 or less. However I notice that many of the system > > > catalogue > > > > > tables have 12 or more. Is there anything I should/could do > about > > > this? > > > > > > > [snip] > > > > Anyway there is nothing you can do about catalog extent growth. > > > > > > > > Art S. Kagel > > > > > > > Well.. Art, I have to disagree with you on this one. There > definitely > > > is somthing that can be done. The system catalogue tables can be > > > altered to increase the extent size so that they don't get so > > > > Well, the next size anyway. That is correct. I'll accept the > comment. > > Also you make a good point below about the extents spanning the > chunks. > > This is not a performance issue, which is where my head was, but then > the > > original post said nothing about performance, did it? Good catch > Matur. > > > > > fragmented. Actually this should be done on databases that have a > large > > > number of objects to be created, like all the major ERP apps, before > any > > > tables or other objects are created. > > > > > > There also is a problem caused by allowing your systems catalogue > tables > > > to extent many times. The system catalogues may span across every > > > chunk in the dbspace that you created the database in. If you have > a > > > large database this could be very serious, because those chunks are > > > solely dedicated to that dbspace and cannot be moved unless a full > > > dbexport and dbimport are performed. You can empty a dbspace, but > if a > > > system catalogue table has an extent in it, you will not be able to > drop > > > it. You also cannot reorganize or move the system catalogue tables. > It > > > is a very good idea to increase the next extent size of your system > > > catalogue table to prevent this from happening. > > > > > > Matur Suksema > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.