Re: unused indexes (Informix 7.31)
On May 27, 10:17 am, revilofe <revil...@gmail.com> wrote:
> Hi everybody
>
> how do I know which index is used on a table when the program execute?
> I want to do a clean up of unused indexes and tables.
>
> Thanks.
>
> (informix 7.31)
I use this bash script in 10. It might work for 7.31
Change the #!/usr/local/bin/bash line to whatever the command "which
bash" returns. If you don't have bash then you can probably use ksh.
#!/usr/local/bin/bash
unset DBACCNOIGN
PROC_NAME=`basename $0`
trap "cleanup" 0 1 2 11
function cleanup {
rm -f /tmp/${PROC_NAME}_${$}*
}
function usage {
printf "\\n\\n\\n Usage: %s -[a|d]012345678 database <table> \\n\\n\\n" $
(basename $0) 1>&2
printf "\\nThis script shows the table access statistics for a given
table or for all of the tables in the database.\\n" 1>&2
printf "\\noption 'a' is ascending sort order. It is the default\\n"
1>&2
printf "\\noption 'd' is descending sort order.\\n" 1>&2
printf "\\n0 - 9 are the fields to sort on.\\n" 1>&2
printf "\\n0 - Table Name.\\n" 1>&2
printf "\\n1 - Disk Reads.\\n" 1>&2
printf "\\n2 - Page Reads.\\n" 1>&2
printf "\\n3 - Buffer Reads.\\n" 1>&2
printf "\\n4 - Disk Writes.\\n" 1>&2
printf "\\n5 - Page Writes.\\n" 1>&2
printf "\\n6 - Buffer Writes.\\n" 1>&2
printf "\\n7 - Locks Requested.\\n" 1>&2
printf "\\n8 - Sequential Scans.\\n" 1>&2
printf "\\nYou can combine the options such as %s -d12 bmn\\n" $
(basename $0) 1>&2
exit 0
}
function perror {
printf "\\n\\n\\n ***** ERROR ***** Database: %s does not exist.\\n\\n\\n"
$db 1>&2
}
#sortField[0]="tabname"
#sortField[1]="isreads"
#sortField[2]="pagreads"
#sortField[3]="bufreads"
#sortField[4]="iswrites"
#sortField[5]="pagwrites"
#sortField[6]="bufwrites"
#sortField[7]="lockreqs"
#sortField[8]="seqscans"
orderBy=""
orderDir="asc"
while getopts "ad012345678" opt ; do
case $opt in
d ) orderDir="desc" ;;
[012345678] )
(( opt1 = opt + 1 ))
orderBy="$orderBy, $opt1"
;;
\\? ) usage ;;
esac
done
orderBy=${orderBy#,} # remove leading comma
if [ "$orderBy" = "" ] ; then
orderBy="1"
fi
if [ "$orderDir" = "desc" ] ; then
orderBy=$( echo "$orderBy" | sed 's/,/ desc,/g' )
orderBy="$orderBy desc"
fi
shift $(( OPTIND - 1 ))
if [ $# -lt 1 -o $# -gt 2 ] ; then
usage
fi
db=$1
if [ `sdb | grep "^${db}$" | wc -l` -eq 0 ] ; then
perror
usage
fi
if [ $# -eq 2 ] ; then
where=" and tabname = \\"$2\\" "
iwhere=" and indexname = \\"$2\\" "
fi
# dbsname - dbsname
# tabname - tabname
# partnum - partnum
# lockreqs - pf_rqlock
# lockwts - pf_wtlock
# deadlks - pf_deadlk
# lktouts - pf_lktouts
# isreads - pf_isread
# iswrites - pf_iswrite
# isrewrites - pf_isrwrite
# isdeletes - pf_isdelete
# bufreads - pf_bfcread
# bufwrites - pf_bfcwrite
# seqscans - pf_seqscans
# pagreads - pf_dskreads
# pagwrites - pf_dskwrites
dbaccess $db <<EOF >/dev/null 2>&1
set isolation to dirty read;
unload to "/tmp/${PROC_NAME}_${$}_stats"
select
t.tabname[1,25],
sum(p.pf_isread),
sum(p.pf_dskreads),
sum(p.pf_bfcread),
sum(p.pf_iswrite),
sum(p.pf_dskwrites),
sum(p.pf_bfcwrite),
sum(p.pf_rqlock),
sum(p.pf_seqscans)
from
sysmaster:sysptntab p,
systables t
where
t.partnum = p.partnum
$where
group by
1
union
select
indexname[1,25],
sum(p.pf_isread),
sum(p.pf_dskreads),
sum(p.pf_bfcread),
sum(p.pf_iswrite),
sum(p.pf_dskwrites),
sum(p.pf_bfcwrite),
sum(p.pf_rqlock),
sum(p.pf_seqscans)
from
sysmaster:sysptntab p,
sysfragments f
where
f.partn = p.partnum and
f.fragtype = "I"
$iwhere
group by
1
union
select
t.tabname[1,25],
sum(p.pf_isread),
sum(p.pf_dskreads),
sum(p.pf_bfcread),
sum(p.pf_iswrite),
sum(p.pf_dskwrites),
sum(p.pf_bfcwrite),
sum(p.pf_rqlock),
sum(p.pf_seqscans)
from
systables t,
sysmaster:sysptntab p,
sysfragments f
where
t.tabid = f.tabid and
f.partn = p.partnum and
f.fragtype <> "I"
$where
group by
1
order by
$orderBy;
EOF
awk -F'|' '
BEGIN{
printf( "\\n\\n" )
printf( "%-25s %-11s %-11s %-11s %-11s %-11s %-11s %-11s %-11s\\n",
\\
"Table ", "Disk Reads ", "Page Reads ", "Buf
Reads ", "Disk Writes", "Page Writes", "Buf Writes ", "Lock Reqs ",
"Seq Scans " )
printf( "%-25s %-11s %-11s %-11s %-11s %-11s %-11s %-11s %-11s\\n",
\\
"=========================", "===========", "===========",
"===========", "===========", "===========", "===========",
"===========", "===========" )
}
{
printf( "%-25s %11lu %11lu %11lu %11lu %11lu %11lu %11lu %11lu\\n",
\\
$1, $2, $3, $4, $5, $6, $7, $8, $9 )
}
' /tmp/${PROC_NAME}_${$}_stats
exit
On Thu, May 29, 2008 at 3:43 PM, bozon <curtis@crowson1.com> wrote:
> On May 27, 10:17 am, revilofe <revil...@gmail.com> wrote:
>> Hi everybody
>>
>> how do I know which index is used on a table when the program execute?
>> I want to do a clean up of unused indexes and tables.
>>
>> Thanks.
>>
>> (informix 7.31)
>
> I use this bash script in 10. It might work for 7.31
>
> Change the #!/usr/local/bin/bash line to whatever the command "which
> bash" returns. If you don't have bash then you can probably use ksh.
>
> #!/usr/local/bin/bash
>
> unset DBACCNOIGN
>
> PROC_NAME=`basename $0`
>
> trap "cleanup" 0 1 2 11
>
> function cleanup {
> rm -f /tmp/${PROC_NAME}_${$}*
> }
>
>
> function usage {
>
> printf "\\n\\n\\n Usage: %s -[a|d]012345678 database <table> \\n\\n\\n" $
> (basename $0) 1>&2
> printf "\\nThis script shows the table access statistics for a given
> table or for all of the tables in the database.\\n" 1>&2
> printf "\\noption 'a' is ascending sort order. It is the default\\n"
> 1>&2
> printf "\\noption 'd' is descending sort order.\\n" 1>&2
> printf "\\n0 - 9 are the fields to sort on.\\n" 1>&2
> printf "\\n0 - Table Name.\\n" 1>&2
> printf "\\n1 - Disk Reads.\\n" 1>&2
> printf "\\n2 - Page Reads.\\n" 1>&2
> printf "\\n3 - Buffer Reads.\\n" 1>&2
> printf "\\n4 - Disk Writes.\\n" 1>&2
> printf "\\n5 - Page Writes.\\n" 1>&2
> printf "\\n6 - Buffer Writes.\\n" 1>&2
> printf "\\n7 - Locks Requested.\\n" 1>&2
> printf "\\n8 - Sequential Scans.\\n" 1>&2
>
> printf "\\nYou can combine the options such as %s -d12 bmn\\n" $
> (basename $0) 1>&2
>
> exit 0
>
> }
>
>
> function perror {
>
> printf "\\n\\n\\n ***** ERROR ***** Database: %s does not exist.\\n\\n\\n"
> $db 1>&2
>
> }
>
> #sortField[0]="tabname"
> #sortField[1]="isreads"
> #sortField[2]="pagreads"
> #sortField[3]="bufreads"
> #sortField[4]="iswrites"
> #sortField[5]="pagwrites"
> #sortField[6]="bufwrites"
> #sortField[7]="lockreqs"
> #sortField[8]="seqscans"
>
> orderBy=""
> orderDir="asc"
> while getopts "ad012345678" opt ; do
> case $opt in
> d ) orderDir="desc" ;;
> [012345678] )
> (( opt1 = opt + 1 ))
> orderBy="$orderBy, $opt1"
> ;;
> \\? ) usage ;;
> esac
> done
>
> orderBy=${orderBy#,} # remove leading comma
>
>
>
> if [ "$orderBy" = "" ] ; then
> orderBy="1"
> fi
>
> if [ "$orderDir" = "desc" ] ; then
> orderBy=$( echo "$orderBy" | sed 's/,/ desc,/g' )
> orderBy="$orderBy desc"
> fi
>
> shift $(( OPTIND - 1 ))
>
> if [ $# -lt 1 -o $# -gt 2 ] ; then
> usage
> fi
>
>
> db=$1
>
> if [ `sdb | grep "^${db}$" | wc -l` -eq 0 ] ; then
> perror
> usage
> fi
>
> if [ $# -eq 2 ] ; then
> where=" and tabname = \\"$2\\" "
> iwhere=" and indexname = \\"$2\\" "
> fi
>
> # dbsname - dbsname
> # tabname - tabname
> # partnum - partnum
> # lockreqs - pf_rqlock
> # lockwts - pf_wtlock
> # deadlks - pf_deadlk
> # lktouts - pf_lktouts
> # isreads - pf_isread
> # iswrites - pf_iswrite
> # isrewrites - pf_isrwrite
> # isdeletes - pf_isdelete
> # bufreads - pf_bfcread
> # bufwrites - pf_bfcwrite
> # seqscans - pf_seqscans
> # pagreads - pf_dskreads
> # pagwrites - pf_dskwrites
>
> dbaccess $db <<EOF >/dev/null 2>&1
> set isolation to dirty read;
> unload to "/tmp/${PROC_NAME}_${$}_stats"
> select
> t.tabname[1,25],
> sum(p.pf_isread),
> sum(p.pf_dskreads),
> sum(p.pf_bfcread),
> sum(p.pf_iswrite),
> sum(p.pf_dskwrites),
> sum(p.pf_bfcwrite),
> sum(p.pf_rqlock),
> sum(p.pf_seqscans)
> from
> sysmaster:sysptntab p,
> systables t
> where
> t.partnum = p.partnum
> $where
> group by
> 1
> union
> select
> indexname[1,25],
> sum(p.pf_isread),
> sum(p.pf_dskreads),
> sum(p.pf_bfcread),
> sum(p.pf_iswrite),
> sum(p.pf_dskwrites),
> sum(p.pf_bfcwrite),
> sum(p.pf_rqlock),
> sum(p.pf_seqscans)
> from
> sysmaster:sysptntab p,
> sysfragments f
> where
> f.partn = p.partnum and
> f.fragtype = "I"
> $iwhere
> group by
> 1
> union
> select
> t.tabname[1,25],
> sum(p.pf_isread),
> sum(p.pf_dskreads),
> sum(p.pf_bfcread),
> sum(p.pf_iswrite),
> sum(p.pf_dskwrites),
> sum(p.pf_bfcwrite),
> sum(p.pf_rqlock),
> sum(p.pf_seqscans)
> from
> systables t,
> sysmaster:sysptntab p,
> sysfragments f
> where
> t.tabid = f.tabid and
> f.partn = p.partnum and
> f.fragtype <> "I"
> $where
> group by
> 1
> order by
> $orderBy> ;
> EOF
>
>
>
>
> awk -F'|' '
>
> BEGIN{
> printf( "\\n\\n" )
> printf( "%-25s %-11s %-11s %-11s %-11s %-11s %-11s %-11s %-11s\\n",
> \\
> "Table ", "Disk Reads ", "Page Reads ", "Buf
> Reads ", "Disk Writes", "Page Writes", "Buf Writes ", "Lock Reqs ",
> "Seq Scans " )
> printf( "%-25s %-11s %-11s %-11s %-11s %-11s %-11s %-11s %-11s\\n",
> \\
> "=========================", "===========", "===========",
> "===========", "===========", "===========", "===========",
> "===========", "===========" )
> }
> {
> printf( "%-25s %11lu %11lu %11lu %11lu %11lu %11lu %11lu %11lu\\n",
> \\
> $1, $2, $3, $4, $5, $6, $7, $8, $9 )
> }
>
> ' /tmp/${PROC_NAME}_${$}_stats
>
>
> exit
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Thanks Bozon, but your script uses the command 'sdb', what is this
command? On my system it's for the symbolic debugger.
Jim