Sent: Thursday, January 08, 2004 6:34 AM
Subject: RE: table reorganizations
Jolene,
Tables should never *need* to be reorganized. This is an old
falacy. If
you know how big a table is going to grow, say in a year, then
place it in a
Locally Managed
.
There are of course other cases but you get my point ;)
Cheers
Richard
- Original Message -
From: Mercadante, Thomas F
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 08, 2004 6:34 AM
Subject: RE: table reorganizations
Jolene,
Tables should
Niall,
In the first case disk space was the primary reason, performance improvement
being a positive side-affect, so as to avoid a major disk array upgrade. I know
that new inserts would use the deleted space in the pruged tables. However that
free-space within the table block isn't very
- Original Message -
From: Mercadante, Thomas F
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 08, 2004 6:34 AM
Subject: RE: table reorganizations
Jolene,
Tables should never *need* to be reorganized. This is an old falacy. If
you know how big
-
From: Mercadante, Thomas F
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 08, 2004 6:34 AM
Subject: RE: table reorganizations
Jolene,
Tables should never *need* to be reorganized. This is an old falacy.
If
you know how big a table is going to grow, say
-
From: Mercadante, Thomas F
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 08, 2004 6:34 AM
Subject: RE: table reorganizations
Jolene,
Tables should never *need* to be reorganized. This is an old
falacy. If
you know how big a table is going to grow
08, 2004 6:34 AM
Subject: RE: table reorganizations
Jolene,
Tables should never *need* to be reorganized. This is an old
falacy. If
you know how big a table is going to grow, say in a year, then
place it in a
Locally Managed tablespace with extent sizes
.
There are of course other cases but you get my point ;)
Cheers
Richard
- Original Message -
From: Mercadante, Thomas F
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 08, 2004 6:34 AM
Subject: RE: table reorganizations
, January 08, 2004 6:34 AM
Subject: RE: table reorganizations
Jolene,
Tables should never *need* to be reorganized. This is an
old
falacy. If
you know how big a table is going to grow, say in a year, then
place it in a
Locally Managed tablespace
Hi Chris
Richard,
I agree there are a number of reasons for reorganising
tables. LMTs remove the
need to reorganise a tablespace but not to reorganise a
table. Two further real- ilfe examples of table reorgs:
1) The purge programs have at last been written and run
deleting data 2
it just shattered
BTW: Hippo Birdie!
Cheers
Nuno Souto
[EMAIL PROTECTED]
- Original Message -
soul, you'll see that I'm a real sweetheart. Humble and modest, too. Mirror, mirror
on the wall,
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nuno
I'm surprised at these responses. I'm asking what sql statement most
people use to identify tables that need reorganization because of
holes.
We had an Oracle consultant here and he uses
Select table_name,
blocks-((num_rows*avg_row_len/block_size)*(1+(pct_free/100))) blkdiff
From
get my point ;)
Cheers
Richard
- Original Message -
From:
Mercadante, Thomas F
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 08, 2004 6:34
AM
Subject: RE: table reorganizations
Jolene,
Tables should never *need* to be reorganized
Comments in-line.
-Original Message-
Sent: Wednesday, January 07, 2004 10:59 PM
To: Multiple recipients of list ORACLE-L
There are a lot of tuning authorities that make hard
and fast rules about how to find problem areas by simply
running a few queries.
It is unfortunately, not that
Nelson, I think you're right on both accounts.
About how a fragmented table would show up in 10046 data: there are lots
of people on the list. Maybe one has the time to try it. If it takes
more time, it'll show up in response time *somehow*. If not, then it's
simply not a problem for the case
Title: Message
Jolene,
When I
first saw your posting, I considered if it was intended as
bait;-)
You'll
have to forgive me for thinking so, as these are popular topics of
contention.
Now,
for the forthright (and hopefully bait-free) answer:
Please
define what you mean by
Lemme guess: you just started on your new job as a DBA? You are
another person to which can only wholeheartedly recommend Jonathan's book.
As for your questions, the answer is 42.
On 01/07/2004 02:39:26 PM, Shrake, Jolene wrote:
What SQL statement do you use to identify tables that need
Mladen Gogala scribbled on the wall in glitter crayon:
Lemme guess: you just started on your new job as a DBA? You are
another person to which can only wholeheartedly recommend Jonathan's
book. As for your questions, the answer is 42.
actually, if she's just starting out, i'd recommend
Shrake, Jolene wrote:
What SQL statement do you use to identify tables that need
reorganization?
How do you identify tables that are used in full table scans? How
often do you run this query?
Thanks,
Jolene
Jolene,
If your tables are reasonably sized initially, very few reasons
SQL select owner, object_name from dba_objects
2 where object_type like 'TABLE%' and status like 'NEEDS REORG%'
no rows selected
which obviously tells me that my system is fine. No reorgs required.
At 12:59 PM 1/7/2004, you wrote:
Lemme guess: you just started on your new job as a DBA? You
Actually, read The Goal by Eliyahu Goldratt first, then the techie books.
57 is the magic number at Heinz...
Thater, William wrote:
Mladen Gogala scribbled on the wall in glitter crayon:
Lemme guess: you just started on your new job as a DBA? You are
another person to which can only
I usually recommend Gospel by Jonathan for its completeness
and a wide range of subjects. The book you mentioned is great
for beginner as well. As for the number 42, I'll continue using
it until this Saturday (1/10/2004) when it will become 43. Inflation
is not as big as you think.
PS:
---
I was
And for us dylsexics it has always been 24
At 01:09 PM 1/7/2004, you wrote:
and are you sure it's not 57 now due to inflation?
--
Bill Shrek Thater ORACLE DBA
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official
Mladen Gogala scribbled on the wall in glitter crayon:
I was born on 1/10/1961, and that makes January 10th so special.
I don't have to work on that great day, mostly because it's Saturday.
happy early birthday, you young whippersnapper you.;-)
--
Bill Shrek Thater ORACLE DBA
I'm
.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
Stephane Faroult
Sent: Wednesday, January 07, 2004 2:14 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: table reorganizations
Shrake, Jolene
I'm surprised at these responses. I'm asking what sql statement most
people use to identify tables that need reorganization because of
holes.
We had an Oracle consultant here and he uses
Select table_name,
blocks-((num_rows*avg_row_len/block_size)*(1+(pct_free/100))) blkdiff
From dba_tables
Title: RE: table reorganizations
beers for all in celebration of the birthday!! Rachel's buying:-)
greg
-Original Message-
From: Mladen Gogala [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 07, 2004 3:25 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: table
The point of these questions is... why do you think you have to
reorganize the tables?
Define a hole. How does it get created? Is it ever filled in (as in,
do you ever insert rows)? Do you ever delete or update?
Figure out WHY you want to do something before you try to solve it.
or, in the same
I guess some of the folks on the list are in a playful
mood today...
The need for a table reorganization depends on how it is
used.
The query you cite might be illuminating if the table in
question is mostly accessed by full table scans, as it
seems to identify tables with large gaps due to
Or in yet other words, is it worth spending two or three days
(preparation + actual reorg - preferably on a sunday morning between 2
and 4am) on an inherently risky operation to shave 0.01% off response
times ? Nobody will notice, or hardly. There is certainly much more to
be gained checking
Shrake, Jolene [EMAIL PROTECTED] wrote:
I'm surprised at these responses. I'm asking what sql statement most
people use to identify tables that need reorganization because of
holes.
You don't have to be. There is plenty of material available today online
that demonstrates the futility of
First, with things like ASSM you no longer have to worry about that.
Second, why would you want to reorganize tables? Just because some
artificially determined number is not what your consultant says it
should be? Do you have any chained rows? Did you analyze table for
chained rows? What
ahem. pretty free with the non-existent budget, aren't you?
I can probably afford virtual beers, or even the virtual single-malt of
your choice :)
--- Loughmiller, Greg [EMAIL PROTECTED] wrote:
beers for all in celebration of the birthday!! Rachel's buying:-)
greg
-Original
Yep, I agree that reorganizing table in order to get it more compact is
pointless if you have more data coming in all the time anyway (most of big
tables tend to grow).
But there is one (not very likely) case where rebuild might help to reuse
hidden space - it's with freelist managed tables where
Hi,
Guys, in this case can we chill (in a friendly way)
about the naive questions. At least her company has a
job posting for Senior Oracle DBA. E.g.:
http://pella.kenexa.com/pellav4/newhr/jobdesc.asp?ID=1194
Maybe they really do need help? Sorry, their web site
is really slow, too. ;-)
HTH.
select *
from dba_objects
where object_id in (select obj# from sys.obj$ sample (5))
/
ps: this is not a serious answer ... don't try this on your production system.
Raj
Rajendra dot Jamadagni at nospamespn dot com
Title: Message
Jolene,
Tables
should never *need* to be reorganized. This is an old falacy. If you
know how big a table is going to grow, say in a year, then place it in a Locally
Managed tablespace with extent sizes to hold enough data for one year (say
1M).
You
should never have to
Using iSQL*Plus, I like to run 'select * from
dba_objects' as my 'TPC for dummies' test in the lab.
Hehehe.
In isolation (LAN/DB), the query completes in just
over 45 seconds, weighing in at a 250MB RAM IE session
on W2K client.
Agreed its not probably the type of thing to do in
production, nor
Michael Thomas [EMAIL PROTECTED] wrote:
Guys, in this case can we chill (in a friendly way)
about the naive questions. At least her company has a
job posting for Senior Oracle DBA. E.g.:
Chill? Don't recall toasting...
Yeah, looks like the con-sultant might have caused
enough trouble.
I'm surprised at these responses.
Please don't take offense. It appears that you have
received some very informative answers in addition to
the facetious ones. ( which I *did* expect. Mladen
never lets me down. )
To reiterate the point of responses to your question:
Running a SQL query to
On 2004.01.07 22:59, Jared Still wrote:
I'm surprised at these responses.
Please don't take offense. It appears that you have
received some very informative answers in addition to
the facetious ones. ( which I *did* expect. Mladen
never lets me down. )
I'm trying to find the right
41 matches
Mail list logo
