how to calculate additional extents from sysmaster 
Author Message
 how to calculate additional extents from sysmaster

Hi,

I am writign a script to calculate additional extents in
a table. Meaning, if a table has 150 extents, how many more
extents can it take before it runs out of extents.

My colleague told me how to calculate using oncheck.
I found a way to get that figure using sysmaster.
The idea is to get the free space available in the
table partition info. From the free space available,
divide the figure by 8 to get the additional number
of extents possible.

This is the script:-

database sysmaster;

select partnum from systabnames where dbsname = "database_name"
and tabname = "table_name" into temp tmp_partnum with no log ;

select physaddr
from   sysptntab
where  sysptntab.partnum in
(select partnum from tmp_partnum)
into temp tmp_physaddr with no log ;

select min(pg_frcnt) pg_frcnt
from   sysphyspaghdrs where pg_pagenum in
(select physaddr from tmp_physaddr) ;

If pg_frcnt returns 140, then 17 more extents are possible.
(140/8) = 17.

Two questions:-

1. This is trivial. The above 3 SQLs can be easily
combined into 1 SQL. However its performance sucks.
It took 5 mintutes to return the result using a single
SQL, as compared to 5 sec for the 3 SQL mentioned above.
Why such a big difference?

2. In the last SQL, I am joining pg_pagenum with
the physaddr of sysptntab. Actually it should be
pg_physaddr joining with sysptntab.physaddr. However
the performance sucks. It takes ages to get the result.
That is because sysphyspaghdrs is indexed on pg_partnum
+ pg_pagenum. sysphyspaghdrs is a view on syspaghdrs
with the view conditions set to pg_pagenum = 0.

Now in sysphypaghdrs, pg_pagenum = pg_physaddr in almost
all cases. That is why substituted pg_pagenum with pg_physaddr
and got the result instantly.
So my question is, will it always be true that in sysphyspaghdrs,
pg_pagenum = pg_physaddr.

TIA.

Ravi.



Sat, 12 Jun 2004 23:38:14 GMT
 how to calculate additional extents from sysmaster

Quote:
----- Original Message -----

Newsgroups: comp.databases.informix
Sent: Tuesday, December 25, 2001 3:38 PM
Subject: how to calculate additional extents from sysmaster

> Hi,

> I am writign a script to calculate additional extents in
> a table. Meaning, if a table has 150 extents, how many more
> extents can it take before it runs out of extents.

Errrmmmmmmmmmmmm......

Don't bother.
Rebuild the table (unload, drop, recreate with:
EXTENT SIZE <your calc for size of entire table + reasonable growth>
NEXT SIZE <a reasonable figure depending on expected growth>)

If you're on a version prior to 7.2, you'll find performance is appalling on
a table with > 8 extents. It's good practise to keep an eye on the number of
extents and keep it low, anyway.
That's part of a DBA's job, innit?

Malc



Sun, 13 Jun 2004 07:58:45 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. SYSMASTER database and extents

2. Moving sysmaster table extents

3. sysmaster extents

4. sysmaster extents

5. Overlapping extents in sysmaster

6. Is sysmaster db always called sysmaster?

7. Calculating when a segment will need a new extent

8. calculating next extent

9. Algorithm for calculating extent size in LMT

10. Calculating First Extent Size

11. Calculating max extent

12. How 2 calculate Free spaces in Extents ?


 
Powered by phpBB® Forum Software