sql anywhere 12 statistics problem

[Former Member]

Hello,

I have sql anywhere database in version 11 after unload database (means rebuild to version 12) I have problem with preformace of executed procedures.

After some google reads I try make CREATE STATISTICS... on tables which I think statistics are bad. After manualy maked CREATE STATISTICS I see big difference in time of execution some procedures.

I read about update statistics in sql anywhere 11 and they need be maked manually. In version db 12 there is statistics governor which in my db in version 12 I think not working.

I check and in my db option update_statistics is enabled.

Should I make update statistics manually during migration ? before or after to make governor work ?

How I can check if statistics governor is work ?

[Former Member]

Hello Marek,

How did you come to the conclusion statistics governor isn't working? Do you not see any update in update_time of SYSCOLSTAT or SYSCOLSTATS for the table (unless you manually called CREATE STATISTICS)? This is an indication of self-monitoring of statistics governor working.

The server checks statistics of tables that are loaded into memory, and flushes them every 30 minutes and checkpoint (self-monitoring). During the process, it updates histograms in ISYSCOLSTAT table and determines if statistics needs to be fixed.

Regards,

Jinwoo

[Former Member]

Hello Jinwoo,

Thanks for your interest and replay.

To check statistics governor is woring I check update_time in SYSCOLSTAT by query

select SYS.SYSTABLE.table_name,SYSCOLSTAT.*

from SYSCOLSTAT

join SYS.SYSTABLE on SYS.SYSTABLE.table_id = SYSCOLSTAT.table_id

where SYS.SYSTABLE.table_name = 'some tables...'

and in some columns update_time is acutal I think it is from last checkpoint, but there is also some columns which update_time is old 3 - 5 days. I don't know is this old updata_time of statistics are ok ?

On copy of production database I make test by using query which is run by my ERP (very slow) and total runtime from execute query in PlanViewer is 20s, After test I make CREATE STATISTICS on all tables from this query and runtime is aprox 1 - 2 s. That is big difference.

Another question is if I make CREATE STATISTICS on all tables in database this not delete any data or links  or other information from my production database ?

On SyBooks Online I found that information

"Old statistics are not loaded when a database is rebuilt   When you rebuild a version 11 or earlier database, the LOAD STATISTICS statement silently skips loading old string statistics into the new database, but upgrades the version of the string statistics. See LOAD STATISTICS statement."

Should I make some specific thing with statistics after rebuild from 11 to 12 ?

Regards,

Marek