Updating statistics on geodatabase system tables using a Python script

Database statistics are used by the DBMS optimizer to choose the optimal execution plan for the query being executed. You should update statistics after the content of a table changes significantly. In the case of ArcSDE geodatabase system tables, you should update statistics after new tables or feature classes have been added to the geodatabase, a large number of versioned edits have been performed, or the geodatabase has been compressed.

Since updating database statistics can be an I/O-intensive operation, you might want to do this while most users are logged out of the database. You can write a Python script to call the Analyze Datasets tool to update statistics on the ArcSDE geodatabase system tables and schedule the script to run during the night. You can use Scheduled Tasks in Windows to set a time to run your script. On Linux, you can set up a cron job to run the script.

Steps:
  1. Copy one of the following scripts to a computer where Python and one of the following ArcGIS clients are installed:
    • ArcGIS for Desktop (Standard or Advanced)
    • ArcGIS Engine with the Geodatabase Update extension
    • ArcGIS Runtime
    • ArcGIS for Server (Standard or Advanced)

    Alter the scripts with information specific to your site.

    This script uses an existing database connection file on the local machine to connect to the database and run the script:

    # Name: UStatsSysTbls.py
    # Description: Updates statistics on enterprise geodatabase  
    # system tables using an existing .sde file.
    
    # Author: Esri
    
    # Import system modules
    import arcpy, os
    
    # set workspace
    workspace = arcpy.GetParameterAsText(0)
    
    # set the workspace environment
    arcpy.env.workspace = workspace
    
    # Execute analyze datasets for system tables
    arcpy.AnalyzeDatasets_management(workspace, "SYSTEM", "", "","","")
    print "Analyze Complete"
    

    This sample script contains the information necessary to connect to an Oracle database to update statistics on the system tables:

    # Name: UStatsSysOracle.py
    # Description: Updates statistics on system tables in an enterprise geodatabase in Oracle.
    
    # Author: Esri
    
    # Import system modules
    import sys
    import arcpy
    import os
    
    # Provide connection information
    server = servername
    service = "5151 | sde:oracle:sid"
    account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
    #Leave username and password blank if using OPERATING_SYSTEM_AUTH
    username = gdb_admin_user_name
    password = gdb_admin_password
    version = sde.DEFAULT
    
    
    # Set local variables
    if os.name.lower() == "nt":
       slashsyntax = "\\"
       if os.environ.get("TEMP") == None:
          temp = "c:\\temp"
       else:
          temp = os.environ.get("TEMP")
    else:
       slashsyntax = "/"
       if os.environ.get("TMP") == None:
          temp = "/usr/tmp"
       else:
          temp = os.environ.get("TMP")
    
    Connection_File_Name = temp + slashsyntax + "connection.sde"
    
    # Check for the .sde file and delete it if present
    if os.path.exists(Connection_File_Name):
       os.remove(Connection_File_Name)
    
    #Variable defined within the script; other variable options commented out at the end of the line
    saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME
    saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION
    
    print "Creating ArcSDE Connection File..."
    # Create ArcSDE Connection File
    # Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
    arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)
    
    # Update statistics on system tables
    arcpy.AnalyzeDatasets_management(Connection_File_Name, "SYSTEM","","","","")
    print "Analyze Complete"
    

    This sample script contains information to use an operating system authenticated dbo user to connect to SQL Server and update statistics on system tables:

    # Name: UStatsSysSqlServer.py
    ## Description: Updates statistics on system tables in an enterprise geodatabase in SQL Server.
    
    # Author: Esri
    
    # Import system modules
    import sys
    import arcpy
    import os
    
    # Provide connection information
    server = servername
    service = "5151 | sde:sqlserver:sqlserver_instance"
    database = database_name
    account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
    #Leave username and password blank if using OPERATING_SYSTEM_AUTH
    username = gdb_admin_user_name
    password = gdb_admin_password
    version = sde.DEFAULT
    
    
    # Set local variables
    if os.name.lower() == "nt":
       slashsyntax = "\\"
       if os.environ.get("TEMP") == None:
          temp = "c:\\temp"
       else:
          temp = os.environ.get("TEMP")
    else:
       slashsyntax = "/"
       if os.environ.get("TMP") == None:
          temp = "/usr/tmp"
       else:
          temp = os.environ.get("TMP")
    
    Connection_File_Name = temp + slashsyntax + "connection.sde"
    
    # Check for the .sde file and delete it if present
    if os.path.exists(Connection_File_Name):
       os.remove(Connection_File_Name)
    
    #Variable defined within the script; other variable options commented out at the end of the line
    saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME
    saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION
    
    print "Creating ArcSDE Connection File..."
    # Create ArcSDE Connection File
    # Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
    arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)
    
    # Update statistics on system tables
    arcpy.AnalyzeDatasets_management(Connection_File_Name, "SYSTEM","","","","")
    print "Analyze Complete"
    

    In this example, the sde user connects to a PostgreSQL database:

    # Name: UStatsSyspg.py
    # Description: Updates statistics on system tables in an enterprise geodatabase in PostgreSQL.
    
    # Author: Esri
    
    # Import system modules
    import sys
    import arcpy
    import os
    
    # Provide connection information
    server = servername
    service = "5151 | sde:postgresql:servername"
    database = database_name
    account_authentication = DATABASE_AUTH
    username = gdb_admin_user_name
    password = gdb_admin_password
    version = sde.DEFAULT
    
    
    # Set local variables
    if os.name.lower() == "nt":
       slashsyntax = "\\"
       if os.environ.get("TEMP") == None:
          temp = "c:\\temp"
       else:
          temp = os.environ.get("TEMP")
    else:
       slashsyntax = "/"
       if os.environ.get("TMP") == None:
          temp = "/usr/tmp"
       else:
          temp = os.environ.get("TMP")
    
    Connection_File_Name = temp + slashsyntax + "connection.sde"
    
    # Check for the .sde file and delete it if present
    if os.path.exists(Connection_File_Name):
       os.remove(Connection_File_Name)
    
    #Variable defined within the script; other variable options commented out at the end of the line
    saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME
    saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION
    
    print "Creating ArcSDE Connection File..."
    # Create ArcSDE Connection File
    # Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
    arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)
    
    # Update statistics on system tables
    arcpy.AnalyzeDatasets_management(Connection_File_Name, "SYSTEM","","","","")
    print "Analyze Complete"
    

    In this example, the sde user connects to a DB2 database:

    # Name: UStatsSysDb2.py
    # Description: Updates statistics on system tables in an enterprise geodatabase in DB2.
    
    # Author: Esri
    
    # Import system modules
    import sys
    import arcpy
    import os
    
    # Provide connection information
    server = servername
    service = "5151 | sde:db2"
    database = db_alias
    account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
    #Leave username and password blank if using OPERATING_SYSTEM_AUTH
    username = gdb_admin_user_name
    password = gdb_admin_password
    version = sde.DEFAULT
    
    
    # Set local variables
    if os.name.lower() == "nt":
       slashsyntax = "\\"
       if os.environ.get("TEMP") == None:
          temp = "c:\\temp"
       else:
          temp = os.environ.get("TEMP")
    else:
       slashsyntax = "/"
       if os.environ.get("TMP") == None:
          temp = "/usr/tmp"
       else:
          temp = os.environ.get("TMP")
    
    Connection_File_Name = temp + slashsyntax + "connection.sde"
    
    # Check for the .sde file and delete it if present
    if os.path.exists(Connection_File_Name):
       os.remove(Connection_File_Name)
    
    #Variable defined within the script; other variable options commented out at the end of the line
    saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME
    saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION
    
    print "Creating ArcSDE Connection File..."
    # Create ArcSDE Connection File
    # Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
    arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)
    
    # Update statistics on system tables
    arcpy.AnalyzeDatasets_management(Connection_File_Name, "SYSTEM","","","","")
    print "Analyze Complete"
    

    In this example, the sde user connects to an Informix database:

    # Name: UStatsSysIDS.py
    # Description: Updates statistics on system tables in an enterprise geodatabase in Informix IDS.
    
    # Author: Esri
    
    # Import system modules
    import sys
    import arcpy
    import os
    
    # Provide connection information
    server = servername
    service = "5151 | sde:informix"
    database = odbc_dsn
    account_authentication = OPERATING_SYSTEM_AUTH | DATABASE_AUTH
    #Leave username and password blank if using OPERATING_SYSTEM_AUTH
    username = gdb_admin_user_name
    password = gdb_admin_password
    version = sde.DEFAULT
    
    
    # Set local variables
    if os.name.lower() == "nt":
       slashsyntax = "\\"
       if os.environ.get("TEMP") == None:
          temp = "c:\\temp"
       else:
          temp = os.environ.get("TEMP")
    else:
       slashsyntax = "/"
       if os.environ.get("TMP") == None:
          temp = "/usr/tmp"
       else:
          temp = os.environ.get("TMP")
    
    Connection_File_Name = temp + slashsyntax + "connection.sde"
    
    # Check for the .sde file and delete it if present
    if os.path.exists(Connection_File_Name):
       os.remove(Connection_File_Name)
    
    #Variable defined within the script; other variable options commented out at the end of the line
    saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME
    saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION
    
    print "Creating ArcSDE Connection File..."
    # Create ArcSDE Connection File
    # Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password
    arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)
    
    # Update statistics on system tables
    arcpy.AnalyzeDatasets_management(Connection_File_Name, "SYSTEM","","","","")
    print "Analyze Complete"
    
  2. After you alter the script to contain your connection information, schedule the script to run at a specific time each night.
    • On Windows, open Scheduled Tasks from the Control Panel and use the wizard to add a scheduled task. When asked what program to run, browse to your Python script.
    • On Linux, create a cron text file that contains information on the day and time you want the script to run, then load the file into cron using the crontab program.

      For example, the following information sets the Python script (named ustatssysids.py) to run every Saturday at 1:30 a.m.:

      30 1 * * 6 /usr/bin/ustatssysids.py

      See the Linux man pages provided with your Linux installation for information on using cron.
10/24/2012