| Gai |
Posted: Oct 06, 2006 04:12:57 PM |
Total Post: 8
Joined: Aug, 2006
|
Hi
I am working on a query that refrences 4 tables and the query is going to give out abt 2.5 million records. Now I have this query in a sql script and I call this sql script from a shell script. I spool the result of this query into a file and I find that it takes almost 3 hrs for the process to be completed. When I try executing the query on Toad/sqlplus, it gives me the result in 1 min. Does it mean that the spooling of file takes a long time? Does it usually take this time for 2.5 million records?
The surprising thing is that when I run it in the test environment, I dont get any errors and the process completes in 3 hrs which is fine. But when I run it in production, I get a rollback segment error. This is just a select statement and I am not writing into any temporary tables. What could be the reason for the failure of the query in production environment? Has it got anything to do with the settings fo the database? Any help to solve the problem is greatly appreciated. Thanks!
Gayathri |
|
|
 Gai |
| Posted: Oct 06, 2006 04:19:02 PM |  |
|
Total Post: 8
Joined: Aug, 2006
|
Forgot to mention that Iam working in oracle 9i...
|
|
|
|
|
| Vigyan |
| Posted: Oct 09, 2006 03:23:37 PM | |
|
Total Post: 386
Joined: May, 2001
|
Hi Gai,
Yes, spooling file can take longer time as it depends on type of data you have, disk speed and other related hardware components.
Oracle use rollback segment for READ CONSISTENCY. You should increase its size. You can test it by creating a bigger rollback segment and assign it to the transaction by using SET TRANSACTION command.
Hope it helps.
Vigyan
|
|
|
|
|
| PP |
| Posted: Oct 26, 2006 01:13:45 AM | |
|
Total Post: 11
Joined: Oct, 2006
|
I am quite eager to know what is the purpose of generating a 2.5 million record report.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
