同一条sql在不同时间执行效率差了3倍

lasdaybg

今天发现某生产环境在同一个小时内CPU占用差别挺大

前半小时：

Elapsed:		30.02 (mins)
DB Time:		162.60 (mins)

后半小时：

Elapsed:		30.02 (mins)
DB Time:		65.15 (mins)

前半小时sql耗时统计：

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
5,647.06	36,816	0.15	57.88	85.48	0.07	8tsb78qx45unv	JDBC Thin Client	UPDATE CPEHG_BASE SET TYPEID =...
1,021.76	18,039	0.06	10.47	96.79	0.00	0d4mxb2ccvmd4	JDBC Thin Client	UPDATE Log_Cmd_Table SET para=...
483.07	8,060	0.06	4.95	96.44	0.06	5vqy35rtsdtjn	JDBC Thin Client	insert into Log_Cmd_Table (id,...
461.30	4,561	0.10	4.73	90.36	0.66	950h4wt6k3ww7	JDBC Thin Client	UPDATE CPEHG_BASE SET REQUESTU...
231.70	2	115.85	2.37	99.85	0.03	3cw7c3x8pygcz	JDBC Thin Client	SELECT count(*) FROM CPEHG_POL...
125.33	1,554	0.08	1.28	96.23	0.14	c4usrd4661x20	JDBC Thin Client	insert into CPEHG_SERVICE_DETA...
121.15	2,548	0.05	1.24	89.75	3.93	d7rzhc7r01x83	JDBC Thin Client	insert into CPEHG_SERVICE_DETA...
112.57	1,840	0.06	1.15	90.62	0.23	0xh8ymdah16h2	JDBC Thin Client	insert into CPEHG_SERVICE_CONF...
108.00	2	54.00	1.11	99.85	0.01	0x3g2fc3y6acd	JDBC Thin Client	SELECT count(*) FROM CPEHG_POL...
107.97	36,162	0.00	1.11	92.19	1.43	3uvq17caa5ft8	JDBC Thin Client	delete from cpehg_service_deta...

后半小时sql耗时统计：

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
1,692.67	37,067	0.05	43.30	90.80	0.19	8tsb78qx45unv	JDBC Thin Client	UPDATE CPEHG_BASE SET TYPEID =...
493.73	35,175	0.01	12.63	96.83	0.00	0d4mxb2ccvmd4	JDBC Thin Client	UPDATE Log_Cmd_Table SET para=...
202.81	11,629	0.02	5.19	96.56	0.29	5vqy35rtsdtjn	JDBC Thin Client	insert into Log_Cmd_Table (id,...
141.92	4,492	0.03	3.63	92.09	1.55	950h4wt6k3ww7	JDBC Thin Client	UPDATE CPEHG_BASE SET REQUESTU...
94.99	194	0.49	2.43	99.27	39.68	8j2q03002svu7	JDBC Thin Client	select * from CPEHG_SERVICE wh...
93.13	450	0.21	2.38	99.78	0.00	b66jvws1qu7sb	JDBC Thin Client	SELECT COUNT(1) FROM CPEHG_BAS...
75.41	41	1.84	1.93	99.56	28.98	991065vqz4stx	JDBC Thin Client	select t1.deviceid, t1.service...
74.41	674,517	0.00	1.90	100.31	0.00	80w7qpnhucwn2	JDBC Thin Client	SELECT * FROM CPEHG_BASE_VIEW ...
51.54	1,692	0.03	1.32	95.88	0.37	c4usrd4661x20	JDBC Thin Client	insert into CPEHG_SERVICE_DETA...
50.25	560	0.09	1.29	99.24	0.00	c23jkhpwr6njg	JDBC Thin Client	SELECT * FROM CPEHG_UPDATEHIS ...
46.87	2,739	0.02	1.20	84.72	10.04	d7rzhc7r01x83	JDBC Thin Client	insert into CPEHG_SERVICE_DETA...

看Buffer Gets统计，差距应该都在这里：
前半小时：

Buffer Gets	Executions	Gets per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
176,259,218	36,816	4,787.57	38.41	5,647.06	85.5	.1	8tsb78qx45unv	JDBC Thin Client	UPDATE CPEHG_BASE SET TYPEID =...
79,471,815	18,039	4,405.56	17.32	1,021.76	96.8	0	0d4mxb2ccvmd4	JDBC Thin Client	UPDATE Log_Cmd_Table SET para=...
36,385,270	8,060	4,514.30	7.93	483.07	96.4	.1	5vqy35rtsdtjn	JDBC Thin Client	insert into Log_Cmd_Table (id,...
22,756,601	183	124,353.01	4.96	89.34	99.3	40	8j2q03002svu7	JDBC Thin Client	select * from CPEHG_SERVICE wh...
19,805,243	4,561	4,342.30	4.32	461.30	90.4	.7	950h4wt6k3ww7	JDBC Thin Client	UPDATE CPEHG_BASE SET REQUESTU...
17,898,864	2	8,949,432.00	3.90	231.70	99.8	0	3cw7c3x8pygcz	JDBC Thin Client	SELECT count(*) FROM CPEHG_POL...
15,771,536	2	7,885,768.00	3.44	108.00	99.9	0	0x3g2fc3y6acd	JDBC Thin Client	SELECT count(*) FROM CPEHG_POL...
14,712,148	43	342,142.98	3.21	79.02	99.5	29.1	991065vqz4stx	JDBC Thin Client	select t1.deviceid, t1.service...
7,868,771	1,554	5,063.56	1.71	125.33	96.2	.1	c4usrd4661x20	JDBC Thin Client	insert into CPEHG_SERVICE_DETA...
6,986,255	2,548	2,741.86	1.52	121.15	89.8	3.9	d7rzhc7r01x83	JDBC Thin Client	insert into CPEHG_SERVICE_DETA...
5,901,955	449	13,144.67	1.29	39.80	99.1	0	c23jkhpwr6njg	JDBC Thin Client	SELECT * FROM CPEHG_UPDATEHIS ...
5,589,205	657,237	8.50	1.22	59.32	100	0	80w7qpnhucwn2	JDBC Thin Client	SELECT * FROM CPEHG_BASE_VIEW ...

[size=13.3333330154419px]后半小时：

Buffer Gets	Executions	Gets per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
51,140,811	37,067	1,379.69	26.47	1,692.67	90.8	.2	8tsb78qx45unv	JDBC Thin Client	UPDATE CPEHG_BASE SET TYPEID =...
35,879,822	35,175	1,020.04	18.57	493.73	96.8	0	0d4mxb2ccvmd4	JDBC Thin Client	UPDATE Log_Cmd_Table SET para=...
24,124,482	194	124,353.00	12.49	94.99	99.3	39.7	8j2q03002svu7	JDBC Thin Client	select * from CPEHG_SERVICE wh...
13,944,222	41	340,102.98	7.22	75.41	99.6	29	991065vqz4stx	JDBC Thin Client	select t1.deviceid, t1.service...
13,490,776	11,629	1,160.10	6.98	202.81	96.6	.3	5vqy35rtsdtjn	JDBC Thin Client	insert into Log_Cmd_Table (id,...
7,377,449	560	13,174.02	3.82	50.25	99.2	0	c23jkhpwr6njg	JDBC Thin Client	SELECT * FROM CPEHG_UPDATEHIS ...
6,106,526	4,492	1,359.42	3.16	141.92	92.1	1.5	950h4wt6k3ww7	JDBC Thin Client	UPDATE CPEHG_BASE SET REQUESTU...
5,735,658	674,517	8.50	2.97	74.41	100.3	0	80w7qpnhucwn2	JDBC Thin Client	SELECT * FROM CPEHG_BASE_VIEW ...
2,739,192	450	6,087.09	1.42	93.13	99.8	0	b66jvws1qu7sb	JDBC Thin Client	SELECT COUNT(1) FROM CPEHG_BAS...
2,710,105	3	903,368.33	1.40	21.19	97.5	1.8	572fbaj0fdw2b	sqlplus@RMS_DATA2 (TNS V1-V3)	select output from table(dbms_...
2,266,508	1,692	1,339.54	1.17	51.54	95.9	.4	c4usrd4661x20	JDBC Thin Client	insert into CPEHG_SERVICE_DETA...

但是不明白为什么相近的两个时间段会存在这么大差异，可能是什么原因引起的？

lfree

最大的可能我猜的是并行.

或者某人修改了某些参数..

你看看执行计划不就清楚了吗?

howard_zhang

应该还是业务量上存在变化，AWR只是跑的一部分SQL，建议你查查ACTIVE_SESSION_HISTORY.

另外也有可能执行计划又变化，特别是如果你没有禁用ACS。

总而言之你给的信息太少了。