RE: Strange counts in onstat -u output
This is a multipart message in MIME format.
--=_alternative 00705C3885256FC0_=
Content-Type: text/plain; charset="US-ASCII"
How many rows in each table? is b.appl_no indexes? do the set explain
and post. It could be performing an auto index....
"Bill Dare" <dareb@jevic.com>
Sent by: owner-informix-list@iiug.org
03/10/2005 02:03 PM
To
<owner-informix-list@iiug.org>, "REBELLO, Rulesh Felix"
<rrebello@fsl.org.jm>, "Informix-List" <informix-list@iiug.org>
cc
Subject
RE: Strange counts in onstat -u output
Any temp tables created for this query?
set explain on;
and then run the query to see what the optimizer is doing.
Does appl_no have an index in table licence? If not, that's why you are
creating a temp table and writing to it.
Regards,
Bill
> -----Original Message-----
> From: owner-informix-list@iiug.org [SMTP:owner-informix-list@iiug.org]
> On Behalf Of REBELLO, Rulesh Felix
> Sent: Thursday, March 10, 2005 11:58 AM
> To: owner-informix-list; Informix-List
> Subject: Strange counts in onstat -u output
>
> Group:
>
> I have Informix 9.40.UC3 running on Linux.
> Only one database of size approximately 500 MB
>
> I am doing the following select on 2 tables
>
> select count(*) from application a, licence b where a.appl_no => b.appl_no ;
>
> "appl_no" is serial8 datatype and is a primary key in
> "application" table.
>
>
> Table details :
>
> Table Name application
> Row Size 749
> Number of Rows 66310
> Number of Columns 32
> Index size 496
>
>
> Table Name licence
> Row Size 113
> Number of Rows 62018
> Number of Columns 19
> Index size 105
>
> When I check the onstat -u output while this query is running ...I
> get a very very high count in the "nwrites" field as compared to
> "nreads"
>
> I don't understand this ...
> The "nwrites" count is like 20 - 40 times more than the "nreads"
> count.
>
> Can somebody please throw some light on this .????
>
> regards
>
> Rulesh Rebello
>
>
sending to informix-list
--=_alternative 00705C3885256FC0_=
Content-Type: text/html; charset="US-ASCII"
<br><font size=2 face="sans-serif">How many rows in each table? is
b.appl_no indexes? do the set explain and post. It could be
performing an auto index....</font>
<br>
<br>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td width=40%><font size=1 face="sans-serif"><b>"Bill Dare" <dareb@jevic.com></b>
</font>
<br><font size=1 face="sans-serif">Sent by: owner-informix-list@iiug.org</font>
<p><font size=1 face="sans-serif">03/10/2005 02:03 PM</font>
<td width=59%>
<table width=100%>
<tr>
<td>
<div align=right><font size=1 face="sans-serif">To</font></div>
<td valign=top><font size=1 face="sans-serif"><owner-informix-list@iiug.org>,
"REBELLO, Rulesh Felix" <rrebello@fsl.org.jm>, "Informix-List"
<informix-list@iiug.org></font>
<tr>
<td>
<div align=right><font size=1 face="sans-serif">cc</font></div>
<td valign=top>
<tr>
<td>
<div align=right><font size=1 face="sans-serif">Subject</font></div>
<td valign=top><font size=1 face="sans-serif">RE: Strange counts in
onstat -u output</font></table>
<br>
<table>
<tr valign=top>
<td>
<td></table>
<br></table>
<br>
<br>
<br><font size=2><tt>Any temp tables created for this query?<br>
</tt></font>
<br><font size=2><tt>set explain on;<br>
</tt></font><br><font size=2><tt>and then run the query to see what the optimizer is
doing.<br>
</tt></font>
<br><font size=2><tt>Does appl_no have an index in table licence? If not,
that's why you are<br>
creating a temp table and writing to it.<br>
</tt></font>
<br><font size=2><tt>Regards,<br>
Bill<br>
</tt></font>
<br>
<br><font size=2><tt>> -----Original Message-----<br>
> From: owner-informix-list@iiug.org
[SMTP:owner-informix-list@iiug.org]<br>
> On Behalf Of REBELLO, Rulesh Felix<br>
> Sent: Thursday, March 10, 2005
11:58 AM<br>
> To: owner-informix-list; Informix-List<br>
> Subject: Strange counts in
onstat -u output<br>
><br>
> Group:<br>
><br>
> I have Informix 9.40.UC3 running on Linux.<br>> Only one database of size approximately 500 MB<br>
><br>
> I am doing the following select on 2 tables<br>
><br>
> select count(*) from application a, licence b where a.appl_no =<br>
> b.appl_no ;<br>
><br>
> "appl_no" is serial8 datatype and is
a primary key in<br>
> "application" table.<br>
><br>
><br>
> Table details :<br>
><br>
> Table Name
application<br>
> Row Size
749<br>
> Number of Rows 66310<br>
> Number of Columns 32<br>
> Index size
496<br>
><br>
><br>
> Table Name
licence<br>
> Row Size
113<br>
> Number of Rows 62018<br>
> Number of Columns 19<br>
> Index size
105<br>
><br>
> When I check the onstat -u output while this query is running
...I<br>
> get a very very high count in the "nwrites" field
as compared to<br>
> "nreads"<br>
><br>
> I don't understand this ...<br>
> The "nwrites" count is like 20 - 40 times more than
the "nreads"<br>
> count.<br>
><br>
> Can somebody please throw some light on this .????<br>
><br>
> regards<br>
><br>
> Rulesh Rebello<br>
><br>
><br>
</tt></font>
<br><font size=2><tt>sending to informix-list</tt></font>
<br>
--=_alternative 00705C3885256FC0_=--
sending to informix-list