IDS Forum

Re: Auto Update Statistics

Posted By: George_Palmer@aotx.uscourts.gov
Date: Tuesday, 1 June 2010, at 9:56 a.m.

In Response To: Re: Auto Update Statistics (Art Kagel)

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.

Messages In This Thread

• Auto Update Statistics
  JOERG REDEMANN -- Monday, 31 May 2010, at 5:51 a.m.
  • Re: Auto Update Statistics
    JOERG REDEMANN -- Monday, 31 May 2010, at 6:04 a.m.
  • Re: Auto Update Statistics
    Art Kagel -- Monday, 31 May 2010, at 8:34 a.m.
    • Re: Auto Update Statistics
      JOERG REDEMANN -- Monday, 31 May 2010, at 8:55 a.m.
    • Re: Auto Update Statistics
      George_Palmer@aotx.uscourts.gov -- Tuesday, 1 June 2010, at 9:56 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.