Printable View
Hi friends,
I am running 8.1.6 on an UNIX machine.
I am using set transaction for deleting (or selecting too) records from a table which has around 50,000 records. It is taking abnormally long time.
I have 7 rollback segments, one of 20 mb and the other 6 of 4 mb. I am using the 20mb segment for these transasctions and there are no other transactions going on. What may be the reason in these delays?
Thanks
manjunath
create appropriate indexes on the columns which are used in where clause..
How big are the redo logs and how many have you got?
You may be encountering contention here.
The redo logs cannot be written away to the archive logs before they are needed again.
That is one suggestion if you are in archive log mode.
What is the syntax for the select and delete statement you are using?
What are you selecting on using indexes or not?????
There could be many reasons, SGA size as all processed data needs to be pulled into memory before it can be processed.
What do you consider an abnormally long time?
50,000 rows is nothing really, do you have long columns or lobs containing huge amounts of data?
More info required please?
you should break your testing into two pieces - for select and for deletes.
A select query will be slow if the right indexes are missing
A delete sql will be slow if there are many indexes on that table.
- Rajeev
I am using the following:
delete table_name;
my redo log files are 2 and each of them are 50 MB in size.
i am NOT running in archive mode.
I dont have any indexes on this table.
Ffunny thing is if i try creating a new table like the problem table , populate with the records from the problem table and then try to delete from the new table, the operations take hardly anytime(less than 2 seconds). But delete from the problem table takes more than 20 minutes and then too I am forced to kill the session.
I cant understand why an operation behave differently for two different tables having same structure and records.
manjunath
Hi ,
If you are not using any where clause..
Use this
Truncate Table table_name Drop Storage;
Thta's weird, Table does not have index and takes 20 minutes to delete records..
Tkanks.
HI everybody,
I just killed the session which was executing the delete operation and then retried. Guess what? The same operation took less than 2 seconds, beats me what was wrong earlier, though.
Can someone help in knowing what might have been wrong? Like I said earlier, only one session was going on when the problem occured.
Thanks
manjunath