Batch update_dbms_stats?

[sspeery]

I manage an ArcSDE installation at a large US research university in which feature class data for a single SDE instance is distributed across several dozen Oracle schema owners, enabling some measure of decentralized responsibility for data management within each of my academic and administrative departments.  I would like to improve my workflow for the DBMS statistics updates ("Analyze" from ArcCatalog) that need to happen before and after I execute a compress on the database. 

I'm wondering if anyone here has had any luck running
         

sdetable -o update_dbms_stats

...in a batch operation for all feature classes in the table registry?  It is prohibitively time-consuming to run this command on the hundreds of feature classes that exist across all the departments' ArcSDE accounts - but if I understand the command reference, this command expects a '-t' argument specifying an individual table. 

I could imagine wrapping this command in a shell or python script that would open grab a list of all the feature classes known in the repository across all schemas in the Oracle instance (via SQL querying table_registry?  by parsing the output of one of the command-line utilities?), then iterate across that list, and run 'analyze' for each.

Is there something I'm missing here?  This seems to be an awfully involved procedure to accomplish something that the documentation suggests needs to happen weekly, before and after each compress.  I cannot rely on each individual data-owning department to right click each of their feature classes and select "Analyze" at a time that is convenient for the Enterprise GIS division, so I need some way to automate this and perform analyze operations  database-wide, just like I do for compress.  The complexity of my proposed solutions suggest to me that I am missing something obvious and simple, which I hope to be the case.  If the price to pay for improving transaction efficiency on ArcSDE is to temporarily become the object of ridicule on a forum for overlooking the obvious, sign me up.

Thanks in advance for your help!

___________________________________________________
Seth Peery
Senior GIS Architect, Enterprise GIS
Virginia Tech Geospatial Information Sciences
Virginia Polytechnic Institute and State University

[CharlesHarris]

Hi Seth-

It's Jeremy Hazel, we worked together fall semester in 2009.  I'm interested to know if you are using any replication at all or are you only maintaining a single database?  I'm having some issues with compressing a replicated database and thought maybe you could help.  I've posted a thread on this forum here http://forums.arcgis.com/threads/26048-SDE-compression-and-One-way-replication.

We are using the sdetable -o update_dbms_stats within a batch file here before and after our compression operation but are not updating near as many tables as you.  We are specifying the tables to update stats on individually.  Would it be worth it for you to use python to iterate through the table_registry, grab the name of each table and write it out to the -t option of the sdetable -o update_dbms_stats command in a txt file, appending a new command for each table and then run the resulting txt file as a batch process?  Just a thought...

[H__KorayGUNDUZ]

Run Script Toad or SQL Developer


/*************************************************
*Koray GUNDUZ (Ibb Cbs) - 2010
*Procedure analyze all index and tables
*************************************************/

SET SERVEROUTPUT ON

DECLARE

    CURSOR Owner_Cur IS
        SELECT DISTINCT(OWNER) owner
    FROM sde.table_registry
    ORDER BY owner;

    CURSOR Index_Cur IS
        SELECT owner, index_name
        FROM dba_indexes
        WHERE owner IN
             (SELECT DISTINCT(owner)
          FROM sde.table_registry)
        AND INDEX_TYPE = 'NORMAL'
        ORDER BY owner, index_name;

    SQL_STMT VARCHAR2(200);

BEGIN

    DBMS_OUTPUT.ENABLE (100000);

    FOR IndexRec IN Index_Cur LOOP
        SQL_STMT := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
     --   DBMS_OUTPUT.PUT_LINE(SQL_STMT);
        EXECUTE IMMEDIATE SQL_STMT;
     --   DBMS_OUTPUT.NEW_LINE;
    END LOOP;

    FOR OwnerRec IN Owner_Cur LOOP
        DBMS_OUTPUT.PUT_LINE('Analyzing schema : ' || OwnerRec.owner);
        DBMS_STATS.GATHER_SCHEMA_STATS(OwnerRec.owner);
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;

END;

/

[sspeery]

Thanks to all who responded.  After considering your replies and advice, I wrote the following bash shell script to perform the batch analyze operation.  I've tested it on my ArcSDE instance of ~1300 objects, and found that it works pretty well in my environment.  I'm releasing what I've created back to the community in hopes that someone else will find it useful.   I've tried to generalize the script as much as possible to facilitate reuse by allowing users to configure the necessary input parameters up front.  If this is the wrong place for this (ArcScripts, perhaps?) I can post it elsewhere.  Any comments and suggestions for improvement are welcome.

#!/bin/bash

##########################################################################
# Batch analyze script
# Query ArcSDE to get a list of all datasets in the database, then
# iterate through each owner.dataset to update DBMS statistics (ANALYZE).
# Output is logged to a file and optionally, a summary may be e-mailed to
# the ArcSDE administrator.
# Tested to work with ArcSDE/Oracle on RHEL 5.
# Author: Seth Peery, Virginia Polytechnic Institute and State University
# E-mail: sspeery@vt.edu
# Last Modified 2011-04-15
# Licensed under a Creative Commons Attribution 3.0 Unported License
#                       http://creativecommons.org/licenses/by/3.0/
##########################################################################

