Home » RDBMS Server » Server Utilities » schema after export and import slowlier
schema after export and import slowlier [message #306209] Thu, 13 March 2008 05:16 Go to next message
brandointheweb
Messages: 25
Registered: August 2005
Location: Germany
Junior Member
Hi all together,
I have a question for export and import oracle-schemes
With following export-file I created my .dmp-file:
USERID=MyUser/PW@SID1
log=E:\DATENBANKEN\Export\exp_MyUser_1303.log
DIRECT=N
FILE=E:\DATENBANKEN\Export\exp_MyUser_1303.dmp
owner=MyUser
COMPRESS=Y
RECORD=N
STATISTICS=NONE


I imported this user with following file into a new instance:
USERID=MyUser1/PW@SID2
FILE=exp_MyUser_1303.dmp.dmp
fromuser=MyUser
touser=MyUser1
IGNORE=Y
COMMIT=Y
LOG=MyUser1.log


All this worked fine!
BUT:
I noticed that all of my procedures, functions etc. run aproox. 2-3 times slowlier as in the original schema.
I checked everything: all configuration of my instances is the same. The only difference is, that the slowlier schema was imported.

Can anyone give me a hint what to look for?!
Best regards!
Re: schema after export and import slowlier [message #306229 is a reply to message #306209] Thu, 13 March 2008 06:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Probably just because of statistics. You did not import any records.
And not related to this issue, you are inviting trouble with
COMPRESS=Y and COMMIT=Y
Re: schema after export and import slowlier [message #306231 is a reply to message #306229] Thu, 13 March 2008 06:43 Go to previous messageGo to next message
brandointheweb
Messages: 25
Registered: August 2005
Location: Germany
Junior Member
Mahesh,
what You mean by Statistics?
Of course I imported records!
Re: schema after export and import slowlier [message #306236 is a reply to message #306231] Thu, 13 March 2008 06:50 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Sorry my mistake.
Collect statistics again.
execute this from sqlplus.
Substitute scott with your schema name.
exec dbms_stats.gather_schema_stats('SCOTT',method_opt=>'For all indexed columns',cascade=>true);


With this you collect the statistics for all tables, for all indexed columns and indexes. Based on this information CBO will lay the best path to fetch the data.

[Updated on: Thu, 13 March 2008 06:53]

Report message to a moderator

Previous Topic: Backup error import
Next Topic: moving partitioned tables between databases
Goto Forum:
  


Current Time: Sat Dec 10 06:52:10 CST 2016

Total time taken to generate the page: 0.08549 seconds