[
https://issues.apache.org/jira/browse/DERBY-5680?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13258752#comment-13258752
]
Mamta A. Satoor commented on DERBY-5680:
----------------------------------------
Apologies up front if this has already been discussed.
But I was wondering if all the stats could be dropped up front during hard
upgrade time(this will take care of orphaned stats. If there is no way of
clearing everything in the statistics table, then we could drop stats one table
at a time at the time of hard upgrade).
Statistic creation deamon will have to collect stats again since we have
dropped all the stats at upgrade time but since stat gathering happens in
background, it probably won't be very noticeable.
We know of one case DERBY-5681 where a drop constraint can leave orphaned stats
row. There is already a patch on that issue which will make sure that we do not
have such stats row left behind. If that is the only case where orphaned stats
get left behind, then we shouldn't run into orphaned stats again on an upgraded
database.
> indexStat daemon processing tables over an over even when there are no
> changes in the tables
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-5680
> URL: https://issues.apache.org/jira/browse/DERBY-5680
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.8.2.2
> Reporter: Brett Bergquist
> Attachments: derby-5680-1a-drop_orphaned_stats.diff
>
>
> I think there is something wrong with the indexStats.
> The problem happens on many tables in the database.
> None of these tables are changing however, no inserts or deletes or updates.
> They are being queried, however.
> Here is one such table.
> Here is the statistics for this table:
> Table (Index) 2 3
> ACCOUNTTABLE_CONFIG_BUNDLE (SQL081029110443810) numunique= 38390
> numrows= 38390 2012-03-30 13:00:26.84
> ACCOUNTTABLE_CONFIG_BUNDLE (SQL100922215819290) numunique= 38390
> numrows= 38390 2012-03-30 13:00:26.917
> There are in fact 38390 rows in the table.
> Here is some of the indexStat trace:
> Fri Mar 30 12:47:12 EDT 2012 Thread[DRDAConnThread_43,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": update scheduled,
> reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=12)
> Fri Mar 30 12:47:48 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": wrote stats for index
> SQL081029110443810 (fc33890d-011d-491f-3d8c-0000376d74d3): rows=38390,
> card=[38390]
> Fri Mar 30 12:47:48 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": wrote stats for index
> SQL100922215819290 (75608675-012b-3c38-b55c-000043ea6398): rows=38390,
> card=[38390]
> Fri Mar 30 12:47:48 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": scan durations
> (c30625=91ms,c30625=98ms)
> Fri Mar 30 12:47:48 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": generation complete (210 ms)
> Fri Mar 30 12:47:49 EDT 2012 Thread[DRDAConnThread_44,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": update scheduled,
> reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=19)
> Fri Mar 30 12:48:25 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": wrote stats for index
> SQL081029110443810 (fc33890d-011d-491f-3d8c-0000376d74d3): rows=38390,
> card=[38390]
> Fri Mar 30 12:48:25 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": wrote stats for index
> SQL100922215819290 (75608675-012b-3c38-b55c-000043ea6398): rows=38390,
> card=[38390]
> Fri Mar 30 12:48:25 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": scan durations
> (c30625=93ms,c30625=95ms)
> Fri Mar 30 12:48:25 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": generation complete (211 ms)
> Fri Mar 30 12:48:25 EDT 2012 Thread[DRDAConnThread_50,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": update scheduled,
> reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=18)
> Fri Mar 30 12:48:57 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": wrote stats for index
> SQL081029110443810 (fc33890d-011d-491f-3d8c-0000376d74d3): rows=38390,
> card=[38390]
> Fri Mar 30 12:48:57 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": wrote stats for index
> SQL100922215819290 (75608675-012b-3c38-b55c-000043ea6398): rows=38390,
> card=[38390]
> Fri Mar 30 12:48:57 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": generation complete (243 ms)
> Fri Mar 30 12:49:27 EDT 2012 Thread[DRDAConnThread_56,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": update scheduled,
> reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=20)
> Fri Mar 30 12:49:36 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": wrote stats for index
> SQL081029110443810 (fc33890d-011d-491f-3d8c-0000376d74d3): rows=38390,
> card=[38390]
> Fri Mar 30 12:49:37 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": wrote stats for index
> SQL100922215819290 (75608675-012b-3c38-b55c-000043ea6398): rows=38390,
> card=[38390]
> Fri Mar 30 12:49:37 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": scan durations
> (c30625=111ms,c30625=108ms)
> Fri Mar 30 12:49:37 EDT 2012 Thread[index-stat-thread,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": generation complete (238 ms)
> Fri Mar 30 12:49:37 EDT 2012 Thread[DRDAConnThread_49,5,main] {istat}
> "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE": update scheduled,
> reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=18)
> As can be seen, the "i-est" appears to be wrong and is used over and over
> even though the statistics for the indexes have been updated.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
