CPU语句占用高解决一例

最新推荐文章于 2024-05-12 13:25:38 发布

转载最新推荐文章于 2024-05-12 13:25:38 发布 · 329 阅读

文章标签：

#python #数据库

查看主机CPU，发现只有10%的IDLE，高CPU进程的语句的基本为11个地区全扫描，存大较多的latch:cache buffers chains. 通过查看报告，发现这条语句1小时执行超过10万次，

SELECT ps_id, action_id, ps_param, TO_CHAR (busi_code), TO_CHAR (stop_type),

TO_CHAR (done_code), TO_CHAR (ps_type), TO_CHAR (prio_level),

TO_CHAR (sort_id), TO_CHAR (sub_valid_date, 'yyyymmddhh24miss'),

TO_CHAR (ps_service_type), bill_id, sub_bill_id,

TO_CHAR (create_date, 'yyyymmddhh24miss'), TO_CHAR (retry_times),

TO_CHAR (fail_num), fail_reason, TO_CHAR (op_id), TO_CHAR (sp_id),

region_code, TO_CHAR (org_id), TO_CHAR (old_ps_id), ps_net_code,

old_ps_param, target_param

FROM ps_pro_split4

WHERE ps_status = 0 AND ps_net_code = :f1

ORDER BY dead_line, prio_level DESC;

select ps_status , ps_net_code,count(*) from ps_pro_split4 group by ps_status, ps_net_code order by count(*) desc;

PS_STATUS	PS_NET_CODE	COUNT(*)
0	GCSTATUS	35140
1	GCSTATUS	4000
1	SJB	43
1	LYH	16
0	CPSTATUS	3
0	SPMS	3
1	LSRX	3
1	VAC	3
1	NB6_HW	2
0	ZXWG	2
1	HYS	2
1	ZXWG	2
9	NB6_HW	1

与这个应用的开发交流后得知每个对于上面的查询的每一列为条件查询次数基本相同，如果在这两个字段（PS_STATUS，PS_NET_CODE）建上联合索引的话，可以加快以下的查询:

1	SJB	43
1	LYH	16
0	CPSTATUS	3
0	SPMS	3
1	LSRX	3
1	VAC	3
1	NB6_HW	2
0	ZXWG	2
1	HYS	2
1	ZXWG	2
9	NB6_HW

但目前主要积压在GCSTATUS

的处理上，对于GCSTATUS来说使用表扫描效最高，目前由于ps_net_code 这个条件上用的在绑定变量，在SQL parse时bind peeking容易造成执行计划变化，建议这个字段不用绑定变量，并在两个字段建一个联合索引，让这个功能的语句每次执行时，进行一次hard parse会做一次bind peeking,可以避免发生SQL执行计划变化。