SYB: Update statistics – function datachange bug or feature?
Hi ASE specialists,
once again I’m facing an interesting issue on Sybase / SAP ASE.
May be you know how important it is to have updated statistics in your DB. The newer the statistics, the better for the execution plan (in normal case).
If you have configured your ATM (Automatic Table Maintenance) in dbacockpit correct, you should have good statistics if all is running fine (e.g. job scheduler).
Wrong or right?
Let’s test it with function module SYB_GET_DATACHANGE. Here you can specify the table and get back the data change ratio (%) of the table and table partitions. You also can check this manually with the “datachange” function (select datachange(object_name, partition_name, colname)), but the ABAP part uses exactly the same.
May be it is a little bit more comfortable to execute it in ABAP 😉
So currently we have a data change ratio of 62% but no partitions have changes.
OK however, we will collect statistics. Therefore we use the “update all statistics” command. Why updated all statistics? To avoid missing statistic on partitions, indexes and columns.
“update all statistics updates all the statistics information for a given table. Adaptive Server keeps statistics about the distribution of pages within a table, and uses these statistics when considering whether or not to use a parallel scan in query processing on partitioned tables, and which index (es) to use in query processing. The optimization of your queries depends on the accuracy of the stored statistics.”
select datachange('SAPSR3./BIC/FZSDHUC03',NULL,NULL) go --------------------------- 62.873411 (1 row affected) update all statistics "SAPSR3./BIC/FZSDHUC03" go sp_flushstats go (return status = 0) select datachange('SAPSR3./BIC/FZSDHUC03',NULL,NULL) go --------------------------- 62.873411
sp_flushstats is used to flush the counters to disk. This will be also done by the housekeeper. It is not necessary to do this here manually.
You should never use datachange without specifing a column, because the result will be aggregated across all columns, but SAP is doing it (SYB_GET_DATACHANGE), so… 😉
Normally the data change ratio should be nearly 0. No changes happened to the table, so is it a bug?
To get more details you can use the tool optdiag.
Here the syntax:
optdiag statistics <SID>..<table> -Usapsa -Ppasswd -X -o output.out
The output showed me that not all columns were updated with the “update all statistics” command, but also after manually updating this columns the result of the datachange output is the same.
So it seems to be a bug. OSS message is created, but currently no answer to it. The used DB version was ASE 15.7 SP122.
The development team released a note within 2 days with a correction on SYB_UPDATE_STATS:
2079837 – SYB: Avoid redundant statistics update due to CR 770415
It seems to be connected with the hashing in some releases, if we have a look into the coding:
“to avoid running into CR 770415, we need to enforce no hashing for partitioned tables on older releases
if partcnt > 1 and ( dbrel < ‘22.214.171.124‘ or ( dbrel+0(4) = ‘16.0’ and dbrel < ‘16.0.01.00‘ ) ).
Please implement this note when your AE release is lower than 15.7 SP132 and on ASE 16 lower than SP1.
This will solve this issue!
I hope could help you to understand the statistics.
If you have any further questions, don’t hestate to comment the blog or contact me or one of my colleagues at Q-Partners ( info_at_qpcm_dot_de )
Technology Consultant at Q-Partners (www.qpcm.eu)