Where to find MAXxxxxxx control file parameters in Data Dictionary

PURPOSE
How to find  information about the following  controlfile parameters
- MAXLOGFILES

- MAXDATAFILES
- MAXLOGHISTORY
- MAXLOGMEMBERS
- MAXINSTANCES

Explanation

• The values of MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXINSTANCES and MAXLOGHISTORY are set either during CREATE DATABASE, or CREATE CONTROLFILE

• In all Oracle versions, the CREATE CONTROLFILE syntax can be regenerated from the data dictionary using 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;'  and can be checked from trace
• For  parameters (exepct  MAXLOGMEMBERS) can be seen  from  v$controlfile_record_section
  select decode(TYPE,
    'REDO LOG',    'MAXLOGFILES',
    'DATAFILE',     'MAXDATAFILES',
    'CKPT PROGRESS','MAXINSTANCES',
    'REDO THREAD','MAXINSTANCES',
    'LOG HISTORY','MAXLOGHISTORY') TYPE,RECORDS_TOTAL
  from v$controlfile_record_section
  where type in
  ('REDO LOG','DATAFILE','CKPT PROGRESS','REDO THREAD','LOG HISTORY');
  
  
  TYPE RECORDS_TOTAL
  
  --------------- -------------
  MAXINSTANCES 11
  MAXINSTANCES 8
  MAXLOGFILES 128
  MAXDATAFILES 10000
  MAXLOGHISTORY 10225
• MAXLOGMEMBERS is available via "x$kccdi.dimlm".
  

Database Crashes With ORA-00494

Symptoms

Database may crashed with the following error in the alert file

ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 5488'

Incident details in: d:\oracle\admin\ecore\diag\rdbms\ecore\ecore\incident\incdir_12529\ecore_lgwr_5484_i12529.trc
Killing enqueue blocker (pid=5488) on resource CF-00000000-00000000 by (pid=5484)
by killing session 3.1
Killing enqueue blocker (pid=5488) on resource CF-00000000-00000000 by (pid=5484)
by terminating the process
LGWR (ospid: 5484): terminating the instance due to error 2103
Fri Dec 17 03:05:09 2010
Instance terminated by LGWR, pid = 5484

Cause


The lgwr has killed the ckpt process, causing the instance to crash.
From the alert.log we can see:
That the database has waited too long for a CF enqueue, so the following error has been reported.
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by by 'inst 1, osid 5488'

Then the LGWR has killed the blocker, which was in this case the CKPT process which cause the instance to crash.
Checking the alert.log we can see that the frequency of redo log files switch is very high(almost every 1 min).

Solution


1-We usually suggest to configure the redo log switches to be done every 20~30 min to reduce the contention on the control files.

You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to
determine the size of your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online
logs to be at least this size.

References

BUG:7448854 - ORA-00494 CAUSE THE INSTANCE TO CRASH
Metalink Note: 753290.1

Oracle enqueue wait

List of Oracle 10g enqueue waits  are  following .The aggregated statistics for each of these enqueue types is displayed by the view V$ENQUEUE_STAT


enq: AD - allocate AU:Synchronizes accesses to a specific OSM (Oracle Software Manager) disk AU


enq: AD - deallocate AU:Synchronizes accesses to a specific OSM disk AU

enq: AF - task serialization:Serializes access to an advisor task

enq: AG - contention:Synchronizes generation use of a particular workspace

enq: AO - contention:Synchronizes access to objects and scalar variables

enq: AS - contention:Synchronizes new service activation

enq: AT - contention:Serializes alter tablespace operations

enq: AW - AW$ table lock:Allows global access synchronization to the AW$ table (analytical workplace tables used in OLAP option)

enq: AW - AW generation lock:Gives in-use generation state for a particular workspace

enq: AW - user access for AW:Synchronizes user accesses to a particular workspace

enq: AW - AW state lock:Row lock synchronization for the AW$ table

enq: BR - file shrink:Lock held to prevent file from decreasing in physical size during RMAN backup

enq: BR - proxy-copy:Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup

enq: CF - contention:The CF enqueue is a Control File enqueue   and happens during parallel access to the control files.  The CF enqueue can be seen during any action that requires reading the control file, such as redo log archiving, redo log switches and begin backup commands

enq: CI - contention:The CI enqueue is the Cross Instance enqueue  and happens when a session executes a cross instance call such as a query over a database link

enq: CL - drop label:Synchronizes accesses to label cache when dropping a label

