Usage for optdiag

Take these into consideration when using optidag.

  • Set the SYBASE environment variable to the location of the current version of SAP ASE before using optdiag.

  • By default, optdiag does not include the system tables when you display statistics for a database. To include the system tables in the output, use the -s flag.

  • You cannot specify a particular partition on the optdiag command line; optdiag displays statistics for all partitions of a specified table.

  • When you use binary mode, optdiag displays the human-readable values with comment marks (#s) at the beginning of the lines, as shown in this example:
    Statistics for column:          "price"
    Last update of column statistics: Jan 20 1998   7:16PM
    Statistics loaded from Optdiag.
        Range cell density:         0x3f8b9cfefece26bf
    #   Range cell density:         0.0134830400000000
        Total density:              0x3f8b9cfefece26bf
    #   Total density:              0.0134830400000000
        Range selectivity:          default used (0.33)
    #   Range selectivity:          default used (0.33)
        In between selectivity:     default used (0.25)
    #   In between selectivity:     default used (0.25)
  • When you use optdiag with an input file to change statistics, it ignores all characters after the “#” in a line.

  • Converting floating-point values may lead to rounding errors when you use files for input.

    When you are loading statistics on the same hardware platform, edit the statistics using the binary values to provide greater precision.

  • optdiag displays:
    • The statistic sampling percent last used, which indicates that statistics are gathered with a user-specified sampling percent.

    • Statistics for each partition of a multi-partitioned table or index.

    • Global- and partition-level statistics for each column in a table with multiple partitions.

  • Use ddlgen partition names and the optdiag utility to analyze optimizer behavior by creating empty partitioned tables with simulated metadata.

See also:
  • Performance and Tuning Guide – for details on optidag output, and changing statistics using optidag
  • Reference Manual: Commandscreate index, delete statistics, set, update statistics
  • Reference Manual: Proceduressp_addlogin, sp_configure, sp_defaultlanguage, sp_droplanguage, sp_flushstats, sp_helplanguage