Home » Databases » Sybase » ASE » Some interesting update statistics with sampling behavior

Some interesting update statistics with sampling behavior

Nishant Rubani posted the following observation on the behavior of Sybase ASE’s update statistics (sybase.public.ase.performance+tuning):

Hi All,

I am executing below the sets of queries in series.

update statistics bal_mov_calc_report ( effective_date) with sampling = 50 percent
update statistics bal_mov_calc_report ( unique_id) with sampling = 50 percent
update statistics bal_mov_calc_report ( account_key) with sampling = 50 percent
update statistics bal_mov_calc_report ( product_key) with sampling = 50 percent
update statistics bal_mov_calc_report ( external_ref) with sampling = 50 percent

but each statement is using huge I/O. I thought pages accessed by the first query will be stored in memory and hence next queries will take less I/O but this is not happening. This table is 14287256 KB data and 12707165 rowcount. All page lock.

Strange is, this is not true with below table:
update statistics bal_pos_calc_report ( effective_date) with sampling = 50 percent
update statistics bal_pos_calc_report ( product_key) with sampling = 50 percent
update statistics bal_pos_calc_report ( unique_id) with sampling = 50 percent
update statistics bal_pos_calc_report ( adjusted) with sampling = 50 percent
update statistics bal_pos_calc_report ( account_key) with sampling = 50 percent
update statistics bal_pos_calc_report ( source_system) with sampling = 50 percent

First query is taking big I/O but next queries are taking very less I/O and time. Second table is 13592368 KB data and 7437834 rowcount. Data Row lock.

Can you please help with the cause/solution of this?

Regards,
Nishant

Kevin Sherlock wrote up an excellent reproduction of the problem:

I see some very interesting behavior here while taking a closer look at this.

I’m working with

Adaptive Server Enterprise/12.5.3/EBF 13325 ESD#7/P/Sun_svr4/OS 5.8/ase1253/1951/64-bit/FBO/Fri Mar 24 11:00:22 2006


My cache size for this test is 125Mb and my table is about 210Mb large (data pages).  There are two things that are apparent to me about statistics sampling:

  • Requesting a sampling percentage is a lot like requesting histogram steps.

You can request a certain percentage to sample, but what you actually get sampled may be a whole different thing. Makes me wonder if ASE should store the ACTUAL sampled percentage value as well as the REQUESTED sample percent, much like it does with requested/actual steps.

  • Locking scheme not only greatly affects the number of ACTUAL sampled pages, but the sampling algorithm is different for DOL vs APL tables. For DOL tables, two or more requests for <n>% sampling will sample the exact same pages of the table (assuming the table is static,ie no insert/update/deletes between samples). For APL tables, one request for <n>% sample may sample very different pages than the next request for <n%> sample.

To illustrate, I have a table which has 2,085,463 rows. As an APL table it’s data page chain consists of 114,782 pages. As an Datarows locked table, it’s data page chain has 128,923 pages. I run "update statistics mytable (column) with sampling = 50 percent" on each column (I happen to have 9 columns) of the table.

For the APL version of this table, the below numbers are measured

LReads APF PReads PageCnt ReqSam ActSam ElpsSec LR_Sec

—— ——- —— ——- —— ——- ——- ——-

57395 0 36695 114782 50 50 35 1639.00

57395 0 26445 114782 50 50 42 1366.00

57395 0 42175 114782 50 50 52 1103.00

57395 0 20086 114782 50 50 44 1304.00

57395 0 35947 114782 50 50 50 1147.00

57395 0 44462 114782 50 50 53 1082.00

57395 0 24336 114782 50 50 46 1247.00

57395 0 19959 114782 50 50 45 1275.00

57395 0 19498 114782 50 50 41 1399.00

For the DOL version of this table, the below numbers are measured

LReads APF PReads PageCnt ReqSam ActSam ElpsSec LR_Sec

—— ——- —— ——- —— ——- ——- ——-

42980 4 34775 128923 50 33 24 1790.00

42980 4 0 128923 50 33 21 2046.00

42980 4 0 128923 50 33 24 1790.00

42980 4 0 128923 50 33 24 1790.00

42980 4 0 128923 50 33 25 1719.00

42980 4 0 128923 50 33 24 1790.00

42980 4 0 128923 50 33 24 1790.00

42980 4 0 128923 50 33 22 1953.00

42980 4 0 128923 50 33 22 1953.00

The data above support Nishant’s stipulation. For APL, the pages sampled appear to be truly random for each separate request. For DOL, the first run reads in the pages to cache, and subsequent runs incur no cache misses. That is, they appear to be reading the same pages as the previous request.

Not only that, but when I request 50% sampling on APL, I actually get close to 50% pages sampled. When I ask for 50% of my DOL table, only 33% of the pages are sampled. So for DOL, not only am I reading the cached pages every time, I’m also reading fewer!

