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

On Nov 10, 5:25 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On Nov 10, 3:52 pm, zigzag...@xxxxxxxxx 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.
.

Relevant Pages