enq: CL - compare labels:Synchronizes accesses to label cache for label comparison

enq: CM - gate:Serializes access to instance enqueue

enq: CM - instance:Indicates OSM disk group is mounted

enq: CT - global space management:Lock held during change tracking space management operations that affect the entire change tracking file

enq: CT - state:Lock held while enabling or disabling change tracking to ensure that it is enabled or disabled by only one user at a time

enq: CT - state change gate 2:Lock held while enabling or disabling change tracking in RAC

enq: CT - reading:Lock held to ensure that change tracking data remains in existence until a reader is done with it

enq: CT - CTWR process start/stop:Lock held to ensure that only one CTWR (Change Tracking Writer, which tracks block changes and is initiated by the alter database enable block change tracking command) process is started in a single instance

enq: CT - state change gate 1:Lock held while enabling or disabling change tracking in RAC

enq: CT - change stream ownership:Lock held by one instance while change tracking is enabled to guarantee access to thread-specific resources

enq: CT - local space management:Lock held during change tracking space management operations that affect just the data for one thread

enq: CU - contention:Recovers cursors in case of death while compiling

enq: DB - contention:Synchronizes modification of database wide supplemental logging attributes

enq: DD - contention:Synchronizes local accesses to ASM (Automatic Storage Management) disk groups

enq: DF - contention:Enqueue held by foreground or DBWR when a datafile is brought online in RAC

enq: DG - contention:Synchronizes accesses to ASM disk groups

enq: DL - contention:Lock to prevent index DDL during direct load

enq: DM - contention:Enqueue held by foreground or DBWR to synchronize database mount/open with other operations

enq: DN - contention:Serializes group number generations

enq: DP - contention:Synchronizes access to LDAP parameters

enq: DR - contention:Serializes the active distributed recovery operation

enq: DS - contention:Prevents a database suspend during LMON reconfiguration

enq: DT - contention:Serializes changing the default temporary table space and user creation

enq: DV - contention:Synchronizes access to lower-version Diana (PL/SQL intermediate representation)

enq: DX - contention:Serializes tightly coupled distributed transaction branches

enq: FA - access file:Synchronizes accesses to open ASM files

enq: FB - contention:This is the Format Block enqueue, used only when data blocks are using ASSM (Automatic Segment Space Management or bitmapped freelists).  As we might expect, common FB enqueue relate to buffer busy conditions, especially since ASSM tends to cause performance problems under heavily DML loads

enq: FC - open an ACD thread:LGWR opens an ACD thread

enq: FC - recover an ACD thread:SMON recovers an ACD thread

enq: FD - Marker generation:Synchronization

enq: FD - Flashback coordinator:Synchronization

enq: FD - Tablespace flashback on/off:Synchronization

enq: FD - Flashback on/off:Synchronization

enq: FG - serialize ACD relocate:Only 1 process in the cluster may do ACD relocation in a disk group

enq: FG - LGWR redo generation enq race:Resolves race condition to acquire Disk Group Redo Generation Enqueue

enq: FG - FG redo generation enq race:Resolves race condition to acquire Disk Group Redo Generation Enqueue

enq: FL - Flashback database log:Synchronizes access to Flashback database log

enq: FL - Flashback db command:Synchronizes Flashback Database and deletion of flashback logs

enq: FM - contention:Synchronizes access to global file mapping state

enq: FR - contention:Begins recovery of disk group

enq: FS - contention:Synchronizes recovery and file operations or synchronizes dictionary check

enq: FT - allow LGWR writes:Allows LGWR to generate redo in this thread

enq: FT - disable LGWR writes:Prevents LGWR from generating redo in this thread

enq: FU - contention:Serializes the capture of the DB feature, usage, and high watermark statistics

enq: HD - contention:Serializes accesses to ASM SGA data structures

enq: HP - contention:Synchronizes accesses to queue pages

enq: HQ - contention:Synchronizes the creation of new queue IDs

enq: HV - contention:The HV enqueue is similar to the HW enqueue but for parallel direct path INSERTs

enq: HW - contention:The HW High Water enqueue  occurs when competing processing are inserting into the same table and are trying to increase the high water mark of a table simultaneously. The HW enqueue can sometimes be removed by adding freelists or moving the segment to ASSM

enq: IA - contention:Information not available

enq: ID - contention:Lock held to prevent other processes from performing controlfile transaction while NID is running

enq: IL - contention:Synchronizes accesses to internal label data structures

