Почему оптимизатор ведет себя некорректно?
соорудил запрос, без всяческих хинтов
запускаю - жутко долго выполняется

пишу ему set forceplan on
запускаю - красота, чпык и готово

Zhora,

делал даже reorg rebuid и затем на всякий случай update statistics по нужным таблицам - не помогло.
оптимизатор тупо делает ерунду и спасает только форсплан он

для пробы, для одного случая, написал абстрактный план, так чтоб чтение шло по индексам - все быстро зашуршало...
но ёпта - не нравится мне писать АП по всякой мелочи...

конкретный вопрос в том, что оптимизатор принимает крайне неправильное решение при выборке данных.
я привел действия которые уже проделал и написал о результатах.

и прошу подсказать: что это могло с ним случиться такое и как это решить?

ниже привожу примеры

1.

QUERY PLAN FOR STATEMENT 1 (at line 1).




STEP 1
The type of query is SELECT.


FROM TABLE
nal_doc
nd
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


FROM TABLE
recv_doc
rd
Nested iteration.
Index : recv_doc_id_rec_1001954072
Forward scan.
Positioning by key.
Keys are:
id_recv ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


FROM TABLE
document
d
Nested iteration.
Index : document_id_doc_15346285101
Forward scan.
Positioning by key.
Keys are:
id_doc ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


Server Message: Number 3630, Severity 10
Server 'sybase_ds', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 37425416.


Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: document scan count 2016209, logical reads: (regular=2116581 apf=0 total=2116581), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: recv_doc scan count 2016209, logical reads: (regular=2085064 apf=0 total=2085064), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: nal_doc scan count 1, logical reads: (regular=65157 apf=0 total=65157), physical reads: (regular=2 apf=0 total=2), apf IOs used=0
Server Message: Number 3631, Severity 10
Server 'sybase_ds', Line 1:
Total actual I/O cost for this command: 8533640.
Total writes for this command: 0

Execution Time 125.
SQL Server cpu time: 12500 ms. SQL Server elapsed time: 12483 ms.
(15 rows affected)


2.

QUERY PLAN FOR STATEMENT 1 (at line 1).




STEP 1
The type of query is SELECT.


FROM TABLE
document
d
Nested iteration.
Index : vid_doc_dat_uchet
Forward scan.
Positioning by key.
Keys are:
vid_doc ASC
dat_uchet ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


FROM TABLE
recv_doc
rd
Nested iteration.
Index : id_doc_kod_recv
Forward scan.
Positioning by key.
Keys are:
id_doc ASC
kod_recv ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


FROM TABLE
nal_doc
nd
Nested iteration.
Index : id_recv
Forward scan.
Positioning by key.
Keys are:
id_recv ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


Server Message: Number 3630, Severity 10
Server 'sybase_ds', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 699722210.


Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: document scan count 1, logical reads: (regular=1590 apf=0 total=1590), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: recv_doc scan count 1948, logical reads: (regular=8270 apf=0 total=8270), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: nal_doc scan count 9595, logical reads: (regular=14626 apf=0 total=14626), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Server Message: Number 3631, Severity 10
Server 'sybase_ds', Line 1:
Total actual I/O cost for this command: 48972.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 43 ms.
(15 rows affected)


3.

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using the Abstract Plan in the PLAN clause.




STEP 1
The type of query is SELECT.


FROM TABLE
document
d
Nested iteration.
Index : vid_doc_dat_uchet
Forward scan.
Positioning by key.
Keys are:
vid_doc ASC
dat_uchet ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


FROM TABLE
recv_doc
rd
Nested iteration.
Index : id_doc_kod_recv
Forward scan.
Positioning by key.
Keys are:
id_doc ASC
kod_recv ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


FROM TABLE
nal_doc
nd
Nested iteration.
Index : id_recv
Forward scan.
Positioning by key.
Keys are:
id_recv ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


Server Message: Number 3630, Severity 10
Server 'sybase_ds', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 699722210.


Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: document scan count 1, logical reads: (regular=1590 apf=0 total=1590), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: recv_doc scan count 1948, logical reads: (regular=8270 apf=0 total=8270), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: nal_doc scan count 9595, logical reads: (regular=14626 apf=0 total=14626), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Server Message: Number 3631, Severity 10
Server 'sybase_ds', Line 1:
Total actual I/O cost for this command: 48972.
Total writes for this command: 0

Execution Time 1.
SQL Server cpu time: 100 ms. SQL Server elapsed time: 50 ms.
(15 rows affected)

MasterZivOn 15.07.2011 11:54, Imperous wrote:

В таком виде перепиши запрос :

select nd.*, rd.value_recv, d.dat_uchet
from document d
inner join recv_doc rd on d.id_doc = rd.id_doc and rd.kod_recv = 27
inner join nal_doc nd on rd.id_recv = nd.id_recv
where d.dat_uchet <= "2011/06/01" and d.vid_doc = 39

и попробуй.

время выполнения - 38 сек...

cherrex_DenImperous,

Так а что вы хотите? Ну заболел оптимизатор, но вы нашли целых две таблетки (forceplan и абстрактный план), которыми это лечиться. Универсальной прививки от ошибок оптимизатора - нет. Но я бы еще бы покопал в сторону статистики.

та дело в том что таблиц более 350, и ведь еще несколько месяцев назад я не знал такой беды..
и очень много процедур чтоб их всех пропатчить.
мне почему-то не нравится forceplan как решение.
кстати никаких обновлений версий ASE не делалось уже более 2 лет, сейчас стоит 12.5.4. железо быстрое и тоже не менялось более полугода, даже скажу иначе, на новом железе все закрутилось немного быстрей чем на старом.

а куда именно копать статистику?

document
авторServer Message: sybase_ds - Msg 2401, Level 11, State 2:
Character set conversion is not available between client character set 'iso_1' and server character set 'cp1251'.

Server Message: sybase_ds - Msg 2411, Level 10, State 1:
No conversions will be done.

OptDiag/12.5.2/EBF 11798/P/NT (IX86)/OS 4.0/ase1252/1831/32-bit/OPT/Fri Apr 09 04:35:35 2004
Adaptive Server Enterprise/12.5.4/EBF 16831 ESD#9.1/P/x86_64/Enterprise Linux/ase1254/2146/64-bit/OPT/Wed Apr 15 03:57:32 2009

Server name: "sybase_ds"

Specified database: "db1"
Specified table owner: not specified
Specified table: "document"
Specified column: not specified

Table owner: "dbo"
Table name: "document"

Statistics for table: "document"

Data page count: 12960
Empty data page count: 0
Data row count: 238049.0000000000000000
Forwarded row count: 665.0000000000000000
Deleted row count: 2580.0000000000000000
Data page CR count: 1192.0000000000000000
OAM + allocation page count: 319
First extent data pages: 760
Data row size: 97.0000000000000000

Derived statistics:
Data page cluster ratio: 0.9999900000000001
Space utilization: 0.8899569951653285
Large I/O efficiency: 0.9999300048996574

