RE: Is this Needed?
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <dofreeman@xxxxxxxxxxx>, "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- Date: Wed, 26 Oct 2005 11:37:02 -0400
Donald,
Is this a needed activity, probably not as a whole, though I will agree
that it's easier to export/import the entire schema. You may have one or more
tables that have been heavy insert and delete that are not chained per say, but
have a lot of wasted space therein. Now the maintenance action that's being
suggested will clean that up, to what other purpose than reclaiming disk space
I don't know. We have a very large table in PeopleSoft that gets archived
periodically. A typical archive run will eliminate 40% to 50% of the rows in
the table & we do export & reimport that table every time they archive just for
the purpose of reclaiming space in the tablespace. They use the utilization
rate of the tablespace as an indicator of having to run the archive process. I
don't agree with the methods, but it does work.
Now, just like our little monster you've probably got people complaining
about performance, and someone has made a decision based on that complaint and
what they believe is the root cause. Shame, good thought, bad decision. We
also had those problems, but after reviewing/capturing sql statement that ran
against that table we found the true problem which was a misused index. BTW:
since I didn't already mention it this application runs in RULE mode. So, what
good is an index on a table that is going to return 80% of the data rows, none
that's what. We had a serious discussion with the application developers &
they did some work. End result no one complains about this table, until the
tablespace utilization in up in the 90% range at which time they believe it's
time to archive again.
And for those who don't think that old thinking is still not around, here's
something from a vendor that I got recently:
"Anything over 100 for an index is a concern "
Old beliefs die hard.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Freeman, Donald
Sent: Wednesday, October 26, 2005 11:19 AM
To: Christian Antognini; Freeman, Donald
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Is this Needed?
I just checked and code tables are set to 10% while key application tables are
set to 30%. I'm pretty sure these aren't going to be changed. I don't think
there is any row chaining there. What are you getting at?
Don Freeman
Database Administrator 1
Bureau of Information Technology
Pennsylvania Department of Health
717-783-8095 Ext 337
-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini@xxxxxxxxxxxx]
Sent: Wednesday, October 26, 2005 11:05 AM
To: dofreeman@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Is this Needed?
Don
> Is this a waste of time?
As usually... it depends. IMHO there are three cases:
- you waste time
- you improve performance
- you decrease performance
Of course the third one is the real problem! And about it... a simple
question: how you will set PCTFREE before inporting the data?
Regards,
Chris
New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com
<http://www.trivadis.com>
Italiano: Lugano (24-Nov)
Français: Genève (17-Nov)
Deutsch: Düsseldorf (23-Nov), Zürich (29-Nov), Stuttgart (13-Dec)
Other related posts:
