SQL慢_分析 执行计划突变

1.分析及解决方案概述
分析原因 通过对现有信息的分析，可以看到SQL执行慢，是由于执行计划突变引起。
解决方案 针对现有情况，建议如下：
1）绑定执行计划

2.问题描述
03月14日SQL执行慢，需要从根本上分析问题原因，避免该类问题再次发生；
操作系统版本 Linux
数据库架构 RAC
数据库版本 19.24
业务名称 xxx

3.问题分析
3.1问题现象

某条SQL以前执行2分钟结束，昨天突然执行很慢。

3.2ASH分析
14号上午10点左右，确实抓到SQL慢，执行1小时左右

SQL> alter session set nls_date_format='yyyy-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_time>to_date('20260314 10:00','yyyymmdd hh24:mi') and sample_time<to_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.369

3.3执行计划以及SQL monitor分析分析

SQL> select sql_id,sql_text from v$sql where sql_id='au5gd3fdcg0xz'; no rows selected SQL> select sql_id,sql_text from v$sqlarea where sql_id='au5gd3fdcg0xz'; no rows selected SQL> select sql_id,sql_text from dba_hist_sqltext where sql_id='au5gd3fdcg0xz'; 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_id=a.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_NUMBER=b.INSTANCE_NUMBER and a.sql_id='au5gd3fdcg0xz' 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 : 1

3.4对应统计信息分析
可以发现13号进行了统计信息分析，14号SQL慢

OWNER OBJECT_NAME ANALYZED ROWCNT BLKCNT ------------ ------------------------ ------------------ ----------- --------- xxxxxx yyyyyy 20250916 22:05:19 10048242 406569

3.5对应SQL分析
可以发现对同一张表进行了取长度、取模运算，这种情况数据库执行计划无法评估准确

SQL> select sql_id,sql_text from dba_hist_sqltext where sql_id='au5gd3fdcg0xz'; 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_id=a.customer_id)

3.6问题分析总结
综合上述分析，我们可以看到：
SQL执行慢，是由于执行计划突变引起。

4.处理/优化建议
针对现有情况，建议如下：
1）绑定执行计划