That query (taken here from ADSM.QuickFacts) confuses me entirely - can
someone please explain?
SELECT CAST((100 - ( CAST(MAX_REDUCTION_MB AS FLOAT) * 256 ) /
(CAST(USABLE_PAGES AS FLOAT) -
CAST(USED_PAGES AS FLOAT) ) * 100)
AS
DECIMAL(4,2)) AS PERCENT_FRAG FROM
DB
It finds the number of unused pages (usable_pages - used_pages).
Then it takes max-reduction and divides by unusable pages.
But, so what? I don't get it.
The unusable pages - max_reduction tells you how much of your data base is
NOT usable.
BUT again, so what?
That doesn't say whether you need to do a data base reorg or not, does it?
If my max reduction is 8 pages and my unused pages are 10, I've got 2
unusable pages.
But if my data base is 1,000,000 pages, that certainly isn't much
"fragmentation", the way a DB administrator (or space manager) would
traditionally see it. Certainly no reason to do a DB reorg.
WHY isn't the division done with the total usable pages as the numerator?
The data base size has to enter in to the decision to reorg, somehwere.
I'm confoooooooooooosed....
(But then, it's Monday....)
-----Original Message-----
From: Loon, E.J. van - SPLXM [mailto:Eric-van.Loon AT KLM DOT COM]
Sent: Monday, October 20, 2003 6:38 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Online DB Reorg
Hi Guys!
The SQL statement can also be found at Richard's quickfacts page:
http://people.bu.edu/rbs/ADSM.QuickFacts
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines
-----Original Message-----
From: Remco Post [mailto:r.post AT SARA DOT NL]
Sent: Monday, October 20, 2003 12:30
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Online DB Reorg
On Sat, 18 Oct 2003 14:35:09 -0400
"Talafous, John G." <Talafous AT TIMKEN DOT COM> wrote:
> Remco,
> Would you be willing to share your SQL query that reports on DB
> fragmentation?
>
I was allready looking at Eric (he probably saved my thingy somewhere
usefull, I just saved it in my sent-mail folder), here it is...
select cast((100 - ( cast(MAX_REDUCTION_MB as float) * 256 ) / -
(cast(USABLE_PAGES as float) - cast(USED_PAGES as float) ) * 100) as -
decimal(4,2)) as percent_frag from db
Note that I still think this is one of the more useless queries I've ever
build...
> Thanks to all,
> John G. Talafous IS Technical Principal
> The Timken Company Global Software Support
> P.O. Box 6927 Data Management
> 1835 Dueber Ave. S.W. Phone: (330)-471-3390
> Canton, Ohio USA 44706-0927 Fax : (330)-471-4034
> talafous AT timken DOT com http://www.timken.com
--
Met vriendelijke groeten,
Remco Post
SARA - Reken- en Netwerkdiensten http://www.sara.nl
High Performance Computing Tel. +31 20 592 8008 Fax. +31 20 668 3167
"I really didn't foresee the Internet. But then, neither did the computer
industry. Not that that tells us very much of course - the computer industry
didn't even foresee that the century was going to end." -- Douglas Adams
**********************************************************************
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain confidential
and privileged material intended for the addressee only. If you are not the
addressee, you are notified that no part of the e-mail or any attachment may
be disclosed, copied or distributed, and that any other action related to
this e-mail or attachment is strictly prohibited, and may be unlawful. If
you have received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. Koninklijke Luchtvaart
Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be
liable for the incorrect or incomplete transmission of this e-mail or any
attachments, nor responsible for any delay in receipt.
**********************************************************************
|
