Auto Update Statistics

Hi there, I'm currently evaluating upgrade procedure from version 10 to 11.50. First tests do not reveal any problems. So far so good. But: I upgraded a really large instance (~500 GB with ~600 databases with an overall more than 500000 database objects). I also have the latest OAT version installed and I am investigating the Auto Update Statistics feature. The aus_evaluator_dbs procedure is running now for 3 (!!) days and still more than 300 databases left. It's easy to track this in OAT that statistics evaluation for each database takes ~20 minutes. Questions: Is this normal? Is there a way to speed this up? Any docomentation out there with "best practises" for AUS? In addition: I just wanted to run a dbexport on one of the 600 db's and encountered a "Database is currently opened by another user." error. Nothing else than database 'sysadmin' can be found in database list of 'onstat -g sql'. I would guess this is due to running statistics evaluation. How can I check this ? Why is this particular databases locked while others are not? Thanks in advance for hints and tips. Hardware/Software: AIX 5 on 4 Core IBM Power 5 16 GB RAM EMC SAN storage (during statistics evaluation 50 % CPU load) Informix 11.50.FC6W3WE

Update: Every database is locked for which evaluation statistics is finished! I'm very confused about this AUS feature and how to handle it. Documention I have red so far is very poor IMHO and does not provide detailed information about the AUS background...

On a large complex installation of 11.50 I would: 1. Disable AUS 2. Disable the default sensors and alerts - if you want to gather operating statistics using the task scheduler create your own sensors that write their output somewhere other than the database server's storage. If you do that and write alert data ONLY to the ph_alert table you can use the alert mechanisms. The overhead of the default sensors on any non-trivial database is HUGE. 3. Use my dostats utility running multiple databases in parallel - You can use the new scheduler feature to separate the evaluation phase from a scheduled execution phase to run later. 4. Use AGS's Sentinel with Server Studio Enterprise to gather and report server operating statistics and handle alerts. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 31, 2010 at 5:51 AM, JOERG REDEMANN <joerg.redemann@sabre.com>wrote: > Hi there, > I'm currently evaluating upgrade procedure from version 10 to 11.50. First > tests do not reveal any problems. So far so good. > > But: I upgraded a really large instance (~500 GB with ~600 databases with > an > overall more than 500000 database objects). I also have the latest OAT > version > installed and I am investigating the Auto Update Statistics feature. > > The aus_evaluator_dbs procedure is running now for 3 (!!) days and still > more > than 300 databases left. It's easy to track this in OAT that statistics > evaluation for each database takes ~20 minutes. > > Questions: > Is this normal? > Is there a way to speed this up? > Any docomentation out there with "best practises" for AUS? > > In addition: > I just wanted to run a dbexport on one of the 600 db's and encountered a > "Database is currently opened by another user." error. Nothing else than > database 'sysadmin' can be found in database list of 'onstat -g sql'. > I would guess this is due to running statistics evaluation. How can I check > this > ? Why is this particular databases locked while others are not? > > Thanks in advance for hints and tips. > > Hardware/Software: > AIX 5 on 4 Core IBM Power 5 > 16 GB RAM > EMC SAN storage > (during statistics evaluation 50 % CPU load) > Informix 11.50.FC6W3WE > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502b1d64e9d920487e3134d

Art, Thanks a lot for your opinion! Regards

Hi, Just a little note on the "In addition:" section. First dbexport needs exclusive access to a database in order to export it. The database is not "locked" other then the Shared lock that is put on it when a session opens the DB. Second the onstat -g sql output only shows the home database for a session. The session may have opened many different databases on many different INFORMIXSERVERS. The best way to find out which sessions have a given DB open (that I have found) is to query the sysmaster:sysopendb where odb_dbname is the name you are interested in. You can also find the session by examining the locks output from onstat -k but you need to look up the rowid of the DB from sysmaster:sysdatabases and then trace the address back to the session. You appear to have already figured out it was the AUS thread that had the DB open. That will happen if you need exclusive access while AUS evaluator is running. George. From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 05/31/2010 07:34 AM Subject: Re: Auto Update Statistics [20265] Sent by: ids-bounces@iiug.org On a large complex installation of 11.50 I would: 1. Disable AUS 2. Disable the default sensors and alerts - if you want to gather operating statistics using the task scheduler create your own sensors that write their output somewhere other than the database server's storage. If you do that and write alert data ONLY to the ph_alert table you can use the alert mechanisms. The overhead of the default sensors on any non-trivial database is HUGE. 3. Use my dostats utility running multiple databases in parallel - You can use the new scheduler feature to separate the evaluation phase from a scheduled execution phase to run later. 4. Use AGS's Sentinel with Server Studio Enterprise to gather and report server operating statistics and handle alerts. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 31, 2010 at 5:51 AM, JOERG REDEMANN <joerg.redemann@sabre.com>wrote: > Hi there, > I'm currently evaluating upgrade procedure from version 10 to 11.50. First > tests do not reveal any problems. So far so good. > > But: I upgraded a really large instance (~500 GB with ~600 databases with > an > overall more than 500000 database objects). I also have the latest OAT > version > installed and I am investigating the Auto Update Statistics feature. > > The aus_evaluator_dbs procedure is running now for 3 (!!) days and still > more > than 300 databases left. It's easy to track this in OAT that statistics > evaluation for each database takes ~20 minutes. > > Questions: > Is this normal? > Is there a way to speed this up? > Any docomentation out there with "best practises" for AUS? > > In addition: > I just wanted to run a dbexport on one of the 600 db's and encountered a > "Database is currently opened by another user." error. Nothing else than > database 'sysadmin' can be found in database list of 'onstat -g sql'. > I would guess this is due to running statistics evaluation. How can I check > this > ? Why is this particular databases locked while others are not? > > Thanks in advance for hints and tips. > > Hardware/Software: > AIX 5 on 4 Core IBM Power 5 > 16 GB RAM > EMC SAN storage > (during statistics evaluation 50 % CPU load) > Informix 11.50.FC6W3WE > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502b1d64e9d920487e3134d ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.