Re: Are there performancebenefits to reorganizing database-using export/import?

On Nov 10, 5:25 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 10, 3:52 pm, zigzag..._at_yahoo.com wrote:
>
>
>
>
>
> > I am using Oracle 9.2.0.5 on HP UNIX 11i. Since it is a production
> > system, I cannot change version of Oracle any time soon, so please do
> > not suggest that.
>
> > I have a production database using Oracle 9.2.0.5, which has been
> > running for last 3 years since it was upgraded from 8.1.7. At that
> > time we had done full export of 8.1.7 database and then created 9.2.
> > instance and then imported all the application schemas.
>
> > Load on our database has been increasing and there are constant
> > pressures from management to improve performance. We have looked at
> > indexes many times, have lots of memory for SGA and have tuned various
> > init.ora parameters. We utlize three pools buffer, keep and recycle.
> > Being a third party packages, we cannot rewrite queries. Avg CPU
> > Utilization (6 CPU"s) is 30%, memeory utilization is 60% (we have 24
> > GB memeory on the server), so I do not see we are taxing any
> > resources, i..e, adding more CPU's and memory should not help.
>
> > Application is a mix of OLTP and reporting, it is definitely more read
> > than write.
>
> > Are there any benefits to reorganize database using export/import,
> > i.e., we will do a full export of existing database and then delete
> > all objects from application schemas and do schema imports. We will
> > run the dbms_Stats again to recomputed statistics. Of course, we will
> > test all of that in a test environment before making change sin
> > production.
>
> > I have heard different views on reorganization. Some people say it is
> > useless, some people say it can improve performance since data will be
> > compacted in fewer blocks.
> > Appreciate your feedback.
>
> In agreement with Joel's post.
>
> Zigzag, there are some warnings about doing what you suggest, and
> those warnings are well defined in "Expert Oracle Database
> Architecture" (pages 340-341):http://books.google.com/books?id=8_WChqD3nc4C&pg=PA340&lpg=PA340
>
> There are also a couple excellent threads on asktom.oracle.com which
> warn about exporting your database, dropping the database, crossing
> your fingers, and importing.
>
> What happens if you accidentally change the character set when you
> recreate the database and forget to generate an import log file?
> Data, are we missing rows?  The empty set character in ASCII requires
> a single byte, but that same character requires more than one byte in
> other character sets.  So, what happens when a column is defined as
> VARCHAR2(30) and an existing row contains a column value that is
> exactly 30 characters long with an empty set character in the column
> data?
>
> What happens if you accidentally transfer the export file using ASCII
> FTP before trying to import the file (from its transferred to
> location).
>
> You must consider the risks before proceeding.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Thanks a lot, will keep these points in mid. Received on Mon Nov 10 2008 - 17:01:05 CST