已索引
2025.05.20 19:50 分， DBA 接到应用人员电话，说是变更后应用日志报 broken pipe 错误。
变更内容为：将历史表 H 修改为 H.OLD 后，重新创建了一个 H 表，然后将最近一个月的数据从 H.OLD 表导入 H 表。
DBA 登录系统后，查询当前没锁等待现象，但有两个会话在 H 表上持有锁。
根据会话信息找到当前会话正在执行的 SQL 语句为 UPDATE H .... WHERE ID = .. 语句。
这个 UPDATE 语句一直在 v$session 中，表明该语句一直在执行。
查询该语句的执行计划：
alter session set nls_date_format='yyyy-mm-dd hh24:mi;ss';
set lines 200;
set pages 800;
col operation for a18;
select INST_ID,PLAN_HASH_VALUE,ID,PARENT_ID,OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,TIMESTAMP
from GV$SQL_PLAN
where SQL_ID='b8drmbgrxt51y'
order by TIMESTAMP,ID;
PLAN_HASH_VALUE ID PARENT_ID OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE TIMESTAMP
--------------- ---------- ---------- ------------------------------ ------------ ------------------------------- -------------------- -------------------
2794891096 0 UPDATE STATEMENT 2022-08-03 00:20;01
2794891096 1 0 UPDATE XX_YQ_BUSIXXXX_GATEWAY 2022-08-03 00:20;01
2794891096 2 1 INDEX UNIQUE SCAN SYS_C0054478 INDEX (UNIQUE) 2022-08-03 00:20;01
2670273405 0 UPDATE STATEMENT 2025-05-20 18:25;06
2670273405 1 0 UPDATE XX_YQ_BUSIXXXX_GATEWAY 2025-05-20 18:25;06
2670273405 2 1 TABLE ACCESS FULL XX_YQ_BUSIXXXX_GATEWAY TABLE 2025-05-20 18:25;06发现该语句执行计划发生了变化，之前走的索引，现在走的全表扫描。查看具体的执行计划：
SQL> select PLAN_TABLE_OUTPUT from table(dbms_xplan.display_cursor('b8drmbgrxt51y',null,'allstats last'));
Plan hash value: 2670273405
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 5 (100)| |
| 1 | UPDATE | XX_YQ_BUSIXXXX_GATEWAY | | | | |
| 2 | TABLE ACCESS FULL| XX_YQ_BUSIXXXX_GATEWAY | 1 | 2946 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------统计 SQL 语句的执行时间，发现该表在 18:30 左右刚刚建立好的时候，语句执行很快，都在 1s 内。
后面时间慢慢增长，18：40左右增长到 30s左右，19：10后开始超过 120s（业务超时时间）。
很明显，随着数据的增长（将最近一个月的数据从 H.OLD 表导入 H 表），全表扫描的性能急剧下降，问题出现在没有走索引而走的全表扫描上。
进一步分析， H.OLD 表有且只有一个缺省索引，在 ID 列上，H 表没有任何索引。
在 H 表的 ID 列上创建索引后，问题解决。
PLAN_HASH_VALUE ID PARENT_ID OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE TIMESTAMP
--------------- ---------- ---------- ------------------------------ ------------ ------------------------------- -------------------- -------------------
2794891096 0 UPDATE STATEMENT 2022-08-03 00:20;01
2794891096 1 0 UPDATE XX_YQ_BUSIXXXX_GATEWAY 2022-08-03 00:20;01
2794891096 2 1 INDEX UNIQUE SCAN SYS_C0054478 INDEX (UNIQUE) 2022-08-03 00:20;01
2670273405 0 UPDATE STATEMENT 2025-05-20 18:25;06
2670273405 1 0 UPDATE XX_YQ_BUSIXXXX_GATEWAY 2025-05-20 18:25;06
2670273405 2 1 TABLE ACCESS FULL XX_YQ_BUSIXXXX_GATEWAY TABLE 2025-05-20 18:25;06
3887297726 0 UPDATE STATEMENT 2025-05-20 20:41;32
3887297726 1 0 UPDATE XX_YQ_BUSIXXXX_GATEWAY 2025-05-20 20:41;32
3887297726 2 1 INDEX UNIQUE SCAN SYS_C0084606 INDEX (UNIQUE) 2025-05-20 20:41;32
Plan hash value: 3887297726
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 3 (100)| |
| 1 | UPDATE | XX_YQ_BUSIXXXX_GATEWAY | | | | |
| 2 | INDEX UNIQUE SCAN| SYS_C0084606 | 1 | 2946 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------再次查询 SQL 语句的执行时间，都在 1s 内完成。
