find indexes not being used
Does anyone have a way to determine which indexes are not being used, or have not been used in a while? We are running IDS 9.4 on HPUX 11I
The Mighty Kevin Fennimore once gave me a shell that took the output
from "onstat -g ppf" and made it all pretty. I use it now to track
activity on tables and indexes.
Kevin and his guys at UCI Consulting do some amazing things with IDS.
If you have an opportunity to sit in on one of their presentations or
seminars, do it(The IDS Forum 2005 in Atlanta comes to mind). I'm sure
he wouldn't mind that I pass this along. If he does, well then I guess
I'm in deep you-know-what now...
If you tank your instance using it, don't blame UCI, me or the IIUG...
But you can blame IBM...
It goes something like this:
#!/bin/ksh
########################################################################
#
# Show table stats
#
########################################################################
usage() {
echo "Usage:"
echo " $(basename $0) [ -a ] [ -s sort_by ] [ -d delimiter ] "
echo " [ number_of_tables ]"
echo " -a ascend sort, default is descend"
echo " -d delimiter"
echo " -s sort by which column"
exit 1
}
integer banner=1
sort_key=" -k 7n"
desc=1
d=" "
integer num=20
while getopts :bas:d: options
do
case $options in
a) desc=0;;
d) d=$OPTARG;;
s) sort_key=" -k ${OPTARG},${OPTARG}n";;
?) usage;;
esac
done
shift $(($OPTIND-1))
[ $# -ge 1 ] && num=$1
num=num+1
onstat -V|awk -F" " '{if($5<"7.31") print " "; else print "-h";}'|read
partnum_f
[ "$d" = " " ] && printf
"%-26s s%6s%5s%3s s s%8s%8s s%8s%8s%4s%3s s\\
" tabname$d \\\\
lkrqs$d lkwts$d dlks$d to$d isrd$d iswrt$d isrwt$d isdel$d
bfrd$d bfwrt$d seqsc$d hr$d tp$d dbspace$d
[ desc -eq 1 ] && sort_key=${sort_key}"r"abnormal="Abnormal termination. Cleaning up and exiting..."
trap "echo $abnormal;rm -f $onstat $iostat; exit 1" 1 2 3 13 15
date "+%Y-%m-%d %H:%M:%S"|read date_s time_s
onstat -g ppf|tail +6 \\\\
|awk -F" " '
BEGIN{
lkrqs=0; lkwts=0; dlks=0; touts=0; isrd=0; iswrt=0;
isrwt=0; isdel=0; bfrd=0; bfwrt=0; seqsc=0;
}
{
lkrqs=lkrqs+$2; lkwts=lkwts+$3; dlks=dlks+$4; touts=touts+$5;
isrd=isrd+$6; iswrt=iswrt+$7;
isrwt=isrwt+$8; isdel=isdel+$9; bfrd=bfrd+$10; bfwrt=bfwrt+$11;
seqsc=seqsc+$12;
if($10>0||$11>0) print $0;
}
END{
printf "%s %d %d %d %d %d %d %d %d %d %d %d
%s\\
","total",lkrqs,lkwts,dlks, \\\\
touts,isrd,iswrt,isrwt,isdel,bfrd,bfwrt,seqsc,"N";
}'|sort $sort_key|head -$num\\\\|while read partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd
bfwrt seqsc rhitrate
do
[ "$partnum" = "total" ] && tabname=$partnum || prtnum2name.sh
$partnum_f $partnum|read tabname type dbspace
[ -z "$tabname" ] && tabname="Unknow"
[ -z "$type" ] && type="N"
[ -z "$dbspace" ] && dbspace="N"
[ -z "$rhitrate" ] && rhitrate="N"
printf "%-26s s%6s%5s%3s s s%8s%8s s%8s%8s%4s%3s s\\
"
$tabname$d $lkrqs$d $lkwts$d $dlks$d \\\\
$touts$d $isrd$d $iswrt$d $isrwt$d $isdel$d $bfrd$d
$bfwrt$d $seqsc$d $rhitrate$d $type$d $dbspace$d
done
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Anthony
Sent: Tuesday, July 12, 2005 2:47 PM
To: ids@iiug.org
Subject: find indexes not being used [5402]
Does anyone have a way to determine which indexes are not being used, or
have not been used in a while?
We are running IDS 9.4 on HPUX 11I
Anthony wrote
> Sent: 12 July 2005 20:47
> To: ids@iiug.org
> Subject: find indexes not being used [5402]
>
> Does anyone have a way to determine which indexes are not
> being used, or have not been used in a while?
>
> We are running IDS 9.4 on HPUX 11I
>
This might do the trick ...
select
s.dbsname,
s.tabname
from sysmaster:sysptprof s, sysindexes i
where dbsname not matches "sys*"
and s.tabname=i.idxname
and s.isreads+s.bufreads+s.pagreads+s.iswrites+s.bufwrites+s.pagwrites
< 1
But you will have to make sure update statistics is not updating the
table.
( run after an onstat -z and immediately before update stats )
And TABLESTATS must be on in your ONCONFIG.
Colin Bull
=======================================================================
This email may contain confidential and privileged information and is intended
for the named or
authorised recipients only. If you are not the named or authorised recipient
of this email,
please note that any copying, distribution, disclosure or use of its contents
is strictly
prohibited. If you have received this email in error please notify the sender
immediately and
then destroy it. The views expressed in this email are not necessarily those
held by VNL, and VNL
does not accept any liability for any action taken in reliance on the contents
of this message.
VNL does not guarantee that the integrity of this email has been maintained,
nor that it is free
of viruses, interceptions or interference.
_______________________________________________________________________
This email has been scanned for all known viruses by the MessageLabs Email
Security System.
_______________________________________________________________________
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g