db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <kristian.waa...@oracle.com>
Subject Re: Question on why indexStat deamon is being triggered
Date Mon, 02 Apr 2012 07:52:55 GMT
On 31.03.12 22:28, Mike Matrigali wrote:
> Bergquist, Brett wrote:
>> I hitched up the debugger and caught the indexStat return "49" for the
>> number of rows. I walked the stack back and found this was for
>> conglomeratename = "f3ec4922-011d-491f-3d8c-0000376d74d3"
>>
>> So I did this query:
>>
>> select
>> c.TABLEID,
>> c.CONGLOMERATENUMBER,
>> c.CONGLOMERATENAME,
>> c.ISINDEX,
>> c.ISCONSTRAINT,
>> c.CONGLOMERATEID,
>> t.TABLEID,
>> t.TABLENAME,
>> t.TABLETYPE,
>> s.STATID,
>> s.REFERENCEID,
>> s.TABLEID,
>> s.CREATIONTIMESTAMP,
>> s.TYPE,
>> s.VALID,
>> s.COLCOUNT,
>> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
>> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID =
>> t.TABLEID join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID where
>> c.CONGLOMERATENAME = 'f3ec4922-011d-491f-3d8c-0000376d74d3'
>>
>> Which is for one of the tables that I am having problems with. This
>> returned:
>>
>> TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT
>> CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID TABLEID
>> CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
>> f3ec4922-011d-491f-3d8c-0000376d74d3 30768
>> f3ec4922-011d-491f-3d8c-0000376d74d3 false false
>> 0e34c923-011d-491f-3d8c-0000376d74d3
>> f3ec4922-011d-491f-3d8c-0000376d74d3 BC01_CONFIGURATION_SET T
>> 645c405f-0136-6999-c1b4-000065089f97
>> 2c5f8294-012b-3c38-b55c-000043ea6398
>> f3ec4922-011d-491f-3d8c-0000376d74d3 2012-03-31 12:50:05.348 I true 1
>> numunique= 20334 numrows= 20334
>> f3ec4922-011d-491f-3d8c-0000376d74d3 30768
>> f3ec4922-011d-491f-3d8c-0000376d74d3 false false
>> 0e34c923-011d-491f-3d8c-0000376d74d3
>> f3ec4922-011d-491f-3d8c-0000376d74d3 BC01_CONFIGURATION_SET T
>> 49fc4ea2-0129-489a-0a26-00000732b350
>> 592fcc26-011d-491f-3d8c-0000376d74d3
>> f3ec4922-011d-491f-3d8c-0000376d74d3 2010-06-17 23:16:40.018 I true 1
>> numunique= 49 numrows= 49
>> f3ec4922-011d-491f-3d8c-0000376d74d3 30768
>> f3ec4922-011d-491f-3d8c-0000376d74d3 false false
>> 0e34c923-011d-491f-3d8c-0000376d74d3
>> f3ec4922-011d-491f-3d8c-0000376d74d3 BC01_CONFIGURATION_SET T
>> 2c44c05e-0136-6999-c1b4-000065089f97
>> e9a40921-011d-491f-3d8c-0000376d74d3
>> f3ec4922-011d-491f-3d8c-0000376d74d3 2012-03-31 12:50:03.427 I true 1
>> numunique= 20334 numrows= 20334
>>
>> So there seems to be 3 statistics rows for this table. And low and
>> behold there is the "numrows = 49" which I see the indexStat when I
>> turn on tracing.
>>
> First thought is I didn't think we needed any statistics at all
> for a single column primary key.

That's probably DERBY-3790 ([1]).
I'll see if I can write a quick patch proposal and then we can take it 
from there.

>
> There should be entries in this catalog for every index on a table, so
> it is not necessarily expected to be one per table. I think for each
> index there is an entry for each column in the index (actually the stat
> applies to multiple columns so in a 3 column index I think there is an
> entry for (col1), (col1,col2), and (col1, col2, col3). But looking at
> your ddl I would only expect 1 entry, as there is one
> index on the table, and it has one column. I am not sure what the
> expectation is for the foreign key that matches the primary key, maybe
> it is 2 rows?
>
> I do wonder if the foreign key is causing some extra issues. I think
> in the long past we would end up creating 2 indexes in this case and
> they would be exactly the same. So functionality was added to just
> "logically" create the index and it would rely on the underlying
> physical index sort of "shared" by the primary key on id and the foreign
> constraint on id. We have had bugs in this area in the past and wonder
> if it left an "orphan" row in the statistics.

As mentioned in another post, I think there's an easy way to avoid 
scanning the same index twice, but I need to make sure the check is 
valid first.
I'm thinking of simply checking if we've seen the index conglomerate 
number already, and if so, ignore that index. The question is, can two 
truly different indexes be represented by the same conglomerate number?

>
> Interesting that the second column it looks like it was created in 2010.
>
> seems like there might be multiple problems here. It would be great to
> figure out how the multiple rows got in there in the first place. I
> would suggest seeing what the system does for ddl in a brand new
> database, turn off index stat thread, run your ddl, add some rows to the
> table, and hand run update statistics procedure.

Just for information, the istat daemon and the system procedure share 
almost all of the core code at the moment. The addition of automatic 
updates of index cardinality statistics consisted mainly of adding the 
"daemon framework" and triggering logic.

>
> I would not be surprised if existing code does not expect the number of
> rows you are seeing. If this is really a buggy orphan row, probably best
> zero admin fix is to change the update statistics code to look for
> other rows and delete them.
> you can follow the update statistic code starting at:
> java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction/updateStatistics
>
>
> There may be a index stat daemon bug here too, but I think it might just
> be getting confused by the bad data in the catalog. Maybe it just keeps
> reading all the rows and queueing work, but calling update statistics
> only update 2 of the 3 rows.

Yes, the triggering code is driven by the existing statistics for the 
table, whereas the update code is driven by the list of indexes on the 
table.


-- 
Kristian

[1] https://issues.apache.org/jira/browse/DERBY-3790

< snip >

Mime
View raw message