# PREREQUISITE:
# Set the following variables to match your environment
# N.B. be sure to chmod the referent of PASSWORDFILE to 600!
OUT_DIR=/path/to/script/output          # logfile destination
PASSWORDFILE=$OUT_DIR/sdepass.txt       # a single-line file with sde password
SERVER_NAME="myserver.example.com"      # hostname of SDE server
DBMS_INSTANCE="my_TNS_entry"            # TNS entry for SDE's Oracle Instance
SEND_EMAIL=true                         # set to true to send $SUMMARYFILE
ADMIN_EMAIL="foo@example.com"           # admin's e-mail address
LAYERLIST=/tmp/allsdelayers.txt         # no real need to change this

# Now, process the above to set password and output location variables.
PASSWORD=$(cat $PASSWORDFILE | head -n 1)
LOGFILE=$OUT_DIR/analyze_log_$(date +%F)
SUMMARYFILE=$OUT_DIR/analyze_summary_$(date +%F)

# Write the opening banner.  Note that the first line truncates
# any existing logfile with the same name.
# If you want this to operate silently,
# replace 'tee -a' with '>>' and 'tee' with '>'.
echo '********************************************' | tee  $SUMMARYFILE $LOGFILE
echo '******* ArcSDE Batch Analyze Utility *******' | tee -a $SUMMARYFILE $LOGFILE
echo '********************************************' | tee -a $SUMMARYFILE $LOGFILE
echo 'Analyze operation initiated ' $(date) | tee -a $SUMMARYFILE $LOGFILE
echo "Logging output to $LOGFILE" | tee -a $SUMMARYFILE

# Here's the SQL command we send to SQL*PLUS
# to get a list of all the objects in sde.table_registry
{
echo "set pagesize 0";
echo "set feedback off";
echo "select owner || '.' || table_name from table_registry order by owner, table_name;";
} | sqlplus -s sde/$PASSWORD@$DBMS_INSTANCE > $LAYERLIST;

echo 'Processing' $(cat $LAYERLIST | wc -l ) 'layers...' | tee -a $SUMMARYFILE
echo 'Any errors encountered during this process will be listed below.' | tee -a $SUMMARYFILE
echo '----------------------------------------------------------------' | tee -a $SUMMARYFILE


# Iterate through the layer list and run sdetable -o update_dbms_stats.
numLayers=0
numProcessedOK=0

for i in $(cat $LAYERLIST);
do
        # Attempt to run Analyze on each layer
        sdetable -o update_dbms_stats -u sde -p $PASSWORD -t $i &> /tmp/dbstats.out;
        # Test for errors by grepping for the string "Error"
        errorsPresent=$(grep Error /tmp/dbstats.out | wc -l)

        let numLayers=$numLayers+1
        if [ $errorsPresent -gt 0 ];
        then
                # Log the ArcSDE error messages (anything that says "Error")
                grep Error /tmp/dbstats.out | tee -a $LOGFILE $SUMMARYFILE
        else
                # Log the last line of ArcSDE dbms_stats command output
                # as our indication of success
                tail -n 1 /tmp/dbstats.out >> $LOGFILE
                let numProcessedOK=$numProcessedOK+1
        fi
done

echo '----------------------------------------------------------------' | tee -a -a $SUMMARYFILE
echo 'Done.'
echo $numProcessedOK layers out of $numLayers analyzed OK. | tee -a $SUMMARYFILE $LOGFILE
echo 'Analyze operation completed' $(date) | tee -a $SUMMARYFILE $LOGFILE

if [ "$SEND_EMAIL" == "true" ];
then
        SUBJECT="$SERVER_NAME Analyze log for $(date +%F)"
        EMAIL=$ADMIN_EMAIL
        EMAILMESSAGE=$SUMMARYFILE
        /bin/mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE
fi

___________________________________________________
Seth Peery
Senior GIS Architect, Enterprise GIS
Virginia Tech Geospatial Information Sciences
Virginia Polytechnic Institute and State University

[sspeery]

Jeremy,  I'm not currently using replication, but would be happy to discuss this with you further offline to keep the current thread on-topic.  Send me an e-mail directly if you're still interested and we can chat further.  You can find all my contact information at http://gis.vt.edu.

___________________________________________________
Seth Peery
Senior GIS Architect, Enterprise GIS
Virginia Tech Geospatial Information Sciences
Virginia Polytechnic Institute and State University

Hi Seth-

It's Jeremy Hazel, we worked together fall semester in 2009.  I'm interested to know if you are using any replication at all or are you only maintaining a single database?  I'm having some issues with compressing a replicated database and thought maybe you could help.  I've posted a thread on this forum here http://forums.arcgis.com/threads/26048-SDE-compression-and-One-way-replication.

We are using the sdetable -o update_dbms_stats within a batch file here before and after our compression operation but are not updating near as many tables as you.  We are specifying the tables to update stats on individually.  Would it be worth it for you to use python to iterate through the table_registry, grab the name of each table and write it out to the -t option of the sdetable -o update_dbms_stats command in a txt file, appending a new command for each table and then run the resulting txt file as a batch process?  Just a thought...