oracle10 千万级数据存取优化

[list]

     今天对一个存有1000万数据的大表进行了优化，尽是简单的优化，效果很明显。下面把自己的优化经过简单总结备忘一下。
     1:创建表的备份，把生产表的querySql copy后修改table 为temp_tbl
     2:从原有表中copy 数据插入到temp_tbl中语句如下
       insert into temp_tbl select * from source_tbl
     3:查看表数据量
       selct count(1) from temp_tbl
     4:查看表所在空间大小
       select segment_name, bytes/1024/1024/1024 from user_segments
       where segment_NAME = 'TEMP_TBL';
     5:查看当前表索引
       select * from user_indexes where TABLE_Name = 'TEMP_TBL'
     6:利用存储过程做基数数据准备
    

      CREATE OR REPLACE PROCEDURE CASE_ACLINE_TEMP_INSERT
AS
SQL_STMT VARCHAR2(1000) ;
TYPE T_CUR IS REF CURSOR;
V_PCUR T_CUR;
TYPE CASE_IDS_TBL IS TABLE OF CASE_INFO_TEMP.ID%TYPE
INDEX BY PLS_INTEGER;
CASE_IDS CASE_IDS_TBL ;
RESULTCOUNT INTEGER ;
BEGIN
   SQL_STMT := 'SELECT ID FROM CASE_INFO_TEMP WHERE ID > 725' ;
   EXECUTE IMMEDIATE  'SELECT COUNT(1) FROM ('||SQL_STMT||') ' INTO RESULTCOUNT ;
   IF RESULTCOUNT = 0 THEN
      RETURN ;
   END IF ;

   OPEN V_PCUR FOR SQL_STMT;
     FETCH V_PCUR BULK COLLECT INTO CASE_IDS;
     FOR I IN CASE_IDS.FIRST .. CASE_IDS.LAST
     LOOP
     SQL_STMT := 'INSERT INTO CASE_ACLINE_TEMP SELECT ID,NAME,VALID,I_NODE ,';
     SQL_STMT := SQL_STMT || 'J_NODE,I_OFF,J_OFF,NOTE,'||CASE_IDS(I)||',R,X,B,UPDATETIME,I_P,I_Q,';
     SQL_STMT := SQL_STMT || 'J_P,J_Q,I_QC,J_QC FROM CASE_ACLINE WHERE CASE_ID=725' ;
     --DBMS_OUTPUT.PUT_LINE(SQL_STMT);
     EXECUTE IMMEDIATE SQL_STMT ;
     COMMIT ;
   END LOOP;
   CLOSE V_PCUR ;
  
END CASE_ACLINE_TEMP_INSERT;             

     

     7:在java中开启100个线程模拟100个用户 对该表进行单次10000数据插入(前提：temp_tbl     表中存有1000万数据)
[/list]
    

     @Service
public class CaseAclineServiceImpl extends CaseServiceImpl<CaseAcline> implements
		CaseAclineService {
       @Autowired
       private CaseAclineDao aclineDao;
       public void insertTemp(int caseID, CountDownLatch counter) {
		long l1 = System.currentTimeMillis() ;
		this.aclineDao.insertTemp(caseID);
		counter.countDown() ;
		long l2 = System.currentTimeMillis() ;
		System.out.println("编号 "+caseID+ "插入消耗："+(l2-l1)+"毫秒");
	}
	
	public void queryTemp(int caseID, CountDownLatch counter) {
		long l1 = System.currentTimeMillis() ;
		Query q = new Query() ;
		q.addQueryParam("caseID", caseID) ;
		//PagingResult<CaseAcline> pr = this.aclineDao.queryTemp(q);
		this.aclineDao.insertTemp(caseID);
		//PagingResult<CaseAcline> caseAcline = this.aclineDao.queryTemp(q) ;
		long l2 = System.currentTimeMillis() ;
		System.out.println("编号 "+caseID+ " 查询消耗："+((l2-l1)/1000)+"秒");
		counter.countDown() ;
		
	}

}


/**   
 * @filename: TestWorker   
 * @description: TODO  
 * @author java 小生 
 * @date 2013-2-27 上午11:32:28      
 */
public class TestWorker extends Thread{
   private CaseAclineService caseAclineService;
   private CountDownLatch counter;
   private int caseID ;
   TestWorker(CaseAclineService caseAclineService,CountDownLatch counter,int caseID ){
	   this.caseAclineService = caseAclineService ;
	   this.counter = counter ;
	   this.caseID = caseID ;
	  
   }
	
   @Override	
   public void run(){
	   //System.out.println(caseID);
	   caseAclineService.queryTemp(caseID, counter);
   }
}

  

/**   
 * @filename: CaseAclineServiceTest   
 * @description: TODO  
 * @author java 小生 
 * @date 2013-2-26 下午11:27:50      
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:applicationContext.xml"})
public class CaseAclineServiceTest{
	CountDownLatch counter = new CountDownLatch(100);
	ExecutorService executorService  = Executors.newCachedThreadPool();
	public int caseID = 11796 ;
	
	@Autowired
	private CaseAclineService caseAclineService;
	
	//@Test
	public void doInsert(){
        long l1 = System.currentTimeMillis() ;
		for (int i = 0; i < 100; i++) {
			executorService.submit(new TestWorker(caseAclineService,counter,caseID++));
		}
		try {
			counter.await() ;
		long l2 = System.currentTimeMillis() ;
			System.out.println("100个线程插入完成,耗时:"+((l2-l1)/1000)+" 秒");
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void doQuqery(){
        long l1 = System.currentTimeMillis() ;
        for (int i = 0; i < 100; i++) {
			executorService.submit(new TestWorker(caseAclineService,counter,caseID++));
		}
		try {
			counter.await() ;
		long l2 = System.currentTimeMillis() ;
			System.out.println("100个线程查询完成,耗时:"+((l2-l1)/1000)+" 秒");
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
	}

}

     

 

     8:插入操作情况如下
　　 峰值 内存占用达85% ,上下文切换较高 , 100次插入，成功96 ，失败4个

     耗时（秒） 线程数
     0-60 17
     0-70 31
     0-100 48
     成功 96
　　　　
　　利用hints  /*+APPEND*/ 做插入，不删除外键索引 ,执行情况如下：
     耗时（秒） 线程数
     0-10 35
     0-20 41
     0-30 24
     成功 100
　　
    去掉外键索引后的执行情况如下：
     耗时（秒） 线程数
     0-1 8
     0-2 12
     0-3 24
     0-4 43
     0-5 13
    成功 100

     8:100个线程做关联查询查询操作（单次查询数据位10000）情况如下
     没有添加任何索引的查询结果
     耗时（秒） 线程数
     0-40 13
     0-50 70
     0-60 17
　　
     添加索引后 100个线程都在1秒内完成查询操作
　　
  


     总结：为了提高插入速度 用刀了hints 中的/*+APPEND*/  从表中末尾追加,避免对索引过多维护消耗时间;
           为了提高查询的速度，增加了一个外键索引。



     对于hint 和索引的细节 还请百度 或是google ，小生了解有限。