[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;
--//看到开发这样写代码,真心的很无语.这样问题已经存在多年,这么多人,无数的眼睛在看代码没人提出异议吗?可悲可叹..
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2213259/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2213259/