Statistics for index: "document_id_doc_15346285101" (nonclustered)
Index column list: "id_doc"
Leaf count: 1607
Empty leaf page count: 0
Data page CR count: 17508.0000000000000000
Index page CR count: 207.0000000000000000
Data row CR count: 45015.0000000000000000
First extent leaf pages: 21
Leaf row size: 11.0000000000000000
Index height: 2

Derived statistics:
Data page cluster ratio: 0.6332473870804365
Index page cluster ratio: 0.9957325746799431
Data row cluster ratio: 0.8562620321330033
Space utilization: 0.8139150830501173
Large I/O efficiency: 0.9709944751381219

Statistics for index: "dat_uchet_id_doc" (nonclustered)
Index column list: "dat_uchet", "id_doc"
Leaf count: 3660
Empty leaf page count: 0
Data page CR count: 19448.0000000000000000
Index page CR count: 625.0000000000000000
Data row CR count: 48620.0000000000000000
First extent leaf pages: 51
Leaf row size: 19.9870644316630500
Index height: 2

Derived statistics:
Data page cluster ratio: 0.6201135131688030
Index page cluster ratio: 0.9478450968144909
Data row cluster ratio: 0.8402709392382828
Space utilization: 0.6493370974507675
Large I/O efficiency: 0.7325554792953557

Statistics for index: "dat_uchet_id_doc_vid_doc" (nonclustered)
Index column list: "dat_uchet", "id_doc", "vid_doc"
Leaf count: 3733
Empty leaf page count: 0
Data page CR count: 19430.0000000000000000
Index page CR count: 710.0000000000000000
Data row CR count: 48625.0000000000000000
First extent leaf pages: 27
Leaf row size: 23.9870644316630500
Index height: 2

Derived statistics:
Data page cluster ratio: 0.6205364733888794
Index page cluster ratio: 0.9255970606246172
Data row cluster ratio: 0.8402487601912722
Space utilization: 0.7640493314471435
Large I/O efficiency: 0.6575397624320515

Statistics for index: "vid_doc_dat_uchet" (nonclustered)
Index column list: "vid_doc", "dat_uchet"
Leaf count: 1193
Empty leaf page count: 0
Data page CR count: 40902.0000000000000000
Index page CR count: 357.0000000000000000
Data row CR count: 65848.0000000000000000
First extent leaf pages: 44
Leaf row size: 6.2434482912964659
Index height: 2

Derived statistics:
Data page cluster ratio: 0.3881377293024848
Index page cluster ratio: 0.8015340364333653
Data row cluster ratio: 0.7638508148581872
Space utilization: 0.6222807462005021
Large I/O efficiency: 0.4185393258426966

Statistics for index: "kod_ush_dat_uchet" (nonclustered)
Index column list: "kod_ush", "dat_uchet"
Leaf count: 1436
Empty leaf page count: 0
Data page CR count: 30634.0000000000000000
Index page CR count: 347.0000000000000000
Data row CR count: 53899.0000000000000000
First extent leaf pages: 26
Leaf row size: 6.1316496206611015
Index height: 2

Derived statistics:
Data page cluster ratio: 0.4446504338519170
Index page cluster ratio: 0.8670382165605095
Data row cluster ratio: 0.8168543014043772
Space utilization: 0.5077210604676503
Large I/O efficiency: 0.5179381443298968

Statistics for index: "kod_ush_vid_doc_dat_uchet" (nonclustered)
Index column list: "kod_ush", "vid_doc", "dat_uchet"
Leaf count: 1297
Empty leaf page count: 0
Data page CR count: 44623.0000000000000000
Index page CR count: 350.0000000000000000
Data row CR count: 69357.0000000000000000
First extent leaf pages: 42
Leaf row size: 6.3579213571156812
Index height: 2

Derived statistics:
Data page cluster ratio: 0.3649159043965772
Index page cluster ratio: 0.8350970017636684
Data row cluster ratio: 0.7482855596660722
Space utilization: 0.5828777318056003
Large I/O efficiency: 0.4641833810888252

Statistics for index: "par_id" (nonclustered)
Index column list: "par_id"
Leaf count: 1071
Empty leaf page count: 0
Data page CR count: 25634.0000000000000000
Index page CR count: 612.0000000000000000
Data row CR count: 28305.0000000000000000
First extent leaf pages: 7
Leaf row size: 6.3283911107132464
Index height: 2

Derived statistics:
Data page cluster ratio: 0.0999326548937444
Index page cluster ratio: 0.4898612593383138
Data row cluster ratio: 0.9303844072427896
Space utilization: 0.7025967382357035
Large I/O efficiency: 0.2187718888629466

Statistics for index: "kod_ush_vid_doc_dat_doc" (nonclustered)
Index column list: "kod_ush", "vid_doc", "dat_doc"
Leaf count: 1316
Empty leaf page count: 0
Data page CR count: 64503.0000000000000000
Index page CR count: 307.0000000000000000
Data row CR count: 95564.0000000000000000
First extent leaf pages: 31
Leaf row size: 7.6485855918358210
Index height: 2

Derived statistics:
Data page cluster ratio: 0.3304818751529467
Index page cluster ratio: 0.8766290182450044
Data row cluster ratio: 0.6320363026641471
Space utilization: 0.6910787356833612
Large I/O efficiency: 0.5365967365967367

Statistics for index: "id_an_kod_uch" (nonclustered)
Index column list: "id_an", "kod_ush"
Leaf count: 1093
Empty leaf page count: 0
Data page CR count: 42828.0000000000000000
Index page CR count: 941.0000000000000000
Data row CR count: 44290.0000000000000000
First extent leaf pages: 40
Leaf row size: 6.0243940648568808
Index height: 2

Derived statistics:
Data page cluster ratio: 0.0342284550371081
Index page cluster ratio: 0.1589958158995816
Data row cluster ratio: 0.8594779939495559
Space utilization: 0.6553834924202584
Large I/O efficiency: 0.1452004860267315

Statistics for column: "dat_doc"
Last update of column statistics: Nov 19 2009 4:30:55:143AM

Range cell density: 0.0030392926310691
Total density: 0.0030392926310691
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "dat_doc"
Column datatype: datetime
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= "Dec 31 1899 11:59:59:996PM"
2 0.05262334 <= "Sep 15 2006 12:00:00:000AM"
3 0.05382037 <= "Mar 12 2008 12:00:00:000AM"
4 0.05345206 <= "May 16 2008 12:00:00:000AM"
5 0.05369146 <= "Jul 23 2008 12:00:00:000AM"
6 0.05856246 <= "Sep 1 2008 12:00:00:000AM"
7 0.05384799 <= "Oct 6 2008 12:00:00:000AM"
8 0.05397691 <= "Nov 6 2008 12:00:00:000AM"
9 0.05327711 <= "Dec 8 2008 12:00:00:000AM"
10 0.05349809 <= "Jan 9 2009 12:00:00:000AM"
11 0.05330473 <= "Feb 10 2009 12:00:00:000AM"
12 0.05390324 <= "Mar 20 2009 12:00:00:000AM"
13 0.05299166 <= "Apr 24 2009 12:00:00:000AM"
14 0.05290879 <= "May 27 2009 12:00:00:000AM"
15 0.06034880 <= "Jun 30 2009 12:00:00:000AM"
16 0.05778899 <= "Jul 31 2009 12:00:00:000AM"
17 0.05493453 <= "Sep 1 2009 12:00:00:000AM"
18 0.05613156 <= "Oct 1 2009 12:00:00:000AM"
19 0.05400453 <= "Nov 2 2009 12:00:00:000AM"
20 0.01693339 <= "May 2 8209 12:00:00:000AM"

