Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Cluster Ratio Calculation Builtin

4 posts in Product Futures Discussion Last posting was on 2003-08-18 19:49:49.0Z
putnamr Posted on 2003-08-15 20:25:38.0Z
Sender: 134f.3f3d410b.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Cluster Ratio Calculation Builtin
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f3d41c8.1356.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 15 Aug 2003 13:25:44 -0700, 10.22.241.42
Lines: 19
NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
Date: 15 Aug 2003 13:25:38 -0700
X-Trace: forums-1-dub 1060979138 10.22.241.42 (15 Aug 2003 13:25:38 -0700)
X-Original-Trace: 15 Aug 2003 13:25:38 -0700, forums-2-dub.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1334
Article PK: 95574

Hello,

As of ASE 12.5.0.3 ASE has a new builtin which can give you
the following:
Data Page Cluster Ratio "DPCR", Data Row Cluster Ratio
"DRCR", Index Page Cluster Ratio "IPCR", Large I/O
Efficiency "LGIO", and Space Utilization "SPUT".

DERIVED_STAT(Object name/id, Index name/id, Option):

Examples)
DERIVED_STAT(so.id, si.indid, "dpcr")
DERIVED_STAT(so.id, si.indid, "drcr")
DERIVED_STAT(so.id, si.indid, "ipcr")
DERIVED_STAT(so.id, si.indid, "lgio")
DERIVED_STAT(so.id, si.indid, "sput")

Thanks,
Ryan Putnam


putnamr Posted on 2003-08-15 20:29:30.0Z
Sender: 135a.3f3d4217.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Cluster Ratio Calculation Builtin
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f3d42b1.1367.846930886@sybase.com>
References: <3f3d41c8.1356.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 15 Aug 2003 13:29:37 -0700, 10.22.241.42
Lines: 212
NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
Date: 15 Aug 2003 13:29:30 -0700
X-Trace: forums-1-dub 1060979370 10.22.241.42 (15 Aug 2003 13:29:30 -0700)
X-Original-Trace: 15 Aug 2003 13:29:30 -0700, forums-2-dub.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1335
Article PK: 95573

Below is a view which could be useful:

The view SINF_STATS_INDEXES requires the following be
inserted into the master.dbo.spt_values table.

INSERT INTO master.dbo.spt_values (name, number, type, low,
high, msgnum)
VALUES ("INDEX IO RATE", 0, "IO", 250000, 500000, 1000000)
go

low = low I/O rate per min in pages
high = med I/O rate per min in pages
msgnum = high I/O rate per min in pages

CREATE VIEW SINF_STATS_INDEXES
AS
-- AUTHOR: Ryan Putnam
-- INTENT: Catalog View

