Oracle数据库开发实战经验手记 ——那些年踩过的坑与性能优化之道（持续更新~~~）

本文链接：https://blog.csdn.net/Pte_moon/article/details/146025101

在研发过程中，使用 Oracle 数据库时可能会遇到一些性能和兼容性的问题。以下是一些本人在工作期间曾遇到的关于Oracle的一些问题，希望可以帮助到有需要的同学们。

1. 使用 `LISTAGG` 语法拼接字符超长

问题描述：

使用 LISTAGG 语法拼接字符时，可能会遇到字符超长的情况，通常限制为 4000 字符。
可以使用 XMLAGG 进行改写，但其效率较慢，影响查询性能。

解决方案：

如果必须保证数据的准确性，可以使用 XMLAGG。
否则，考虑先对结果进行截取，然后再做拼接，在业务上做出取舍。
比如这里我们举个例子：
我们要查询某个专业有哪些班级，如果班级超长，显示不下，我们可以截取一部分，但是如果业务要求必须显示所有，那我们可以针对某个专业的班级超过10个的情况，直接跳转到另一个弹窗，专门去显示这个专业的所有班级，新增一个查询接口，去查询某个专业的所有班级信息，这样我们就可以从业务上去解决这个问题，技术上也不存在难点。

2. 第三方数据库连接工具查询速度差异

问题描述：

使用某些第三方数据库连接工具进行 SQL 查询，一般比线上的分页查询要快一些。
线上的真实查询时间一般还要需要乘以 2，因为分页查询会多查询一个总记录数（total），需要遍历完所有数据。
使用第三方工具调试sql 的时候，建议使用datagrip，功能强大！用习惯了真的感觉比navicat好用多了（非广）。
优化sql的时候不要总是想着加索引加索引的，其实考虑到数据量还没到千万级的情况，其实加不加索引真的区别不大，大概率就是写的sql不合理！考虑局部调试下sql，看看哪里慢了，然后从局部开始优化sql ~

3. 复杂业务 SQL 中视图的使用

问题描述

复杂的业务 SQL 中存在多个视图，如果要在视图中进行数据筛选，建议使用 IN 或 = 进行值匹配，而不是范围查询（如大于小于）。
视图中的范围查询可能导致 Oracle 执行计划在做笛卡尔积后再应用过滤条件，导致查询速度变慢。

解决方案：

尽量避免在视图中使用范围查询，直接在主查询中对关联表进行数据筛选。
如果无法避免，考虑重构 SQL 或优化视图定义。

在实际工作中，作者曾遇到过类似的复杂 SQL，在视图中进行了字段的范围查询，导致主查询通过 JOIN 操作连接这些视图时，视图中的数据过滤条件总是在执行笛卡尔积之后才生效，从而导致总体数据量特别大，严重影响查询性能。通过将视图中的范围查询改为 IN 或 = 进行值匹配，或者直接去掉视图并将视图那部分的 SQL 合并到主查询中，可以使过滤条件在早期阶段生效，显著减少中间结果集的数据量，提高查询效率。

“直击那个让执行计划发疯的魔鬼视图，用等值过滤利刃肢解笛卡尔积怪兽，见证SQL从30秒到0.8秒的惊天逆转”

4. 导出百万级数据时的 FETCHSIZE 设置

问题描述：

导出百万级数据且导出数据的查询SQL 复杂的情况下，Oracle 的默认 FETCHSIZE 为 10，导致导出速度极慢，再加上深分页的影响，那就更慢了。
MySQL 本身是半双工通讯方式，不需要考虑 FETCHSIZE，而 Oracle 需要根据业务需求调整 FETCHSIZE。

正常来说分页场景，一般也就一页五六十条数据，所以oracle的10条一般大家是感受不到速度上的差异的，但一旦涉及到一次性查询全量的数据，这个差异一下子就出来了！

解决方案：

根据业务需求设置合适的 FETCHSIZE，例如将其提升到 1000，可以显著提高导出速度。
注意不要将 FETCHSIZE 设置得过高，以免导致内存溢出（OOM）。

“那个优化FetchSize的深夜，我亲手把1小时的导出变成1分钟，仿佛听见Oracle在哭泣！”

5. 高版本Oracle的玄学问题

问题SQL（低版本正常，高版本偶发异常）：

特别是当这段sql写在存储过程的时候，就会出现问题，问题很玄学，至今不知道具体的答案。
– 旧写法（存在版本兼容问题）
SELECT str FROM( SELECT TO_CHAR(REGEXP_SUBSTR(a.strs ,'[^,]+',1,l))str FROM (SELECT p_idstrValue strs FROM dual) a, (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=(...)) b ) GROUP BY str
优化后稳定版本：

– 新写法（通用稳定）
SELECT str FROM( SELECT TO_CHAR(REGEXP_SUBSTR(p_idstrValue ,'[^,]+',1,LEVEL))str FROM dual CONNECT BY LEVEL <= REGEXP_COUNT(p_idstrValue,',')+1 ) GROUP BY str

6. oracle备份数据，闪回库到指定时间

如果存在备份数据，或者恢复表数据的同学，一定要收藏下面的sql哦，说不定哪天就用上了~~~

--1、备份
CREATE TABLE T_TABLE_A_BF AS SELECT * FROM T_TABLE_A;

--2、启动表的row movement特性
ALTER TABLE T_TABLE_A ENABLE ROW MOVEMENT;

--3、闪回指定时间的快照
FLASHBACK TABLE T_TABLE_A TO TIMESTAMP
TO_TIMESTAMP('2023-07-14 10:50:00','yyyy-mm-dd hh24:mi:ss');

--4、关闭表的row movement功能
ALTER TABLE T_TABLE_A DISABLE ROW MOVEMENT;

7. 调试工具小细节 - DataGrip

当我们使用 DataGrip 进行调试时，只是单纯对表中数据做操作，可以通过工具栏的roll back进行回滚，涉及到对目标数据库中的表字段进行操作、修改表结构、创建函数、视图或存储过程等操作通常是不被事务控制的。这意味着这些操作一旦执行就会立即生效，无法回滚。因此，在执行这类操作之前，请务必确保您的SQL语句准确无误，并考虑在测试环境中先行验证，以避免对生产数据造成不必要的影响。
datagrip工具栏