Unanswered: Degradation in performance executing truncates/inserts
Oracle 18.104.22.168 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.
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.
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...
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.