Re: Why is rootdbs busy?

Nice script, but I'm afraid that there are some problems. 1) This does not calculate the hash value correctly. The hashing algorithm includes the char value of the name, the position of the character, and the number of hash buckets (DD_HASHSIZE). 2) Unless the sysxxx tables in each database are queried, there is no reason to include them. 3) If the tables are altered or have update statistics done against them, then they are invalidated. This will result in two entries in the dictionary. The invalidated one will remain as long as there are any open statements (i.e. prepared statements/declared cursors) existing for that table, any new statements will be made against the new cache entry. In general, I think that the following algorithm would be a good starting point.... DD_HASHMAX = 10 DD_HASHSIZE = ((total_number_of_tables_to_keep_in_cache * 1.25) / DD_HASHMAX) If DD_HASHSIZE < 31 then set DD_HASHSIZE to 31. Why set DD_HASHMAX to 10??? Well, if the applications tend to use prepared statements and keep their declared cursors, then it does not matter what DD_HASHMAX is set to. The statements are already pointing to the dictionary information. They already have raised the reference count. The execution of the statement does not require searching for the dictionary information. But if the application does not use prepared statements and such, then it will have to search for the dictionary information, and in those cases the dictionary chain can become a contention point if it becomes too long, containing too many unreferenced tables. So 10 is a good starting point for DD_HASHMAX. 20 is probably the highest that it should be set. I wouldn't set it below 10 because the probability of thrashing due collision patterns becomes a bit too great. N.B. ==> This is not a fixed sized table. We do not allocate memory for DD_HASHMAX entries. This is a dynamic chain. As a general rule, it's best to keep DD_HASHSIZE as a prime (or near-prime) number. Why?? -- Well obviously we are going to have to be doing some dividing (mod) somewhere to convert the hash number into a bucket number. As with any hashing/mod algorithm, it is possible for collisions to develop. By keeping this to a near-prime number, then you can reduce the probability of collisions. By near prime, I mean a number that is not evenly divisible by the prime numbers below 14. (i.e. 2,3,5,7,11,13). Will this be perfect??? Absolutely not. You will still need to check onstat -g dic to see if adjustments are required after the system is running normally. Finally as a general rule, it's probably better to increase DD_HASHSIZE rather than DD_HASHMAX. By doing so you tend to reduce collision points and reduce the probability of long chains. For instance with SAP accounts, it is fairly common to set this to 501. Now for the $64,000.00 question..... (showing my age a bit)..... does it take up too much memory to keep the entire dictionary in core? How much does that cost me? Answer---- (drum roll and trumpets) It depends. ;-) (Audience responds with groans and wonders "Why can't he ever answer a question directly...") The memory required depends on 1) the number of columns in the table, 2) the number of indexes in the table, 3) the number of triggers on the table, 4) the number of constraints on the table, 5) the number of fragments of the table, ... However, with modern 64-bit versions, you get so much memory addressability, maybe it makes sense to simply plan on all tables being loaded into memory. However, on systems running on smaller memory platforms, maybe you don't want to do that. Maybe it makes more sense to accept the cost of rebuilding the dictionary cache when it's needed. Maybe you need your memory to be used elsewhere. Well --- enough of this discourse. I hope this helps and bid all a fond "adieu". (Takes bow and exits stage left.) ;-) Andy Lennard wrote: > In article <39AD63DE.8CD76D40@bloomberg.net>, "Art S. Kagel" > <kagel@bloomberg.net> writes > >I'm going to differ with Madison and recommend that you increase BOTH > >DD_HASHMAX to say 20 and DD_HASHSIZE to a larger prime number say 53. > >You could BTW estimate your working set of tables add in say 5 time the > >number of databases to hold system catalog tables and adjust the DD_ > >values so their product is slightly larger than that. This will prevent > >and thrashing of the DD cache and insure that there will always be an > >existing entry for every active table everytime it is referenced once the > >server reaches steady state. > > > >Art S. Kagel > > > <snip> > > In case it may be useful (?) here's a tacky script that I've used to > work out what my DD_HASHSIZE and DD_HASHMAX should be set to. > > It may save a bit of trial-and-error. > > #! /usr/bin/ksh > > # Attempt to work out 'optimum' values for DD_HASHSIZE and DD_HASHMAX > # > # DD_HASHSIZE sets the number of slots in the dictionary > # DD_HASHMAX sets the max number of table names allowed in each slot > # > # for efficient searching of the dictionary the max number of tablenames > # present in a given slot should be small > # > # > # A Lennard 20-sep-1999 > # > > if [[ $# -lt 1 ]]; then > echo Usage: $0 database [database...] > exit 1 > fi > > # make a list of all the databases we need to consider > # remember to include sysmaster too... > db_list=\\"sysmaster\\" > while [ $# -gt 0 ] > do > db_list=$db_list,\\"$1\\" > shift > done > > # all tables get put into the dictionary, not just user ones > dbaccess sysmaster - <<EOF 2>/dev/null > output to temp$$ without headings > select tabname > from systabnames > where dbsname in ($db_list)> EOF > > nawk ' > BEGIN { > > # make an array for numeric representation of ascii > for (i = 0; i < 128; i++) { > c = sprintf("%c", i); > char[c] = i; > } > > # initialise a counter for the number of tables in the database > n_tables = 0; > > } > { > # skip null lines > if (length( $1 ) == 0) { next; } > } > { > # on non-blank lines... > > # save the table name > tabname[n_tables] = $1; > > # evaluate the checksum of the characters of the table name > s=0; > for (i = 1; i <= length($1); i++) { > s += char[substr($1, i, 1)]; > } > sum[n_tables] = s; > > n_tables++; > } > > function isprime(i) { > int j; > for (j = 2; j< i/2; j++) { > if (i%j == 0) return 0; > } > return 1; > } > > END { > > printf("%d tablenames found\\n", n_tables); > > # look through the checksums to find the max checksum > max_sum = 0; > for (i = 0; i< n_tables; i++) { > if (sum[i] > max_sum) { > max_sum = sum[i]; > } > } > > # then use this max checksum as a top limit when going through finding > # out how many table names have an identical checksum > > # printf(" checksum tablename\\n -------- -------- > -\\n"