cms系统核心发布提取过程的调整优化

    因为这个版本开发的项目经理对java,数据库开发还都比较熟悉,所以核心的发布提取内容这一部分是使用数据库函数,过程来实现的.上一个cms版本的数据库是9.2.0.6的,这个cms版本的数据库是10.2.0.4的,os是linux as4 update7.

    在这两个cms版本中都有的一个应用需求是提取某个站点及其子站点(依次类推下去),某个分类及其子分类(依次类推下去)下的文章,图片,视频等的内容.在上一个cms实现中是使用oracle的层次查询实现的,但因为oracle的层次查询在估算card上的固有缺陷(可以参看我的上一篇文章"一次层次查询相关的sql的调整优化"中开篇提到的问题)经常导致相关的sql出现性能问题,虽然在这次的cms实现中他们也不知道如何从数据库角度去解决这个问题,甚至他们当时根本就不知道涉及到层次查询的语句性能上总出现问题是因为card估算上的问题,也没有测试这一点在10.2.0.4的数据库版本上有没有改进,但他们这次cms实现避开了oracle的层次查询去实现这个功能,是这样去实现的:规定站点(分类一样的要求,也一样的实现)最多有6级,每个站点最多只能有255个子站点(从01～FF,它自己是00),这样如果一个站点的编码是03 1A,那么它及其它的子站点的编码范围就是[03 1A 00 00 00 00,03 1A FF FF FF FF],当然因为文章所属的站点分布并不一致,所以就要求这个字段上一定要收集了柱状图统计信息,且要求popular value尽量都出现在柱状图统计信息中,其实还要求提取时在这些字段上使用字面值而不是绑定变量(因为这个cms产品设计上的问题,发布提取的过程大部分都是使用字面值,而不是绑定变量来实现的,所以这个要求实现起来没有问题,至于这种实现带来的问题下面会论及到).

    要理解这个过程A(其实也是这个系统)性能问题的关键,就需要简单的说一下相关的产品设计:
    编辑在录入文章的时候,会为这篇文章指定所属节点和分类,指定区域,来源,作者,编辑,分数,显示时间等,这些字段都通过触发器触发到一个表cms_article_compose中去,这个表中还有一些自动统计更新的字段,比如pv,comment_count等,编辑们还会为文章指定关键字(这个存储在另一个IOT表cms_article_keywords中,这个表只有联合主键keyword,aid两个字段,一篇文章有多个关键字的话,对应多行记录,也就是说这里的keyword字段不是逗号分割的多个关键字). 
    编辑们会创建一些块(发布的单元),会指定提取的文章数量,一般就是很少的几篇文章,比如说5篇文章.为这些块指定一些提取规则,比如说要求提取某个(或者某些,其它基本也一样,都可以是逗号分隔的多个属性值的)节点,某个分类下的文章,并且可以指定其它属性要求,比如要求分数大于多少,作者是谁,要求包含哪些关键字(有个规则指定是要求这些关键字都出现,还是出现这些关键字中的任一个就可以)等,还可以指定排序规则,是按显示时间降序排序(默认),还是按pv或者score或者comment_count降序排序.编辑还可以指定这些块上的某些位置必须是某篇文章,比如说指定1,3的位置必须是哪两篇文章,其余的位置由候选文章按照特定的排序规则排序后填充,排序规则就是前面提取规则处指定的排序规则,候选文章由两部分组成,一部分就是符合前面提取规则的文章(这部分的候选文章数量可能是巨大的),另一部分是提取规则之外编辑也希望能够作为候选文章而单独指定的一些文章(这些文章数量很少,实际上很多时候编辑们根本不指定这部分文章的).
    编辑们在录入新的文章之后,程序会调用另一个过程来扫描提取规则表查看这篇文章符合哪些块的提取规则(实际上这个过程和提取符合规则的文章的过程A的实现正好是逆向的),然后将这些块插入到一个发布队列表中去,后台的一个守护进程不断的扫描这个队列表,发现数据行的话,便开启一个新的进程调用提取内容的过程A来完成这个块的发布.所以一篇文章的发布可能会触发多个块的重新发布的(当然重新发布的结果可能是这个块的页面内容根本就没变),在编辑们发布文章频繁的时候,这个过程A的并发调用还是不小的.
    实际的业务逻辑要更复杂一些,不过大致的业务逻辑就是这样的.

    其实这个应用从数据库和主机操作系统端来看的话,表现出的性能问题还是蛮多样的:
    有时候(并不频繁出现)从数据库端表现为严重的latch: library cache,latch free,latch: shared pool等待,操作系统层面上表现为system cpu偏高,换页操作频繁,io等待有时候偏大.如果单从数据库角度来看,似乎和分析特别是这个过程A中大量的使用字面值引发的硬分析有关.但这个问题不仅和系统当初的设计有关,现在有1W多个块规则,规则又都是不一样的,也就是说where条件中不仅仅是字面值不同,而是过滤要求本身很多就不同,所以规则之间很多是不能共享的;而且和系统数据本身也有关,很多的字段比如说节点,分类,作者,编辑,关键字下的数据分布本身就是不均匀的,而且来说也都不存在典型输入值,所以这里不使用绑定变量,而使用字面值,并且收集相关字段上的柱状图统计信息其实就是很自然的要求了.所以从这两点来说,修改过程使用绑定变量就很困难了,不说修改后的性能问题,修改后就能减少硬分析,减少相关的latch等待吗?这些都很难说.而且从awr报表来看,其实硬分析,软分析从绝对数量上来说真的不能说高的,每秒钟的硬分析10以内,软分析300以内(和当时的系统吞吐量低有很大关系),而且共享池内存的使用并不大,剩余空间还蛮多的,所以我感觉这里出现这样的latch等待似乎和分析没有很直接的关联.当然,现在回过头来看这个问题,其实和我的第一篇文章"一台数据库短时间hang住问题的解决"提到的问题一样,和数据库的配置有关,使用文件系统的数据库却没有使用直接IO,而使用了文件系统缓存,而且没有把SGA固定到物理内存里,最终导致了这样的性能问题,解决方案当然就是改变数据库配置,使用直接IO,使用HugePages把SGA固定到物理内存里.当然,我当时很迷惘,不知道该如何处理这个问题,那就先绕开这个问题,处理其它的问题吧!
    系统告警日志文件中经常出现temp表空间不足的错误信息,awr报表中temp表空间的读写是最频繁的(前面提到io偏大,os swap space的读写是一方面,其实数据库临时表空间文件的读写是更重要的一个贡献因素),和其它的数据文件的读写明显不在一个级别上,PGA Cache Hit %经常是80%左右,甚至更低,pga要求optimal size在[512M,1024M]的操作经常出现,甚至[1G,2G]的操作也会出现在awr报表中,所以one-pass的操作时有出现,甚至multi-pass的操作偶尔也会出现.
    在上午9:30--11:30这段业务繁忙时段,查看了连续几天这个时段的awr报表,Top 5 Timed Events中前2位,而且比例比较大的总是CPU time和log file sync,而且从相应时段的os性能监控来看,user cpu 70%左右,甚至更高.log file sync这种同步写等待肯定和业务逻辑中频繁的commit有关,而上面temp表空间文件读写又那么频繁,会导致io带宽不足,必然会加剧log file sync等待的时间.

    其实定位这些问题的根源还是比较简单的,awr报表中,过程A的DB TIME基本上是60%左右.虽然说这个过程中调用的sql都是使用字面值的,导致可能每个sql占据的db time比例都不会太大,但因为一些sql的性能太差了,awr报表中按照多个基准降序排列的sql中排在前面的都是过程A中同一处拼装的一些sql.定位造成临时表空间读写频繁的sql语句,通过DBA_HIST_SQLSTAT中按照DIRECT_WRITES_DELTA降序排序定位之前某个时间段的sql语句,或者通过v$sql_workarea_active定位现在正在频繁读写临时表空间的sql语句.或者是从ash报表中又或者直接抓取现在正在执行的sql语句,从共享池中抓取sql语句,其实都指向几乎同样的一批sql.当然从开发人员那里,从编辑那里得知哪些块的发布特别慢,在业务空闲的时候执行测试(避免大量并发执行对它的影响,当然这时需要查看pga的使用情况,在缺少并发的情况下,很可能是不会读写临时表空间的),通过dbms_profiler包或者是sql_trace也很容易定位到问题sql.

    既然问题sql找到了(虽然说很多吧,但也需要一个个的查看,也许问题就是一样的呢),下面就要定位问题出在哪里,问题定位之后,如何解决或者说缓解或者说绕开这个问题了.
    下面是捕捉到的一个简化了不必要字段后的SQL(原来提取的字段很多的):

