unused indexes (Informix 7.31)

[revilofe]

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)

[revilofe]

[Kevin Cherkauer]

Check the manuals for the "SET EXPLAIN" statement. Here is a link for it in
11.10. (I hope this also exists in 7.31! I don't have a link to 7.31 manuals
handy.)

http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqls.doc/sqls813.htm

--
Kevin Cherkauer
Software Engineer
IBM Informix Dynamic Server -- Database Kernel

"revilofe" <revi...@gmail.com> wrote in message
news:e4c4d42c-1d24-464d...@25g2000hsx.googlegroups.com...

[da...@smooth1.co.uk]

Unused since server boot? If the indexes are dettached try querying
sysptntab in sysmaster.

[Habichtsberg, Reinhard]

Are you sure sysptntab (also in view sysptprof) collects the number of
access since last boot or onstat -z and not the one of current sessions?

> -----Ursprüngliche Nachricht-----
> Von: informix-l...@iiug.org
> [mailto:informix-l...@iiug.org]Im Auftrag von
> da...@smooth1.co.uk
> Gesendet am: Dienstag, 27. Mai 2008 20:06
> An: inform...@iiug.org
> Betreff: Re: unused indexes (Informix 7.31)

> _______________________________________________
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>

[Habichtsberg, Reinhard]

There was an thread not long ago:

On Mar 31, 4:38 pm, tomc...@gmail.com wrote:

>> howdy

>> does informix have system views/meta data that indicates the last time
>> an index was used?

>> thanks
>> tom

> Will need some mods for IDS 10+ if using CPS (configurable page
> sizes), and perhaps a tweak to get rid of system catalogs (unless
> wanted).
>
> HTH
> Mark Scranton
> Xtivia Inc.
>
> /* start here */
>
> database sysmaster;
> set isolation dirty read;
>
> select sysptprof.dbsname database,
> systabnames.tabname indexname,
> keylen,
> round((ti_nptotal* sysshmvals.sh_pagesize)/1024,0)
> totalsize_K,
> sum(lockreqs +
> lockwts +
> deadlks +
> lktouts +
> isreads +
> iswrites +
> isrewrites +
> isdeletes +
> bufreads +
> bufwrites +
> seqscans +
> pagreads +
> pagwrites) total_operations
> from sysptprof, sysptnkey, systabnames, sysshmvals, systabinfo
> where
> sysptnkey.partnum = sysptprof.partnum
> and
> sysptnkey.partnum = systabnames.partnum
> and
> systabnames.partnum = systabinfo.ti_partnum
> group by 1,2,3,4
> order by 5 desc, 4 desc

Some questions and comments remains:
- The manual of IDS 9.40 Administrators Reference says: The sysptprof table
lists information about a tblspace. Tblspaces correspond
to tables. Profile information for a table is available only when a table is
open. When the last user who has a table open closes it, the tblspace in
shared memory is freed, and any profile statistics are lost.
- My tests doesn't aprove that statement. In my case (IDS 9.40FC9W2) the
statistics remains after any connection to the monitored table were closed.
_What is true?_

- Which of the values is significant for the usage of the index? It can't be
all the values because insert, delete or update of rows of the related table
will change some of the values. That has nothing to do with the usage of the
index by queries. My thought is that isreads, pagereads and bufreads may be
significant but it's only a guess.

- The above query (thanks to Mark) should be changed a bit to cope with
fragmented tables too:

/* start here */

database sysmaster;
set isolation dirty read;

select sysptprof.dbsname database,
systabnames.tabname indexname,
keylen,
sum(round((ti_nptotal* sysshmvals.sh_pagesize)/1024,0))
totalsize_K,
sum(lockreqs) slockreqs,
sum(lockwts) slockwts,
sum(deadlks) sdeadlks,
sum(lktouts) slktouts,
sum(isreads) sisrewrites,
sum(iswrites) sisrewrites,
sum(isrewrites) sisrewrites,
sum(isdeletes) sisdeletes,
sum(bufreads) sbufreads,
sum(bufwrites) sbufwrites,
sum(seqscans) sseqscans,
sum(pagreads) spagreads,
sum(pagwrites) spagwrites
from sysptprof, sysptnkey, systabnames, sysshmvals, systabinfo
where
sysptnkey.partnum = sysptprof.partnum
and
sysptnkey.partnum = systabnames.partnum
and
systabnames.partnum = systabinfo.ti_partnum
group by 1,2,3
order by 5 desc, 4 desc

BTW: sysptprof is a view in 9.40 and 10.0 including systabnames, thus
systabnames could be remove from the query.

Any comments are appreciated.

Reinhard.

[Christian Knappke]

From the keyboard of "Habichtsberg, Reinhard"
<RHabic...@arz-emmendingen.de>:

> There was an thread not long ago:

> - Which of the values is significant for the usage of the index?

[...]

> Any comments are appreciated.

As someone mentioned in another posting in this thread, *detached*
indices have their own partition and are listed separately beside
the tables. Attached indices are invisible in this context and
their statistic values are merely added to the table's.

HTH and best regards
Christian
--
#include <std_disclaimer.h>
/* The opinions stated above are my own and not
necessarily those of my employer. */

[bozon]

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

[Jim Tranny]

> _______________________________________________
> Informix-list mailing list
> Inform...@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