max pages / table query

Posted in 2008

Topics: Storage & Space Management, Server Administration

Robert Roussey(IT) — Wed, 07 May 2008 17:09:20 — source: IIUG Forums & Mailing Lists

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

↪ replying to Robert Roussey(IT)

Art Kagel — Wed, 07 May 2008 17:52:36 — source: IIUG Forums & Mailing Lists

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.

↪ replying to Art Kagel

Robert Roussey(IT) — Wed, 07 May 2008 18:55:45 — source: IIUG Forums & Mailing Lists

I apologize, forgot; IDS 10.00.UC4, RedHat AS 3. That query does not work as sysextents has no 'partnum' or 'npused'. Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com Office: 248.727.2620 Cell: 586.838.8980 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, May 07, 2008 1:53 PM To: ids@iiug.org Subject: Re: max pages / table query [12015] 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. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Robert Roussey(IT)

Art Kagel — Wed, 07 May 2008 19:07:42 — source: IIUG Forums & Mailing Lists

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.

↪ replying to Robert Roussey(IT)

Robert Roussey(IT) — Wed, 07 May 2008 19:30:04 — source: IIUG Forums & Mailing Lists

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.