Solved

Performance problem Oracle 9.2.0.1.0 after export import

Posted on 2008-10-16
3
370 Views
Last Modified: 2013-12-19
During server reinstall process we had to use exp/imp Oracle utils to backup/restore data.
For some strange reason our new database now works slower than old one. I have compared internal structure of old and new databases using Toad and  found some differences in indexes and constraints lists.
Some indexes/constraints which exists in old database doesn't exist in
the  new one. And strangely there are some indexes/constraints in new database which i can't find in the old one.
The import string used was
imp.EXE FEEDBACK=100000 BUFFER=4000000 COMMIT=N USERID=....    FILE=D:\BACKUP\file.dat LOG=D:\BACKUP\R_SPECTRUM.log FROMUSER=SPECTRUM TOUSER=SPECTRUM IGNORE=Y

0
Comment
Question by:mschaeffers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
David earned 500 total points
ID: 22729912
First, constraints are either explicitly named or system named when they are created.  Unless you did the former, then the different SYS_ objects will not match; the count, however, should be identical.  Second, your indexes may have relational dependencies (foreign keys).  If you will search again (here in EE or elsewhere), you will find a "best practice" to import your tables in one pass with indexes=n, then import your indexes into a text file (indexes=y rows=n indexfile=xx grants=y).  Edit the text file for tablespace names and dependencies, then execute it as a second step.  A schema comparison (using TOAD or other) should give you more satisfactory results.
Third, your imported data probably doesn't have clean and accurate statistics.  Tables and indexes should be reanalyzed at this point in order for the cost-based optimizer (CBO) to work efficiently.
Kind regards,
dvz
zone expert
0
 

Author Comment

by:mschaeffers
ID: 22730051
Herllo dvz

Thanks for your help.
Constraints were create by a script so I don't think they were explicietely named. See example below

CREATE TABLE DataValues (
      DataID       number(38)       constraint PK_DataValues PRIMARY KEY
                        using index storage (initial 1m next 1m pctincrease 0) tablespace dsindex,
      SubgroupID             number(38)       DEFAULT 0       ,
      SampleNumber      number(38)       DEFAULT 0       ,
      Value                   real       DEFAULT 0      
)
storage (initial 10m next 10m pctincrease 0)
tablespace dsdata
;

Yes you are right the database has foreign keys
We have already started to use the database so it is pretty hard to re import again. Doyou have any solutions how to fix the problem in this case. Would reindex fix the problem.

We will also reanalyse the tables and indexes
0
 

Author Closing Comment

by:mschaeffers
ID: 31506670
Hello dvz

Thanks very much for your help.
Constraints were create by a script so I don't think they were explicietely named. See example below

CREATE TABLE DataValues (
      DataID       number(38)       constraint PK_DataValues PRIMARY KEY
                        using index storage (initial 1m next 1m pctincrease 0) tablespace dsindex,
      SubgroupID             number(38)       DEFAULT 0       ,
      SampleNumber      number(38)       DEFAULT 0       ,
      Value                   real       DEFAULT 0      
)
storage (initial 10m next 10m pctincrease 0)
tablespace dsdata
;

Yes you are right the database has foreign keys
We have already started to use the database so it is pretty hard to re import again. Doyou have any solutions how to fix the problem in this case. Would reindex fix the problem.

We will also reanalyse the tables and indexes
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question