Detecting unujsed indexes *NM*
Andy Kent asked how to identify indexes that are never used, on a 7.3/Windows system being migrated to 10.0/Linux, hoping for something like a persistent sysptprof. Suggestions: use onstat -g ppf (partition profile, requires TBLSPACE_STATS enabled) to see whether index partitions show any I/O activity, plus onstat -g opn; also onstat -C part, which reports how often the engine positioned on an index along with splits/compresses. Caveat: ppf only helps for detached indexes, so indexes carried over from v7 mix with data pages. Art thought -C was unavailable in 7.31, but Mark Jamison noted it should exist from the *D8 releases onward with BTSCANNER threads. No confirmation back from the poster.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
\
ANDY KENT wrote: No text Andy. Repost. Art S. Kagel Oninit ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========
How can I tell which indexes have never been used? Client seems to have rather a lot of gratuitous single-column ones. I was hoping there might be a kind of persistent sysptprof out there somewhere ... It's a 7.3/ Windows system being migrated to 10.0/ Linux, 10.0 has only got test activity in it though.
ANDY KENT wrote:
> How can I tell which indexes have never been used? Client seems to have
rather
> a lot of gratuitous single-column ones. I was hoping there might be a kind of
> persistent sysptprof out there somewhere ...
>
> It's a 7.3/ Windows system being migrated to 10.0/ Linux, 10.0 has only got
> test activity in it though.
>
Try onstat -g ppt <partnum | 0 for all>. If the client has partition
stats turned on (TBLSPACE_STATS != 0) you will be able to see if the
index's partition(s) have had any IO activity on them. onstat -g opn
may help also.
Art S. Kagel
================================================================================
===========
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
================================================================================
===========
Also might want to look at onstat -C part
This will tell you how many times the index was used by
the engine to position on a specific value along with
how many compresses and splits have been done.
I like onstat -g ppf like art mentioned but it will only
work if you have detached indexes. So for indexes
which have been upgraded from 7 that stats would
be mixed with data pages.
John
ANDY KENT wrote:
> How can I tell which indexes have never been used? Client seems to ha=
ve
rather
> a lot of gratuitous single-column ones. I was hoping there might be a=
kind
of
> persistent sysptprof out there somewhere ...
>
> It's a 7.3/ Windows system being migrated to 10.0/ Linux, 10.0 has on=
ly
got
> test activity in it though.
>
Try onstat -g ppt <partnum | 0 for all>. If the client has partition
stats turned on (TBLSPACE_STATS !=3D 0) you will be able to see if the
index's partition(s) have had any IO activity on them. onstat -g opn
may help also.
Art S. Kagel
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
=
Andy Kent wrote:
> tvm - does it work in 7.31?
>
> Please post to the SIG - it's suddenly decided not to let me post a reply -
> but once I go back to work in a couple of minutes I won't be able to access
> email (gaaaaa!)
>
Sending to both. Onstat -g ppf does work in 7.31, yes. Don't think
that -C does though.
Art S. Kagel
Oninit
> Thanks and kind regards
>
> Andy
>
>
>
> -----Original Message-----
> From: Art S. Kagel (Oninit LLC) [mailto:art@oninit.com]
> Sent: 22 January 2008 13:51
> To: andykent@freeuk.com
> Subject: Re: Detecting unused indexes [11047]
>
>
> ANDY KENT wrote:
>
>> How can I tell which indexes have never been used? Client seems to have
>>
> rather
>
>> a lot of gratuitous single-column ones. I was hoping there might be a kind
>>
> of
>
>> persistent sysptprof out there somewhere ...
>>
>> It's a 7.3/ Windows system being migrated to 10.0/ Linux, 10.0 has only
>>
> got
>
>> test activity in it though.
>>
>>
>
> Try onstat -g ppt <partnum | 0 for all>. If the client has partition
> stats turned on (TBLSPACE_STATS != 0) you will be able to see if the
> index's partition(s) have had any IO activity on them. onstat -g opn
> may help also.
>
> Art S. Kagel
>
>
>
================================================================================
===========
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
================================================================================
===========
Which version of 7.31?
in 7.31.UD8 and in 7.31.FD8, and presumable 7.31.TD8 we introduced the
BTSCANNER threads. As such onstat -C part should be there.
----- Original Message ----
From: Art S. Kagel (Oninit LLC) <art@oninit.com>
To: ids@iiug.org
Sent: Tuesday, January 22, 2008 8:59:45 AM
Subject: Re: Detecting unused indexes [11055]
Andy Kent wrote:
> tvm - does it work in 7.31?
>
> Please post to the SIG - it's suddenly decided not to let me post a
reply -
> but once I go back to work in a couple of minutes I won't be able to
access
> email (gaaaaa!)
>
Sending to both. Onstat -g ppf does work in 7.31, yes. Don't think
that -C does though.
Art S. Kagel
Oninit
> Thanks and kind regards
>
> Andy
>
>
>
> -----Original Message-----
> From: Art S. Kagel (Oninit LLC) [mailto:art@oninit.com]
> Sent: 22 January 2008 13:51
> To: andykent@freeuk.com
> Subject: Re: Detecting unused indexes [11047]
>
>
> ANDY KENT wrote:
>
>> How can I tell which indexes have never been used? Client seems to
have
>>
> rather
>
>> a lot of gratuitous single-column ones. I was hoping there might be
a kind
>>
> of
>
>> persistent sysptprof out there somewhere ...
>>
>> It's a 7.3/ Windows system being migrated to 10.0/ Linux, 10.0 has
only
>>
> got
>
>> test activity in it though.
>>
>>
>
> Try onstat -g ppt <partnum | 0 for all>. If the client has partition
> stats turned on (TBLSPACE_STATS != 0) you will be able to see if the
> index's partition(s) have had any IO activity on them. onstat -g opn
> may help also.
>
> Art S. Kagel
>
>
>
================================================================================
===========
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
================================================================================
===========
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!