As I said, I don't know this to be an AIX-only problem. If you are on a service pack earlier than SP03 you may be vulnerable to this bug so I strongly suggest you test this first on a newly-started copy of production and check your lock table HWM after the test.Thanks for the heads up though - we are on 11.6. But on Redhat Linux / VMware.
There are variations to the UPDATE STATISTICS command. You can optionally update table stats, index stats, or columns stats or all column stats. Did you do all of them?Thanks - it all worked w.o. problems. I just ran it on the tables in question. I don't think it has ever been run. Didn't see much if any of a performance gain though.
I believe the "whole shebang" is "UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS". Admittedly, the docs on this could do with some improvement. It says, "To get the best SQL query performance" and "a full set of SQL statistics", execute the statement you wrote. That makes it sound like you can't do any better. Then in the next paragraph it says "you get even better statistics by executing" the statement I wrote above. The difference is that without ALL, you're only getting data-distribution statistics on columns that are index components, not all columns in the table. That may or may not matter, depending on your queries.I did the whole shebang - UPDATE TABLE STATISTICS AND INDEX STATISTICS AND COLUMN STATISTICS on a couple of tables.
find dictdb._statbase no-lock. find last dictdb._file no-lock where _file._tbl-type = "T" use-index _file-number. find last dictdb._index no-lock where not _index._index-name begins "_" use-index _index-number. display "Current -basetable : " _statbase._tablebase skip "Current -baseindex : " _statbase._indexbase skip "Highest table number: " _file._file-number format "->>>>>>>>>9" skip "Highest index number: " _index._idx-num format "->>>>>>>>>9" skip "Min -tablerangesize : " _file._file-number - _statbase._tablebase + 1 format "->>>>>>>>>9" skip "Min -indexrangesize : " _index._idx-num - _statbase._indexbase + 1 format "->>>>>>>>>9" skip with no-labels.
That's good, though it begs the question, where are they now? On a NAS?We are in a VMware environment and the database is sharing IO with other databases and applications. The disks of this system are all currently using the same type of storage on the vm hosts. Moving the after image file system/disk to storage optimized for database log files is on our todo list.