RE: Update statistics
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C05A25.767CD55A
Content-Type: text/plain;
charset="iso-8859-1"
Short answer. It depends.
statistics are used by the optimizer to select the best query plan.
My philosophy.
update statistics low for the entire database
Check for problem queries. Anything which is taking too long you check thequery plan - then test whether a better update stats plan will help that
query (or set of queries)
In most cases (in the DSS world), updating stats anything but low is a waste
of time. Of course I have the luxury of a small number of queries - I can
take the time to check for problem ones. You may not have that luxury in
the OLTP world.
You may note that most of the tech people within Informix that I have talked
with view my philosophy as blasphemy.
(she's a witch - burn her, burn her)
cheers
j.
-----Original Message-----
From: Dirk Moolman [mailto:dirkm@reach.co.za]
Sent: Wednesday, November 29, 2000 8:58 AM
To: informix-list
Subject: Update statistics
I've read about this in the manuals, I've asked advise and looked at
samples, but update statistics is still not 100% clear to me.
I also got different answers from different people with regards to how it
must be done. On my machine I run -
update statistics low drop distributions; (whole database)
update statistics high (for all leading columns indexes - using the
sysindexes.part1 column)
update statistics medium (for all other columns used in indexes -
nonleading columns in indexes)
update statistics for procedure;
Is this correct ?
I've also written a 4gl program (because I don't like the scripts too much)
that is a little different -
I still run update statistics low drop distributions; (whole database)
it will then update statistics high for all leading columns in indexes, and
on all other columns (if they are used in indexes or not) I run update
statistics medium.
Can anyone give me a short and exact answer as to how it must be done ?
Thanks
Dirk
Dirk Moolman
Database Administrator
Reach Technologies
"Bravery is the capacity to perform properly even when scared half to
death."
- General Omar Bradley
------_=_NextPart_001_01C05A25.767CD55A
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.50.4134.600" name=GENERATOR></HEAD>
<BODY>
<DIV>
<DIV><SPAN class=790295116-29112000><FONT face=Arial color=#0000ff size=2>Short
answer. It depends.</FONT></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=790295116-29112000><FONT face=Arial color=#0000ff
size=2>statistics are used by the optimizer to select the best query
plan.</FONT></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><FONT face=Arial color=#0000ff size=2>My
philosophy.</FONT></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><FONT face=Arial color=#0000ff size=2>update
statistics low for the entire database</FONT></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><FONT face=Arial color=#0000ff size=2>Check
for problem queries. Anything which is taking too long you check the query
plan - then test whether a better update stats plan will help that query (or set
of queries)</FONT></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=790295116-29112000><SPAN class=057195316-29112000><FONT
face=Arial color=#0000ff size=2>In most cases (in the DSS world), updating stats
anything but low is a waste of time. Of course I have the luxury of a
small number of queries - I can take the time to check for problem ones.
You may not have that luxury in the OLTP world.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><SPAN class=057195316-29112000><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=790295116-29112000><SPAN class=057195316-29112000><FONT
face=Arial color=#0000ff size=2>You may note that most of the tech people within
Informix that I have talked with view my philosophy as
blasphemy.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><SPAN
class=057195316-29112000></SPAN></SPAN><SPAN class=790295116-29112000><SPAN
class=057195316-29112000><FONT face=Arial color=#0000ff
size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=790295116-29112000><SPAN class=057195316-29112000><FONT
face=Arial color=#0000ff size=2>(she's a witch - burn her, burn
her)</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><SPAN class=057195316-29112000><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=790295116-29112000><SPAN class=057195316-29112000><FONT
face=Arial color=#0000ff size=2>cheers</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><SPAN class=057195316-29112000><FONT
face=Arial color=#0000ff size=2>j.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=790295116-29112000><SPAN class=057195316-29112000><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Dirk Moolman
[mailto:dirkm@reach.co.za]<BR><B>Sent:</B> Wednesday, November 29, 2000 8:58
AM<BR><B>To:</B> informix-list<BR><B>Subject:</B> Update
statistics<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=925404913-29112000>I've read about
this in the manuals, I've asked advise and looked at samples, but update
statistics is still not 100% clear to me.</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=925404913-29112000>I also got
different answers from different people with regards to how it must be
done. On my machine I run -</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=925404913-29112000>update statistics
low drop distributions; (whole database)</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=925404913-29112000>update statistics
high (for all leading columns indexes - using the sysindexes.part1
column)</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=925404913-29112000>update statistics
medium (for all other columns used in indexes - nonleading columns in
indexes)</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=925404913-29112000>update statistics
for procedure;</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=925404913-291120