Statistics for column: "dat_uchet"
Last update of column statistics: Nov 8 2010 11:01:28:030AM

Range cell density: 0.0292655051974063
Total density: 0.0293786842691230
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "dat_uchet"
Column datatype: datetime
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00161695 <= "Dec 31 1899 11:59:59:996PM"
2 0.05994450 <= "Feb 1 2008 12:00:00:000AM"
3 0.05682250 <= "Jun 1 2008 12:00:00:000AM"
4 0.07124074 <= "Sep 1 2008 12:00:00:000AM"
5 0.06641788 <= "Nov 1 2008 12:00:00:000AM"
6 0.06436452 <= "Jan 1 2009 12:00:00:000AM"
7 0.02490321 < "Mar 1 2009 12:00:00:000AM"
8 0.02836649 = "Mar 1 2009 12:00:00:000AM"
9 0.05925072 <= "May 1 2009 12:00:00:000AM"
10 0.06458832 <= "Jul 1 2009 12:00:00:000AM"
11 0.06511425 <= "Sep 1 2009 12:00:00:000AM"
12 0.03567352 < "Nov 1 2009 12:00:00:000AM"
13 0.03402860 = "Nov 1 2009 12:00:00:000AM"
14 0.06199226 <= "Jan 1 2010 12:00:00:000AM"
15 0.05914442 <= "Mar 1 2010 12:00:00:000AM"
16 0.06104111 <= "May 1 2010 12:00:00:000AM"
17 0.07405501 <= "Jul 1 2010 12:00:00:000AM"
18 0.03724571 < "Sep 1 2010 12:00:00:000AM"
19 0.03913681 = "Sep 1 2010 12:00:00:000AM"
20 0.03505248 <= "Nov 1 2010 12:00:00:000AM"

Statistics for column group: "dat_uchet", "id_doc"
Last update of column statistics: Nov 8 2010 11:01:28:030AM

Range cell density: 0.0000055949690039
Total density: 0.0000055949690039
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "dat_uchet", "id_doc", "vid_doc"
Last update of column statistics: Nov 8 2010 11:01:28:030AM

Range cell density: 0.0000055949690039
Total density: 0.0000055949690039
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "dt"
Last update of column statistics: Nov 19 2009 4:30:53:143AM

Range cell density: 0.0000092312153092
Total density: 0.0000092312153092
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "dt"
Column datatype: datetime
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.58484191 <= "Feb 7 2001 4:28:08:996PM"
2 0.02305667 <= "Oct 22 2003 2:17:36:000PM"
3 0.02305667 <= "Sep 1 2006 10:00:47:000AM"
4 0.02305667 <= "Mar 25 2008 3:07:28:000PM"
5 0.02305667 <= "May 21 2008 9:54:01:000AM"
6 0.02305667 <= "Jul 17 2008 3:21:58:000PM"
7 0.02305667 <= "Sep 12 2008 2:20:52:000PM"
8 0.02305667 <= "Oct 16 2008 8:46:15:000AM"
9 0.02305667 <= "Nov 12 2008 12:18:01:000PM"
10 0.02305667 <= "Dec 18 2008 2:37:29:000PM"
11 0.02305667 <= "Jan 30 2009 12:03:35:000PM"
12 0.02305667 <= "Mar 10 2009 3:38:15:000PM"
13 0.02305667 <= "Apr 16 2009 2:37:38:000PM"
14 0.02305667 <= "May 29 2009 11:59:44:000AM"
15 0.02305667 <= "Jul 7 2009 4:14:48:000PM"
16 0.02305667 <= "Aug 14 2009 12:15:21:000PM"
17 0.02305667 <= "Sep 22 2009 10:07:33:000AM"
18 0.02305667 <= "Oct 21 2009 3:59:50:000PM"
19 0.02305667 <= "Nov 18 2009 4:01:28:000PM"
20 0.00013812 <= "Nov 18 2009 4:36:09:000PM"

Statistics for column: "id_an"
Last update of column statistics: Nov 8 2010 11:01:34:030AM

Range cell density: 0.0003852499957599
Total density: 0.0054053922902437
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_an"
Column datatype: integer
Requested step count: 50
Actual step count: 27
Sampling Percent: 0

Step Weight Value

1 0.94720590 < 24
2 0.01190609 = 24
3 0.00031332 <= 25
4 0.00541034 <= 26
5 0.00010071 <= 27
6 0.00328984 <= 28
7 0.00299331 <= 29
8 0.00014547 <= 30
9 0.00279189 <= 31
10 0.00363673 <= 32
11 0.00000000 < 67
12 0.00359756 = 67
13 0.00217085 <= 68
14 0.00006154 <= 76
15 0.00000000 < 87
16 0.00393326 = 87
17 0.00135398 <= 102
18 0.00005595 <= 152
19 0.00000000 < 176
20 0.00365911 = 176
21 0.00008952 <= 408
22 0.00000000 < 797
23 0.00473334 = 797
24 0.00000000 < 865
25 0.00189669 = 865
26 0.00062104 < 1014
27 0.00003357 = 1014

Statistics for column group: "id_an", "kod_ush"
Last update of column statistics: Nov 8 2010 11:01:34:030AM

Range cell density: 0.0003852499957599
Total density: 0.0031452785904173
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "id_doc"
Last update of column statistics: Nov 8 2010 11:01:25:030AM

Range cell density: 0.0000055949690039
Total density: 0.0000055949690039
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_doc"
Column datatype: integer
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 2001000141
2 0.05262628 <= 2008002897
3 0.05262628 <= 2008013334
4 0.05262628 <= 2008024075
5 0.05262628 <= 2008034545
6 0.05262628 <= 2008044576
7 0.05262628 <= 2009008195
8 0.05262628 <= 2009018240
9 0.05262628 <= 2009028333
10 0.05262628 <= 2009038484
11 0.05262628 <= 2009048604
12 0.05262628 <= 2009058915
13 0.05262628 <= 2009069336
14 0.05262628 <= 2010005874
15 0.05262628 <= 2010015985
16 0.05262628 <= 2010026187
17 0.05262628 <= 2010036629
18 0.05262628 <= 2010046786
19 0.05262628 <= 2010056937
20 0.05272699 <= 2010067254

