Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[Javier Cintron fcintr...@yahoo.com [firebird-support]]

 Thank you for your help. It was very helpful!


On Sunday, May 13, 2018, 12:50:26 AM CDT, Helen Borrie hele...@iinet.net.au 
[firebird-support]  wrote:  
 
     
Javier wrote:
>
> Ok, but how do I restore the statistics? Using "update" statements?

You do not "restore the statistics". The statistic that gets updated
by a SET STATISTICS call is the selectivity of the index. Low value =
high selectivity = Good; high value = low selectivity = bad. The
value immediately after the call reflects the state of the index at
that point. So, at that point, the optimizer has the best chance to
prepare the most efficient plan by deciding which indexes will be the
most helpful.

After that, the actual condition of an index selected for the plan may
degrade gradually as rows are inserted and deleted. When you start to
notice a decline in the performance of queries over a table with a
high level of inserts and deletes, it is probably time to run SET
STATISTICS again. Selectivity is not static - it changes
infinitessimally with each insertion and deletion, or dramatically
with a major bulk insert or delete. Eventually, the statistic may
tend to become too outdated to obtain the same level of performance as
when the numbers were fresh.

The optimizer always calculates a plan based on the most recently
calculated statistics. What you can do is run the queries in isql with
SET PLANONLY while the statistics are fresh. That will tell you the
optimizer's choices based on the current selectivity and record count.

You can save that plan in a text file. If you decide from your test
results that you like it, you can copy it into your application
queries using the optional PLAN clause. If you supply a plan in your
select query, the optimizer will not try to create one. That might or
might not work positively over time, of course.

On the whole, the optimizer is going to produce the most effective
plan, since it is aware of the current size if the table and may be
able to make dynamic adjustments that your static plan cannot take
into account.

Hth.
Helen


  #yiv1957919616 #yiv1957919616 -- #yiv1957919616ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1957919616 