select a.ID,a.TITLE,a.TITLE1,a.CATEGORY_NAME,a.TYPE, b.rank,a.EDITOR_COMMENT,a.display_time from (select a.*, rownum r from (select * from( /*Sub1*/ select a.ID,a.TITLE,a.TITLE1,a.CATEGORY_NAME,a.TYPE, a.EDITOR_COMMENT,a.display_time from cms_article_compose a, (select distinct aid from cms_article_keywords where keyword = '新闻' or keyword = '行情' or keyword = '评测' or keyword = '导购' or keyword = '技术文档' ) b where (a.SITEMAP_ID_LEVEL_CODE between 17179869184 and 21474836479) and ( (a.CATEGORY_ID_LEVEL_CODE between 4311744512 and 4328521727) or (a.CATEGORY_ID_LEVEL_CODE between 4328521728 and 4345298943) or (a.CATEGORY_ID_LEVEL_CODE between 4345298944 and 4362076159) or (a.CATEGORY_ID_LEVEL_CODE between 4362076160 and 4378853375) or (a.CATEGORY_ID_LEVEL_CODE between 4378853376 and 4395630591) or (a.CATEGORY_ID_LEVEL_CODE between 4395630592 and 4412407807) or (a.CATEGORY_ID_LEVEL_CODE between 4412407808 and 4429185023) or (a.CATEGORY_ID_LEVEL_CODE between 4429185024 and 4445962239) or (a.CATEGORY_ID_LEVEL_CODE between 4445962240 and 4462739455) or (a.CATEGORY_ID_LEVEL_CODE between 4462739456 and 4479516671) or (a.CATEGORY_ID_LEVEL_CODE between 4479516672 and 4496293887) ) and a.id = b.aid and a.status = 5 and a.id not in (select id from block_article_list_temp2) and a.display_time is not null and a.id not in (select a.ID from cms_article_compose a, cms_block_article b where a.status = 5 and b.sitemap_id = 40840 and b.template_id = 4171 and b.block_code = 'topnews' and b.type = 1 and sysdate between nvl(b.start_time, sysdate - 100) and nvl(b.end_time, sysdate + 100) and b.rank = 0 and a.id = b.article_id and a.id not in (select id from block_article_list_temp2) ) and a.id not in (select ARTICLE_ID from cms_block_article b where b.sitemap_id = 40840 and b.template_id = 4171 and b.block_code = 'topnews' and b.type = 2 and sysdate between nvl(b.start_time, sysdate - 100) and nvl(b.end_time, sysdate + 100) ) /*Sub1*/ union all select a.ID, nvl(b.article_title, a.TITLE) title, a.TITLE1, a.CATEGORY_NAME,a.TYPE,a.EDITOR_COMMENT,a.display_time from cms_article_compose a, cms_block_article b where a.status = 5 and b.sitemap_id = 40840 and b.template_id = 4171 and b.block_code = 'topnews' and b.type = 1 and sysdate between nvl(b.start_time, sysdate - 100) and nvl(b.end_time, sysdate + 100) and b.rank = 0 and a.id = b.article_id and a.id not in (select id from block_article_list_temp2) ) a order by a.display_time desc) a where rownum <= 5) a, (select rank, rownum r from (select rownum rank from dual connect by rownum <= 5 minus select rank from block_article_list_temp2 order by rank)) b where a.r = b.r;

    因为业务逻辑还是稍微复杂一些,所以这里使用了一个临时表block_article_list_temp2,先插入编辑指定位置的文章到这个临时表中去.实际上这个sql执行的是一个insert into block_article_list_temp2的语句,我这里把insert部分给省略了,这里的sql完成的是规则指定的候选文章和规则之外编辑手工指定的候选文章union all后排序取前n篇文章填补剩余位置的功能.
    通过10046事件跟踪或者是alter session set statistics_level=all;后执行sql语句,查看执行统计信息,很容易定位资源,时间消耗在了哪里的:就是我红色标示的这两个地方.这里cms_article_compose 213W的数据,union all之前的部分sub1,近29W的数据,对这么大的数据集(实际select的字段有近30个的)进行排序,当然要消耗大量的CPU,这样的操作大量并发的时候,PGA内存空间显然是不够的,所以出现了大量的读写临时表空间的操作.还有就是这里的distinct 无论是sort unique还是hash unique,都是需要消耗CPU的,同时大量并发时也是可能导致读写临时表空间的操作.

  先说这里的distinct的改进: select a.ID,a.TITLE,a.TITLE1,a.CATEGORY_NAME,a.TYPE, a.EDITOR_COMMENT,a.display_time from cms_article_compose a, (select distinct aid from cms_article_keywords where keyword = '新闻' or keyword = '行情' or keyword = '评测' or keyword = '导购' or keyword = '技术文档' ) b where a.id = b.aid and a.status = 5 其实可以改写成下面这样的: select a.ID,a.TITLE,a.TITLE1,a.CATEGORY_NAME,a.TYPE, a.EDITOR_COMMENT,a.display_time from cms_article_compose a where a.status=5 and a.id in (select aid from cms_article_keywords where keyword = '新闻' or keyword = '行情' or keyword = '评测' or keyword = '导购' or keyword = '技术文档' );

    我们知道in其实有三种执行路径,以相应的提示来表示就是:1.no_semijoin,其实也就是这里distinct后连接的实现,也就是说原来distinct的实现其实只是in的一种实现.2.semijion的实现,细分起来有nl_sj,hash_sj,merge_sj. 3.no_unnest的实现形式,也就是大部分情形下低效的filter的实现.也就是说改写成in之后就有3种实现方式了,更多的选择在很多时候意味着oracle能选择更好的实现形式,从而实现更好的性能.

    上面捕捉到的SQL不仅排序导致CPU消耗大,而且大的逻辑IO也导致了CPU消耗大.能不能避免排序操作和减少大的逻辑IO呢?cms_article_compose一共210多W数据量,编辑指定的规则总共可以提取到30W条数据,但实际上只是order by a.display_time desc后取前5条记录.如果走display_time这个索引上的全扫描的话,从概率上来讲每7个索引条目就可可以发现1个满足条件的记录行,所以按照索引链去走的话,访问35个索引条目就可以找到满足条件的5条记录的,再加上通过rowid回访表的操作,逻辑IO上应该远小于现在的几十W甚至上百W的逻辑IO(cms_article_compose的全表扫描的逻辑IO就是20多W,其实拼装出来的很多sql要远比这个复杂,一旦因为各种原因优化器选择了一个次优的执行计划的话,逻辑IO很可能是上百W的),而且避免了排序操作对CPU的大量消耗和导致的读写临时表空间的操作.