Statistics for column: "id_user_app"
Last update of column statistics: Nov 19 2009 4:30:53:143AM

Range cell density: 0.0092048794007101
Total density: 0.0263838258360816
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_user_app"
Column datatype: numeric(6,0)
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.61635143 <= -1
2 0.01716359 <= 245
3 0.00000000 < 248
4 0.05811127 = 248
5 0.02301063 <= 336
6 0.01427230 <= 343
7 0.00000000 < 345
8 0.02614132 = 345
9 0.00711773 <= 351
10 0.00000000 < 353
11 0.04475977 = 353
12 0.01998122 <= 359
13 0.03713560 <= 360
14 0.01036813 <= 369
15 0.03103074 <= 370
16 0.00699803 <= 382
17 0.01671240 <= 383
18 0.01297398 <= 417
19 0.00000000 <= 419
20 0.05787186 <= 552

Statistics for column: "kod_ush"
Last update of column statistics: Nov 8 2010 11:01:34:030AM

Range cell density: 0.0327614996917251
Total density: 0.1291645597642608
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "kod_ush"
Column datatype: numeric(2,0)
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 < 0
2 0.03151087 = 0
3 0.18490253 <= 1
4 0.00118054 <= 2
5 0.17840119 <= 3
6 0.00773784 <= 7
7 0.06581362 <= 8
8 0.03693799 <= 10
9 0.06728510 <= 11
10 0.03059329 <= 13
11 0.00000000 < 15
12 0.03373207 = 15
13 0.06109147 <= 16
14 0.03062127 <= 17
15 0.20712575 <= 18
16 0.00224918 <= 20
17 0.00000000 < 22
18 0.05203321 = 22
19 0.00839805 < 29
20 0.00038605 = 29

Statistics for column group: "kod_ush", "dat_uchet"
Last update of column statistics: Nov 8 2010 11:01:31:030AM

Range cell density: 0.0046876971926777
Total density: 0.0043617750075188
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "kod_ush", "vid_doc"
Last update of column statistics: Nov 8 2010 11:01:34:030AM

Range cell density: 0.0242480311213085
Total density: 0.0819867970611881
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "kod_ush", "vid_doc", "dat_doc"
Last update of column statistics: Nov 8 2010 11:01:34:030AM

Range cell density: 0.0005875679633795
Total density: 0.0005348549579809
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "kod_ush", "vid_doc", "dat_uchet"
Last update of column statistics: Nov 8 2010 11:01:32:030AM

Range cell density: 0.0030214610177209
Total density: 0.0027725971850782
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "name_host_doc"
Last update of column statistics: Nov 19 2009 4:30:53:143AM

Range cell density: 0.0081026344518719
Total density: 0.0346722362078071
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "num_reg"
Last update of column statistics: Nov 19 2009 4:30:55:143AM

Range cell density: 0.0032530551960334
Total density: 0.0032530551960334
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "num_reg"
Column datatype: char(10)
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= " "
2 0.05749434 <= "1 "
3 0.05273383 <= "1176 "
4 0.05617760 <= "14 "
5 0.05266017 <= "1684 "
6 0.05266017 <= "1989 "
7 0.05266938 <= "2262 "
8 0.05292720 <= "261 "
9 0.05726414 <= "3 "
10 0.05277988 <= "339 "
11 0.05265097 <= "3879 "
12 0.05472275 <= "43 "
13 0.05446493 <= "48 "
14 0.05282592 <= "527 "
15 0.05266938 <= "581 "
16 0.05265097 <= "6372 "
17 0.05707998 <= "7 "
18 0.05272463 <= "789 "
19 0.05320344 <= "90 "
20 0.02964034 <= "стоки "

Statistics for column: "par_id"
Last update of column statistics: Nov 8 2010 11:01:33:030AM

Range cell density: 0.0000558686718471
Total density: 0.7987877480593173
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "par_id"
Column datatype: integer
Requested step count: 20
Actual step count: 6
Sampling Percent: 0

Step Weight Value

1 0.00000000 < 0
2 0.89374596 = 0
3 0.05264866 <= 2009052578
4 0.05264866 <= 2010063800
5 0.00095114 < 2010067160
6 0.00000559 = 2010067160

Statistics for column: "sum_all_d"
Last update of column statistics: Nov 19 2009 4:30:54:143AM

Range cell density: 0.0003630330098461
Total density: 0.0346115697014129
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "sum_all_d"
Column datatype: money
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= -3828067.20
2 0.00455793 <= -0.01
3 0.00000000 < 0.00
4 0.18524520 = 0.00
5 0.05264176 <= 28.60
6 0.05266017 <= 62.88
7 0.05266017 <= 124.80
8 0.05262334 <= 207.40
9 0.05288116 <= 338.96
10 0.05262334 <= 510.34
11 0.05264176 <= 792.10
12 0.05262334 <= 1100.54
13 0.05262334 <= 1596.65
14 0.05262334 <= 2388.72
15 0.05262334 <= 3814.56
16 0.05269700 <= 6000.00
17 0.05267859 <= 12858.88
18 0.05262334 <= 33175.75
19 0.05263255 <= 198105.00
20 0.02034033 <= 26978477.50

Statistics for column: "sum_nds_d"
Last update of column statistics: Nov 19 2009 4:30:54:143AM

Range cell density: 0.0001676877377439
Total density: 0.3037604858105269
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "sum_nds_d"
Column datatype: money
Requested step count: 20
Actual step count: 14
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= -1917451.43
2 0.00244931 <= -0.01
3 0.00000000 < 0.00
4 0.55107641 = 0.00
5 0.05300087 <= 8.17
6 0.05266017 <= 25.48
7 0.05264176 <= 69.83
8 0.05549622 <= 175.54
9 0.05262334 <= 386.11
10 0.05277988 <= 829.44
11 0.05262334 <= 2864.29
12 0.05262334 <= 21695.66
13 0.02201617 < 4496412.92
14 0.00000921 = 4496412.92

Statistics for column: "vid_doc"
Last update of column statistics: Nov 8 2010 11:01:30:030AM

Range cell density: 0.0231393230146692
Total density: 0.0907832512043105
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "vid_doc"
Column datatype: integer
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= -1
2 0.10172772 <= 5
3 0.02341495 <= 7
4 0.00000000 < 9
5 0.12558468 = 9
6 0.00000000 < 12
7 0.17822215 = 12
8 0.04025021 <= 20
9 0.00000000 < 23
10 0.10660095 = 23
11 0.09423606 <= 24
12 0.00388850 <= 25
13 0.00000000 < 28
14 0.11115525 = 28
15 0.05305709 <= 41
16 0.02968131 <= 62
17 0.03380480 <= 63
18 0.06757044 <= 83
19 0.03078352 < 99
20 0.00002238 = 99

Statistics for column group: "vid_doc", "dat_uchet"
Last update of column statistics: Nov 8 2010 11:01:30:030AM

