Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003

    Unanswered: Degradation in performance executing truncates/inserts

    Oracle Solaris 8 on 4800. When executing an insert or truncate we experiance a degradation in performance that appeared to by I/O driven. Whilt wait I/O is minimal and I/O utilisation normal 20-50%, I/O seems to be chocked. It is as if Oracle is being held back by somethings.

    Important to not have tried test harness (large no. of inserts) on a SAN attached disk and internal, both of which experiance similar symptons.

    10CPU Domain with plenty opf free mem!

  2. #2
    Join Date
    Sep 2003


    Any thoughts guys?


  3. #3
    Join Date
    Apr 2003
    Greenville, SC (USA)
    When issuing a truncate, the table phyical makeup is truncated to
    it's original initial size. Indexes are also getting pretty badly beat up.
    because the data in the index is marked for deletion, not actually
    deleted until the index is rebuilt.

    After a "few" (hard number to define) trucates, you might look at
    dropping and recreating the table and indexes and see if the
    performance does not improve substancially.


  4. #4
    Join Date
    Sep 2003
    It doesn't matter what the command is when issued from any Oracle statement ...with 1 exception: raw disk operations, such as create datafile. The disk throughput is over 30Mb Sec for raw disk operation but drop to below 3Mb Sec with an insert statement. Not only is the throughput low, it falls away.

    This effects the whole of our DW operation, especially large inserts with not too much sorting.... so it's not just one or two queries.
    The exception being complex queries that write few rows.

    The dbase runs find on RAID5 (!)... the exact same dbase code runs rubbish on RAID 10. We use VxVM, 10 CPUs etc...

  5. #5
    Join Date
    Mar 2002
    Reading, UK
    Raid 5 is generally a bad move for heavy write situations as your parity disc becomes a bottleneck (and calculating the parity). Was your RAID 1+0 setup properly (in terms of stripe size and striping of files).

    Also is this a problem on all tables or just a few. Is their other activity going on such as other large queries doing selects against the table your truncating. Also check IO against the redo logs as these are critical for heavy update performance. Other things to check are the tables very large with lots of extents and are the tablespaces locally managed.

    There could be lots of other things which might cause issues but these are probably a reasonable starting point.


Posting Permissions

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