IDS Forum

RE: max pages / table query

Posted By: Robert Roussey(IT)
Date: Wednesday, 7 May 2008, at 3:30 p.m.

In Response To: max pages / table query (Robert Roussey(IT))

Much nicer! Thank you.

Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com

________________________________

From: Art Kagel [mailto:art.kagel@gmail.com]
Sent: Wednesday, May 07, 2008 3:08 PM
To: Robert Roussey(IT); ids@iiug.org
Subject: Fwd: max pages / table query [12014]

Not your problem mine. Combined two queries into one in my head. The
corrected query should be:

select dbsname[1,10], tabname[1,20], count(*) as num_extents, sum(size)
as total_pages
from sysmaster:sysextents
group by 1, 2
-- HAVING count(*) > 50
order by 3 desc;

If you just want to look at troublesome tables uncomment the HAVING
clause and adjust the value.

Art

---------- Forwarded message ----------
From: Art Kagel <art.kagel@gmail.com>
Date: Wed, May 7, 2008 at 1:52 PM
Subject: Re: max pages / table query [12014]
To: Robert.Roussey@spiritair.com, ids@iiug.org

Robert Roussey(IT) wrote:

"No more extents" depends on the pagesize of the table, the number of
'special' columns in the table, and the number of attached indexes.
It's just over 200 for a table with not blob, varchar, lvarchar, etc.
columns and no attached indexes in a 2K pagesize dbspace, about 420 for
a 4K page, etc. and reduced by special columns. I've seen a table with
lots of varchars blow up with ~120 extents. Here's the query I use:

SELECT dbsname, tabname, partnum, sum(npused), count(*) as num_extents,
sum(size) as total_pages
from sysmaster:sysextents
group by 1, 2, 3
order by 4 desc;

I want to check for excessive table extent growth. I know people
on

the list have discussed this before. Is there a query against

systmaster that will give me the # of pages / table (partition)
as 16M

is apparently the magic # at which "No more extents" blows up in
your

face and we'd all like to avoid that.

TIA.

Bob Roussey

Unix / Informix Administration

Spirit Airlines

Robert.Roussey@SpiritAir.com

************************************************************************
*******

Forum Note: Use "Reply" to post a response in the discussion
forum.

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Oninit, the IIUG, nor any other
organization with which I am associated either explicitely or
implicitely. Neither do those opinions reflect those of other
individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

Messages In This Thread

• max pages / table query
  Robert Roussey(IT) -- Wednesday, 7 May 2008, at 1:09 p.m.
  • Re: max pages / table query
    Art Kagel -- Wednesday, 7 May 2008, at 1:52 p.m.
    • RE: max pages / table query
      Robert Roussey(IT) -- Wednesday, 7 May 2008, at 2:55 p.m.
  • Fwd: max pages / table query
    Art Kagel -- Wednesday, 7 May 2008, at 3:07 p.m.
  • RE: max pages / table query
    Robert Roussey(IT) -- Wednesday, 7 May 2008, at 3:30 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.