No question at this time
DBA Top 10
1 M. Cadot 9900
2 B. Vroman 9200
3 A. Kavsek 7500
4 T. Boles 6250
5 J. Schnackenberg 5100
6 P. Wisse 2700
7 M. Hidayathullah ... 1500
8 T. P 1200
9 G. Lambregts 1100
9 . Lauri 1100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48352
Total active users1553
Act. users last 24h3
Act. users last hour0
Registered user hits last week285
Registered user hits last month926
Go up

Tracing index modifications
Next thread: getting row lock information
Prev thread: Transparent instance failover?

Message Score Author Date
Hi, The view all_tab_modifications allow to tra...... Lauri Feb 11, 2019, 10:46
Hi Lauri, No. There is no view to track index m...... Philip Wisse Feb 11, 2019, 10:52
Hi, if you're trying to find out how indexes ar...... Ales Kavsek Feb 11, 2019, 11:34
Hello Lauri, interesting question indeed... An...... Bruno Vroman Feb 11, 2019, 11:58
Hi, Thanks to all for the fast answers. @Phi...... Lauri Feb 11, 2019, 14:27
Hi, > I was afraid of it... > Mmhh.., what a p...... Ales Kavsek Feb 11, 2019, 23:57
Hi, Why? The view all_tab_modifications allows ...... Lauri Feb 12, 2019, 10:05

Follow up by mail Click here


Subject: Tracing index modifications
Author: Lauri, Netherlands
Date: Feb 11, 2019, 10:46, 40 days ago
Os info: All
Oracle info: Oracle12.x
Error info: N/A
Message: Hi,

The view all_tab_modifications allow to track table modifications. Does someone know if there is a view or a way to check index modifications.

Thanks and Regards,

Lauri
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Tracing index modifications
Author: Philip Wisse, Netherlands
Date: Feb 11, 2019, 10:52, 40 days ago
Message: Hi Lauri,

No. There is no view to track index modifications.

The purpose of table monitoring is to allow smart stats calculations.

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Tracing index modifications
Author: Ales Kavsek, Slovenia
Date: Feb 11, 2019, 11:34, 40 days ago
Message: Hi,

if you're trying to find out how indexes are used (on not?)...

https://oracle-base.com/articles/12c/index-usage-tracking-12cr2

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Tracing index modifications
Author: Bruno Vroman, Belgium
Date: Feb 11, 2019, 11:58, 40 days ago
Message: Hello Lauri,

interesting question indeed...
An approximation "has my index been modified" can be derived from dba_tab_modifications (at least for rows inserted and deleted, but even this is an approximation) but the fact that a table has been modified doesn't mean that any given index has been modified (example: table with columns ( id, name, description, ... ) and indexes on ( id ) and on ( name ); if we do "update t set name = 'NEWNAME' ..." then the index on name is modified, but if we do "update t set description = 'NEWDESCRIPTION' ..." than it is not)

And tracking index usage doesn't give info about the fact that the index has been modified or not as well...

Still Oracle must have a way, to be able to decide when its index statistics are stale (like the info from dba_tab_modifications at table level)...
Maybe it does so by comparing the (refreshed) table statistics (at column level) with the index statistics (of the same columns)? (but when I try to understand how, complexity arises quickly, for example for composite indexes)
So: mystery! Maybe you open a SR and you share the results with us? ;-)

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Tracing index modifications
Author: Lauri, Netherlands
Date: Feb 11, 2019, 14:27, 40 days ago
Message: Hi,

Thanks to all for the fast answers.

@Philip:
I was afraid of it... and had checked the Oracle doc.

@Ales:
Good link, but I was trying to found out how to track the "modifications" the same way it is done in all_tab_modifications.

@Bruno:
You wrote "the fact that a table has been modified doesn't mean that any given index has been modified".
Yes, this is true.
Mmhh.., what a pity, no "easy" work-around.

Kind Regards,

Lauri.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re-Tracing index modifications
Author: Ales Kavsek, Slovenia
Date: Feb 11, 2019, 23:57, 39 days ago
Message: Hi,

> I was afraid of it...
> Mmhh.., what a pity, no "easy" work-around.

But why this concern? Why do you worry about "index modifications"? In the real life what would you do with this info? Oracle is taking "care" of the indexes (including gathering statistics when needed)...sorry, I don't get it !?

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Tracing index modifications
Author: Lauri, Netherlands
Date: Feb 12, 2019, 10:05, 39 days ago
Message: Hi,

Why? The view all_tab_modifications allows to track table modifications (DML), which might be useful for DML table statistics, as well as controling the way statistics are gathered (the the policy we might use).
I was just looking for a comparable option for the indexes.

Kind Regards,

Lauri
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here