Deletes , inserts taking a long time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Deletes , inserts taking a long time

  1. #1
    Join Date
    Sep 2000
    Posts
    103
    I've set up a new database on Win2000. This database is to be used live by about atleast 20 users.
    And while trying out inserts and deletes on a table which has at the moment the most amount of data of about 280,000 rows.

    So, just for the testing purpose of my archivelog mode, I gave a delete statement on this table, so that I could try recovering all the data.

    But this delete stmt took about 4minutes to complete. I even assigned a rollback segment solely for this tranx.

    Is this normal , the delete of 280,000 rows taking 4minutes ? or am I just being paranoid?

    Could someone please advice.

    Thanks a lot

    pst

  2. #2
    Join Date
    Feb 2000
    Posts
    175
    Do you have many indexes on the table?

    Each time you delete from the table you also have to delete from the index....

    If you want to delete all rows from a table you are better to use the 'truncate table' command.

    Cheers
    Moff.

  3. #3
    Join Date
    Apr 2001
    Posts
    107
    Hi,

    The above statement is true (if you have indexes on the tables, it will take more time to delete)
    But, if you want to test your archivelog mode, don't use truncate, because the truncate 'bypasses' the redo logs and don't write them, so, you will be unable to recover...

    For the 4 minutes, I would make a 'lazy dba' answer: it could depends on your system architecture...
    Many points are to check for contention:
    Processor(s), memory, disks speeds, raid configuration, other processes using resources, distribution of your datafiles over disks, fragmentation of your tables/indexes, distribution of indexes/tables/redo log/archived logs over the tablespaces, datafiles and disks, etc...

    Anyway, 4 min for a 280000 rows deletion doesn't make me crazy...

    Hope it helps

    Cheers

    Fabien

  4. #4
    Join Date
    Jul 2001
    Location
    Karachi, Pakistan
    Posts
    3

    Cool Use Partiioning

    Hi Dear,
    1) r u using the partitioning option to ur tables ?
    try to impose partitioning on ur tables ( beware, try to partition the tale on number type column, if available).

    2) for what type of application u r using ur database? i-e for OLTP or DSS environment .... place appropiate type of indexes according to ur application need ...

    3) Also try to impose parallel DML operation (default for ur tables).... by this way u feel some performance improvemnet.

    take care n plz reply me for the same
    Faraz A. Farooqui
    Oracle Consultant
    InfoPak, SBS
    92-21-5862318
    92-21-5862319

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by shadow21
    But, if you want to test your archivelog mode, don't use truncate, because the truncate 'bypasses' the redo logs and don't write them, so, you will be unable to recover...
    This is not true, TRUNCATE is fully logged as any other DDL statement. It is recorded in redo log, so there is no problem with TRUNCATE and database recovery.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    exact, truncate "bypasses" Rollback Segments, not redo logs

  7. #7
    Join Date
    Sep 2000
    Posts
    103
    It's a new compaq server, 1 GHz processor and RAID 1 configuration. We just had this set up recently. We have 9GB internal drive, and the rest 2 18 Gb are housed in the external array cabinet setup as RAID 1.

    There is only one I/O channel into the array cabinet to address this approx. 36G of usable disk space. I'm really doubtful about the whole setup.

    The table I was trying to delete has a composite index. That's all and I'm the only person accessing the database.

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