SELECT su.name AS "OWNER",
so.name AS "OBJNAME",
si.name AS "IDXNAME",
ss.name AS "SEGNAME",
CASE
WHEN si.status2 & 512 = 512 OR si.status & 16 = 16
THEN "CLUSTERED"
WHEN si.indid = 0
THEN "TABLE"
ELSE "NONCLUSTERED"
END AS "IDXTYPE",
si.indid AS "INDID",
st.indexheight AS "IDX_HEIGHT",
CASE
WHEN (so.sysstat2 & 8192) = 8192
THEN "Allpages"
WHEN (so.sysstat2 & 16384) = 16384
THEN "Datapages"
WHEN (so.sysstat2 & 32768) = 32768
THEN "Datarows"
ELSE "Allpages"
END AS 'LOCK_SCHEME',
CASE
WHEN si.indid IN (0,1)
THEN st.pagecnt
ELSE st.leafcnt
END AS "NUM_PAGES",
CASE
WHEN si.indid IN (0,1)
THEN ROWCNT(si.doampg)
ELSE ROWCNT(si.ioampg)
END AS "ROW_COUNT",
st.oamapgcnt AS "OAM_PAGES",
CASE
WHEN si.indid IN (0,1)
THEN st.datarowsize
ELSE st.leafrowsize
END AS "AVG_ROW_SIZE",
CASE
WHEN si.indid IN (0,1)
THEN st.dpagecrcnt
ELSE st.ipagecrcnt
END AS "EXT_CRCNT",
CASE
WHEN si.indid IN (0,1)
THEN CEILING(st.pagecnt / 8.0)
ELSE CEILING(st.leafcnt / 8.0)
END AS "EXT_CNT",
CASE
WHEN si.indid IN (0,1) AND st.datarowsize > 0
THEN CONVERT(INT, (pagesize(so.name) - CASE WHEN
so.sysstat2 & 16384 = 16384 OR so.sysstat2 & 32768 = 32768
THEN 44 ELSE 32 END) / st.datarowsize)
WHEN si.indid IN (0,1)
THEN 0.0
WHEN st.leafrowsize > 0
THEN CONVERT(INT, (pagesize(so.name) - CASE WHEN
so.sysstat2 & 16384 = 16384 OR so.sysstat2 & 32768 = 32768
THEN 44 ELSE 32 END) / st.leafrowsize)
ELSE 0.0
END AS "ROWS_PER_PAGE",
CASE
WHEN si.indid IN (0,1) AND st.datarowsize > 0
THEN CEILING(ROWCNT(si.doampg) / CONVERT(INT,
(pagesize(so.name) - CASE WHEN so.sysstat2 & 16384 = 16384
OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32 END) /
st.datarowsize))
WHEN si.indid IN (0,1)
THEN 0.0
WHEN st.leafrowsize > 0
THEN CEILING(ROWCNT(si.ioampg) / CONVERT(INT,
(pagesize(so.name) - CASE WHEN so.sysstat2 & 16384 = 16384
OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32 END) /
st.leafrowsize))
ELSE 0.0
END AS "OPT_NUM_PAGES",
CASE
WHEN si.indid IN (0,1) AND st.datarowsize > 0
THEN CEILING(CEILING(ROWCNT(si.doampg) /
CONVERT(INT, (pagesize(so.name) - CASE WHEN so.sysstat2 &
16384 = 16384 OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32
END) / st.datarowsize)) / 8.0)
WHEN si.indid IN (0,1)
THEN 0.0
WHEN st.leafrowsize > 0
THEN CEILING(CEILING(ROWCNT(si.ioampg) /
CONVERT(INT, (pagesize(so.name) - CASE WHEN so.sysstat2 &
16384 = 16384 OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32
END) / st.leafrowsize)) / 8.0)
ELSE 0.0
END AS "OPT_NUM_EXTS",
CASE
WHEN si.indid IN (0,1) AND st.datarowsize > 0
THEN st.pagecnt - CEILING(ROWCNT(si.doampg) /
CONVERT(INT, (pagesize(so.name) - CASE WHEN so.sysstat2 &
16384 = 16384 OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32
END) / st.datarowsize))
WHEN si.indid IN (0,1)
THEN 0.0
WHEN st.leafrowsize > 0
THEN st.leafcnt - CEILING(ROWCNT(si.ioampg) /
CONVERT(INT, (pagesize(so.name) - CASE WHEN so.sysstat2 &
16384 = 16384 OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32
END) / st.leafrowsize))
ELSE 0.0
END AS "WASTED_PAGES",
CASE
WHEN si.indid IN (0,1) AND
CEILING(ROWCNT(si.doampg)) > 0 AND st.datarowsize > 0
THEN CEILING(st.pagecnt / 8.0) -
CEILING(CEILING(ROWCNT(si.doampg) / CONVERT(INT,
(pagesize(so.name) - CASE WHEN so.sysstat2 & 16384 = 16384
OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32 END) /
st.datarowsize)) / 8.0)
WHEN si.indid IN (0,1)
THEN 0.0
WHEN CEILING(ROWCNT(si.ioampg)) > 0 AND
st.leafrowsize > 0
THEN CEILING(st.leafcnt / 8.0) -
CEILING(CEILING(ROWCNT(si.ioampg) / CONVERT(INT,
(pagesize(so.name) - CASE WHEN so.sysstat2 & 16384 = 16384
OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32 END) /
st.leafrowsize)) / 8.0)
ELSE 0.0
END AS "WASTED_EXTS",
ABS(st.forwrowcnt) AS "FROW_CNT",
ABS(st.delrowcnt) AS "DROW_CNT",
CASE
WHEN si.indid IN (0,1) OR si.status2 & 512 = 512 OR
si.status & 16 = 16
THEN (POWER(LOG10( (st2.pagecnt + 11)),9) /
spt.low )
* (1.0 + (
POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
ELSE (POWER(LOG10( (st2.pagecnt + 11)),8) / spt.low
)
* (1.0 + (
POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
END AS "REORG_MIN_MAX",
CASE
WHEN si.indid IN (0,1) OR si.status2 & 512 = 512 OR
si.status & 16 = 16
THEN (POWER(LOG10( (st2.pagecnt + 11)),9) /
spt.high )
* (1.0 + (
POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
ELSE (POWER(LOG10( (st2.pagecnt + 11)),8) /
spt.high )
* (1.0 + (
POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
END AS "REORG_MIN_MED",
CASE
WHEN si.indid IN (0,1) OR si.status2 & 512 = 512 OR
si.status & 16 = 16
THEN (POWER(LOG10( (st2.pagecnt + 11)),9) /
spt.msgnum )
* (1.0 + (
POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
ELSE (POWER(LOG10( (st2.pagecnt + 11)),8) /
spt.msgnum )
* (1.0 + (
POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
END AS "REORG_MIN_MIN",
DERIVED_STAT(so.id, si.indid, "dpcr") AS
"DATA_PGE_CLSTR_RATIO",
DERIVED_STAT(so.id, si.indid, "drcr") AS
"DATA_ROW_CLSTR_RATIO",
DERIVED_STAT(so.id, si.indid, "ipcr") AS
"IDX_PGE_CLSTR_RATIO",
DERIVED_STAT(so.id, si.indid, "lgio") AS
"LARGE_IO_EFF",
DERIVED_STAT(so.id, si.indid, "sput") AS
"SPACE_UTIL_EFF"
FROM systabstats st,
sysobjects so,
sysusers su,
sysindexes si,
sysindexes si2,
systabstats st2,
syssegments ss,
master.dbo.spt_values spt
WHERE st.id = so.id
AND so.uid = su.uid
AND st.id = si.id
AND st.indid = si.indid
AND si.segment = ss.segment
AND spt.type = "IO"
AND spt.number = 0
AND st.id = si2.id
AND st.id = st2.id
AND si2.indid IN (0,1)
AND st2.indid = si2.indid
go


Eric Miner Posted on 2003-08-18 17:55:44.0Z
Message-ID: <3F4112FC.21883F6E@sybase.com>
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
To: putnamr@river.it.gvsu.edu
Subject: Re: Cluster Ratio Calculation Builtin
References: <3f3d41c8.1356.846930886@sybase.com> <3f3d42b1.1367.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: eminer2k.sybase.com
X-Original-Trace: 18 Aug 2003 10:56:03 -0700, eminer2k.sybase.com
Lines: 222
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Aug 2003 10:55:09 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Aug 2003 10:55:44 -0700
X-Trace: forums-1-dub 1061229344 10.22.108.75 (18 Aug 2003 10:55:44 -0700)
X-Original-Trace: 18 Aug 2003 10:55:44 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1336
Article PK: 95572

This looks interesting Ryan. Could you give us a short write up on what
the view reports and why it's useful.

Thanks

Eric

putnamr@river.it.gvsu.edu wrote:

> Below is a view which could be useful:
>
> The view SINF_STATS_INDEXES requires the following be
> inserted into the master.dbo.spt_values table.
>
> INSERT INTO master.dbo.spt_values (name, number, type, low,
> high, msgnum)
> VALUES ("INDEX IO RATE", 0, "IO", 250000, 500000, 1000000)
> go
>
> low = low I/O rate per min in pages
> high = med I/O rate per min in pages
> msgnum = high I/O rate per min in pages
>
> CREATE VIEW SINF_STATS_INDEXES
> AS
> -- AUTHOR: Ryan Putnam
> -- INTENT: Catalog View
>
> SELECT su.name AS "OWNER",
> so.name AS "OBJNAME",
> si.name AS "IDXNAME",
> ss.name AS "SEGNAME",
> CASE
> WHEN si.status2 & 512 = 512 OR si.status & 16 = 16
> THEN "CLUSTERED"
> WHEN si.indid = 0
> THEN "TABLE"
> ELSE "NONCLUSTERED"
> END AS "IDXTYPE",
> si.indid AS "INDID",
> st.indexheight AS "IDX_HEIGHT",
> CASE
> WHEN (so.sysstat2 & 8192) = 8192
> THEN "Allpages"
> WHEN (so.sysstat2 & 16384) = 16384
> THEN "Datapages"
> WHEN (so.sysstat2 & 32768) = 32768
> THEN "Datarows"
> ELSE "Allpages"
> END AS 'LOCK_SCHEME',
> CASE
> WHEN si.indid IN (0,1)
> THEN st.pagecnt
> ELSE st.leafcnt
> END AS "NUM_PAGES",
> CASE
> WHEN si.indid IN (0,1)
> THEN ROWCNT(si.doampg)
> ELSE ROWCNT(si.ioampg)
> END AS "ROW_COUNT",
> st.oamapgcnt AS "OAM_PAGES",
> CASE
> WHEN si.indid IN (0,1)
> THEN st.datarowsize
> ELSE st.leafrowsize
> END AS "AVG_ROW_SIZE",
> CASE
> WHEN si.indid IN (0,1)
> THEN st.dpagecrcnt
> ELSE st.ipagecrcnt
> END AS "EXT_CRCNT",
> CASE
> WHEN si.indid IN (0,1)
> THEN CEILING(st.pagecnt / 8.0)
> ELSE CEILING(st.leafcnt / 8.0)
> END AS "EXT_CNT",
> CASE
> WHEN si.indid IN (0,1) AND st.datarowsize > 0
> THEN CONVERT(INT, (pagesize(so.name) - CASE WHEN
> so.sysstat2 & 16384 = 16384 OR so.sysstat2 & 32768 = 32768
> THEN 44 ELSE 32 END) / st.datarowsize)
> WHEN si.indid IN (0,1)
> THEN 0.0
> WHEN st.leafrowsize > 0
> THEN CONVERT(INT, (pagesize(so.name) - CASE WHEN
> so.sysstat2 & 16384 = 16384 OR so.sysstat2 & 32768 = 32768
> THEN 44 ELSE 32 END) / st.leafrowsize)
> ELSE 0.0
> END AS "ROWS_PER_PAGE",
> CASE
> WHEN si.indid IN (0,1) AND st.datarowsize > 0
> THEN CEILING(ROWCNT(si.doampg) / CONVERT(INT,
> (pagesize(so.name) - CASE WHEN so.sysstat2 & 16384 = 16384
> OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32 END) /
> st.datarowsize))
> WHEN si.indid IN (0,1)
> THEN 0.0
> WHEN st.leafrowsize > 0
> THEN CEILING(ROWCNT(si.ioampg) / CONVERT(INT,
> (pagesize(so.name) - CASE WHEN so.sysstat2 & 16384 = 16384
> OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32 END) /
> st.leafrowsize))
> ELSE 0.0
> END AS "OPT_NUM_PAGES",
> CASE
> WHEN si.indid IN (0,1) AND st.datarowsize > 0
> THEN CEILING(CEILING(ROWCNT(si.doampg) /
> CONVERT(INT, (pagesize(so.name) - CASE WHEN so.sysstat2 &
> 16384 = 16384 OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32
> END) / st.datarowsize)) / 8.0)
> WHEN si.indid IN (0,1)
> THEN 0.0
> WHEN st.leafrowsize > 0
> THEN CEILING(CEILING(ROWCNT(si.ioampg) /
> CONVERT(INT, (pagesize(so.name) - CASE WHEN so.sysstat2 &
> 16384 = 16384 OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32
> END) / st.leafrowsize)) / 8.0)
> ELSE 0.0
> END AS "OPT_NUM_EXTS",
> CASE
> WHEN si.indid IN (0,1) AND st.datarowsize > 0
> THEN st.pagecnt - CEILING(ROWCNT(si.doampg) /
> CONVERT(INT, (pagesize(so.name) - CASE WHEN so.sysstat2 &
> 16384 = 16384 OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32
> END) / st.datarowsize))
> WHEN si.indid IN (0,1)
> THEN 0.0
> WHEN st.leafrowsize > 0
> THEN st.leafcnt - CEILING(ROWCNT(si.ioampg) /
> CONVERT(INT, (pagesize(so.name) - CASE WHEN so.sysstat2 &
> 16384 = 16384 OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32
> END) / st.leafrowsize))
> ELSE 0.0
> END AS "WASTED_PAGES",
> CASE
> WHEN si.indid IN (0,1) AND
> CEILING(ROWCNT(si.doampg)) > 0 AND st.datarowsize > 0
> THEN CEILING(st.pagecnt / 8.0) -
> CEILING(CEILING(ROWCNT(si.doampg) / CONVERT(INT,
> (pagesize(so.name) - CASE WHEN so.sysstat2 & 16384 = 16384
> OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32 END) /
> st.datarowsize)) / 8.0)
> WHEN si.indid IN (0,1)
> THEN 0.0
> WHEN CEILING(ROWCNT(si.ioampg)) > 0 AND
> st.leafrowsize > 0
> THEN CEILING(st.leafcnt / 8.0) -
> CEILING(CEILING(ROWCNT(si.ioampg) / CONVERT(INT,
> (pagesize(so.name) - CASE WHEN so.sysstat2 & 16384 = 16384
> OR so.sysstat2 & 32768 = 32768 THEN 44 ELSE 32 END) /
> st.leafrowsize)) / 8.0)
> ELSE 0.0
> END AS "WASTED_EXTS",
> ABS(st.forwrowcnt) AS "FROW_CNT",
> ABS(st.delrowcnt) AS "DROW_CNT",
> CASE
> WHEN si.indid IN (0,1) OR si.status2 & 512 = 512 OR
> si.status & 16 = 16
> THEN (POWER(LOG10( (st2.pagecnt + 11)),9) /
> spt.low )
> * (1.0 + (
> POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
> ELSE (POWER(LOG10( (st2.pagecnt + 11)),8) / spt.low
> )
> * (1.0 + (
> POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
> END AS "REORG_MIN_MAX",
> CASE
> WHEN si.indid IN (0,1) OR si.status2 & 512 = 512 OR
> si.status & 16 = 16
> THEN (POWER(LOG10( (st2.pagecnt + 11)),9) /
> spt.high )
> * (1.0 + (
> POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
> ELSE (POWER(LOG10( (st2.pagecnt + 11)),8) /
> spt.high )
> * (1.0 + (
> POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
> END AS "REORG_MIN_MED",
> CASE
> WHEN si.indid IN (0,1) OR si.status2 & 512 = 512 OR
> si.status & 16 = 16
> THEN (POWER(LOG10( (st2.pagecnt + 11)),9) /
> spt.msgnum )
> * (1.0 + (
> POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
> ELSE (POWER(LOG10( (st2.pagecnt + 11)),8) /
> spt.msgnum )
> * (1.0 + (
> POWER(LOG10(ROWCNT(si2.doampg)+11),2) / 4) )
> END AS "REORG_MIN_MIN",
> DERIVED_STAT(so.id, si.indid, "dpcr") AS
> "DATA_PGE_CLSTR_RATIO",
> DERIVED_STAT(so.id, si.indid, "drcr") AS
> "DATA_ROW_CLSTR_RATIO",
> DERIVED_STAT(so.id, si.indid, "ipcr") AS
> "IDX_PGE_CLSTR_RATIO",
> DERIVED_STAT(so.id, si.indid, "lgio") AS
> "LARGE_IO_EFF",
> DERIVED_STAT(so.id, si.indid, "sput") AS
> "SPACE_UTIL_EFF"
> FROM systabstats st,
> sysobjects so,
> sysusers su,
> sysindexes si,
> sysindexes si2,
> systabstats st2,
> syssegments ss,
> master.dbo.spt_values spt
> WHERE st.id = so.id
> AND so.uid = su.uid
> AND st.id = si.id
> AND st.indid = si.indid
> AND si.segment = ss.segment
> AND spt.type = "IO"
> AND spt.number = 0
> AND st.id = si2.id
> AND st.id = st2.id
> AND si2.indid IN (0,1)
> AND st2.indid = si2.indid
> go


putnamr Posted on 2003-08-18 19:49:49.0Z
Sender: 436c.3f412cfa.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Cluster Ratio Calculation Builtin
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f412ddd.43ac.846930886@sybase.com>
References: <3f3d41c8.1356.846930886@sybase.com> <3f3d42b1.1367.846930886@sybase.com><3F4112FC.21883F6E@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Aug 2003 12:49:49 -0700
X-Trace: forums-1-dub 1061236189 10.22.241.41 (18 Aug 2003 12:49:49 -0700)
X-Original-Trace: 18 Aug 2003 12:49:49 -0700, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1337
Article PK: 95575


> This looks interesting Ryan. Could you give us a short
> write up on what the view reports and why it's useful.

I use the view to get an idea of the cluster ratio, space
utilization, large I/O efficiencies for objects. In
addition, I use the view to determine approximately how many
extents and pages are being wasted due to the size of the
table/index. I also use three other column reorg_min_min,
reorg_min_med, reorg_min_max to get a gut feel of how long
it will take to reorg the object. I also include the extent
that the object is on, so that one can easily determine what
objects are the biggest and/or have the most wasted space on
a segment. Mainly, I use the view for a daily reorg process
that I run to determine what objects qualify for the reorg.
I need to know times, and what objects are the highest
candidates for reorgs.

Thanks,
Ryan