同一条SQL开发环境比生产环境执行速度快案例分享

原创董小姐 2025-03-20

134

问题背景

核心应用在业务高峰期崩溃，开发反馈SQL执行时间长导致，让进行排查，SQL如下：

SELECT  ID,OPERATE_USER_ID,JOB_ID,ROLE_ID,STATE,CHECK_DATA,ROW_STATE,UPDATE_TIME,CREATE_USER_ID,UPDATE_USER_ID,DELETED,CREATE_TIME,DESCRIPTION  FROM AAF_JOB_ROLE 
 WHERE  DELETED='0'
AND (ROW_STATE = '01');复制

同样的SQL在单位开发环境查询输出时间是1ms，开发想不通同一条SQL为啥在配置比单位高的生产服务器上执行时间比单位服务器长，让排查原因。

分析过程

查该表记录数

发现where条件后面的条件输出结果和不加where条件输出结果一样，所以放弃给where条件的列创建索引，没意义。

select count(*) from AAF_JOB_ROLE; 返回137294 行记录
select count(*) from AAF_JOB_ROLE WHERE  DELETED='0' AND (ROW_STATE = '01'); 返回137294 行记录复制

查看表结构

该表无大字段，就是一普通表，无索引，因为表中DELETED列的值都是0，ROW_STATE列的值都是01，where后面的条件相当于全表查询。

统计信息是否过旧

select owner, table_name , object_type, stale_stats, last_analyzed
from dba_tab_statistics
where owner = 'PORTAL'
and table_name in( 'AAF_JOB_ROLE');


OWNER			       TABLE_NAME	    OBJECT_TYPE  STA LAST_ANAL
------------------------------ -------------------- ------------ --- ---------
PORTAL		       AAF_JOB_ROLE	       TABLE	 NO  2025-3-11 下午10:04:17 

说明：
stale_stats：NO未过期  YES 过期

补充：如果统计信息过期，重新收集统计信息

--查询找出统计信息过期的原因
select table_owner, table_name, inserts, updates, deletes, timestamp
from all_tab_modifications
where table_owner = 'PORTAL'
and table_name = 'AAF_JOB_ROLE';复制

统计信息没过期是2025-3-11 下午10:04:17 ，但是于2025-3-17下午9:54:29进行过插入和删除操作

进行统计信息收集后在数据库连接工具中查询依然最短15秒输出结果最长1分20秒输出结果

--重新收集表的统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PORTAL',
tabname => 'AAF_JOB_ROLE',
estimate_percent => 100,
method_opt => 'for columns owner size auto',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/

说明：
method_opt => 'for all columns size auto' 表示对出现在 where 条件中的列自动判断是否收集直方图
no_invalidate 不失效    = false   不失效 =失效  你要拿这个一用，很繁忙的系统立马硬解析飙升，业务就要找你了
半夜的时候可以设为false

ANALYZE TABLE T1 COMPUTE STATISTICS;复制

执行时间查询

服务器上查询执行时间

set timing on 
set autotrace traceonly
SELECT  ID,OPERATE_USER_ID,JOB_ID,ROLE_ID,STATE,CHECK_DATA,ROW_STATE,UPDATE_TIME,CREATE_USER_ID,UPDATE_USER_ID,DELETED,CREATE_TIME,DESCRIPTION  FROM AAF_JOB_ROLE 
 WHERE  DELETED='0'
AND (ROW_STATE = '01');复制