Range cell density: 0.0030422542219440
Total density: 0.0030404495339967
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "vid_doc", "dat_uchet", "id_doc"
Last update of column statistics: Mar 31 2008 4:18:24:030PM

Range cell density: 0.0000789452909134
Total density: 0.0000789452909134
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

No statistics for remaining columns: "num_doc"
(default values used)

Optdiag succeeded.

nal_doc
авторServer Message: sybase_ds - Msg 2401, Level 11, State 2:
Character set conversion is not available between client character set 'iso_1' and server character set 'cp1251'.

Server Message: sybase_ds - Msg 2411, Level 10, State 1:
No conversions will be done.

OptDiag/12.5.2/EBF 11798/P/NT (IX86)/OS 4.0/ase1252/1831/32-bit/OPT/Fri Apr 09 04:35:35 2004
Adaptive Server Enterprise/12.5.4/EBF 16831 ESD#9.1/P/x86_64/Enterprise Linux/ase1254/2146/64-bit/OPT/Wed Apr 15 03:57:32 2009

Server name: "sybase_ds"

Specified database: "db1"
Specified table owner: not specified
Specified table: "nal_doc"
Specified column: not specified

Table owner: "dbo"
Table name: "nal_doc"

Statistics for table: "nal_doc"

Data page count: 61225
Empty data page count: 0
Data row count: 2015655.0000000000000000
Forwarded row count: 2831.0000000000000000
Deleted row count: 524.0000000000000000
Data page CR count: 5816.0000000000000000
OAM + allocation page count: 984
First extent data pages: 2351
Data row size: 58.0000000000000000

Derived statistics:
Data page cluster ratio: 0.9999900000000001
Space utilization: 0.9537868419861070
Large I/O efficiency: 0.9999300048996574

Statistics for index: "uc_id_nal_doc" (nonclustered)
Index column list: "id_nal_doc"
Leaf count: 13154
Empty leaf page count: 0
Data page CR count: 7815.0000000000000000
Index page CR count: 1675.0000000000000000
Data row CR count: 61452.0000000000000000
First extent leaf pages: 77
Leaf row size: 11.0000000000000000
Index height: 2

Derived statistics:
Data page cluster ratio: 0.9936273874140902
Index page cluster ratio: 0.9973933443392128
Data row cluster ratio: 0.9991390095644585
Space utilization: 0.8419513054985155
Large I/O efficiency: 0.9820803822851778

Statistics for index: "id_doc" (nonclustered)
Index column list: "id_doc"
Leaf count: 7536
Empty leaf page count: 0
Data page CR count: 59859.0000000000000000
Index page CR count: 4003.0000000000000000
Data row CR count: 63365.0000000000000000
First extent leaf pages: 0
Leaf row size: 6.0364488675450261
Index height: 3

Derived statistics:
Data page cluster ratio: 0.0627258739757398
Index page cluster ratio: 0.5357901122232333
Data row cluster ratio: 0.9981609367003308
Space utilization: 0.8064784434058160
Large I/O efficiency: 0.2353235073694729

Statistics for index: "id_doc_id_recv" (nonclustered)
Index column list: "id_doc", "id_recv"
Leaf count: 17419
Empty leaf page count: 0
Data page CR count: 7867.0000000000000000
Index page CR count: 2240.0000000000000000
Data row CR count: 61837.0000000000000000
First extent leaf pages: 196
Leaf row size: 14.9999082767160450
Index height: 3

Derived statistics:
Data page cluster ratio: 0.9927160357576427
Index page cluster ratio: 0.9959320254576471
Data row cluster ratio: 0.9989421679268792
Space utilization: 0.8669968333952081
Large I/O efficiency: 0.9723125996810208

Statistics for index: "id_recv" (nonclustered)
Index column list: "id_recv"
Leaf count: 13158
Empty leaf page count: 0
Data page CR count: 7788.0000000000000000
Index page CR count: 1680.0000000000000000
Data row CR count: 61635.0000000000000000
First extent leaf pages: 96
Leaf row size: 10.9999606900211620
Index height: 2

Derived statistics:
Data page cluster ratio: 0.9941474041798981
Index page cluster ratio: 0.9969599583079997
Data row cluster ratio: 0.9990454458769857
Space utilization: 0.8416923464377469
Large I/O efficiency: 0.9791631229800988

Statistics for index: "id_recv_id_doc" (clustered)
Index column list: "id_recv", "id_doc"
Leaf count: 19299
Empty data page count: 0
Data page CR count: 7789.0000000000000000
Index page CR count: 2454.0000000000000000
Data row CR count: 61641.0000000000000000
First extent leaf pages: 119
Leaf row size: 14.9999082767160450
Index height: 2

Derived statistics:
Data page cluster ratio: 0.9941295920251062
Index page cluster ratio: 0.9975719530972403
Data row cluster ratio: 0.9990423782151013
Space utilization: 0.7825388797819125
Large I/O efficiency: 0.9832877190939265

Statistics for column: "am_otch"
Last update of column statistics: Nov 19 2009 4:31:29:143AM

Range cell density: 0.0008513528727269
Total density: 0.0019748365519972
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "am_otch"
Column datatype: money
Requested step count: 50
Actual step count: 50
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= -74009.91
2 0.03425406 <= 0.00
3 0.02123984 <= 0.12
4 0.02150190 <= 0.23
5 0.02212169 <= 0.34
6 0.02084477 <= 0.45
7 0.02243290 <= 0.57
8 0.02056305 <= 0.69
9 0.02142394 <= 0.82
10 0.02151435 <= 0.96
11 0.02052308 <= 1.10
12 0.02045102 <= 1.25
13 0.02132697 <= 1.41
14 0.02115925 <= 1.58
15 0.02145473 <= 1.77
16 0.02162638 <= 1.99
17 0.02155759 <= 2.23
18 0.02128373 <= 2.50
19 0.02088539 <= 2.82
20 0.02058795 <= 3.17
21 0.02176790 <= 3.54
22 0.02062988 <= 3.96
23 0.02045036 <= 4.39
24 0.02046019 <= 4.91
25 0.02060170 <= 5.43
26 0.02050671 <= 5.95
27 0.02089325 <= 6.43
28 0.02100660 <= 7.04
29 0.02043071 <= 7.77
30 0.02051981 <= 8.57
31 0.02045626 <= 9.45
32 0.02052571 <= 10.57
33 0.02113501 <= 11.86
34 0.02042612 <= 13.36
35 0.02047067 <= 15.50
36 0.02043660 <= 17.78
37 0.02041629 <= 20.44
38 0.02041957 <= 23.25
39 0.02045888 <= 26.46
40 0.02042219 <= 30.00
41 0.02088670 <= 34.20
42 0.02041891 <= 40.16
43 0.02044250 <= 49.41
44 0.02051653 <= 68.95
45 0.02041695 <= 104.54
46 0.02040909 <= 175.54
47 0.02040909 <= 460.54
48 0.02040843 <= 3642.06
49 0.00690414 < 394482.62
50 0.00000066 = 394482.62