enq: IM - contention for blr:Serializes block recovery for IMU txn

enq: IR - contention:Synchronizes instance recovery

enq: IR - contention2:Synchronizes parallel instance recovery and shutdown immediate

enq: IS - contention:Synchronizes instance state changes

enq: IT - contention:Synchronizes accesses to a temp object’s metadata

enq: JD - contention:Synchronizes dates between job queue coordinator and slave processes

enq: JI - contention:Lock held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view

enq: JQ - contention:Lock to prevent multiple instances from running a single job

enq: JS - contention:Synchronizes accesses to the job cache

enq: JS - coord post lock:Lock for coordinator posting

enq: JS - global wdw lock:Lock acquired when doing wdw ddl

enq: JS - job chain evaluate lock:Lock when job chain evaluated for steps to create

enq: JS - q mem clnup lck:Lock obtained when cleaning up q memory

enq: JS - slave enq get lock2:Gets run info locks before slv objget

enq: JS - slave enq get lock1:Slave locks exec pre to sess strt

enq: JS - running job cnt lock3:Lock to set running job count epost

enq: JS - running job cnt lock2:Lock to set running job count epre

enq: JS - running job cnt lock:Lock to get running job count

enq: JS - coord rcv lock:Lock when coord receives msg

enq: JS - queue lock:Lock on internal scheduler queue

enq: JS - job run lock - synchronize:Lock to prevent job from running elsewhere

enq: JS - job recov lock:Lock to recover jobs running on crashed RAC inst

enq: KK - context:Lock held by open redo thread, used by other instances to force a log switch

enq: KM - contention:Synchronizes various Resource Manager operations

enq: KO - fast object checkpoint:The KO enqueue (a.k.a. enq: KO - fast object checkpoint) is seem in Oracle STAR transformations and high enqueue waits can indicate a sub-optimal DBWR background process


enq: KP - contention:Synchronizes kupp process startup

enq: KT - contention:Synchronizes accesses to the current Resource Manager plan

enq: MD - contention:Lock held during materialized view log DDL statements

enq: MH - contention:Lock used for recovery when setting Mail Host for AQ e-mail notifications

enq: ML - contention:Lock used for recovery when setting Mail Port for AQ e-mail notifications

enq: MN - contention:Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session

enq: MR - contention:Lock used to coordinate media recovery with other uses of datafiles

enq: MS - contention:Lock held during materialized view refresh to set up MV log

enq: MW - contention:Serializes the calibration of the manageability schedules with the Maintenance Window

enq: OC - contention:Synchronizes write accesses to the outline cache

enq: OL - contention:Synchronizes accesses to a particular outline name

enq: OQ - xsoqhiAlloc:Synchronizes access to olapi history allocation

enq: OQ - xsoqhiClose:Synchronizes access to olapi history closing

enq: OQ - xsoqhistrecb:Synchronizes access to olapi history globals

enq: OQ - xsoqhiFlush:Synchronizes access to olapi history flushing

enq: OQ - xsoq*histrecb:Synchronizes access to olapi history parameter CB

enq: PD - contention:Prevents others from updating the same property

enq: PE - contention:The PE enqueue is the Parameter Enqueue, which happens after “alter system” or “alter session” statements

enq: PF - contention:Synchronizes accesses to the password file

enq: PG - contention:Synchronizes global system parameter updates

enq: PH - contention:Lock used for recovery when setting proxy for AQ HTTP notifications

enq: PI - contention:Communicates remote Parallel Execution Server Process creation status

enq: PL - contention:Coordinates plug-in operation of transportable tablespaces

enq: PR - contention:Synchronizes process startup

enq: PS - contention:The PS enqueue is the Parallel Slave synchronization enqueue which is only seen with Oracle parallel query.  The PS enqueue happens when pre-processing problems occur when allocating the factotum (slave) processes for OPQ

enq: PT - contention:Synchronizes access to ASM PST metadata

enq: PV - syncstart:Synchronizes slave start_shutdown

enq: PV - syncshut:Synchronizes instance shutdown_slvstart

enq: PW - prewarm status in dbw0:DBWR0 holds this enqueue indicating pre-warmed buffers present in cache

enq: PW - flush prewarm buffers:Direct Load needs to flush prewarmed buffers if DBWR0 holds this enqueue

enq: RB - contention:Serializes OSM rollback recovery operations

enq: RF - synch: per-SGA Broker metadata:Ensures r/w atomicity of DG configuration metadata per unique SGA

