某社保系统在业务高峰期间系统响应缓慢.下面出现在这个征状时的awr报告
在16时分钟的运行时间内DB Time;到了300分钟.服务器是2颗(2核的)CPU
共享池的大小为9808M,每秒解析次数183次,每秒硬解析43
等待事件中latch:library cache,latch:shared pool,latch free是主要的等待事件.这些是与解析相关的.
在16分钟的运行时间内latch:library cache总的等待时间为18433秒.latch:shared pool总的等待时间为4330,latch free总的等待时间为3499
Cursor_sharing参数为exact
最消耗共享池内存和存在高版本游标的是insert语句. 通过version_rpt3_23.sql脚本查看insert语句不能共享的原因是绑定变量丢失.
INSERT INTO KCD4 (AAC001, AAE030, AAE031, AAE117, AAE208, AAE209, AAZ107, AAZ208, AAZ216, AAZ217, AJE001, AKA030, AKA130, AKC228, AKC291, AKE029, AKE030, AKE032, AKE033, AKE034, AKE035, AKE036, AKE037, AKE038, AKE039, AKE040, AKE041, AKE042, AKE043, AKE044, AKE045, AKE046, AKE047, AKE049, AKE050, AKE051, ALC113, ALC136, ALE001, AME001, AKB081, AKC264, AAA027, AKE025, AKE009, AKE011, AKE012, AKE016, AKE018, AKE019, AKE026, AKE071, AKE072, AKE073, AKE074, AKE075, AKE076, AKE077, AKE078, AKE079, PRSENO, BKA001, AAE100, BKA003, AKE083, AKE084, AKE085, AKE086, AKE087, AKE088, AKE089, AKE090/*AKE080*/) VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72)
上面的语句的形式为
Insert into table(column1, column2, column72) values (:1, :2, :3, …:72)
这种插入语句是通过对象映射工具生成的.其中values子句中存在许多的null值.对于insert into 或者select into可以考虑不要使用绑定变量.对于一个字段很多的表，在insert的时候不要把值为null, 字段允许为空的所有字段都体现在insert语句中.因为当字段为null时,在解析时是没办法判断类型和长度的,当这样的字段太多时sql语句是没办法共享的.对于这种问题修改insert语句的写法是最佳的解决办法.上面的awr报告中的共享池内存在自动管理模式下增长到了9808M,buffer cache为6,528M.可以考虑手工来指定shared pool和buffer cache的大小.