Statistics for column: "id_doc"
Last update of column statistics: Nov 19 2009 4:31:18:143AM

Range cell density: 0.0137999510547972
Total density: 0.0137999510547972
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_doc"
Column datatype: integer
Requested step count: 20
Actual step count: 19
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 2004000040
2 0.05872911 <= 2004000416
3 0.06131833 <= 2004000872
4 0.06261686 <= 2005000163
5 0.06556773 <= 2005000710
6 0.05437422 <= 2005001396
7 0.05639737 <= 2006000440
8 0.05739257 <= 2006000808
9 0.05762188 <= 2006001122
10 0.05698112 <= 2007000264
11 0.05656641 <= 2007000662
12 0.05765529 <= 2007001156
13 0.05901542 <= 2008008718
14 0.05964437 <= 2008019916
15 0.06116960 <= 2009001403
16 0.05955724 <= 2009014707
17 0.06504491 <= 2009038275
18 0.05034691 < 2009062447
19 0.00000066 = 2009062447

Statistics for column group: "id_doc", "id_recv"
Last update of column statistics: Nov 19 2009 4:31:18:143AM

Range cell density: 0.0000006551834837
Total density: 0.0000006551834837
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "id_nal_doc"
Last update of column statistics: Nov 19 2009 4:31:14:143AM

Range cell density: 0.0000006551663140
Total density: 0.0000006551663140
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_nal_doc"
Column datatype: integer
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 2004000010
2 0.05263148 <= 2004080488
3 0.05263148 <= 2004160945
4 0.05263148 <= 2005038751
5 0.05263148 <= 2005119187
6 0.05263148 <= 2005199572
7 0.05263148 <= 2006019890
8 0.05263148 <= 2006100290
9 0.05263148 <= 2006190548
10 0.05263148 <= 2006270904
11 0.05263148 <= 2007080877
12 0.05263148 <= 2007161241
13 0.05263148 <= 2007241601
14 0.05263148 <= 2008059473
15 0.05263148 <= 2008139976
16 0.05263148 <= 2008220428
17 0.05263148 <= 2009060724
18 0.05263148 <= 2009141211
19 0.05263148 <= 2009221918
20 0.05263344 <= 2009302420

Statistics for column: "id_recv"
Last update of column statistics: Nov 19 2009 4:31:22:143AM

Range cell density: 0.0000006551834837
Total density: 0.0000006551834837
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_recv"
Column datatype: integer
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 2004034479
2 0.05263148 <= 2004162434
3 0.05263148 <= 2004283905
4 0.05263148 <= 2005056708
5 0.05263148 <= 2005174875
6 0.05263148 <= 2005289176
7 0.05263148 <= 2006020179
8 0.05263148 <= 2006147091
9 0.05263148 <= 2006270879
10 0.05263148 <= 2006388098
11 0.05263148 <= 2007107965
12 0.05263148 <= 2007235045
13 0.05263148 <= 2007368676
14 0.05263148 <= 2008113969
15 0.05263148 <= 2008334983
16 0.05263148 <= 2008584668
17 0.05263148 <= 2009141880
18 0.05263148 <= 2009318741
19 0.05263148 <= 2009528636
20 0.05263344 <= 2009793478

Statistics for column group: "id_recv", "id_doc"
Last update of column statistics: Nov 19 2009 4:31:22:143AM

Range cell density: 0.0000006551834837
Total density: 0.0000006551834837
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "prix_bal"
Last update of column statistics: Nov 19 2009 4:31:49:143AM

Range cell density: 0.0000286102855822
Total density: 0.9594949765516599
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "prix_bal"
Column datatype: money
Requested step count: 50
Actual step count: 6
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= -3671188.60
2 0.00688973 <= -0.02
3 0.00000000 < 0.00
4 0.97953784 = 0.00
5 0.01357177 < 19724131.00
6 0.00000066 = 19724131.00

Statistics for column: "ras_bal"
Last update of column statistics: Nov 19 2009 4:31:44:143AM

Range cell density: 0.0000077858337655
Total density: 0.9980171480330967
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "ras_bal"
Column datatype: money
Requested step count: 50
Actual step count: 6
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= -19574.92
2 0.00001965 <= -0.02
3 0.00000000 < 0.00
4 0.99900806 = 0.00
5 0.00097161 < 1739346.63
6 0.00000066 = 1739346.63

Statistics for column: "val_dox"
Last update of column statistics: Nov 19 2009 4:31:39:143AM

Range cell density: 0.0000000000000000
Total density: 1.0000000000000000
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "val_dox"
Column datatype: money
Requested step count: 50
Actual step count: 2
Sampling Percent: 0

Step Weight Value

1 0.00000000 < 0.00
2 1.00000000 = 0.00

Statistics for column: "val_ras"
Last update of column statistics: Nov 19 2009 4:31:34:143AM

Range cell density: 0.0000056455820672
Total density: 0.9998152521547690
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "val_ras"
Column datatype: money
Requested step count: 50
Actual step count: 6
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= -83438.97
2 0.00003734 <= -79.98
3 0.00000000 < 0.00
4 0.99990761 = 0.00
5 0.00005438 < 41118.90
6 0.00000066 = 41118.90

No statistics for remaining columns: "id_nal_zatr"
(default values used) "kor_iznos"

Optdiag succeeded.

recv_doc
авторServer Message: sybase_ds - Msg 2401, Level 11, State 2:
Character set conversion is not available between client character set 'iso_1' and server character set 'cp1251'.

Server Message: sybase_ds - Msg 2411, Level 10, State 1:
No conversions will be done.

OptDiag/12.5.2/EBF 11798/P/NT (IX86)/OS 4.0/ase1252/1831/32-bit/OPT/Fri Apr 09 04:35:35 2004
Adaptive Server Enterprise/12.5.4/EBF 16831 ESD#9.1/P/x86_64/Enterprise Linux/ase1254/2146/64-bit/OPT/Wed Apr 15 03:57:32 2009

Server name: "sybase_ds"

Specified database: "db1"
Specified table owner: not specified
Specified table: "recv_doc"
Specified column: not specified

Table owner: "dbo"
Table name: "recv_doc"

Statistics for table: "recv_doc"

Data page count: 141940
Empty data page count: 0
Data row count: 5368774.0000000000000000
Forwarded row count: 0.0000000000000000
Deleted row count: 24665.0000000000000000
Data page CR count: 12918.0000000000000000
OAM + allocation page count: 2783
First extent data pages: 2135
Data row size: 44.3384860711582720

Derived statistics:
Data page cluster ratio: 0.9999900000000001
Space utilization: 0.8376972865734965
Large I/O efficiency: 0.9999300048996574

Statistics for index: "recv_doc_id_rec_1001954072" (nonclustered)
Index column list: "id_recv"
Leaf count: 37786
Empty leaf page count: 0
Data page CR count: 50305.0000000000000000
Index page CR count: 5444.0000000000000000
Data row CR count: 230971.0000000000000000
First extent leaf pages: 438
Leaf row size: 11.0000000000000000
Index height: 3

