大数据应用时Oracle优化实例

从上篇的原理，解决实际项目的问题，遇到一个优化问题：

•一个月没有执行完的SQL:

•业务边IO及CPU占用 TOP 1

•需要业务理解的SQL

•底层 IO及CPU占用TOP 1

•模型

1、一个月没有执行完的SQL

•Mergetb_wz_allusing tb_wz_all@tocais

•优化过程如下：

•使用分而治之思想（不仅仅局限此一类，有关大量查找， 大量（或全表）插入或修改等均是适用的）：

•打开相同id in[minID, maxID]块，同时进行相同的排序;

•cursorc_wz_all(minIDnumber, maxIDnumber) is

•    select *

•      from tb_wz_all

•     where id >= minID

•       and id < maxID

•     order by id;

• 

•cursor c_wz_all_tocais(minIDnumber, maxIDnumber) is

•    select *

•      from tb_wz_all@tocais

•     where id >= minID andid < maxID

•       and lastupdate_date >

•           to_date(v_tbWzAll_lastUpdateDate, 'yyyy-mm-ddhh24:mi:ss')

•       and ym_top not in ('nslook007.com', …  'nb-114.com')

•     order by id;

• 

•得到整个个过程的范围：

•selectmin(id), max(id)

•      into v_wz_all_minID, v_wz_all_maxID

•      from tb_wz_all@tocais

•     where lastupdate_date >

•           to_date(v_tbWzall_lastUpdateDate, 'yyyy-mm-ddhh24:mi:ss');   

• 

•调整NUM_ROWS值，即一次取多小条控制参数：

•    if v_wz_all_maxID - v_wz_all_minID <20000 then

•      NUM_ROWS := v_wz_all_maxID - v_wz_all_minID;

•    else

•      NUM_ROWS := floor((v_wz_all_maxID - v_wz_all_minID) /1000);

•      if (NUM_ROWS < 10000) then

•        NUM_ROWS := 10000;

•      end if;

•endif;

• 

•    //初始化

•    v_curID := v_wz_all_minID;

//整个过程处理

while v_curID <=v_wz_all_maxID loop

 

 //分块处理

 open c_wz_all(v_curID,v_curID +NUM_ROWS);

  open c_wz_all_tocais(v_curID,v_curID +NUM_ROWS);   

   ……

   while c_wz_all_tocais%found loop

         ……       

         if v_exec_type = 1then

            v_merge_count :=v_merge_count + 1;

            //insert;

         elsifv_exec_type = 2then

            v_merge_count :=v_merge_count + 1;

            //update

         end if;

         ……

    end loop;

     

    if v_merge_count<> 0 then

        commit;

     end if;

     close c_wz_all_tocais;

     close c_wz_all;

   

     v_merge_count :=0;

   

      v_curID :=v_curID +NUM_ROWS;

end loop;

•v_merge_count可以再改进，比如可以多批聚集到8000条一次提交。强调一下，有人认为既然大事务占用undo表空间，那么是不是一条一提交就可节省了呢。其实不是的，每次提交都会生成一个checkpoint额外数据。业务层的域名存活验证每条一提交，每天提交500多万就占用了大量CPU、IO及占更多的undo表空间，是需要优化的。

• 

•优化前后的结果比较：优化前，达一个月来没有执行成功，优化后执行2个小时内完成，甚至有时半个小时，与数据库当前状态有关。

•

•算法复杂度是一致。对比原整体Merge好处理到底在哪： 

–分块排序可以在内存在执行(还能节省内存，IO,指外排序)

–分块可以分批提交，减少UNDO表空间

–分块不会产生锁表，不影响其它事务执行

• 

相反整个Merge因为关联的数据多，占用过多内存以后还需要使用外排序，会对表加锁，修改的数据不能及时得到提交。这会给整个系统带来很大负面的影响，特别又因为其具有长期性执行，导致系统很容易出现更多的大事务同时长时间执行，从而最终导致系统出现各种问题。同时容易产生ORA-01555错误。

•UNDO表相关：提交以后还需保留一段的时间后才能让其从UnExpired=》Expired,未提交时为Active状态。Active、UnExpired为占用UNDO表空间。Expired、或Free是可用的。从UnExpired=》Expired过程需要的时间有undo_retention指定，在提交后的undo_retention时间内也可以恢复，用于误操作与快照。磁盘冗余过多可设置大些，设置过小则会抛出ORA-01555:快照过旧错误。原因是每个SQL查询时，在查询点时刻所有查询涉及的数据要求维持一快照由UNDO来支持。此过程容易产生脏数据。

•

•安全的:

