[20180829]减少日志生成量.txt

[20180829]减少日志生成量.txt --//最近一段时间优化一下生产数据库,主要是问题比我预计要严重,实际上exadata实在太快了,把许多问题都给掩盖了. --//实际上这个问题很早就存在,我实在不想提,基于国内许多应用都可能存在类似问题,还是写一下. SQL ordered by Executions     %CPU - CPU Time as a percentage of Elapsed Time     %IO - User I/O Time as a percentage of Elapsed Time     Total Executions: 13,385,158     Captured SQL account for 65.4% of Total Executions  Rows Processed     Rows per Exec     Elapsed Time (s)     %CPU     %IO     SQL Id            SQL Module     SQL Text .... 140,257     139,411                     0.99                14.74     101.1      0     5f2atm993xz6w     PORTAL.EXE     update PD_PMXS SET PDBZ =:"SYS..." 140,256     140,256                     1.00                19.11     102        0     bs2qwd0crz5f3     PORTAL.EXE     update PD_DLB SET PDBZ =:"SYS_..." --//一天不到1万人次就诊,修改PD_DLB表在1个小时内就14万次,注意看Rows per Exec,每次修改1条.很明显在做无效刷频. --//我曾经跟一些开发讲过,在写代码时注意这些刷频语句.这些语句单条执行很快,但是执行很频繁,累积起来就很可怕. --//甚至最终就是这样运行模式导致运行缓慢.. --//真心感到可悲的是,我们团队大部分比我熟悉表结构,PD_DLB(排队表)这个表当天处理完后要删除里面的记录的. --//也就是最大记录量当天就诊人次,不大可能出现每小时14万次的修改,这么多人看awr报表,就没人注意到这么简单的问题吗? 5f2atm993xz6w update PD_PMXS SET PDBZ =:"SYS_B_0" , STATUS =:"SYS_B_1" WHERE RDID =:1 修改为 update PD_PMXS SET PDBZ =:"SYS_B_0" , STATUS =:"SYS_B_1" WHERE RDID =:1 and  PDBZ <> :"SYS_B_0" and STATUS <>:"SYS_B_1" --//错误,应该修改如下: 修改为 update PD_PMXS SET PDBZ =:"SYS_B_0" , STATUS =:"SYS_B_1" WHERE RDID =:1 and  (PDBZ,STATUS) not in(( :"SYS_B_0" , :"SYS_B_1" ); bs2qwd0crz5f3 update PD_DLB SET PDBZ =:"SYS_B_0" WHERE RDID =:1   修改为 update PD_DLB SET PDBZ =:"SYS_B_0" WHERE RDID =:1 and  PDBZ <> :"SYS_B_0" --//补充一下实际上不能这样1条1条改,猜测是打开brid的游标,然后循环修改相关记录. --//这样执行效率很低,而是一气呵成,一次修改需要的记录. --//不想使用logminer探查,随手找一个brid查询,使用as of查询方式. SELECT ROWID x         ,versions_starttime         ,versions_endtime         ,versions_xid         ,versions_operation         ,versions_startscn         ,versions_endscn         ,PD_DLB.PDBZ,pd_dlb.*     FROM PD_DLB VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;    WHERE RDID =11327282 ORDER BY versions_endscn; --//这样运行10分钟都没结果出来,只能改成查询10分钟之前的变化. SELECT ROWID x         ,versions_starttime         ,versions_endtime         ,versions_xid         ,versions_operation         ,versions_startscn         ,versions_endscn         ,PD_DLB.PDBZ,pd_dlb.*     FROM PD_DLB VERSIONS BETWEEN TIMESTAMP sysdate-15/1440 and sysdate    WHERE RDID =11327282 ORDER BY versions_endscn; --//结果不贴出了.15分钟内查询到112条,基本在做无用功.可以看出15*60/112 = 8.035, 8秒有一次刷新. SELECT ROWID x         ,versions_starttime         ,versions_endtime         ,versions_xid         ,versions_operation         ,versions_startscn         ,versions_endscn         ,PD_PMXS.status,PDBZ,PD_PMXS.*     FROM PD_PMXS VERSIONS BETWEEN TIMESTAMP sysdate-15/1440 and sysdate    WHERE RDID =11327282 ORDER BY versions_endscn; --//看到开发这样写代码,真心的很无语.这样问题已经存在多年,这么多人,无数的眼睛在看代码没人提出异议吗?可悲可叹..