• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Performance problem Oracle 9.2.0.1.0 after export import

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
mschaeffers
Asked:
mschaeffers
  • 2
1 Solution
 
DavidSenior Oracle Database AdministratorCommented:
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
 
mschaeffersAuthor Commented:
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
 
mschaeffersAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now