mschaeffers
asked on
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.l
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.l
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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