Database Technologies...!

DB - Oracle 11g Creating large locally partitioned indexes on  huge partitioned table, 1. Define the index unusable and invisible with parallel and nologging options. CREATE BITMAP INDEX "DWH_SLT"."IND_SL2008_NONROM_MOB_NO_HIST" ON "DWH_SLT"."SLT_NON_ROM_2008_09_HIST" ("MOBILE_NO") nologging local unusable parallel 6 invisible tablespace SLT_OCS_INDX1; 2. Rebuild each local partition of the index . eg. 1: alter index "DWH_SLT"."IND_SL2008_NONROM_MOB_NO_HIST" rebuild partition CDR_14_JAN3_N parallel(degree 6); 2: alter index "DWH_SLT"."IND_SL2008_NONROM_MOB_NO_HIST" rebuild partition CDR_14_FEB1_N parallel(degree 6); This can be done by lopping through all the index partitions via a procedure. After this all index partitions needs to be usable. 3. Make the index logging and disable parallel. alter index DWH_SLT.IND_SL2008_NONROM_MOB_NO_HIST logging noparallel; 4. Make the

DB - Oracle 11g/10g OS - AIX 6.1/5.3 When running oracle on AIX, one issue that can occur is high CPU with os process lrud using large % of the available CPU and large Kernel cpu usage as well. This will cause the instance to hang with errors such as 'ORA-00020: maximum number of processes exceeded' . Topas output on this situation can be similar to bellow, lrud is the page stealing process used by AIX virtual memory manager. So this shows a situation where OS VMM is heavily active. Below lists some checkpoints from both Oracle and OS end for this issue. From Oracle --> When oracle is not using Direct I/O which means OS level caching is not used the OS VMM will be freed from all the overhead due to oracle I/O activity. So using Direct I/O with large SGA can be a solution. The parameter 'filesystemio_options' defines the method oracle uses for I/O. By setting SETALL for this parameter we can enable Direct I/O from db end. From AIX --> Some