When we go to a performance and tuning gig, one of the first questions we always ask is, “When is the last time you updated your statistics?” You might be surprised at how frequently our assignment is cut short by simply updating the client’s statistics. If you’ve ever had a straightforward application with small tables, but terrible performance, or a complex application with erratic performance, or a seemingly well-performing application that suddenly became very slow, there’s a good chance the problem was out-of-date statistics.
SAP Sybase Adaptive Server Enterprise (ASE) uses a cost-based optimizer which balances the cost of finding the best query execution plan with the costs of running the query in different ways. To do this, it checks a histogram that identifies the data distribution, and uses this information to determine join orders, index selection, and more. You might think that the server would keep this information up-to-date, but because of the resource cost, it does not. You will need to perform this critical task (updating statistics) as often as your data skews.
In high-volume environments, you can update statistics on a restored database, and then import them into the production server using the optdiag utility, but that’s a lot more work.
Options for updating statistics
ASE provides a number of options for updating statistics, depending on the specific needs you have.
To update information about the distribution of key values in specified indexes, for all columns in an index, table, or partition, the syntax is:
update statistics table_name [[ partitiondata_partition_name ] [ (column_list ) ] | index_name[ partitionindex_partition_name ] ] [ using step values ] [ with consumers = consumers ][, sampling=Npercent ]
To update the statistics for all columns in an index, the syntax is:
update index statistics table_name [[ partitiondata_partition_name ] | [ index_name [ partition index_partition_name ] ] ] [ usingstep values ] [ with consumers = consumers ] [, sampling=N percent]
To update all statistics information for a given table, the syntax is:
update all statistics table_name [ partition data_partition_name]
You can run update all statistics on a single data partition.
To update statistics that are stored in systabstats table, such as rowcount, cluster ratios, and so on, the syntax is:
update table statistics table_name [ partition data_partition_name ]
Performing update table statistics does not affect column statistics stored in sysstatistics.
Which update statistics option is best?
We normally use update index statistics to get current statistics for all the columns in the indexes; we typically aren’t so concerned about statistics for non-indexes columns (if you’re going to search for something, index it, and if you’re not going to search for something, why use the resources to collect the statistics?)
Your situation, of course, may be different. Here are a few considerations when choosing which option will work best in your circumstances:
- You can update statistics for a specific partition. This can be really useful if you’re using semantic partitioning. Why? Consider this: if you are partitioned by date, and have your most recent data on the current partition, you can choose to update statistics for ONLY the more recent data, rather than the entire table of >1TB.
- You can update statistics for either a specific index or all of the indexes on a specific table. In general, you would run this on an entire table; for very large tables, you can update statistics on just the columns that skew the most.
- You can change the number of histogram steps to create (the default is 20 steps). You can configure this at the server level (new in late ASE 15). We typically build it into the scripts as a parameter so that changes of server-wide defaults at upgrade time don’t undo our decision-making processes.
When you increase the steps, it changes the number of histogram points that the optimizer has to work with. When would you do this? If you ever find your performance to be erratic, increasing the number of steps may be a good place to start.
Be sure to note that the random distribution of data doesn’t imply a steady distribution of data; in practice, it often promises the opposite.
- Using the consumers option, you can control the degree of parallelism, which can dramatically speed up the scans (depending on whether you’re already swamping your I/O subsystem). Of course, this is limited to the level of parallelism you’ve already set up on your server.
- The sampling allows you to reduce the number of rows you have to scan in order to build the histogram. This can save a lot of time if you have multi-terabyte tables or databases.
You can examine the statistics (including time since the last update) by querying the systabstats and sysstatistics table, or by using the optdiag utility from the OS prompt.
Does updating statistics cause locking and contention?
Some have claimed the update statistics command causes blocking and contention. In earlier versions of ASE, this may have sometime happened; however, in the newer releases of ASE, locking is minimal and this should not be a significant concern.
In general, if you are updating statistics for a table that has allpages locking defined, the server places an intent lock at the table level, and a page lock on the page currently being scanned. (Locking level 1). Otherwise, it does not lock (that is, with dirty reads / locking level 0).
It’s important to be up-to-date
As noted at the beginning, out-of-date statistics is a frequent cause of poor performance. Assess your circumstances and develop a plan for keeping your statistics up to date, so that the query optimizer can do its job. It’s more than worth the time you’ll invest.
Hi I have a set of sybase questions.Any one review it and answer those questions pls
Choose the steps followed by optimization
to analyze a query (choose 3)
the final access method is determined
Each table is analyzed
Adds optimized search arguments and join
the cost of using each index that matches a
search a argument or join columns
The query is prased and normalized
of the following is not true about this query?
* from sales_order
order_amt/12 > 15000
salesman = ?Pablo?
query optimizer will consider performing a table scan.
query optimizer will consider using the index on salesman
query optimizer will consider using the index on order_amt
query optimizer will choose an access method based on cost
optimizer considers the size of the table in determining which aspects of a
Scan versus Parallel Scan
table versus Outer Table
selection versus table scan
based versus hash based scan
the following query:
Select T.title, P.pub_name from
titles T, Publishers P
Where T.price > $10.00 and
T.pub_id = P.pub_id abd P.pub_state = ?NY?
Which of the following indexes will
the ASE optimizer consider using? (Choose 3)
unique clustered index idx4 on publishers (pub_id)
clustered index idx5 on publishers (pub_name)
index idx1 on titles (price)
unique index idx3 on titles (title_id)
index idx2 on titles (pub_id)
is an insert trigger on the table T1. What will happen if we fire the following
into T1 values (“abc”)
server will give an error and the trigger will not fire.
will not be inserted but the trigger will be fired without giving any error.
will not be inserted and trigger will not be fired without giving any error.
server will give and error but the trigger will be fired.
many times will the loop execute?
is then decided that the check constraint should be changed to ‘(a>b)’
commands and/ or stored procedure must be executed, and in which order, to
accomplish this change?
alter table; alter table
table; alter table
alter table; alter table
of the following statements about optimizer statistics are true? (Choose 3)
default, dropping then recreating an index on a table with data overwrites any
statistics on its leading column.
default creating an index on an empty table generates statistics on its leading
an index does not delete any column statistics
default creating an index on a table with data generates statistics on its
zoxy free games says
Can you tell us more about this? I’d love to find out more details.