Derived statistics:
Data page cluster ratio: 0.8472902245483708
Index page cluster ratio: 0.9782227330470026
Data row cluster ratio: 0.9829665529840818
Space utilization: 0.7806795702577210
Large I/O efficiency: 0.8677234790824627

Statistics for index: "id_doc_kod_recv" (nonclustered)
Index column list: "id_doc", "kod_recv"
Leaf count: 31895
Empty leaf page count: 0
Data page CR count: 195650.0000000000000000
Index page CR count: 7740.0000000000000000
Data row CR count: 376368.0000000000000000
First extent leaf pages: 431
Leaf row size: 7.4046993446365095
Index height: 3

Derived statistics:
Data page cluster ratio: 0.5039191355873126
Index page cluster ratio: 0.8655224308442023
Data row cluster ratio: 0.9551491399956456
Space utilization: 0.6225810138959885
Large I/O efficiency: 0.5151073294080731

Statistics for index: "id_doc_parn_id_id_recv" (clustered)
Index column list: "id_doc", "parn_id", "id_recv"
Leaf count: 65948
Empty data page count: 0
Data page CR count: 108192.0000000000000000
Index page CR count: 12257.0000000000000000
Data row CR count: 286907.0000000000000000
First extent leaf pages: 721
Leaf row size: 16.1692430355791360
Index height: 3

Derived statistics:
Data page cluster ratio: 0.6639632343106805
Index page cluster ratio: 0.9304554276999861
Data row cluster ratio: 0.9722648547858991
Space utilization: 0.6575050746025548
Large I/O efficiency: 0.6725799871787400

Statistics for index: "id_recv_parn_id" (nonclustered)
Index column list: "id_recv", "parn_id"
Leaf count: 41400
Empty leaf page count: 1
Data page CR count: 51168.0000000000000000
Index page CR count: 6009.0000000000000000
Data row CR count: 231957.0000000000000000
First extent leaf pages: 506
Leaf row size: 12.1692430355791360
Index height: 3

Derived statistics:
Data page cluster ratio: 0.8439644467681851
Index page cluster ratio: 0.9769772256728778
Data row cluster ratio: 0.9827779110643269
Space utilization: 0.7882686831175870
Large I/O efficiency: 0.8612081877184221

Statistics for index: "kod_recv" (nonclustered)
Index column list: "kod_recv"
Leaf count: 24977
Empty leaf page count: 0
Data page CR count: 474604.0000000000000000
Index page CR count: 23626.0000000000000000
Data row CR count: 474913.0000000000000000
First extent leaf pages: 10
Leaf row size: 6.0289793178708821
Index height: 3

Derived statistics:
Data page cluster ratio: 0.0006758973685937
Index page cluster ratio: 0.0618193465727098
Data row cluster ratio: 0.9362954706424578
Space utilization: 0.6473133586260584
Large I/O efficiency: 0.1321481481481482

Statistics for index: "kod_recv_id_doc_id_recv" (nonclustered)
Index column list: "kod_recv", "id_doc", "id_recv"
Leaf count: 75061
Empty leaf page count: 0
Data page CR count: 132593.0000000000000000
Index page CR count: 23464.0000000000000000
Data row CR count: 513442.0000000000000000
First extent leaf pages: 1208
Leaf row size: 19.0000000000000000
Index height: 3

Derived statistics:
Data page cluster ratio: 0.7683069766128235
Index page cluster ratio: 0.7856055300100491
Data row cluster ratio: 0.9289240867416106
Space utilization: 0.6788132398902306
Large I/O efficiency: 0.3998782306919542

Statistics for index: "kod_recv_parn_id" (nonclustered)
Index column list: "kod_recv", "parn_id"
Leaf count: 27900
Empty leaf page count: 0
Data page CR count: 447474.0000000000000000
Index page CR count: 22646.0000000000000000
Data row CR count: 517367.0000000000000000
First extent leaf pages: 26
Leaf row size: 6.4530946394281354
Index height: 3

Derived statistics:
Data page cluster ratio: 0.1398911981810321
Index page cluster ratio: 0.2152220219564149
Data row cluster ratio: 0.9281731541502944
Space utilization: 0.6202615792755837
Large I/O efficiency: 0.1540014383224618

Statistics for index: "kod_recv_value_recv" (nonclustered)
Index column list: "kod_recv", "value_recv"
Leaf count: 35544
Empty leaf page count: 0
Data page CR count: 4590166.0000000000000000
Index page CR count: 27376.0000000000000000
Data row CR count: 4680878.0000000000000000
First extent leaf pages: 80
Leaf row size: 7.7148220498329483
Index height: 3

Derived statistics:
Data page cluster ratio: 0.0194530074724408
Index page cluster ratio: 0.2626282113115334
Data row cluster ratio: 0.1316085416142927
Space utilization: 0.5820638965436128
Large I/O efficiency: 0.1622954412624197

Statistics for column: "id_doc"
Last update of column statistics: Nov 19 2009 4:36:30:143AM

Range cell density: 0.0034538888510573
Total density: 0.0034538888510573
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_doc"
Column datatype: integer
Requested step count: 10
Actual step count: 10
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 2001000141
2 0.11116349 <= 2002000320
3 0.11325160 <= 2003000495
4 0.11349939 <= 2004000872
5 0.11510854 <= 2006000075
6 0.11140634 <= 2007000217
7 0.11163051 <= 2008004987
8 0.11111108 <= 2008038984
9 0.11632358 <= 2009027256
10 0.09650545 <= 2009064428

Statistics for column group: "id_doc", "parn_id"
Last update of column statistics: Nov 19 2009 4:36:30:143AM

Range cell density: 0.0034535947750785
Total density: 0.0000006274238300
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "id_doc", "parn_id", "id_recv"
Last update of column statistics: Nov 19 2009 4:36:30:143AM

Range cell density: 0.0000002594574175
Total density: 0.0000002594574175
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "id_doc", "kod_recv"
Last update of column statistics: Nov 19 2009 4:36:28:143AM

Range cell density: 0.0034534764474056
Total density: 0.0034534764474056
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "id_recv"
Last update of column statistics: Nov 19 2009 4:36:33:143AM

Range cell density: 0.0000002594574175
Total density: 0.0000002594574175
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_recv"
Column datatype: integer
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 1990000000
2 0.05263146 <= 2001289680
3 0.05263146 <= 2002163091
4 0.05263146 <= 2002391949
5 0.05263146 <= 2003242939
6 0.05263146 <= 2004028961
7 0.05263146 <= 2004232038
8 0.05263146 <= 2005077179
9 0.05263146 <= 2005280577
10 0.05263146 <= 2006101492
11 0.05263146 <= 2006314545
12 0.05263146 <= 2007128302
13 0.05263146 <= 2007331551
14 0.05263146 <= 2008163032
15 0.05263146 <= 2008381519
16 0.05263146 <= 2008610797
17 0.05263146 <= 2009151567
18 0.05263146 <= 2009364142
19 0.05263146 <= 2009580562
20 0.05263379 <= 2009805415

