1.分析及解决方案概述分析原因 通过对现有信息的分析可以看到SQL执行慢是由于执行计划突变引起。解决方案 针对现有情况建议如下1绑定执行计划2.问题描述03月14日SQL执行慢需要从根本上分析问题原因避免该类问题再次发生操作系统版本 Linux数据库架构 RAC数据库版本 19.24业务名称 xxx3.问题分析3.1问题现象某条SQL以前执行2分钟结束昨天突然执行很慢。3.2ASH分析14号上午10点左右确实抓到SQL慢执行1小时左右SQL alter session set nls_date_formatyyyy-mm-dd hh24:mi:ss; select min(sample_time) from gv$active_session_history; MIN(SAMPLE_TIME) --------------------------------------------------------------------------- 11-MAR-26 03.59.20.642 PM set line 200 pages 1000 col program for a30 col exec_time for a30 select sql_id,sql_exec_start,sql_exec_id,program,sql_plan_hash_value,max(sample_time)-sql_exec_start exec_time from gv$active_session_history where sample_timeto_date(20260314 10:00,yyyymmdd hh24:mi) and sample_timeto_date(20260314 10:20,yyyymmdd hh24:mi) and sql_exec_start is not null --and program like %JDBC% --and machine机器名称 --and user_id(select user_id from dba_users where username用户名称) group by sql_id,sql_exec_start,sql_exec_id,program,sql_plan_hash_value order by exec_time; au5gd3fdcg0xz 2026-03-14 09:14:59 33554432 JDBC Thin Client 3154865339 000000000 01:05:00.3693.3执行计划以及SQL monitor分析分析SQL select sql_id,sql_text from v$sql where sql_idau5gd3fdcg0xz; no rows selected SQL select sql_id,sql_text from v$sqlarea where sql_idau5gd3fdcg0xz; no rows selected SQL select sql_id,sql_text from dba_hist_sqltext where sql_idau5gd3fdcg0xz; SQL_ID SQL_TEXT --------------- -------------------------------------------------------------------------------- au5gd3fdcg0xz select distinct a.celler as phone, a.real_name as code_1, decode(a.gender,F,Ůʿ,M,Јʺ) as code_2, replace(to_char(a.birthday,mm-dd),-,Ղ)||ɕ as code_3 from t_customer a, t_contract_master b, t_contract_product c where (a.customer_id c.insured_1 or b.applicant_id a.customer_id) and b.policy_id c.policy_id and b.liability_state 1 and c.product_num 100 and length(a.celler) 11 and months_between(sysdate, a.birthday) / 12 17 and mod(months_between(sysdate, a.birthday), 12) 0 AND NOT EXISTS (SELECT 1 FROM T_CLAIM_CASE TCC, T_REPORT_CASE TRC WHERE TRC.CASE_ID TCC.CASE_ID() AND TRC.INSURED_ID A.CUSTOMER_ID AND (TRC.ACCIDENT_TYPE 3 OR TCC.ACCIDENT_TYPE 3)) AND NOT EXISTS (select 1 from t_company_customer ccu where ccu.customer_ida.customer_id)发现执行计划突变SQL set lines 200 pages 1000 col shijian for a15 col inst_id for 99 col execu_d for 999999999999 col bg_d for 9999999999 col dr_d for 9999999999 col et_d for 99999999 col ct_d for 99999999214.248.35.41 col io_time for 999999 col clus_time for 999999 col ap_time for 999999 col cc_time for 999999 col OPTIMIZER_MODE for a20 col et_onetime for 999999.999 select to_char(b.END_INTERVAL_TIME,yyyymmdd hh24:mi) shijian, a.plan_hash_value, a.OPTIMIZER_MODE, sum(a.EXECUTIONS_DELTA) execu_d, sum(a.BUFFER_GETS_DELTA ) bg_d, sum(a.DISK_READS_DELTA ) dr_d, sum(a.ELAPSED_TIME_DELTA/1000000) et_d, sum(a.CPU_TIME_DELTA/1000000) ct_d, sum(IOWAIT_DELTA/1000000) io_time, sum(CLWAIT_DELTA/1000000) clus_time, sum(APWAIT_DELTA/1000000) ap_time, sum(ccwait_delta/1000000) cc_time, sum(PX_SERVERS_EXECS_DELTA) px, decode(sum(a.EXECUTIONS_DELTA),0,sum(a.ELAPSED_TIME_DELTA/1000000), sum(a.ELAPSED_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)) et_onetime, decode(sum(a.EXECUTIONS_DELTA),0,sum(a.rows_processed_DELTA),sum(a.rows_processed_DELTA)/sum(a.EXECUTIONS_DELTA)) rw_onetime from dba_hist_sqlstat a,dba_hist_snapshot b where a.SNAP_ID b.SNAP_ID and a.INSTANCE_NUMBERb.INSTANCE_NUMBER and a.sql_idau5gd3fdcg0xz group by to_char(b.END_INTERVAL_TIME,yyyymmdd hh24:mi),a.plan_hash_value,a.OPTIMIZER_MODE order by 1;可以发现慢在NESTED LOOPS需要进行三个4G次SQL set linesize 5000 pagesize 50000 long 999999 longchunksize 999999 select dbms_sqltune.report_sql_monitor(sql_id au5gd3fdcg0xz) from dual; SQL Monitoring Report SQL Text ------------------------------ SELECT DISTINCT a.celler AS phone, a.real_name AS code_1, DECODE(a.gender, F, Ůʿ, M, Јʺ) AS code_2, REPLACE(TO_CHAR(a.birthday, mm-dd), -, Ղ) || ɕ AS code_3 FROM t_customer a, t_contract_master b, t_contract_product c WHERE (a.customer_id c.insured_1 OR b.applicant_id a.customer_id) AND b.policy_id c.policy_id AND b.liability_state 1 AND c.product_num 100 AND LENGTH(a.celler) 11 AND MONTHS_BETWEEN(SYSDATE, a.birthday) / 12 17 AND MOD(MONTHS_BETWEEN(SYSDATE, a.birthday), 12) 0 AND NOT EXISTS ( SELECT 1 FROM T_CLAIM_CASE TCC, T_REPORT_CASE TRC WHERE TRC.CASE_ID TCC.CASE_ID() AND TRC.INSURED_ID A.CUSTOMER_ID AND (TRC.ACCIDENT_TYPE 3 OR TCC.ACCIDENT_TYPE 3) ) AND NOT EXISTS ( SELECT 1 FROM t_company_customer ccu WHERE ccu.customer_id a.customer_id ); Error: ORA-1013 ------------------------------ ORA-01013: user requested cancel of current operation Global Information ------------------------------ Status : DONE (ERROR) Instance ID : 2 Session : xxxxxxxx(3223:29446) SQL ID : au5gd3fdcg0xz SQL Execution ID : 33554434 Execution Started : 03/15/2026 23:34:59 First Refresh Time : 03/15/2026 23:35:05 Last Refresh Time : 03/16/2026 00:50:45 Duration : 4546s Module/Action : JDBC Thin Client/- Service : htl1 Program : JDBC Thin Client Fetch Calls : 13.4对应统计信息分析可以发现13号进行了统计信息分析14号SQL慢OWNER OBJECT_NAME ANALYZED ROWCNT BLKCNT ------------ ------------------------ ------------------ ----------- --------- xxxxxx yyyyyy 20250916 22:05:19 10048242 4065693.5对应SQL分析可以发现对同一张表进行了取长度、取模运算这种情况数据库执行计划无法评估准确SQL select sql_id,sql_text from dba_hist_sqltext where sql_idau5gd3fdcg0xz; SQL_ID SQL_TEXT --------------- -------------------------------------------------------------------------------- au5gd3fdcg0xz select distinct a.celler as phone, a.real_name as code_1, decode(a.gender,F,Ůʿ,M,Јʺ) as code_2, replace(to_char(a.birthday,mm-dd),-,Ղ)||ɕ as code_3 from t_customer a, t_contract_master b, t_contract_product c where (a.customer_id c.insured_1 or b.applicant_id a.customer_id) and b.policy_id c.policy_id and b.liability_state 1 and c.product_num 100 and length(a.celler) 11 and months_between(sysdate, a.birthday) / 12 17 and mod(months_between(sysdate, a.birthday), 12) 0 AND NOT EXISTS (SELECT 1 FROM T_CLAIM_CASE TCC, T_REPORT_CASE TRC WHERE TRC.CASE_ID TCC.CASE_ID() AND TRC.INSURED_ID A.CUSTOMER_ID AND (TRC.ACCIDENT_TYPE 3 OR TCC.ACCIDENT_TYPE 3)) AND NOT EXISTS (select 1 from t_company_customer ccu where ccu.customer_ida.customer_id)3.6问题分析总结综合上述分析我们可以看到SQL执行慢是由于执行计划突变引起。4.处理/优化建议针对现有情况建议如下1绑定执行计划
