RE: Inserts are taking time !

This appears to be a classic example of a design that cripples performance and once in production 'nothing' can be done to solve the problem. By nothing, I mean that possible options are not 'possible' due to external
(business, application) reasons.

Here are some areas to examine:
1. Are all the indexes actually in use? Are there some that are redundant
(identical leading columns)? Are there 'performance' indexes that are rarely
used? The fewer the indexes, the fewer i/os for each insert, the faster it will be.
2. Is there an off-time where the indexes can be rebuilt for better performance? Perhaps even nightly?
3. Can you use any sort of partitioning? Can the application use partition elimination?
4. How immediate is the need to see the data? If there can be a delay, consider inserting into a transient table and then performing a bulk load during off hours.
5. Are you using the background processes efficiently? With a single dbwr, you may be getting bogged down in writing all these blocks. If logwr is slow, check for i/o contention.
6. How much space management is occurring? Are you constantly throwing extents for the table/indexes?

Here are some areas that can be given low priority: (knowing full well this is an invitation to heated disagreements with other listers) 1. RBS - Adding rbs space will not help the situation, unless you are seeing rbs related errors. The real problem is the time that may be required to create a read-consistent view of the data. 2. Commit time - The time for a 'commit' to occur is more a function of the number of changes (i.e. table data + each index entry) than the amount of data currently in the table/index.

Good luck. I hope this provides some areas to examine.

Dan Fink

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).