Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it

  1. First I rename the original tables in schema B
  2. Then, using SQL Developer I copy the table to copy from schema A to B. (I right click the table to copy and select Copy and choose the destination schema)
  3. Then I create all the indices that exist in the original tables.(Copying tables does not copy indexes, so you have to create them manually)

After the process is over, I run queries against all the tables. Everything goes fine with 9 of them, but with one of the tables the executed query hangs. The next day, when we run the same query against the same table, the execution takes only a moment. I have no idea what the problem is. I tried not adding indexes but it didn't help. I know he way we copy table content might seem weird.

share|improve this question
    
I bet you forgot to type "commit" after doing your activities –  FreshPhilOfSO Feb 10 at 8:56
    
DDL doe snot need commit, it's done implicitly. Furthermore, I don't do it manually, I use table copy wizard. –  Mike JM Feb 10 at 11:23
add comment

2 Answers

up vote 2 down vote accepted

Could it be related to stastics? Remember the same from a data warehouse job.

Table statistics were generated / refreshed not automatically but - once per night. Which can lead to exactly this behavior - first day they suck, then in the night they get refreshed and the query optimizer suddenly makes correct decisions.

Our solution was to manually regenerate statistics during the load processes.

An alternative would be locking behavior.

share|improve this answer
add comment

Not sure if the query against that one table really "hangs", it might just be taking much longer time to execute. If you say things look fine next day, the lack of statistics on the newly created tables might be the reason for poor performance. Next time, you follow your process of "copying" tables, you may want to explicitly collect statistics on newly created tables and their indexes before you run the queries.

share|improve this answer
    
Why I say it really hangs is because the next day the query runs in lest than just a second. –  Mike JM Feb 10 at 8:45
    
Is this one table big compared to others? –  Jayadevan Feb 10 at 9:09
    
Yes, it is. user34040 was right about statistics. Gathering statistics solved the problem –  Mike JM Feb 10 at 10:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.