#yiv1957919616ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1957919616 
#yiv1957919616ygrp-mkp #yiv1957919616hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv1957919616 #yiv1957919616ygrp-mkp #yiv1957919616ads 
{margin-bottom:10px;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad 
{padding:0 0;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad p 
{margin:0;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad a 
{color:#ff;text-decoration:none;}#yiv1957919616 #yiv1957919616ygrp-sponsor 
#yiv1957919616ygrp-lc {font-family:Arial;}#yiv1957919616 
#yiv1957919616ygrp-sponsor #yiv1957919616ygrp-lc #yiv1957919616hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1957919616 
#yiv1957919616ygrp-sponsor #yiv1957919616ygrp-lc .yiv1957919616ad 
{margin-bottom:10px;padding:0 0;}#yiv1957919616 #yiv1957919616actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1957919616 
#yiv1957919616activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1957919616
 #yiv1957919616activity span {font-weight:700;}#yiv1957919616 
#yiv1957919616activity span:first-child 
{text-transform:uppercase;}#yiv1957919616 #yiv1957919616activity span a 
{color:#5085b6;text-decoration:none;}#yiv1957919616 #yiv1957919616activity span 
span {color:#ff7900;}#yiv1957919616 #yiv1957919616activity span 
.yiv1957919616underline {text-decoration:underline;}#yiv1957919616 
.yiv1957919616attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv1957919616 .yiv1957919616attach div a 
{text-decoration:none;}#yiv1957919616 .yiv1957919616attach img 
{border:none;padding-right:5px;}#yiv1957919616 .yiv1957919616attach label 
{display:block;margin-bottom:5px;}#yiv1957919616 .yiv1957919616attach label a 
{text-decoration:none;}#yiv1957919616 blockquote {margin:0 0 0 
4px;}#yiv1957919616 .yiv1957919616bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1957919616 
.yiv1957919616bold a {text-decoration:none;}#yiv1957919616 dd.yiv1957919616last 
p a {font-family:Verdana;font-weight:700;}#yiv1957919616 dd.yiv1957919616last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1957919616 
dd.yiv1957919616last p span.yiv1957919616yshortcuts 
{margin-right:0;}#yiv1957919616 div.yiv1957919616attach-table div div a 
{text-decoration:none;}#yiv1957919616 div.yiv1957919616attach-table 
{width:400px;}#yiv1957919616 div.yiv1957919616file-title a, #yiv1957919616 
div.yiv1957919616file-title a:active, #yiv1957919616 
div.yiv1957919616file-title a:hover, #yiv1957919616 div.yiv1957919616file-title 
a:visited {text-decoration:none;}#yiv1957919616 div.yiv1957919616photo-title a, 

Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Javier wrote:
>
> Ok, but how do I restore the statistics?  Using "update" statements?

You do not "restore the statistics".  The statistic that gets updated
by a SET STATISTICS call is the selectivity of the index.  Low value =
high selectivity = Good;  high value = low selectivity = bad.  The
value immediately after the call reflects the state of the index at
that point.  So, at that point, the optimizer has the best chance to
prepare the most efficient plan by deciding which indexes will be the
most helpful.

After that, the actual condition of an index selected for the plan may
degrade gradually as rows are inserted and deleted. When you start to
notice a decline in the performance of queries over a table with a
high level of inserts and deletes, it is probably time to run SET
STATISTICS again.  Selectivity is not static - it changes
infinitessimally with each insertion and deletion, or dramatically
with a major bulk insert or delete.  Eventually, the statistic may
tend to become too outdated to obtain the same level of performance as
when the numbers were fresh.

The optimizer always calculates a plan based on the most recently
calculated statistics. What you can do is run the queries in isql with
SET PLANONLY while the statistics are fresh.  That will tell you the
optimizer's choices based on the current selectivity and record count.

You can save that plan in a text file.  If you decide from your test
results that you like it, you can copy it into your application
queries using the optional PLAN clause. If you supply a plan in your
select query, the optimizer will not try to create one.  That might or
might not work positively over time, of course.

On the whole, the optimizer is going to produce the most effective
plan, since it is aware of the current size if the table and may be
able to make dynamic adjustments that your static plan cannot take
into account.

Hth.
Helen

Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[Javier Cintron fcintr...@yahoo.com [firebird-support]]

 Ok, thank you very much!
On Saturday, May 12, 2018, 11:42:52 AM CDT, liviuslivius 
liviusliv...@poczta.onet.pl [firebird-support] 
 wrote:  
 
     

Hi,
i do not suppose that this is possible and especially that you should do this. 
Better then look at queries and addapt it to real situation.


Regards,Karol Bieniaszewski  #yiv2598329866 #yiv2598329866 -- 
#yiv2598329866ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 
0;padding:0 10px;}#yiv2598329866 #yiv2598329866ygrp-mkp hr {border:1px solid 
#d8d8d8;}#yiv2598329866 #yiv2598329866ygrp-mkp #yiv2598329866hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv2598329866 #yiv2598329866ygrp-mkp #yiv2598329866ads 
{margin-bottom:10px;}#yiv2598329866 #yiv2598329866ygrp-mkp .yiv2598329866ad 
{padding:0 0;}#yiv2598329866 #yiv2598329866ygrp-mkp .yiv2598329866ad p 
{margin:0;}#yiv2598329866 #yiv2598329866ygrp-mkp .yiv2598329866ad a 
{color:#ff;text-decoration:none;}#yiv2598329866 #yiv2598329866ygrp-sponsor 
#yiv2598329866ygrp-lc {font-family:Arial;}#yiv2598329866 
#yiv2598329866ygrp-sponsor #yiv2598329866ygrp-lc #yiv2598329866hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2598329866 
#yiv2598329866ygrp-sponsor #yiv2598329866ygrp-lc .yiv2598329866ad 
{margin-bottom:10px;padding:0 0;}#yiv2598329866 #yiv2598329866actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2598329866 
#yiv2598329866activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2598329866
 #yiv2598329866activity span {font-weight:700;}#yiv2598329866 
#yiv2598329866activity span:first-child 
{text-transform:uppercase;}#yiv2598329866 #yiv2598329866activity span a 
{color:#5085b6;text-decoration:none;}#yiv2598329866 #yiv2598329866activity span 
span {color:#ff7900;}#yiv2598329866 #yiv2598329866activity span 
.yiv2598329866underline {text-decoration:underline;}#yiv2598329866 
.yiv2598329866attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv2598329866 .yiv2598329866attach div a 
{text-decoration:none;}#yiv2598329866 .yiv2598329866attach img 
{border:none;padding-right:5px;}#yiv2598329866 .yiv2598329866attach label 
{display:block;margin-bottom:5px;}#yiv2598329866 .yiv2598329866attach label a 
{text-decoration:none;}#yiv2598329866 blockquote {margin:0 0 0 
4px;}#yiv2598329866 .yiv2598329866bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv2598329866 
.yiv2598329866bold a {text-decoration:none;}#yiv2598329866 dd.yiv2598329866last 
p a {font-family:Verdana;font-weight:700;}#yiv2598329866 dd.yiv2598329866last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2598329866 
dd.yiv2598329866last p span.yiv2598329866yshortcuts 
{margin-right:0;}#yiv2598329866 div.yiv2598329866attach-table div div a 
{text-decoration:none;}#yiv2598329866 div.yiv2598329866attach-table 
{width:400px;}#yiv2598329866 div.yiv2598329866file-title a, #yiv2598329866 
div.yiv2598329866file-title a:active, #yiv2598329866 
div.yiv2598329866file-title a:hover, #yiv2598329866 div.yiv2598329866file-title 
a:visited {text-decoration:none;}#yiv2598329866 div.yiv2598329866photo-title a, 
#yiv2598329866 div.yiv2598329866photo-title a:active, #yiv2598329866 
div.yiv2598329866photo-title a:hover, #yiv2598329866 
div.yiv2598329866photo-title a:visited {text-decoration:none;}#yiv2598329866 
div#yiv2598329866ygrp-mlmsg #yiv2598329866ygrp-msg p a 
span.yiv2598329866yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2598329866 
.yiv2598329866green {color:#628c2a;}#yiv2598329866 .yiv2598329866MsoNormal 
{margin:0 0 0 0;}#yiv2598329866 o {font-size:0;}#yiv2598329866 
#yiv2598329866photos div {float:left;width:72px;}#yiv2598329866 
#yiv2598329866photos div div {border:1px solid 
#66;min-height:62px;overflow:hidden;width:62px;}#yiv2598329866 
#yiv2598329866photos div label 
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2598329866
 #yiv2598329866reco-category {font-size:77%;}#yiv2598329866 
#yiv2598329866reco-desc {font-size:77%;}#yiv2598329866 .yiv2598329866replbq 
{margin:4px;}#yiv2598329866 #yiv2598329866ygrp-actbar div a:first-child 
{margin-right:2px;padding-right:5px;}#yiv2598329866 #yiv2598329866ygrp-mlmsg 
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2598329866 
#yiv2598329866ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2598329866 
#yiv2598329866ygrp-mlmsg select, #yiv2598329866 input, #yiv2598329866 textarea 
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv2598329866 
#yiv2598329866ygrp-mlmsg pre, #yiv2598329866 code {font:115% 
monospace;}#yiv2598329866 #yiv2598329866ygrp-mlmsg * 
{line-height:1.22em;}#yiv2598329866 #yiv2598329866ygrp-mlmsg #yiv2598329866logo 
{padding-bottom:10px;}#yiv2598329866 #yiv2598329866ygrp-msg p a 
{font-family:Verdana;}#yiv2598329866 #yiv2598329866ygrp-msg 

Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
i do not suppose that this is possible and especially that you should do this. 
Better then look at queries and addapt it to real situation.


Regards,Karol Bieniaszewski
null

Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[Javier Cintron fcintr...@yahoo.com [firebird-support]]

 Ok, but how do I restore the statistics?  Using "update" statements?
Thanks in advance for your help. 


On Saturday, May 12, 2018, 1:19:10 AM CDT, liviuslivius 
liviusliv...@poczta.onet.pl [firebird-support] 
 wrote:  
 
     

Hi,
to "backup" statistics you can run query on rdb$indices and rdb$index_segment 
tables. And save result to the file.
About table statistics - here is really fine concept that you do not need to 
recalculate anything. Table pages count and record size is taken in calc. In 
fb3 also record compression level.
Regards,Karol Bieniaszewski  #yiv0641671250 #yiv0641671250 -- 
#yiv0641671250ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 
0;padding:0 10px;}#yiv0641671250 #yiv0641671250ygrp-mkp hr {border:1px solid 
#d8d8d8;}#yiv0641671250 #yiv0641671250ygrp-mkp #yiv0641671250hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv0641671250 #yiv0641671250ygrp-mkp #yiv0641671250ads 
{margin-bottom:10px;}#yiv0641671250 #yiv0641671250ygrp-mkp .yiv0641671250ad 
{padding:0 0;}#yiv0641671250 #yiv0641671250ygrp-mkp .yiv0641671250ad p 
{margin:0;}#yiv0641671250 #yiv0641671250ygrp-mkp .yiv0641671250ad a 
{color:#ff;text-decoration:none;}#yiv0641671250 #yiv0641671250ygrp-sponsor 
#yiv0641671250ygrp-lc {font-family:Arial;}#yiv0641671250 
#yiv0641671250ygrp-sponsor #yiv0641671250ygrp-lc #yiv0641671250hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0641671250 
#yiv0641671250ygrp-sponsor #yiv0641671250ygrp-lc .yiv0641671250ad 
{margin-bottom:10px;padding:0 0;}#yiv0641671250 #yiv0641671250actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0641671250 
#yiv0641671250activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0641671250
 #yiv0641671250activity span {font-weight:700;}#yiv0641671250 
#yiv0641671250activity span:first-child 
{text-transform:uppercase;}#yiv0641671250 #yiv0641671250activity span a 
{color:#5085b6;text-decoration:none;}#yiv0641671250 #yiv0641671250activity span 
span {color:#ff7900;}#yiv0641671250 #yiv0641671250activity span 
.yiv0641671250underline {text-decoration:underline;}#yiv0641671250 
.yiv0641671250attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv0641671250 .yiv0641671250attach div a 
{text-decoration:none;}#yiv0641671250 .yiv0641671250attach img 
{border:none;padding-right:5px;}#yiv0641671250 .yiv0641671250attach label 
{display:block;margin-bottom:5px;}#yiv0641671250 .yiv0641671250attach label a 
{text-decoration:none;}#yiv0641671250 blockquote {margin:0 0 0 
4px;}#yiv0641671250 .yiv0641671250bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0641671250 
.yiv0641671250bold a {text-decoration:none;}#yiv0641671250 dd.yiv0641671250last 
p a {font-family:Verdana;font-weight:700;}#yiv0641671250 dd.yiv0641671250last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0641671250 
dd.yiv0641671250last p span.yiv0641671250yshortcuts 
{margin-right:0;}#yiv0641671250 div.yiv0641671250attach-table div div a 
{text-decoration:none;}#yiv0641671250 div.yiv0641671250attach-table 
{width:400px;}#yiv0641671250 div.yiv0641671250file-title a, #yiv0641671250 
div.yiv0641671250file-title a:active, #yiv0641671250 
div.yiv0641671250file-title a:hover, #yiv0641671250 div.yiv0641671250file-title 
a:visited {text-decoration:none;}#yiv0641671250 div.yiv0641671250photo-title a, 
#yiv0641671250 div.yiv0641671250photo-title a:active, #yiv0641671250 
div.yiv0641671250photo-title a:hover, #yiv0641671250 
div.yiv0641671250photo-title a:visited {text-decoration:none;}#yiv0641671250 
div#yiv0641671250ygrp-mlmsg #yiv0641671250ygrp-msg p a 
span.yiv0641671250yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0641671250 
.yiv0641671250green {color:#628c2a;}#yiv0641671250 .yiv0641671250MsoNormal 
{margin:0 0 0 0;}#yiv0641671250 o {font-size:0;}#yiv0641671250 
#yiv0641671250photos div {float:left;width:72px;}#yiv0641671250 
#yiv0641671250photos div div {border:1px solid 
#66;min-height:62px;overflow:hidden;width:62px;}#yiv0641671250 
#yiv0641671250photos div label 
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0641671250
 #yiv0641671250reco-category {font-size:77%;}#yiv0641671250 
#yiv0641671250reco-desc {font-size:77%;}#yiv0641671250 .yiv0641671250replbq 
{margin:4px;}#yiv0641671250 #yiv0641671250ygrp-actbar div a:first-child 
{margin-right:2px;padding-right:5px;}#yiv0641671250 #yiv0641671250ygrp-mlmsg 
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0641671250 
#yiv0641671250ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0641671250 
#yiv0641671250ygrp-mlmsg select, #yiv0641671250 input, #yiv0641671250 textarea 
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv0641671250 
#yiv0641671250ygrp-mlmsg pre, #yiv0641671250 code {font:115% 
monospace;}#yiv0641671250 

Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
to "backup" statistics you can run query on rdb$indices and rdb$index_segment 
tables. And save result to the file.
About table statistics - here is really fine concept that you do not need to 
recalculate anything. Table pages count and record size is taken in calc. In 
fb3 also record compression level.
Regards,Karol Bieniaszewski
null

[firebird-support] Doubts regarding statistics of indexes and tables...

[Javier Cintron fcintr...@yahoo.com [firebird-support]]

Firebird ver 2.5.7.27050 64 bitsSuperserverWindows Server 2012R2

In firebird, how do you calculate statistics to feed the query optimizer? 

Note: I know that the command: 'set statistics index ;' calculate 
statistics for indexes, but what about tables for example?

And my second question:
is there any way to backup the statistics before recalculate them?
Note: This backup can be very useful if the new statistics create a performance 
problem
Thanks in advance for your help