Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
jgleichmann
Active Contributor
3,087

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?

SYB_GET_DATACHANGE

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 :wink:

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.

description in sybase documentation:

"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... :wink:

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.

##########

Update

##########

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 < '15.7.0.132' 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!

##########

Update End

##########

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 )

Best Regards,

Jens Gleichmann

Technology Consultant at Q-Partners (www.qpcm.eu)

Details:

update all statistics

update index statistics

update statistics

update table statistics

optiag

datachange function

3 Comments
Labels in this area