enq: RF - synchronization: critical ai:Synchronizes critical apply instance among primary instances

enq: RF - new AI:Synchronizes selection of the new apply instance

enq: RF - synchronization: chief:Anoints 1 instance's DMON (Data Guard Broker Monitor) as chief to other instance’s DMONs

enq: RF - synchronization: HC master:Anoints 1 instance's DMON as health check master

enq: RF - synchronization: aifo master:Synchronizes critical apply instance failure detection and failover operation

enq: RF - atomicity:Ensures atomicity of log transport setup

enq: RN - contention:Coordinates nab computations of online logs during recovery

enq: RO - contention:Coordinates flushing of multiple objects.The RO enqueue is the Reuse Object enqueue and is a cross-instance enqueue related to truncate table and drop table DDL operations

enq: RO - fast object reuse:Coordinates fast object reuse

enq: RP - contention:Enqueue held when resilvering is needed or when data block is repaired from mirror

enq: RS - file delete:Lock held to prevent file from accessing during space reclamation

enq: RS - persist alert level:Lock held to make alert level persistent

enq: RS - write alert level:Lock held to write alert level

enq: RS - read alert level:Lock held to read alert level

enq: RS - prevent aging list update:Lock held to prevent aging list update

enq: RS - record reuse:Lock held to prevent file from accessing while reusing circular record

enq: RS - prevent file delete:Lock held to prevent deleting file to reclaim space

enq: RT - contention:Thread locks held by LGWR, DBW0, and RVWR (Recovery Writer, used in Flashback Database operations) to indicate mounted or open status

enq: SB - contention:Synchronizes logical standby metadata operations

enq: SF - contention:Lock held for recovery when setting sender for AQ e-mail notifications

enq: SH - contention:Enqueue always acquired in no-wait mode; should seldom see this contention

enq: SI - contention:Prevents multiple streams table instantiations

enq: SK - contention:Serialize shrink of a segment

enq: SQ - contention:The SQ enqueue is the Sequence Cache enqueue  is used to serialize access to Oracle sequences

enq: SR - contention:Coordinates replication / streams operations

enq: SS - contention:Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up.
enq: ST - contention:Synchronizes space management activities in dictionary-managed tablespaces

enq: SU - contention:Serializes access to SaveUndo Segment

enq: SW - contention:Coordinates the ‘alter system suspend’ operation

enq: TA - contention:Serializes operations on undo segments and undo tablespaces

enq: TB - SQL Tuning Base Cache Update:Synchronizes writes to the SQL Tuning Base Existence Cache

enq: TB - SQL Tuning Base Cache Load:Synchronizes writes to the SQL Tuning Base Existence Cache

enq: TC - contention:The TC enqueue is related to the DBWR background process and occur when “alter tablespace” commands are issued.  You will also see the TC enqueue when doing parallel full-table scans where rows are accessed directly, without being loaded into the data buffer cache

enq: TC - contention2:Lock during setup of a unique tablespace checkpoint in null mode

enq: TD - KTF dump entries:KTF dumping time/scn mappings in SMON_SCN_TIME table

enq: TE - KTF broadcast:KTF broadcasting

enq: TF - contention:Serializes dropping of a temporary file

enq: TL - contention:Serializes threshold log table read and update

enq: TM - contention:The TM enqueue related to Transaction Management  and can be seen when tables are explicitly locked with reorganization activities that require locking of a table

enq: TO - contention:Synchronizes DDL and DML operations on a temp object

enq: TQ - TM contention:TM access to the queue table

enq: TQ - DDL contention:DDL access to the queue table

enq: TQ - INI contention:TM access to the queue table

enq: TS - contention:Serializes accesses to temp segments.these enqueues happen during disk sort operations

enq: TT - contention:The TT enqueue  is used to avoid deadlocks in parallel tablespace operations.  The TT enqueue can be seen with parallel create tablespace and parallel point in time recovery (PITR)

enq: TW - contention:Lock held by one instance to wait for transactions on all instances to finish

enq: TX - contention:Lock held by a transaction to allow other transactions to wait for it

enq: TX - row lock contention:Lock held on a particular row by a transaction to prevent other transactions from modifying it

enq: TX - allocate ITL entry:Allocating an ITL entry in order to begin a transaction

enq: TX - index contention:Lock held on an index during a split to prevent other operations on it

enq: UL - contention:The UL enqueue is a User Lock enqueue   and happens when a lock is requested in dbms_lock.request.  The UL enqueue can be seen in Oracle Data Pump