So, this leads me to another test. How many pages ACTUALLY get sampled when I request various percentages.  Here are the APL results for my table:


LReads APF PReads PageCnt ReqSam ActSam ElpsSec LR_Sec

—— ——- —— ——- —— ——- ——- ——-

5741 0 0 114785 5 5 3 1913.00

11477 0 3331 114785 10 9 8 1434.00

16396 0 9718 114785 15 14 14 1171.00

22957 0 7040 114785 20 20 17 1350.00

28700 0 16315 114785 25 25 25 1148.00

28700 0 0 114785 30 25 18 1594.00

38269 0 21857 114785 35 33 32 1195.00

38269 0 0 114785 40 33 24 1594.00

38269 0 0 114785 45 33 23 1663.00

57395 0 39156 114785 50 50 50 1147.00

57395 0 28056 114785 55 50 47 1221.00

57395 0 19950 114785 60 50 44 1304.00

57395 0 19447 114785 65 50 44 1304.00

57395 0 19883 114785 70 50 42 1366.00

57395 0 19935 114785 75 50 45 1275.00

57395 0 19918 114785 80 50 44 1304.00

57395 0 19479 114785 85 50 44 1304.00

57395 0 19898 114785 90 50 44 1304.00

57395 0 19492 114785 95 50 45 1275.00

57395 0 19891 114785 99 50 44 1304.00

114785 88151 27876 114785 100 100 110 1043.00

and Here are the DOL results:

LReads APF PReads PageCnt ReqSam ActSam ElpsSec LR_Sec

—— ——- —— ——- —— ——- ——- ——-

6148 4 3504 128923 5 4 5 1229.00

11732 4 7056 128923 10 9 8 1466.00

16124 4 9080 128923 15 12 11 1465.00

21492 4 9440 128923 20 16 14 1535.00

25796 4 14896 128923 25 20 18 1433.00

25796 4 0 128923 30 20 14 1842.00

32235 4 8193 128923 35 25 20 1611.00

32235 4 0 128923 40 25 18 1790.00

32235 4 0 128923 45 25 18 1790.00

42980 4 27888 128923 50 33 36 1193.00

42980 4 0 128923 55 33 25 1719.00

42980 4 0 128923 60 33 24 1790.00

42980 4 0 128923 65 33 24 1790.00

42980 4 0 128923 70 33 25 1719.00

42980 4 0 128923 75 33 24 1790.00

42980 4 0 128923 80 33 24 1790.00

42980 4 0 128923 85 33 25 1719.00

42980 4 0 128923 90 33 24 1790.00

42980 4 0 128923 95 33 24 1790.00

64467 4 48967 128923 99 50 51 1264.00

128928 8370 120560 128923 100 100 121 1065.00

Note that for APL, a request somewhere between 50%-99% actually scans 50% of the table. So a request of 80% runs just as fast as a request of 50% and my histogram is no different!

For DOL, a similar phenomenon occurs between 50%-95% actually scans 33% of the table. Interesting that 99% request gets 50% actual.  So, in conclusion, be aware that locking scheme has a direct impact on sampling stats performance, and that what you request for a sample (regardless of locking scheme), may not at all reflect what was actually sampled to get your statistics.

Kevin’s excellent investigation into this matter points to that this is either a bug in the implementation or it is a design flaw with update statistics.  This deserves to be fixed.  The odd thing is that this isssue wasn’t identified earlier.  I’m assuming this behavior isn’t expected behavior.  (if it is expected behavior, atleast as far as Sybase Engineering goes, then it would be a piss poor design).

Share Button

Comments

  1. Kevin Sherlock says:

    That last paragraph of this blog entry is not from me. THat is Jason’s comment on the posting.

  2. Sorry Kevin! I thought was obvious but I’ll make it more so..

  3. dooraley says:

    Sybase 12.5…

    Guys, i need some help. This seems something that might have to do with the problem I am having. I have a table with 150mil rows. Each business day about 1.5mil rows get inserted into the table. I have a non-clustered index with 3 keys. create index myindex on mytable (mydate, mytype, mysubtype)…the datatypes are (datetime,int,int). The table contains data from Sept 15, 06 till present for each bank business day. There are about 30 differnt “mytypes” and up to 10 subtypes (just so you get an idea about the density). The problem is this:

    when i do something like select * into #tmp from mytable where mydate = ‘20070315’, it takes about 15 seconds to perform the operation. When i use a much older date, for example, 20060615, this operation takes 40+ minutes. i’ve looked at the exectuion plans and they are identical. For some reason there are about 10 times more logical/physical reads for later dates than for more recent dates. For example, the recent dates take about 20k logical reads and 2k physical reads. The older dates take 300k lgoical and 200k physical. This is really strange and i am not sure whether this is a consistent behavior and how to remedy this problem. Can you please help?

    P.S. there are no clustered indexes on the table.

    Thanks much

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect