Home » SQL & PL/SQL » SQL & PL/SQL » Complex query hangs when selecting from table where fresh data is inserted
Complex query hangs when selecting from table where fresh data is inserted [message #222918] Tue, 06 March 2007 23:38 Go to next message
syvaidya
Messages: 1
Registered: March 2007
Junior Member
In one of our stored procs, I am inserting data in one of the tables in first query, and then there is a SELECT query from that table joined with some other tables.

Insert goes fine, but SELECT query just hangs (we waited for 12 hours, but it did not return). There are only around 2500 records in the table. The query is complicated though.

After putting a simple ANALYZE TABLE in between insert and select, the query ran in 1 sec!

Could anyone please let me know why it should have such a huge impact, considering that there are only 2500 records. Without analyzing it just hangs forever.
Re: Complex query hangs when selecting from table where fresh data is inserted [message #222932 is a reply to message #222918] Wed, 07 March 2007 00:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Compare your execution plans (explain plan) before and after the analyze to see what happens.
Re: Complex query hangs when selecting from table where fresh data is inserted [message #222977 is a reply to message #222932] Wed, 07 March 2007 02:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the table was empty before the insert, then the Stats on the table will be badly wrong after the insert, and the optimizer may well have picked a plan that is badly sub-optimal.
Can you show us the Query and the Explain plan.
Previous Topic: having two PK for a table
Next Topic: How I execute package in function and procedure?
Goto Forum:
  


Current Time: Thu Dec 08 02:02:26 CST 2016

Total time taken to generate the page: 0.07353 seconds