Can we calculate 'data page cluster ratio' using some system tables ?
Manoj
calculate 'data page cluster ratio' using some system tables
?
Regards
Manoj
Carl Kayser
"lgio" | "sput"]) function will provide cluster ratios and space utilization
values.
(Note: the latter will be low except for APL nonclustered indexes.)
More details available at:
http://sybooks.sybase.com/onlinebooks/group-as/asg1251e/blocks/@Generic__BookView
<Manoj> wrote in message news:40f7a410.5f9...@sybase.com...
Sherlock, Kevin
>
> The new (12.5.0.3?) derived_stat (id, indid, ["dpcr" | "drcr" | "ipcr" |
> "lgio" | "sput"]) function will provide cluster ratios and space utilization
> values.
> (Note: the latter will be low except for APL nonclustered indexes.)
Carl, just wondering if you could elaborate on the statement "the latter
will be low except for APL nonclustered indexes". Are you referring to
"space utilization" when you say "the latter"? Why are you saying that
they will be low?
Manoj,
Also note that as a practice, you should always update table/index level
statistics before obtaining the cluster ratios for optdiag,
derived_stat, sp__optdiag or from wherever you get those derived values.
As discussed in a previous thread, the various table/index level stats
(particularly CR counts) aren't kept current in memory unless you run
update statistics to update those values, and therefore reflect accurate
cluster ratios, and other statistics.
> More details available at:
> http://sybooks.sybase.com/onlinebooks/group-as/asg1251e/blocks/@Generic__BookView
>
> <Manoj> wrote in message news:40f7a410.5f9...@sybase.com...
> > We can use optdiag to get data page cluster ratio. Can we
> > calculate 'data page cluster ratio' using some system tables
> > ?
> >
> > Regards
> > Manoj
--
Kevin Sherlock
Staff Info Systems Analyst
Omaha, NE
DexMedia/Amdocs
http://dexonline.com
[TeamSybase]
http://teamsybase.com/kevin.sherlock
Take a look at CodeXchange:
http://www.codexchange.sybase.com
ISUG Enhancement Requests
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement
manoj
Thanks a lot for information.
Is there any method available in ASE 11.9.3 to calculate
'data page cluster ratio' other that optdiag ?
Manoj
> "Sherlock, Kevin"
> <myfirstname.mylastname@d_e_x_m_e_d_i_a.c_o_m> wrote in
> > message news:40FC3117.18E3A62F@d_e_x_m_e_d_i_a.c_o_m...
> > Carl Kayser wrote: >
> > > The new (12.5.0.3?) derived_stat (id, indid, ["dpcr" |
> > > "drcr" | "ipcr" | "lgio" | "sput"]) function will
> provide cluster ratios and space utilization
> > > values.
> > > (Note: the latter will be low except for APL
> nonclustered indexes.) >
> > Carl, just wondering if you could elaborate on the
> > statement "the latter will be low except for APL
> > nonclustered indexes". Are you referring to "space
> utilization" when you say "the latter"?
>
> Yes.
>
> > Why are you saying that they will be low?
>
> Some snips from the attached MS-Word document (which is
> more complete).
>
> Systabstats.datarowsize is not completely defined
> anywhere. It is not necessarily true that the number of
> rows in a page is equal to the pagesize (less the header
> space) divided by the datarowsize. An APL table stores a
> varchar count (1 byte), a row number value (1 byte), and a
> row offset table value (2 bytes) for each row of data.
> However the datarowsize value only includes two bytes.
> Except for APL data pages the computation should be
> (datarowsize +2) or (leafrowsize + 2) instead of
> (datarowsize) or (leafrowsize).
>
> A data example is to have an APL table with a single char
> (17) not null column and 96 rows. The
> systabstats.datarowsize value will be 19. This table will
> exactly fill one data page on a 2K server since (96) * (19
> + 2) = 2016 = (2048 - 32). Insert another row and the
> table will use two pages.
>
> I folllowed up on this issue with Eric Miner in
> June-October, 2002: }
> } I thought on this one and also passed it by my colleague
> who wrote } optdiag. The bottom line reason it's done the
> way it is is to } accommodate all possible page sizes
> which can change in future } versions (12.5 for example).
> }
> } Keep in mind that Space utilization is only and
> estimate. It is not } used by the optimizer or any other
> function. It's there to help you } decide when to reorg.
> }
> } Hi Eric,
> }
> } He say "the computation should use (datarowsize + 2)"
> instead of } datarowsize in a 12.0 server. That may be
> right, however we use the } macros in the function below
> to keep track of sizes, which may change } with new
> releases. Eg, } APL_XXXXXX(pagesize) gives the number of
> bytes available in an APL } page for any page size and any
> release. <code fragment clipped> }
> [Attachment: SpaceUtil.doc]
Manoj Kumar
<manoj> wrote in message news:40fd1582.753...@sybase.com...
Sherlock, Kevin
proprietary information not given out by Sybase. You could try to
derive it yourself by understanding what the Cluster Ratio calculates.
But the answer to your question is no, there isn't a published formula
or function that you could use.
Consider that CR counts are the number of "extent jumps" made when
scanning the entire leaf page chain. The minimum number of "extent
jumps" is equal to the minimum number of extents that could hold the
total number of leaf pages. Given this info, you could either come up
with the actual formula, or something that closely tracks with it.