Re: sysmaster slow
--0-500569858-1106620946=:13343
Content-Type: text/plain; charset=us-ascii
Very good explanation Art. I actually understand it. :-)) Thanks.
One question.
Since the database for this instance is not a logged database, I can assume that there would be no logged temp tables to be sorted in the rootdbs, is that correct ?
Then all the temp tables created explicitly ( without an explicit location ), and implicitly, in a non-logged database would be created in the TEMP dbspaces that are listed in the dbspacetemp ?
Thanks.
Floyd
"Art S. Kagel" <KAGEL@bloomberg.net> wrote:
Floyd Wellershaus wrote:
> --0-1611837224-1106318424=:47941
> Content-Type: text/plain; charset=us-ascii
>
> HI Art,
>
>>>Anyway, you can move the temp table IO's off root by adding one or more
>
> of the non-temp dbspaces to DBSPACETEMP. <<
>
> I am not sure I know what you mean here. move NON-temp dbspaces to DBSPACETEMP ?
> btw, I have 3 dbspaces listed in dbspacetemp.
DBSPACETEMP can contain both dbspaces marked as temp (ie created with the -t
option to onspaces) and those that are 'normal' dbspaces. The engine knows
which is which and will use the listed temp dbspaces to locate non-logged
temp tables (ie those created explicitly with the WITH NO LOG clause). The
engine will also locate logged temp tables in 'normal' dbspaces if any are
listed in DBSPACETEMP, if all of the listed dbspaces are 'temp' spaces then
the engine must locate logged temp tables in ROOTDBS.
> Onstat -g ppf gets output for me, so I assume the tablestats are on. That is neat. Does it tell you io per table ? Where can I read more about that ? for instance, how do I know what tablename is by the partnum ?
See Tsutomu's response for this query.
Art S. Kagel
> Thanks.
>
> "Art S. Kagel" wrote:
> Floyd Wellershaus wrote:
>
> As Martin pointed out, logged temp tables (including those created by an
> INTO TEMP clause) are created in ROOTDB if there are not 'regular' dbspaces
> listed in DBSPACETEMP, however, given the low number of writes on the two
> chunks prod_root_c[12], I'd say that's not the problem, nor is logical log
> IO. Anyway, you can move the temp table IO's off root by adding one or more
> of the non-temp dbspaces to DBSPACETEMP. I agree with Martin that you
> should turn on table level stats if not on already and monitor onstat -g ppf.
>
Art S. Kagel
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Work: 703-733-4126
Pager: 703-705-9241
Email Pager: 7037059241@my2way.com
Home: 703-430-0805
Cell: 703-477-6045
========================
--0-500569858-1106620946=:13343
Content-Type: text/html; charset=us-ascii
<DIV>Very good explanation Art. I actually understand it. :-)) Thanks.</DIV>
<DIV>One question. </DIV>
<DIV>Since the database for this instance is not a logged database, I can assume that there would be no logged temp tables to be sorted in the rootdbs, is that correct ? </DIV>
<DIV>Then all the temp tables created explicitly ( without an explicit location ), and implicitly, in a non-logged database would be created in the TEMP dbspaces that are listed in the dbspacetemp ?</DIV>
<DIV> </DIV>
<DIV>Thanks.</DIV>
<DIV>Floyd</DIV>
<DIV><BR><BR><B><I>"Art S. Kagel" <KAGEL@bloomberg.net></I></B> wrote:</DIV>
<BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Floyd Wellershaus wrote:<BR>> --0-1611837224-1106318424=:47941<BR>> Content-Type: text/plain; charset=us-ascii<BR>> <BR>> HI Art,<BR>> <BR>>>>Anyway, you can move the temp table IO's off root by adding one or more <BR>> <BR>> of the non-temp dbspaces to DBSPACETEMP. <<<BR>> <BR>> I am not sure I know what you mean here. move NON-temp dbspaces to DBSPACETEMP ? <BR>> btw, I have 3 dbspaces listed in dbspacetemp.<BR><BR>DBSPACETEMP can contain both dbspaces marked as temp (ie created with the -t <BR>option to onspaces) and those that are 'normal' dbspaces. The engine knows <BR>which is which and will use the listed temp dbspaces to locate non-logged <BR>temp tables (ie those created explicitly with the WITH NO LOG clause). The <BR>engine will also locate logged temp tables in 'normal' dbspaces if any are <BR>listed in DBSPACETEMP, if all of
the listed dbspaces are 'temp' spaces then <BR>the engine must locate logged temp tables in ROOTDBS.<BR><BR>> Onstat -g ppf gets output for me, so I assume the tablestats are on. That is neat. Does it tell you io per table ? Where can I read more about that ? for instance, how do I know what tablename is by the partnum ?<BR><BR>See Tsutomu's response for this query.<BR><BR>Art S. Kagel<BR><BR>> Thanks.<BR>> <BR>> "Art S. Kagel" <KAGEL@BLOOMBERG.NET>wrote:<BR>> Floyd Wellershaus wrote:<BR>> <BR>> As Martin pointed out, logged temp tables (including those created by an <BR>> INTO TEMP clause) are created in ROOTDB if there are not 'regular' dbspaces <BR>> listed in DBSPACETEMP, however, given the low number of writes on the two <BR>> chunks prod_root_c[12], I'd say that's not the problem, nor is logical log <BR>> IO. Anyway, you can move the temp table IO's off root by adding one or more <BR>> of the non-temp dbspaces to DBSPACETEMP. I agree with
Martin that you <BR>> should turn on table level stats if not on already and monitor onstat -g ppf.<BR>> <BR><SNIP><BR>Art S. Kagel<BR></BLOCKQUOTE><BR><BR><DIV>
<DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV>
<DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A><BR>Work: 703-733-4126</DIV>
<DIV>Pager: 703-705-9241 </DIV>
<DIV>Email Pager: <A href="mailto:7037059241@my2way.com">7037059241@my2way.com</A></DIV>
<DIV>Home: 703-430-0805</DIV>
<DIV>Cell: 703-477-6045<BR>========================</DIV></DIV>
--0-500569858-1106620946=:13343--
sending to informix-list