[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Performance problem Oracle 9.2.0.1.0 after export import

Posted on 2008-10-16
3
Medium Priority
?
373 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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…

650 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