Statistics for column group: "id_recv", "parn_id"
Last update of column statistics: Nov 19 2009 4:36:33:143AM

Range cell density: 0.0000002594574175
Total density: 0.0000002594574175
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "id_shabl"
Last update of column statistics: Nov 19 2009 4:37:34:143AM

Range cell density: 0.0044946596924149
Total density: 0.6525053202717304
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "id_shabl"
Column datatype: integer
Requested step count: 20
Actual step count: 7
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 12
2 0.05740521 <= 119
3 0.06176436 <= 147
4 0.02558094 <= 199
5 0.80724156 <= 200
6 0.04797601 < 325
7 0.00003191 = 325

Statistics for column group: "id_shabl", "id_recv"
Last update of column statistics: Mar 31 2008 3:51:23:430PM

Range cell density: 0.0000003825079131
Total density: 0.0000003825079131
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "id_shabl", "id_doc"
Last update of column statistics: Mar 31 2008 4:07:55:236PM

Range cell density: 0.0057708637951592
Total density: 0.0057708637951592
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "kod_recv"
Last update of column statistics: Nov 19 2009 4:36:46:143AM

Range cell density: 0.0094326619430347
Total density: 0.7051629262994644
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "kod_recv"
Column datatype: integer
Requested step count: 20
Actual step count: 7
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 1
2 0.05296097 <= 15
3 0.03035315 <= 26
4 0.83883411 <= 27
5 0.05808473 <= 59
6 0.01964300 < 69
7 0.00012402 = 69

Statistics for column group: "kod_recv", "parn_id"
Last update of column statistics: Nov 19 2009 4:36:44:143AM

Range cell density: 0.0070166787068791
Total density: 0.0000002594605995
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "kod_recv", "id_doc"
Last update of column statistics: Nov 19 2009 4:36:40:143AM

Range cell density: 0.0034534764474056
Total density: 0.0034534764474056
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "kod_recv", "id_doc", "id_recv"
Last update of column statistics: Nov 19 2009 4:36:40:143AM

Range cell density: 0.0000002594574175
Total density: 0.0000002594574175
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column group: "kod_recv", "value_recv"
Last update of column statistics: Nov 19 2009 4:36:46:143AM

Range cell density: 0.0001320328452696
Total density: 0.0001320328452696
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Statistics for column: "parn_id"
Last update of column statistics: Nov 19 2009 4:37:48:143AM

Range cell density: 0.0000006274238300
Total density: 0.0000006274238300
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "parn_id"
Column datatype: integer
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.95768923 <= 2001056305
2 0.00235068 <= 2008147045
3 0.00235042 <= 2008252204
4 0.00235042 <= 2008358844
5 0.00235042 <= 2008481600
6 0.00235042 <= 2008518430
7 0.00235068 <= 2008592368
8 0.00235042 <= 2008666613
9 0.00235068 <= 2009094610
10 0.00235042 <= 2009171826
11 0.00235068 <= 2009262604
12 0.00235068 <= 2009331893
13 0.00235042 <= 2009398724
14 0.00235068 <= 2009469033
15 0.00235068 <= 2009541517
16 0.00235042 <= 2009615317
17 0.00235042 <= 2009687008
18 0.00235042 <= 2009761749
19 0.00235068 <= 2009805249
20 0.00000104 <= 2009805409

Statistics for column: "sign_an"
Last update of column statistics: Nov 19 2009 4:37:00:143AM

Range cell density: 0.0109563678244781
Total density: 0.9783273483428543
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "sign_an"
Column datatype: numeric(1,0)
Requested step count: 20
Actual step count: 4
Sampling Percent: 0

Step Weight Value

1 0.00000000 < 0
2 0.98904365 = 0
3 0.00000000 < 8
4 0.01095637 = 8

Statistics for column: "value_recv"
Last update of column statistics: Nov 19 2009 4:37:16:143AM

Range cell density: 0.0003232484846743
Total density: 0.0003232484846743
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "value_recv"
Column datatype: char(20)
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= " "
2 0.05286575 <= "2001000157 "
3 0.05263457 <= "2001014321 "
4 0.05263172 <= "2001016923 "
5 0.05263768 <= "2001018253 "
6 0.05265273 <= "2001019598 "
7 0.05263197 <= "2001021246 "
8 0.05267167 <= "2001022783 "
9 0.05265896 <= "2001024093 "
10 0.05265273 <= "2001026051 "
11 0.05265740 <= "2001027886 "
12 0.05265740 <= "2002001854 "
13 0.05265429 <= "2003000163 "
14 0.05265636 <= "2003002353 "
15 0.05263301 <= "2004001856 "
16 0.05264313 <= "2005002936 "
17 0.05263431 <= "2007004282 "
18 0.05263249 <= "2009013598 "
19 0.05263146 <= "2992.32 "
20 0.05216236 <= "справкв см 01-03 - 7"

Optdiag succeeded.

cherrex_DenImperous,

Imperousделал даже reorg rebuid и затем на всякий случай update statistics по нужным таблицам - не помогло.
оптимизатор тупо делает ерунду и спасает только форсплан он

что то не заметно:

авторStatistics for column: "dat_doc"
Last update of column statistics: Nov 19 2009 4:30:55:143AM
.
.
.
Statistics for column group: "dat_uchet", "id_doc"
Last update of column statistics: Nov 8 2010 11:01:28:030AM
.
.
.
итд
знал что этот вопрос последует.
дело в том что я эти действия проделывал на более значимых таблицах, и результат был нулевой.
для проверки конечно же сделал и по этим таблицах, и опять же оптимизатор посчитал что выборки нужно строить иначе.
т.е. как ни крути, дело не в статистике.

и пробовал тупо говорить ему форсплан он, и делать запрос чтоб асе себе записал что план должен быть именно такой, потом выключал форсплан, запускал по-новой и он все равно перестраивал план запроса.

и на всяк случай делал еще sp_recompile по этим таблицам - и опять меня ждал облом, который кстати возможно сделает даже еще хуже так как перестроит готовые нормальные планы...

cherrex_DenImperous,

а что будет если удалить статистику? delete stattistics.

на первый взгляд - супер!!!
отработало все очень шустро и с нормальным планом!
пасиба!
будем смотреть че там дальше будет.


зы
вот чего не ожидал того не ожидал!

может грохнуть теперь всю статистику???
ну одно утро юзеры немного подождут пока сервер будет разогреваться...

MasterZivOn 18.07.2011 14:38, Imperous wrote:
> может грохнуть теперь всю статистику???

Если хочешь, чтобы тебя уволили -- давай.

> ну одно утро юзеры немного подождут пока сервер будет разогреваться...

В смысле, перед тем как расплавиться ?


не драматизируйте, в данном случае это сделать можно.

или есть таки что-то, что может очень плохо сказаться?