0

we have a database with size 16GB.We are running daily backup using EXPDP,but few days ago this EXPDP taking too long to complete(more than 6 hours). my question is

1) does TABLE LOCKS affect performance of EXPDP(i have checked for table locking and found a number tables were on lock(we are updating tables using some procedures which are set to run multiple times for a day)..

2) will hard disk related issue slow down EXPDP performance??

As per your suggestion i have included the query(While the expdp is Running) 

select elapsed_time/1000000 seconds,sql_text,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,USERS_EXECUTING,DISK_READS,BUFFER_GETS,USER_IO_WAIT_TIME from gv$sql where users_executing > 0 order by elapsed_time desc;

For this query i am getting more than 20 records and i will share some records enter image description here

Hasan Fathi
  • 5,610
  • 4
  • 42
  • 60
vishnudas
  • 53
  • 1
  • 10
  • 1
    I'd guess this is a problem with a data dictionary query - one of the queries that generates the metadata, not the data. Even a ridiculously slow hard drive can handle writing 16GB an hour. Look for slow SQL statements that are running at the same time as the export. There are many ways to do that, I'd start with re-running the export and occasionally running a query like this to find slow statements: `select elapsed_time/1000000 seconds, gv$sql.* from gv$sql where users_executing > 0 order by elapsed_time desc;`. If you find something, modify the question to include the query. – Jon Heller Feb 24 '19 at 02:24
  • @JonHeller : Thank you for comments.As per your suggestion i have updated the question.Check the image file and please let me know if you find find any issue – vishnudas Apr 01 '19 at 07:40
  • I don't see any data dictionary queries in the top 7. (Although, on an unrelated note, there does seem to be an extraordinary amount of time spent generating the sequence `LOG_SEQ`. I'd guess the sequence was set to `NOCACHE`. Run `select cache_size from all_sequences where sequence_name = 'LOG_SEQ';` If the cache size is set to 0, then you may be able to significantly improve performance by running `alter sequence log_seq cache 20;`. Also, if it's used in PL/SQL, you can directly assign a sequence instead of selecting from dual.) – Jon Heller Apr 01 '19 at 22:00
  • The export itself is at #7, so you may need to expand the list to find the child queries. You may be able to exclude queries that are application related. – Jon Heller Apr 01 '19 at 22:01
  • @JonHeller :You are right,cache is set 0 for this Sequence.But how Performance could be increased by setting cache_size i am new to Oracle Performance Administration Would you explain this in detail, it would be much appreciable.we a had machine crash and had to up the database on a new machine (we had expdp dump backup) used impdp to up the database.For the first few days everything was gone fine,but in a sudden the whole system gone too slow as on investigation we found that the hard disk is on RAID REBUILD but after the completion of RAID REBUILD there wasn't any change in performance – vishnudas Apr 03 '19 at 11:32
  • Oracle can handle indexes much more efficiently if it can generate values ahead of time and cache them. That's why the default is 20, not 0. Changing that value may make that statement almost disappear from the Top SQL. But that statement may not be directly related to the problem. You still need to find the slow queries related to the export. You may want to look into using tools like AWR, to try to find slow statements and slow wait events. If there are hard drive problems it may show up in those reports. Something is wrong, 16GB should export in 6 minutes, not 6 hours. – Jon Heller Apr 04 '19 at 05:28
  • @JonHeller : Thank you for your valuable replies.As you suggested i will get the AWR report. – vishnudas Apr 09 '19 at 06:46

0 Answers0