其实上面的语句可以简化为: select * from (resultA union all resultB) order by display_time desc 后取前5条记录 我们这里resultA代表符合提取规则的候选结果集,resultB代表编辑手工指定的规则之外的候选结果集.其实这里的resultb相对于resulta来说往往是一个很小的结果集,甚至很多时候resultb就是一个空的结果集,但即使是实际执行结果是resultB是一个空结果集,上面的语句在实际执行结果上来看等价于 select * from resultA order by display_time desc 但在执行时,也不能按 select * from resultA order by display_time desc 去执行,因为执行计划是在实际执行前就确定的,resultb是可能不为空的,你就必须按它不为空去处理. 而 select * from (resultA union all resultB) order by display_time desc 这样的一个结果集,显然是不能使用我上面提到的索引全扫描而避免排序操作的,结果造成对一个很大的结果集排序,导致了现在的问题:user cpu偏高,临时表空间读写频繁 所以就必须分拆成下面这样: insert into tempa select * from (select * from resultA order by display_time desc) where rownum<=5; insert into tempb select * from (select * from resultB order by display_time desc) where rownum<=5; select * from ( select * from (tempa union all tempb) order by display_time desc ) where rownum<=5; 这样 insert into tempa select * from (select * from resultA by display_time desc) where rownum<=5; 就可以使用索引全扫描来改进性能了.

    我原来只想改到这里,其余的还是让oracle自己选择吧.但我发现一个很蹊跷的事情:select * from (select * from resultA by display_time desc) where rownum<=5;默认就可以走index_desc(a (display_time)),而且确实是高效的;但insert into tempa select * from (select * from resultA by display_time desc) where rownum<=5;居然选择了一个不同的执行计划,而且是低效的.我对insert语句的select部分加上index_desc(a (display_time))的提示之后,计算出来的cost确实比它默认选择的执行计划的cost大了很多,所以oracle默认没有选择这个执行计划.而index_desc的执行计划的cost之所以在insert的语句中大是因为计算出来的cost似乎是走索引全扫描取到所有记录的cost,而不是取前5条的cost.而只有select的语句中index_desc的代价计算就是取前5条的cost,所以默认就被选择了.我这里确实可以改写为先只取rowid,然后再连接cms_article_compose取其它字段的形式,但这里代价的计算上还是走索引全扫描取到所有记录的cost,而不是取前5条的cost,只不过这里没有回访表的代价而已,这个计算出来的代价肯定还是要高一些,关键还不在于这一点,而在于我这里order by之前肯定还有其它很多的过滤条件,虽然是只取rowid,但肯定还是要回访表的,然后再关联cms_article_compose表取其它字段,实际上就是回访了两次表记录(虽说第二次回访的记录很少了吧),还有其他一些复杂的过滤条件肯定是不能使用这种方式的,所以我也放弃了这种想法.当时我对于cbo优化器还没有像现在这样较真,非要弄明白为什么优化器就做出了这样的选择,我当时就想,既然oracle这里选择不了最优的执行计划,所以也只能放弃这种打算,自己加提示来完成了.
    但这样就存在一个问题了,这里的提取规则,也就是这里的where里的条件是编辑手工设定的规则,所以实际上有的提取规则对应的sql确实是应该走索引全扫描来避免排序的,有的却是不应该这样走的.所以我就要区别对待了,只能对一些数据集使用这样的提示,一些数据集不能采用这样的提示.从概率上来讲,一般来说,如果resultA是一个大的结果集,走索引全扫描,很快就可以发现前n条记录,从而避免排序操作的;而如果resultA是一个很小的结果集,走索引全扫描的代价可能就很大了,这时候得到这个结果集后再排序很可能就是一个正确的选择了.也就是说可以根据resultA这个结果集的数量来选择不同的执行计划的.
    所以,我添加了一个包packA来收集按照编辑设定的规则,每个块的resultA结果集的数量(其实很多代码就是重用的过程A的代码),其实我添加这个包的前提是块的提取规则一旦确定,极少再修改的.
    然后,发布提取的过程A里根据resultA的结果集的数量,划分了3个不同的等级:
    1.  <500 不添加任何的提示,让oracle自己选择,一般来说就是排序. 如果是新添加的块,还没有结果集数量的统计信息,就按这种情况处理
    2.  >0.02* rows_cms_article_compose (这里的rows_cms_article_compose我是在过程A中写死的,在cms_article_compose表的数量发生大的改变的时候(现在210W的数据,增加十几W数据也应该不算是大的改变吧),修改过程中的这个值就可以了,而这个系统在非工作时间其实压力还是很低的,过程A的调用频率也是很低的,所以在空闲时间修改过程A也不存在问题)
    添加相应的提示,比如按照dislay_time降序排序的时候,添加index_desc(a (display_time))提示,通过索引避免排序操作
    3.  [500,0.02* rows of cms_article_compose] 不添加任何的提示,让oracle自己选择,一般来说就是排序.但我改写了一下这里的实现形式.是排序取前n行数据时,只取rowid字段,然后再关联cms_article_compose表取其它字段.
    我这里之所以对第三种情况进行了这样的特殊处理是因为:一般来说,这里是需要排序的,但select rowid rid from相对于select n个字段 from来说,同样的排序字段,前者对pga的内存要求更少(实际排序时占据pga内存的只有rowid和排序字段,而不是原来很多的字段了),这也意味着它需要更大的并发量的情况下,才需要使用临时表空间,这样也可以在很大程度上缓解现在的临时表空间读写频繁的问题.而对于前两种情况而言,一个不需要排序,一个排序的结果集很小,这时排序的影响倒是可以忽略不计的,先只取出符合条件的前n个rowid,而后连接表取其它字段的实现形式,就像我前面分析到的一样,因为where的过滤条件里还要读取很多其它的字段,所以回访表是肯定的,使用这种实现形式的话,回访表的次数还要多一些,所以还是采取了一般的实现形式.
    我这里对于这三种情况的临界点的选取其实就带有一些经验,猜测的因素在里面了,作为一个初始值,然后根据实际表现出的性能问题慢慢调整这些分界点吧!
    然后定制job定期的运行这个packA的过程来统计候选结果集的数量,因为这里的规则块有1W多个,所以还是使用并行统计的.说白了,就是在主过程中提交多个一次性运行的job来完成实际的统计工作,主过程只是负责监控这些job过程的运行而已.为了保证统计规则块时不重复,不漏算,需要一些并发控制代码.相应的代码是这样的:

