Sizing the dictionary cache. Does anyone bother?

Hi, I was wondering if anyone bothers to change the size and layout of the dictionary cache to improve performance. While looking through 'onstat -g dic' output over a period of time I noticed that the default dictionary allocation of 31 lists of 10 entries meant that some of the lists were not full, yet others were changing as different queries were run. The brief description of the dictionary cache in the Performance Guide leads me to believe that having the table catalog cached will improve performance. ( and presumably if it isn't cached it will have to be read from disk the next time it is referenced ). For a given table, the hash list number seems to be derived from the ascii summation of all the characters of the table name, which is then divided by the total number of lists, and the remainder gives the list number. It just so happens that my choice of table names has given rise to an imbalance in list lengths. I then set out to discover what was an 'optimal' layout for the dictionary to maintain all table catalogs in the cache with the shortest list length. For my development environment this came out as 53 lists of 50 elements each. (I have several databases with different names, but the same tables within them) I have now set DD_HASHSIZE and DD_HASHMAX in the onconfig, to test the effect. But, I have a niggling feeling that this may be 'wasted work'. Is this a useful exercise or is the database engine clever enough not to need this low-level tuning? Many thanks for any and all comments, Andy. ps. I'm running 7.30.UC3 on Solaris2.6. -- Andy Lennard andy@kontron.demon.co.uk

>Hi, > >I was wondering if anyone bothers to change the size and layout of the >dictionary cache to improve performance. This is a fairly common thing to do with SAP and BAAN. Also, if running Enterprise Replication, I would advise you to expand the defaults. > >While looking through 'onstat -g dic' output over a period of time I >noticed that the default dictionary allocation of 31 lists of 10 entries >meant that some of the lists were not full, yet others were changing >as different queries were run. > >The brief description of the dictionary cache in the Performance Guide >leads me to believe that having the table catalog cached will improve >performance. ( and presumably if it isn't cached it will have to be >read from disk the next time it is referenced ). > >For a given table, the hash list number seems to be derived from the >ascii summation of all the characters of the table name, which is >then divided by the total number of lists, and the remainder gives >the list number. > >It just so happens that my choice of table names has given rise to an >imbalance in list lengths. > >I then set out to discover what was an 'optimal' layout for the >dictionary to maintain all table catalogs in the cache with the >shortest list length. For my development environment this came out >as 53 lists of 50 elements each. (I have several databases with Madison Pruet

andy lennard wrote: > > Hi, > > I was wondering if anyone bothers to change the size and layout of the > dictionary cache to improve performance. I certainly do. I have engines with 1500 active tables, how else to force the engine to keep all of that data in memory? [SNIP detail] > I have now set DD_HASHSIZE and DD_HASHMAX in the onconfig, to test the > effect. > But, I have a niggling feeling that this may be 'wasted work'. Is this a > useful exercise or is the database engine clever enough not to need this > low-level tuning? Not wasted at all as long as all of those tables are actively queried/ updated. This definitely speeds processing. You may also want to look into expanding the data distribution cache as well. Art S. Kagel