| Oracle 8.1.6 Export slowing down at "Exporting Views..." [message #70922] |
Wed, 14 August 2002 10:14  |
Shridhar Kumar
Messages: 5
Registered: August 2002
|
Junior Member |
|
|
Oracle Server version is 8.1.6.3.4 I am doing the export with the following command in a batch file:
exp80 user_name/user_password@server_name file=user_name.dmp owner=user_name
The export goes fine with exporting rows and synonyms, taking about only 45-60 seconds. Then it stands still at "Exporting Views..." for more than 45 minutes. Then it finishes the remaining steps within no time.
1. There are no error messages generated.
2. The dmp file is good and imports fine.
3. This started happening with 2 of our 8 branch office server machines.
4. The two affected machines exported fine and finished the process in under 2 minutes until last week.
5. The export is slow on those machines with any user schema, slowing down at "Exporting Views..."
6. I have re-run catexp.sql, catalog.sql and catproc.sql with no improvements.
Users are complaining about the long time needed for the daily backups.
Please suggest any solutions or patches that might be required. Thanks in advance.
|
|
|
|
|
|
|
|
| Re: Oracle 8.1.6 Export slowing down at "Exporting Views..." [message #70926 is a reply to message #70922] |
Wed, 14 August 2002 15:15  |
Shridhar Kumar
Messages: 5
Registered: August 2002
|
Junior Member |
|
|
Thanks Mahesh!
optimizer_mode=RULE (in init.ora file) did the trick!
Before I did this, the database was in fact at optimizer_mode=CHOOSE
Since this value was never touched before, I am not sure how the export suddenly slowed down. It cannot be the data content. Since, when I import the same dump file on to another machine with comparable hardware, the export occurs very quickly (even with optimzer_mode=CHOOSE on the second machine).
When this had happened earlier with an onsite Oracle Server, I had uninstalled Oracle on the problem machine and reimported the data after reinstalling oracle. That had corrected the problem then. I could not afford to do that this time since the servers were at a remote site.
I wish I can get more insight into this. Thank you very much for the solution, however.
|
|
|
|
| Re: Oracle 8.1.6 Export slowing down at "Exporting Views..." [message #70930 is a reply to message #70926] |
Thu, 15 August 2002 10:20 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
|
I beleive what is happening here has to do with Cost Based Optimizer (CBO) and the way it is effected when an import is done. CBO is always in effect. The difference is when you run ANALYZE on the tables you are using CHOOSE and when there are no statistics on the tables you are using RULE. This is easy to verify by using DBMB_UTILITY.ANALYZE_SCHEMA. You can use this utility to compute the stats and delete the stats. There is no need to modify the parameter OPTIMIZER_MODE and do a restart of the DB. Where import comes in you will notice that the ANALYZE switch default is "Y" so it will compute the stats during an import.
|
|
|
|
| Grant, we'd run ANALYZE_SCHEMA prior to this problem! [message #70937 is a reply to message #70926] |
Fri, 16 August 2002 11:43 |
Shridhar Kumar
Messages: 5
Registered: August 2002
|
Junior Member |
|
|
Grant, your post made me realize what precipitated the problem. A couple of days before we noticed this slowdown, I had indeed run the following script:
<bold>
execute dbms_utility.analyze_schema('SYS', 'ESTIMATE');
execute dbms_utility.analyze_schema('SYSTEM', 'ESTIMATE');
execute dbms_utility.analyze_schema(USER, 'ESTIMATE');
</bold>
This was most certainly the cause. But I did this hoping to improve things with the source database.
In the past, I had the front-end application run ANALYZE individually on all the USER tables and indexes without causing this problem. Only this last time I used ANALYZE_SCHEMA and added SYS & SYSTEM.
Grant, do you think that it is OK to run ANALYZE_SCHEMA on the USER schema but not SYS & SYSTEM?
Thanks in advance.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
