Home » RDBMS Server » Performance Tuning » urgent help required
urgent help required [message #64948] Tue, 16 March 2004 05:36 Go to next message
Kaushik Choudhury
Messages: 2
Registered: March 2004
Junior Member

Please help. I have a script which inserts some records from data tables into temporary tables, and then using join statements on the temporary tables, the data tables are deleted(this is because the data tables have some complex referential integrity issues and is cumbersome to delete without the temporary tables). But the problem is the script hangs after inserting into the temporary tables and after executing the first delete statement. The number of records here are large(1-2million) and for smaller tables the whole script runs fine. We have disabled redo log, and decreased the undo time to 3 sec from 3 hours. Still, it seems the system runs out of tablespace. Any suggestions what the problem could be?????

P.S.-I am using oracle 9i
Re: urgent help required [message #64951 is a reply to message #64948] Tue, 16 March 2004 21:33 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member

According to me at the first shot following things should be done :

1) Check the execution plan of select statement which u r using to fetch the data from multiple tables using joins. Check is it properly tuned or need some tuning like status of indexes etc. If it is not using any indexes since u have millions of records try to write the query in better way.(According to me this is the only problem with u)

2) As u said u r using automatict undo management and retention is also very low, so i dont think that there would be any problem at undo segment side except the sizes of files u r using for undo tablespace. But since u r using temporary tables to hold millions of records, just check the size of ur temp tablespace. Try to use unlimited size for the files of temp tablespace and also check the available disk space for the same.

Check these things and revert back.

Daljit Singh.
Re: urgent help required [message #64959 is a reply to message #64948] Thu, 18 March 2004 00:27 Go to previous message
Messages: 91
Registered: March 2004

Instead of deleting, use TRUNCATE.

Previous Topic: Tips
Next Topic: Query taking too long to run
Goto Forum:

Current Time: Wed Sep 30 11:56:22 CDT 2020