•begintrans

•Sqlselect

•update

•commit

• 

•不安全的:

•sqlselect

•begintrans

•update

•commit;

2、业务边IO及CPU占用TOP 1

• 分析：通过类似limitstart, rows机制从类向后整个遍历

•实际中rows取值为1000（底层数据库是这个值， DBA告诉我们每批提5000~10000比较好）。Tb_wz_all_validate达3000万以上，这样完整遍历一遍需要进行：3000万/1000=3万次。

•下面对一个查询过程进行分析：

•读入所有结果集（包括所有的select包含的数据）到内存，内存不够则使用外存，再进行排序，可能会用到索引，无可以不会，根据统计数据及使用索引策略决定。

•有人可能认为会是这样：

•一）、Id是有索引的，且没有其它过滤条件，可以使用B-tree索引数据，根据中序遍历（可能有队列辅助）生成排序好的ID序列。再根据limitstart,rows, 其中有对应的记录offset，这样可以直接取到数。

•二）、或更天真认为，id本来就是安顺序好的，只需按顺序计数则行，甚至下一批可以接着上一批继续。

• 

•分析：Sql语句是完全不确定的，选出的列（同时可能是多表），过滤条件，排序列（有无索引，索引是否与行顺序一致），另外表数据块是有碎片的，特别在不停地被修改时。所有上面的猜想说服力不足。

• 

•可以想象我们为了取出1000（或以标准值8000）一次。需要付出多大的代价。因此分块思想对于实际应用非常有必要。

3、底层 IO及CPU占用TOP 1

•不讨论sql性能问题，问题可以参考业务层数据库TOP 1问题。

•在这里主要说明一下其它的问题：

•子句Mod(id,:1) =:2:

–Mod(id,:1) =:2确实可以让程序部署在多台机器。但我们的现在却是在同一名机器上。

–问题：有些人认为开启比较多的程序数量能提高效率。甚至160。

•           这个想法不作评价。自己思考，提示从cpu、mem、io是否同时都能得到充分利用。

• 

•子句id>：3:

–原思路：每次开始ID从0开始，从选择的1000中找到一个最大的作为下一次的条件，。。。等选到的数量小于1000时，说明没有了再从头开始。

–问题：思考这样能保证所有的数都得到验证吗？另外如此做法需要多少IO。

•

•子句alive_date<trunk(sysdate, ‘dd’)：

–原思路：昨天发现在需要得到及时的验证   

–问题：越靠近00:00:00数据，明天查询时，马上就要再找到进行第二次验证。

•重启查询模型：

–原思路：10分后没有再返回，则重启新的查询

问题：原查询还在进行中，这样一旦出现题，将是恶性循环。本应是负反馈模型的-----正如变频空调。

改造思想

•说明：把数据分为两个区间，第一区间为最近发现的（24小时能扫描并得到验证的数据, 可根据业务需要调整时间）,第二区间为更早的。注意区间划分是动态的。

•第一区间对应的查找语句：

–SelectID, YM, YM_TOP, TO_CHAR(FX_DATE, ‘YYYYMMDD’), ISONLINE FROM TB_WZ_ALL WHEREMOD(ID, :1)=:2 and id between(lastID, lastID + NUM_ROWS) YM_TOP!=’hcxej.info’ and ym_top!=’hhjcx.info’and ym_top!=’qianqian.com’and alive_date < sysdate –24/24; 

•第二区间对应的查找语句：

–SelectID, YM, YM_TOP, TO_CHAR(FX_DATE, ‘YYYYMMDD’), ISONLINE FROM TB_WZ_ALL WHEREMOD(ID, :1)=:2 and id between(lastID, lastID + NUM_ROWS) YM_TOP!=’hcxej.info’ and ym_top!=’hhjcx.info’and ym_top!=’qianqian.com’  

•多进程+多线程模型：每机器部署一个进程，每个进程拥有多个线程共享查询到的数据，在线验证域名是否存活。当然另外可使用一生产者与多消费者模型。

•两个区间数据选择控制：通过配置比如比值5：5。  

•需要注意的是，把id当发现时间来使用可以有风险，不过可以简单把id换为原来的fx_date，但量比较难控制量的问题，这样可能牺牲部分效率。另外运维上保证id一直向后增。或设计时把时间当成ID的前缀部分，同一时刻有多条，可以增加后缀自增部分；多机器部署时可以用MOD值来处理；也可以写数据一过程，由它通一产生，确保唯一。

•优点：可确保所有域名数据得到存活验证。两区间模型实分分段模型，很容易变化为最多段区间，可通过调整比值，很容易达到业务的需要。