1654668e8a21a4e70b.png (9.72 KB, 下载次数: 120)
2025-10-10 14:05 上传
上图可知报告显示时间段数据库业务繁忙；
7322568e8a28ec891a.png (16.99 KB, 下载次数: 118)
2025-10-10 14:07 上传
上图中，Parses超过每秒300，Logons大于每秒2个，表明当前数据库存在争用问题。
8598068e8a48280059.png (22.68 KB, 下载次数: 139)
2025-10-10 14:15 上传
通过Top 10 Foreground Events by Total Wait Time图表，发现第一、二条等待事件与全局锁和数据库连接有关。
3675568e8a51bb06b3.png (15.73 KB, 下载次数: 119)
2025-10-10 14:18 上传
在SQL ordered by Elapsed Time表格中查看执行时间最长的SQL命令，发现排名最前的SQL语句为：
SELECT "LG_PERSON", "LG_DATE" FROM "MI_LOG" "A1"
WHERE "LG_DATE" > ADD_MONTHS(:1, :2)
AND CASE
WHEN INSTR("LG_PERSON", '(') > 0 THEN
SUBSTR("LG_PERSON", INSTR("LG_PERSON", '(', :3, :4) + 1, INSTR("LG_PERSON", ')', :5, :6) - INSTR("LG_PERSON", '(', :7, :8) - 1)
ELSE
"LG_PERSON"
END = :9
这条SQL语句就是导致 `SQL*Net message from dblink` 漫长等待和随之而来 `DFS lock handle` 争用的根源。
**核心问题：在 `WHERE` 子句中对列使用了函数，导致无法使用索引。**
1. **最严重的问题：`CASE...END = :9`**
- 在 `LG_PERSON` 列上使用了复杂的字符串函数（`INSTR`, `SUBSTR`）。
- 数据库**必须为表中的每一行**计算这个 `CASE` 表达式的结果，然后才能与绑定变量 `:9` 进行比较。
- 这必然导致**全表扫描**。如果 `MI_LOG` 表很大，这个操作会极其缓慢。
2. **问题二：复杂的字符串解析**
- 逻辑是：如果 `LG_PERSON` 包含括号 `(`，则提取括号内的内容；否则，使用整个字符串。
- 这个操作本身在应用层做会快得多，在数据库层对每一行数据做，成本非常高。
3. **问题三：通过dblink执行**
- 将一个会导致全表扫描的查询通过数据库链接执行，是“雪上加霜”。
- 所有数据都需要被远程数据库读取、处理，然后通过网络传输到本地数据库，网络延迟和负载都被放大了。
---
### 解决方案
我们的目标是**避免全表扫描**，让查询能够使用索引。
#### **方案一：最优方案 - 修改表结构（增加计算列）**
这是最彻底、性能最好的解决方案，但需要能够修改远程表结构。
1. **在远程数据库的 `MI_LOG` 表上添加一个虚拟列**，用于存储解析后的 `LG_PERSON` 值。
```sql
-- 在远程数据库上执行
ALTER TABLE MI_LOG ADD (
LG_PERSON_PARSED VARCHAR2(100) GENERATED ALWAYS AS (
CASE WHEN INSTR(LG_PERSON, '(') > 0 THEN
SUBSTR(LG_PERSON, INSTR(LG_PERSON, '(') + 1, INSTR(LG_PERSON, ')') - INSTR(LG_PERSON, '(') - 1)
ELSE
LG_PERSON
END
) VIRTUAL
);
```
2. **在这个新列上创建索引**。
```sql
-- 在远程数据库上执行
CREATE INDEX IDX_MI_LOG_PARSED_PERSON ON MI_LOG (LG_PERSON_PARSED);
```
3. **修改你的SQL语句**，直接查询这个新列。
```sql
SELECT LG_PERSON, LG_DATE FROM MI_LOG@dblink_name
WHERE LG_DATE > ADD_MONTHS(:1, :2)
AND LG_PERSON_PARSED = :9;
```
**效果**：修改后，查询将直接使用 `IDX_MI_LOG_PARSED_PERSON` 索引，性能提升几个数量级，网络传输量也大幅减少。
#### **方案二：次优方案 - 使用函数索引（如果无法新增列）**
如果无法添加新列，可以在远程表上创建一个函数索引。
1. **在远程数据库上创建函数索引**。
```sql
-- 在远程数据库上执行
CREATE INDEX IDX_MI_LOG_FUNC_PERSON ON MI_LOG (
CASE
WHEN INSTR(LG_PERSON, '(') > 0 THEN
SUBSTR(LG_PERSON, INSTR(LG_PERSON, '(') + 1, INSTR(LG_PERSON, ')') - INSTR(LG_PERSON, '(') - 1)
ELSE
LG_PERSON
END
);
```
2. **保持SQL语句不变**。Oracle会自动识别并使用这个函数索引。
**注意**：确保你的SQL语句中的 `CASE` 表达式与创建索引时的表达式**完全一致**。
#### **方案三：应用层解决方案 - 拆分查询**
如果既不能改表也不能创建索引，可以尝试优化查询逻辑。
1. **首先，查询可能不需要括号的数据**。
```sql
SELECT LG_PERSON, LG_DATE FROM MI_LOG@dblink_name
WHERE LG_DATE > ADD_MONTHS(:1, :2)
AND LG_PERSON = :9 -- 直接匹配
```
2. **然后，查询需要提取括号内容的数据**（这仍然很慢，但数据量可能较少）。
```sql
SELECT LG_PERSON, LG_DATE FROM MI_LOG@dblink_name
WHERE LG_DATE > ADD_MONTHS(:1, :2)
AND LG_PERSON LIKE '%(' || :9 || ')%' -- 利用LIKE进行初步过滤
```
然后**在应用层**对结果集进行精确验证，检查括号内的内容是否等于 `:9`。
这个方法利用了 `LIKE`，虽然也不是最优，但可能比全表扫描的函数计算稍好一些。它将压力从CPU转移到了字符串匹配上。
#### **方案四：终极架构优化 - 物化视图**
如果以上都不可行，并且这是一个频繁执行的关键查询，请认真考虑使用**物化视图**。
1. 在本地数据库创建一个物化视图，定期从远程 `MI_LOG` 表刷新数据。
2. 在这个本地物化视图上创建所需的索引（包括函数索引或虚拟列索引）。
3. 修改应用，直接查询本地的物化视图。
这完全消除了dblink的等待，并且你可以在本地随意优化索引。
---
### 总结与行动计划
| 方案 | 优点 | 缺点 | 推荐度 |
| :--- | :--- | :--- | :--- |
| **1. 增加虚拟列+索引** | 性能最佳，一劳永逸 | 需要修改远程表结构 | ★★★★★ |
| **2. 创建函数索引** | 性能好，无需改表 | 需要维护索引，表达式必须一致 | ★★★★☆ |
| **3. 拆分查询** | 无需DBA权限，应用层解决 | 逻辑复杂，性能提升有限 | ★★☆☆☆ |
| **4. 物化视图** | 彻底解决dblink问题，性能极佳 | 数据非实时，需要存储空间和刷新机制 | ★★★★☆ |
**立即行动建议：**
1. **联系远程数据库的DBA**，将这条SQL和其性能影响（导致全表扫描）告知他们。
2. **优先提议方案一（添加虚拟列）**，这是最规范的解决方案。
3. 如果被拒绝，**提议方案二（函数索引）**。
4. 如果都无法实现，评估使用**方案四（物化视图）**的可行性。
5. **临时性地**，可以尝试方案三来缓解问题，但这并非长久之计。
**根本原因就是：一个设计不当的SQL，通过dblink执行，引发了全表扫描，进而导致网络长时间等待和本地资源争用。** 修复了这条SQL，两个顶级等待事件都会显著下降。
| 欢迎光临 重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |