IDS Forum

RE: Update statistics performance

Posted By: Kenneth Makobe
Date: Monday, 26 July 2004, at 2:52 a.m.

In Response To: Update statistics performance (Kenneth Makobe )

-----Original Message-----
From: mac horn [mailto:mac.horn@cox.net]
Sent: Sunday, July 25, 2004 4:50 AM
To: 'Kenneth Makobe '
Subject: RE: Update statistics performance [3275]=20


Could you send your update stats script?
I hope you mistyped the size of your database. 35 gig should not take
anywhere that long.
We update stats on 4TB in a little over two hours with the recommended
high/medium.

mac


-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Kenneth Makobe
Sent: Wednesday, July 21, 2004 10:39 PM
To: ids@iiug.org
Subject: Update statistics performance [3275]


Hi all
Update Statistic takes a long duration of up to 13 hours to complete, =
our DB
is more than 35 gb. I tried to use PDQ ,PSORT_NPROCS and DBSPACETEMP =
and the
was only a slight improvement i.e from 13 to 12 hours. our IDS version =
is
17.31 uc5 Kindly advice on how this duration can be reduced
Below is the profile and i/o queue stats and onstat -F profile

Profile
dskreads pagreads bufreads =CAched dskwrits pagwrits bufwrits =CAched
229690758 36261273 617122098 62.78 28229782 19567333 287833239 90.19

isamtot open start read write rewrite delete commit
rollbk
144865383 30923634 247207201 3231528422 186584701 26339127 233319 =
47761
4

gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0

ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 529902.17 13244.00 1267 2534

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
30833338 50871 3330742991 0 0 4886 396427 =
3670835

ixda-RA idx-RA da-RA RA-pgsused lchwaits
6695267 15958151 175978636 198623320 15834291

AIO I/O queues:
q name/id len maxlen totalops dskread dskwrite dskcopy
kio 0 0 0 0 0 0 0
kio 1 0 16 57579748 52927126 4652622 0
kio 2 0 0 0 0 0 0
kio 3 0 16 108007501 96187935 11819566 0
kio 4 0 16 97292492 85534898 11757594 0
adt 0 0 0 0 0 0 0
msc 0 0 1 9313 0 0 0
aio 0 0 2 36214109 2 36213915 0
omstat -F


Fg Writes LRU Writes Chunk Writes
61 5808527 35281444

address flusher state data
60050500 0 I 0 =3D 0X0
600509ec 1 I 0 =3D 0X0
60050ed8 2 I 0 =3D 0X0
600513c4 3 I 0 =3D 0X0
600518b0 4 I 0 =3D 0X0
60051d9c 5 I 0 =3D 0X0
60052288 6 I 0 =3D 0X0
60052774 7 C 79 =3D 0X4f
60052c60 8 I 0 =3D 0X0
6005314c 9 I 0 =3D 0X0
60053638 10 I 0 =3D 0X0
60053b24 11 I 0 =3D 0X0
60054010 12 I 0 =3D 0X0
600544fc 13 I 0 =3D 0X0
600549e8 14 C 78 =3D 0X4e
60054ed4 15 I 0 =3D 0X0
states: Exit Idle Chunk Lru


#!/bin/sh
########################################################################=
####
###
# update_stats D.L. Samuels 02/06/99
#
# Perform Informix recommended sequence of update statistics statements =
on the
# specified tables. Execute with no parameters for explanation of =
usage.
#
########################################################################=
####
###
if [ $# -ne 1 ]
then=20
echo "\nUsage:\n"=20
echo " `expr //$0 : '.*/\(.*\)'` DATABASE_NAME "TABLE_NAME =
TABLE_NAME..""
echo "\nWhere:\n"=20
echo " DATABASE_NAME =3D Name of the database to be updated."
echo " TABLE_NAME =3D Name of tables to update. If more than =
one table"
echo " is specified, the list of table names =
should be"
echo " enclosed in quotes with a white space =
separating"
echo " the table names. Wildcards can also be =
matched"
echo " by enclosing the table name and wildcards =
in"
echo " quotes.\n"
echo " To update all tables specify ALL.\n"

exit 1=20
fi

DBNAME=3D$1


########################################################################=
####
###
# The following query will return the columns needed for the various =
update
# statistics statements. The output of this query is piped to the awk =
script
# which follows.

### Loop through the list of tables specified:

TABLES=3D`cat tables.dat`

for TABNAME in $TABLES
do

-- A single UPDATE STATISTICS MEDIUM for columns which do not head an =
index:
#-- The hard coded value of " NONE" for all of these columns will =
cause
#-- the awk script to generate a single statement.
select t.tabname, " NONE" idxname, "1 medium" level, c.colno part, =
c.colname=20
from syscolumns c, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and c.colno not in (select part1=20
from sysindexes i=20
where i.tabid =3D c.tabid)
union all
#-- A separate UPDATE STATISTICS HIGH for each column which heads an =
index:
#-- (The column name is used as the index name here, so that if a =
column
#-- heads more than one index, it will only be processed once.)
select unique t.tabname, c.colname, "2 high" level, 1 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and c.colno =3D i.part1
union all
-- A separate UPDATE STATISTICS LOW for each multi-column index:
-- (The actual name of each index is included on each row returned =
by
-- this part of the query, allowing the awk script to group all
-- all columns of the same index together.)
select t.tabname, i.idxname, "3 low" level, 1 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part1
union all
select t.tabname, i.idxname, "3 low" level, 2 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part2
union all
select t.tabname, i.idxname, "3 low" level, 3 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part3
union all
select t.tabname, i.idxname, "3 low" level, 4 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part4
union all
select t.tabname, i.idxname, "3 low" level, 5 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part5
union all
select t.tabname, i.idxname, "3 low" level, 6 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part6
union all
select t.tabname, i.idxname, "3 low" level, 7 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part7
union all
select t.tabname, i.idxname, "3 low" level, 8 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part8
union all
select t.tabname, i.idxname, "3 low" level, 9 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part9
union all
select t.tabname, i.idxname, "3 low" level, 10 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part10
union all
select t.tabname, i.idxname, "3 low" level, 11 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part11
union all
select t.tabname, i.idxname, "3 low" level, 12 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part12
union all
select t.tabname, i.idxname, "3 low" level, 13 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part13
union all
select t.tabname, i.idxname, "3 low" level, 14 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part14
union all
select t.tabname, i.idxname, "3 low" level, 15 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part15
union all
select t.tabname, i.idxname, "3 low" level, 16 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part16
order by 1, 3, 2, 4

EOT

} |=20

########################################################################=
####
###
### The following awk script will take the results of the above query =
and
### output appropriate "update statistics" statements. The output is =
piped
### to dbaccess for execution.

awk -v DBNAME=3D$DBNAME '

### Initialization: Open the specified database.
BEGIN \
{
print "database " DBNAME ";";
LAST_BREAK=3D"";
}

### Every line:
{

# Split the line in to fields; Error if we dont have 6 fields!
if (split($0, PARAMETERS) !=3D 6)
{
print "?Internal error" | "cat >&2";
exit 1
}

# Assign fields to more symbolic names:
TABNAME =3D PARAMETERS[1]; # table name
IDXNAME =3D PARAMETERS[2]; # index name
TYPE =3D PARAMETERS[4]; # update type (high, medium, low)
COLNAME =3D PARAMETERS[6]; # column name

# Concatenate fields used for the control break which marks the end
# of one update statistics statement and the beginning of another:
BREAK =3D TABNAME IDXNAME TYPE;

# If this is not the first control break, finish off the previous =
update
# statistics statement:
if (LAST_BREAK !=3D "" && BREAK !=3D LAST_BREAK)
{
print ")"
print ";"
}

# For all control breaks, begin a new update statistics statement:
if (BREAK !=3D LAST_BREAK)
{
printf "%s", "update statistics " TYPE \
" for table " TABNAME " (" COLNAME
}
# If this line is not a control break, then simply add the column =
to
# the parameters of the update statistics statement:
else
{
printf "%s", ", " COLNAME
}

# Keep track of last control break values:
LAST_BREAK=3DBREAK;
}

### End of input: Complete the last "update statistics" statement.
END \
{
if (LAST_BREAK !=3D "")
{
print ")"
print ";"
}
else
{
print "!echo \"No such table(s)\""
}
}
' |=20

########################################################################=
####
###
### Execute the update statistics statements. Each is echoed to the =
standard
### output as it is executed so the user can see what is happening.

#$dbaccess -e

done

------_=_NextPart_000_01C472DC.6D852370--

Messages In This Thread

• Update statistics performance
  Kenneth Makobe -- Thursday, 22 July 2004, at 1:39 a.m.
  • RE: Update statistics performance
    McCabe-Reed, Barbara SIK -- Thursday, 22 July 2004, at 5:17 a.m.
    • Re: RE: Update statistics performance
      CHUAN LU -- Saturday, 25 March 2006, at 3:20 a.m.
  • RE: Update statistics performance
    Sebastian, Norma J. -- Thursday, 22 July 2004, at 7:52 a.m.
    • RE: Update statistics performance
      Jonathan Leffler -- Thursday, 22 July 2004, at 8:37 p.m.
  • RE: Update statistics performance
    Kenneth Makobe -- Monday, 26 July 2004, at 2:52 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.