enq: US - contention:The US enqueue happens with Oracle automatic UNDO management was undo segments are moved online and offline

enq: WA - contention:Lock used for recovery when setting watermark for memory usage in AQ notifications

enq: WF - contention:Enqueue used to serialize the flushing of snapshots

enq: WL - contention:Coordinates access to redo log files and archive logs

enq: WP - contention:Enqueue to handle concurrency between purging and baselines

enq: XH - contention:Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications

enq: XR - quiesce database:Lock held during database quiesce

enq: XR - database force logging:Lock held during database force logging mode

enq: XY - contention:Lock used by Oracle Corporation for internal testing

 
See also:
Reference: http://oracle-dox.net/McGraw.Hill-Oracle.Wait.Interf/8174final/LiB0063.html

enq: TX - index contention

It’s possible that we see high Index leaf block contention  on index associated with  tables, which are having high concurrency from the application.  This usually happens when the application performs lot of INSERTs and DELETEs  .

The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait  for  TX lock in mode 4, until the session that is doing the block splits completes the operations

Causes

• Indexes on the tables which are being accessed heavily from the application.
• Indexes on table columns which are having values inserted by a monotonically increasing.
• Heavily deleted tables

Detecting index leaf block contention
There are many ways to find hot indexes

• Check high  "enq: TX – index contention" system waits   from AWR report
• At the same  time  you will see  high split events  in the  Instance  avtivity stats  in the AWR  report
• If yor system is RAC also   you will see  the  following  wait events in the AWR report
  gc buffer busy waits on Index Branch Blocks
  gc buffer busy waits on Index Leaf Blocks
  gc current block busy on Remote Undo Headers
  gc current split
  gcs ast xid
  gcs refuse xid
• we  can find sql_id ' s  from v$active_session_history
  
  select sql_id,count(*) from v$active_session_history a
  
  where a.sample_time between sysdate - 5/24 and sysdate
  and trim(a.event) like 'enq: TX - index contention%'
  group by sql_id
  order by 2;
• Or we  can  query the segments  from  V$SEGMENT_STATISTICS or from the 'Segments by Row Lock Waits' of the AWR reports.
  
  select * from v$segment_statistics
  where statistic_name ='row lock waits'
  
  and value>0 order by value desc;
• we can  query   dba_hist_enqueue_stat and the stats$enqueuestat  or  v$enqueue_stat       
  select * from  v$enqueue_stat        order  by cum_wait_time;

Solutions 

• Reverse Key  Indexes :Rebuild the as reverse key indexes or hash partition the indexes which are listed in the 'Segments by Row Lock Waits' of the AWR reports.These indexes are excellent for insert performance.  But the downside of it is that, it may affect the performance of index range scans
• Hash partitioned global indexes :When an index is monotonically growing because of a sequence or date key, global hash-partitioned indexes improve performance by spreading out the contention. Thus, hash-partitioned global indexes can improve the performance of indexes in which a small number of leaf blocks in the index have high contention in multi-user OLTP environments
• 
  CACHE size of the sequences:When we use monotonically increasing sequences for populating column values, the leaf block which is having high sequence key will be changing with every  insert, which makes it a hot block and potential candidate for a block split. With CACHE SIZE (and probably with NOORDER option), each instance would use start using the sequence keys with a different range reduces the index keys getting insert same set of leaf blocks
• Index  block size:Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace. Adjusting the index block size may only produce a small effect and changing the index block size should never be your first choice for reducing index block contention, and it should only be done after the other approaches have been examined.
• Rebuild indexes:If  many rows are  deleted  or  it is an  skewed indes   rebuilding will help  for a  while 

See Also:
All Oracle enqueue waits
Solving Waits on "enq: TM - contention"









Index leaf block contention is very common in busy databases and it’s especially common on tables that have monotonically increasing key values.

Oracle data loading performance

Oracle  choices for data loading

• SQL insert and merge statements
• PL/SQL bulk loads for the forall PL/SQL operator
• SQL*Loader
• Oracle10 Data Pump
• Oracle import utility

TIPS FOR DATA LOADING

