how to keep an eye on extents
Hey all. Anyone have a good query that will list by table the number of extents that table is using and the number of extents left to it before error?
"sumGirl" <emebohw@netscape.net> wrote in message
news:a5e13cff.0407071258.38eb00e3@posting.google.com...
> Hey all. Anyone have a good query that will list by table the number
> of extents that table is using and the number of extents left to it
> before error?
If you are not using informix 9.4, then I can help you with a script.
It can be used to alert when a particular table goes above a pre-defined
number of extents, or the number of extents is less than a pre-defined
limit. It can also be used in a script to alert when a particular table
has more than a defined number of pages allocated to it. Informix does
not allow more than 16 million pages for a table.
usage: extent_report -d [-i] [-n] [-r] [-s] [-S] [-p] [-m] [-t] [-R]
-d <database name>
-i (report for indexes,default for tables)
-S (report for system tables)
-n <minimum number of extents>
-r <maximum number of remaining extents>
-p <report used and allocated spaces in pages >
-m <minimum number of allocated pages>
-t <only for table or index mentioned>
-R <minimum number of rows>
-s <sort by table name>
sample output. Note the column Rem Ext which tells how many extents is still
available.
mail me if you interested in the script. The script is in in perl (not perl DBI)
and uses stored procedure.
$ extent_report -d big_bro -s
DATE : Wed Jul 7 17:05:13 EDT
EXTENT REPORT FOR: big_bro@ifmx_flx4 Table: All tables
--------------------------------------------------------------------------------------------------
Table Name |DBSPACCE |Used |Allocated|Num|Rem|Extent |Next |Num of rows
| |space |space |Ext|Ext|Size |Size |
| |(Mb) |(Mb) | | |(KB) |(KB) |
---------------------------------------------------------------------------------------------------
airline_master |bigbro |0 |0 |1 |231|16 |16 |349
airport_master |bigbro |0 |0 |1 |231|16 |16 |2594
rpt_table |bigbro |49 |58 |3 |224|10000 |10000 |264244
search_request |bigbro |389 |390 |9 |222|20000 |20000 |5707304
--------------------------------------------------------------------------------------------------
"sumGirl" <emebohw@netscape.net> wrote in message news:a5e13cff.0407071258.38eb00e3@posting.google.com... > Hey all. Anyone have a good query that will list by table the number > of extents that table is using and the number of extents left to it > before error? I use this. We've adapted it from an original written by Lester Knutsen. --Script: tabextent.sql select n.dbsname[1,20], n.tabname[1,20], count(*) num_of_extents, sum( pe_size) total_size from sysmaster:systabnames n, sysmaster:sysptnext p, systables t where n.partnum = p.pe_partnum and n.partnum = t.partnum and t.tabtype = "T" group by 1,2 order by 3 desc , 4 desc; The algorithim for the maximum number is somewhat arcane I'm afraid.
"Neil Truby" <neil.truby@ardenta.com> wrote in message news:2l38gjF7a55uU1@uni-berlin.de... > "sumGirl" <emebohw@netscape.net> wrote in message > news:a5e13cff.0407071258.38eb00e3@posting.google.com... > > Hey all. Anyone have a good query that will list by table the number > > of extents that table is using and the number of extents left to it > > before error? > > I use this. We've adapted it from an original written by Lester Knutsen. > > --Script: tabextent.sql > > select n.dbsname[1,20], > n.tabname[1,20], > count(*) num_of_extents, > sum( pe_size) total_size > from sysmaster:systabnames n, sysmaster:sysptnext p, systables t > where n.partnum = p.pe_partnum > and n.partnum = t.partnum > and t.tabtype = "T" > group by 1,2 > order by 3 desc , 4 desc; > > The algorithim for the maximum number is somewhat arcane I'm afraid. u take the physical address of the table from the partnum of the table sysptntab. using that partnum go to syspaghdr with this condition where pg_partnum = 0 and g_pagenum = physaddr_just_found_from_sysptntab take the field pg_frcnt and divide it by 8. that will give remaining extents. One of the things I found by experience is that running this as a single query is terribly slow on a live system. I moved the logic to a SP in sysmaster database and did it on a row by row approadh using FOREACH. Lot lot faster. > >