procedure get_stated_block(v_id out number,v_sitemap_id out number,v_template_id out number,v_block_code out varchar2) is CURSOR v_cursor IS SELECT * FROM block_rule_article_stats_temp WHERE status='0' FOR UPDATE; --在select之前完成串行化的工作 BEGIN OPEN v_cursor; --在select之前完成串行化的工作 SELECT Min(id) INTO v_id FROM block_rule_article_stats_temp WHERE status='0'; IF(v_id IS NULL) THEN v_id:=-1; --这里有数据存在的情况下,是不会出现-1这个数据的,-1表示不存在数据了 v_sitemap_id:=null; v_sitemap_id:=null; v_block_code:=null; END IF; IF(v_id<>-1) then UPDATE block_rule_article_stats_temp SET status='1' WHERE id=v_id returning sitemap_id,template_id,block_code into v_sitemap_id,v_template_id,v_block_code; END IF; CLOSE v_cursor; COMMIT; END;

后三个out参数就是块的唯一标识.slave进程也就是实际完成统计工作的job,不断的调用这个过程来得到需要统计的块,直至得到-1,退出job的执行.
    这里说的是一般的情况,实际上和块设定的规则提取的文章的数据分布有关系,总有些例外的情况,比如说可能resultA结果集不大,但实际上走索引全扫描却是高效的,比如说这个块指定的规则提取的文章没有什么历史数据,但编辑们基本上每天都录入几篇新的文章,这里又是按照时间降序排序的话,使用索引避免排序可能就是一个好的执行计划了.为了纠正这种问题,你可以把这个块的结果集统计值改到别的区间试试,找到快的执行方案后,设置统计表中相应数据行的STATS_LOCKED='1',也就是说锁定这个统计值,以后packA的过程收集块的候选结果集的统计信息的时候不再收集这个块的统计信息了,它也就能保持这个执行计划了.
    呵呵,其实我这里借鉴了oracle的dbms_stats包的一些思路.

    其实这个系统的另一个严重的问题是缺少instrument code,所以在系统出现性能问题时,只能依靠oracle提供的工具来查找性能问题,而不能进行自我诊断.而这个cms系统最可能引发性能问题的就是这个提取的过程A,这个过程出现性能问题,一般来说都是因为一些个块的提取出现问题导致的,所以如果过程A本身能提供一些块的执行统计信息,那是最好不过的了,这样在性能出现问题的时候,能够快速的定位是哪些块造成的,甚至在极个别的块刚刚出现性能问题的时候,也能及早的发现并纠正,而不要等到一堆的块都出现问题,整个系统的性能出现问题的时候才去纠正.当然,这种执行统计,肯定是有代价的,所以最好有开关控制着,平时不执行这样的统计代码,但在我需要开启的时候就可以开启这样的执行统计功能.所以我在过程A中添加了代码统计它的执行次数,时间,cpu时间和逻辑IO,这些统计代码只在一个包变量有值的时候才开启.必要的时候,添加logon on database触发器为这个包变量设置值,从而开启执行统计工作,不需要的时候,删除这个触发器就可以停止执行统计工作了.当然这种方法的前提是:发布完一个块了,这个会话就结束了,发布一个新的块的时候,重新登录,发布完了,这个会话就结束了.如果是一个类似deamon的会话早就登录了,不断的查询有没有块可以发布,有的话就发布,发布完了也不退出,而是等着新的块来发布,这种方法就不适用了.当然如果是我上面所说的类似deamon的会话存在,但是实际的发布它是交给其它的新创建的会话来完成的话,这种方法也是适用的.我们系统是deamon进程创建新的会话来完成发布提取工作的,虽说使用了连接池(我以前虽然做过开发,但对连接池却不清楚),但我实验时结果是这样的,建立触发器之后,执行统计信息表数据立即发生变化,删除触发器后,数据很快不再变化,从而证明我的这种触发器的方式是可以的.最不济,我可以建立一个单行单列表来解决这个问题(通过控制表数据来控制程序行为的),但我不想增加这样一个每次执行这个过程都需要访问的表,不想增加这样一个串行点,所以没有这样实现.我采用触发器的这种方式,发现了一些性能差的块,通过修改块的候选结果集数量到另一个区间,性能得到了极大的提升,然后锁定了这个块的候选结果集数量.

    我这样调整后,极大的降低了cpu的使用,在上午9:30--11:30这段业务繁忙时期,修改之前,cpu使用一般是70~80%,修改完之后一般是cpu空闲70~80%;系统io等待也得到了极大的改善,基本上不读写临时表空间了,pga命中率一直是100%,最大的optmial size也就是64M而已;我去掉了这个过程A中不必要的commit,虽然说log file sync等待数量上更多了(数量上的增多和优化后系统吞吐量的提升有关.这个等待主要还是和系统当初的应用设计有关,比如说这次操作往队列表里插入了1行记录,你必须立即commit,你不能聚集了1W行记录的时候再提交的,所以修改起来可能不是太容易),但因为消除了临时表空间读写造成的io争用,这个事件的平均等待时间却由200~300ms下降为了10ms左右.整个来看,原来的awr报表中DB Time/Elapsed基本上是25左右,甚至更多,现在就是5左右.平均事务响应时间从1s下降到了0.1s,尤其是编辑们在查看这些块提取内容的预览(调用的也是这个过程A)的时候,以前可能需要几分钟才出结果的,现在基本上是立刻就返回结果了.
    而对于我最开始处提到的system cpu偏高,换页频繁的现象,以及由此造成的latch争用的问题,这次调整之后也再没有出现过(实际上调整之后,因为系统吞吐量的提升,单位时间内分析,硬分析的绝对数量都增多了).说明在系统负载大大降低之后,使用原来的数据库配置也可以保证不换页.当然,在这次调整2个月之后,我发现这次调整也确实极大的缓解了换页的问题之后,我还是修改数据库配置为使用直接IO,使用HugePages把SGA固定到物理内存里了.
    因为这个系统开发之初,并不知道编辑们会选择哪些过滤条件,会使用什么样的条件组合,所以对选择性高的列和对一部分值来说选择性高的列基本上都是单独建立索引的,很少有复合索引的,对于过滤条件中的多列组合的情况,基本就是让oracle自己选择index_combine来处理的,这样位图和rowid的转换,增加的逻辑IO无疑也是要消耗CPU的一个点.现在系统也运行有一段时间了,很多的规则也固定下来了,所以我建议维护人员根据提取规则表建立一些合适的复合索引.

    其实有时候真的是这样,oracle看到的只是一堆数据,它不会知道这些数据实际代表的含义,看不到这些数据之间的关联;而真正了解系统,了解业务逻辑,了解数据的只有我们,所以有些调整还是只能由我们来做出的.