一点oracle的优化技术

1、导致非共享的sql的原因有2种
   1.1、业务复杂，应用本身就会产生很多不经常执行的SQL语句，这种情况只能通过调大shared pool来解决问题。
   2.2、SQL语句没有设定绑定变量，这样虽然SQL语句结构相同，但是每一个条件值不同的SQL语句都会作为一个单独的对象存储到SHARED POOL中，这种情况
   改写SQL语句为绑定变量的方式来解决。

2、查看SHARED POOL中非共享的SQL语句。
   //select substr(sql_text,1,40),count(1) from v$sql where executions=1 group by substr(sql_text,1,40) order by 2;
   如果发现大量的重复语句可以通过以下语句查出完整的版本：
  
   //SELECT SQL_TEXT FROM V$SQL WHERE SUBSTR(SQL_TEXT,1,40)='INSERT INTO BILL_PRINT_DATA(PRINT ID,B';
   查看语句占用shared pool mem内存的情况：
  
   //select sum(sharable_mem)/1024/1204 from v$sql where substr(sql_text,1,40)='insert into bill_print_date(print_id,b';
  
   查看shared pool 的大小：
   //show parameter shared_pool
  
   总结：数据库硬解析过高的原因是由部分SQL语句没有绑定变量引起的，这些SQL语句一方面作硬解析的时候消耗了CPU资源，另一方面在储存的时候消耗了很多的SHARED POOL内存资源。
   解决办法：
   //SELECT SUBSTR(SQL_TEXT,1,40),COUNT(1) FROM V$SQL WHERE EXECUTIONS=1 GROUP BY SUBSTR(SQL_TEXT,1,40) ORDER BY 2;
  
  
   二、PGA命中率偏低
     PGA命中率低会影响排序和哈希的执行性能，目前PGA为3G，建议将PGA调大为4G，并继续观察PGA的命中率。
   三、KEEP缓冲池命中率很低
  
    指定KEEP池的对象：
    表： BILL.ACCT_ITEM_TOTAL_MONTH
         BILL.ACCT_ITEM
    索引：BILL.UNO_ACCT_ITEM_TOTAL_MONTH_ID
          BILL.IDX_ACCT_ITEM_A_ACCT_ID
          BILL.UNO_ACCT_ITEM_ID
   
   四、SQL执行效率低中的原因之一：
      4.1、存在大量的空闲数据块：
          SQL> SET SERVEROUTPUT
          SP2-0265: serveroutput must be set ON or OFF
          SQL> SET SERVEROUTPUT ON
          //查询表的空闲数据块：
          declare
           l_free_blks NUMBER;
          BEGIN
                dbms_space.free_blocks(segment_owner     => 'SYS',
                                       segment_name      => 'T',
                                       segment_type      => 'TABLE',
                                       freelist_group_id => 0,
                                       free_blks         => l_free_blks);
               DBMS_OUTPUT.PUT_LINE(l_free_blks);
          END;

         //查询表中数据的分布情况，保护数据块保护的数据记录条数
         SQL>
         declare
            cursor v_cur is
              select rowid from SYS.T order by rowid; --查询表PRICING_CONFIG.CYCLE_INTERNAL_EVENT v_rowid varchar2(200);
            ls_my_rowid   varchar2(200);
            v_rowid       varchar2(200);
            rowid_type    number;
            object_number number;
            relative_fno  number;
            block_number  number;
            row_number    number;
           begin
             open v_cur;
             loop
               fetch v_cur
                 into v_rowid;
               exit when v_cur%notfound;
               dbms_rowid.rowid_info(v_rowid,
                                     rowid_type,
                                     object_number,
                                     relative_fno,
                                     block_number,
                                     row_number);
               ls_my_rowid := 'Object# is :' || to_char(object_number) || chr(10) ||
                              'Relative_fno is :' || to_char(relative_fno) || chr(10) ||
                              'Block number is :' || to_char(block_number) || chr(10) ||
                              'Row number is :' || to_char(row_number);
               dbms_output.put_line('file=' || to_char(relative_fno) || 'block=' ||
                                    to_char(block_number) || ' row=' ||
                                    to_char(row_number));
             end loop;
             close v_cur;
           end;

         //建议对表进行more操作，以压缩数据，降低表的高水位计，这样可以减少查询的访问量，提高查询的速度。
         对表做move操作 需要注意的：
        
         alter table move后要注意什么？

         今天想把生产库里一些表的HWM缩减一下，orcle是9i的，于是只能用到alter table move指令；
         但是看到论坛里很多零散的介绍这个指令需要考虑蛮多东西，我怕遗漏，在这里总结一下，是否还有其它需要考虑，生产库，还是谨慎点好：
         1、把表enable movement
         2、alter table move后，需要index rebuild，（目前我的表是非分区，无索引的表，不过这里有一点，也是网上看到，说index rebuild不是根据现有数据，而是根据HWM缩减之前的数据进行rebuild，需要进行drop后重建，这个我还没试验过，不知道是否如此）？
         3、这个表涉及到存储过程，存储过程又被job调用，是否需要手动编译一下这些对象呢？？
         4、把表disable movement；
        
         1. 最好把相关job去掉，去掉之前做好重新执行JOB的脚本，待move后重启执行job;                                                              
         2. move表你要保证1倍的剩余表空间做这些(假设你在同一表空间做move,另外，如果有物化视图、LOB字段、分区表什么的需要更复杂的处理)           
         3. 你move表之前，最后统计一下表的数据量，并计算一下表的大小，最好使用SQL自动生成脚本，然后执行相关脚本即可；                           
         4. move表后，相关索引就不能再使用了，需要重建(推荐加上online选项)，另外move表的时候，会生成大量的日志，如果有必要，请加上nologging选项；
         5.最好把相关存储过程什么的重建一下，move表后做一下统计信息更新，注意move表前的统计信息备份。                                            //查询表的高水位计：
        
        
     
     
           
           
           
           
             
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
   
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7194105/viewspace-681687/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7194105/viewspace-681687/