Solved

Performance problem Oracle 9.2.0.1.0 after export import

Posted on 2008-10-16
3
365 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
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
David earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now