1. Use a large blocksize:Data loads onto large  blocksize  (e.x 32k ) will run faster becuse more rows will be written in  an empty block
2. Use a small db_cache_size:If loading with DML a small data cache will minimize DBWR work during async buffer cleanouts.You can reduce  it  with alter  system  temporarily
3. Size your log_buffer properly:If you have waits associated to log_buffer size “db log sync wait”, try increasing to to 10m.
4. Watch your commit frequency:At each commit, Oracle releases locks and undo segments.Benchmarks suggest that you should commit as infrequently as possible
5. Use large undo segments:In order to avoid a ORA-1555 (snapshot too old) error use  large undo segments.
6. Use append hint:If you must use SQL inserts By using the append hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an append hint.  Note:  Prior to 11g r2, the append hint supports only the subquery syntax of the INSERT statement, not the VALUES clause, where you can use the append_values hint
   
   insert /*+ append */ into customer (select  ....);
7. Use  nologging if possible:NOLOGGING mode (for sql level,table level,database level)  will allow Oracle to avoid almost all redo logging. but the operaions will be unrecoverable
8. Disable archiving if possible:
9. Use PL/SQL bulking:PL/SQL often out-performs standard SQL inserts because of the array processing and bulking in the "forall" statement.   (see also  : PL/SQL forall operator speeds for table inserts )
10. Partition :Load the data in a separate partition, using transportable tablespaces.
11. Use multiple freelists or freelist groups for target tables:Avoid using bitmap freelists ASS management (automatic segment space management) for super high-volume loads.
12. Preallocate  space  for target  tables:Pre allocate  space for  tables and index  with "allocate extent"  clause  in order  to  gain performance on  allocation of segments.
13. Pre-sort the data in index key order:This will make subsequent SQL run far faster for index range scans. 
14. Use parallel DML:Parallelize the data loads according to the number of processors and disk layout.Try to saturate your processors with parallel processes.
15. Disable constraints :Disable during load and re-enable in parallel following the load
16. Disable/drop indexes - It's far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size. If you choose to keep the indexes during inserts, consider creating a reverse key index to minimize insert contention.
17. Disable/drop  triggers if possible:
18. Use RAM Disk:Place undo tablespace and online redo logs on Solid-state disk (RAM SAN),
19. Use SSD RAM Disk:Especially for the insert partition, undo and redo.  You can move the partition to standard disk later.
20. Use SAME RAID:Avoid RAID5 and use Oracle Stripe and Mirror Everywhere approach (RAID 1+0, RAID10).
    
    
     

PL/SQL forall operator speeds for table inserts

Loading an Oracle table from a PL/SQL array involves expensive context switches, and the PL/SQL FORALL operator speed is amazing here is an example


create  table  t_dba_objects as  select * from dba_objects  where  1=2 ;

DECLARE

  TYPE prod_tab IS TABLE OF dba_objects%ROWTYPE;
  dba_objects_tab prod_tab := prod_tab();
  start_time number; end_time number;
BEGIN
  SELECT * BULK COLLECT INTO dba_objects_tab FROM dba_objects;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE t_dba_objects';
  Start_time := DBMS_UTILITY.get_time;
  FOR i in dba_objects_tab.first .. dba_objects_tab.last LOOP
    INSERT INTO t_dba_objects VALUES (
        dba_objects_tab(i).OWNER ,dba_objects_tab(i).OBJECT_NAME,
        dba_objects_tab(i).SUBOBJECT_NAME,dba_objects_tab(i).OBJECT_ID,
        dba_objects_tab(i).DATA_OBJECT_ID,dba_objects_tab(i).OBJECT_TYPE ,
       dba_objects_tab(i).CREATED ,dba_objects_tab(i).LAST_DDL_TIME,
       dba_objects_tab(i).TIMESTAMP ,dba_objects_tab(i).STATUS ,
       dba_objects_tab(i).TEMPORARY,dba_objects_tab(i).GENERATED ,
      dba_objects_tab(i).SECONDARY  );
  END LOOP;
  end_time := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Conventional Insert: '||to_char(end_time-start_time));
  EXECUTE IMMEDIATE 'TRUNCATE TABLE t_dba_objects';
  Start_time := DBMS_UTILITY.get_time;
  FORALL i in dba_objects_tab.first .. dba_objects_tab.last
   INSERT INTO t_dba_objects VALUES dba_objects_tab(i);
  end_time := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Bulk Insert: ' ||to_char(end_time-start_time));
  COMMIT;
END;
/
Conventional Insert: 23689

Bulk Insert: 128

Note:Forall   is very fast then the convential for loop but it uses much  more  UGA memory depend on row count and column count.(e.x for 100m table I  examine that it used  5g UGA memory)