DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: delay in query execution

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    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

  2. #2
    Join Date
    Feb 2001
    Posts
    13
    create appropriate indexes on the columns which are used in where clause..

  3. #3
    Join Date
    Jul 2000
    Posts
    53
    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?
    Steve

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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

  5. #5
    Join Date
    Oct 2000
    Posts
    211
    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

  6. #6
    Join Date
    Feb 2001
    Posts
    184
    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.

  7. #7
    Join Date
    Oct 2000
    Posts
    211
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width