Oracle线上执行SQL特别慢的原因分析

一、背景:

线上反馈一张表select * from table where id=xxx语句执行特别慢,超过60s超时不能处理,第一直觉是索引失效了,开始执行创建索引语句create index index_name on table() online。但是执行了超过20分钟索引还没有创建成功,下意识觉得不正常。

按照过往经验,我应该查查是否有锁表的,但是当时不知道那根脑子抽筋了,在客户现场plsq中瞎点开了,突然发现一个窗口中的语句如下:

for update!!!

for update!!!

for update!!!

这敏感的字眼刺伤了我的眼睛。

再左上角一看,事务未提交的,谁干的!!!

赶快先回滚了,select和insert正常了,内心波涛汹涌,在公司群里发了图片发了牢骚。

二、锁表分析:

使用 plsql进行表数据编辑时如果是用for update 编辑,那么编辑完如果不提交就会直接锁表,还是全表锁。

一定一定务必使用rowid进行更新操作,使用rowid进行更新数据时,属于行级锁,编辑时表不受影响,直到最后一步提交时才当做一个原子去上锁/执行/释放锁一步完成。所以一定要用rowid,官方也推荐用rowid,弃用for update。

<think>好的,我现在需要处理用户关于Oracle性能优化和故障排查的几个问题。首先,用户想知道如何通过创建合适的索引来避免全表扫描。我记得全表扫描在数据量大时效率低下,而索引可以快速定位数据。需要解释B树索引和位图索引的区别,以及何时使用组合索引。还要注意索引维护的开销,比如DML操作的影响。 接下来是调试和提高存储过程效率的问题。应该提到动态性能视图,比如V$SQL和V$SESSION,用来监控执行情况。另外,使用DBMS_PROFILER或DBMS_HPROF进行性能分析,以及开启跟踪功能如SQL_TRACE。参数传递优化和避免隐式转换也是关键点。 关于执行计划优化SQL的例子,用户可能需要具体案例。比如未使用索引导致全表扫描,或者索引失效的情况。需要展示如何通过执行计划发现问题,然后创建函数索引或调整查询条件。 技术选型方面,Spring Cloud Alibaba和Netflix的比较,要突出Alibaba的组件如Nacos、Sentinel的优势,以及更好的本地化支持。这部分需要结合微服务架构的需求来分析。 故障排查CPU 100%的问题,应该按照步骤来:先用top找进程,然后线程,结合Oracle的动态视图查SQL,检查执行计划,资源争用等。可能需要用ASH报告或AWR报告分析。 还要注意引用用户提供的参考资料,特别是引用1到5的内容。例如,引用1提到动态性能视图,引用2讲优化器的作用,引用3关于统计信息和小表索引的问题,引用5中的执行计划代价分析。 需要确保回答结构清晰,分点说明,每个问题都有实际例子或步骤,并且自然地加入引用标记。同时,要生成相关问题,可能涉及索引类型选择、执行计划解读、存储过程参数优化、技术选型考虑因素等。 最后检查LaTeX格式是否正确,数学表达式用$包裹,独立公式用$$,代码块用正确语法,引用标识正确放置。确保使用中文,没有Markdown格式,符合用户的要求。</think>### 一、Oracle中通过索引优化全表扫描 1. **选择合适的索引类型** - **B树索引**:适用于高基数列(唯一值多)的等值查询或范围查询,例如主键字段: ```sql CREATE INDEX idx_emp_id ON employees(employee_id); ``` - **位图索引**:适用于低基数列(如性别、状态)的查询,但DML频繁的表不适用[^3]。 - **组合索引**:针对多条件查询,需注意字段顺序(高频查询条件在前),例如: ```sql CREATE INDEX idx_dept_salary ON employees(department_id, salary); ``` 2. **避免索引失效的场景** - 对索引列使用函数或运算(如`TO_CHAR(create_time)=‘20230101’`)会导致索引失效,需使用函数索引: ```sql CREATE INDEX idx_create_time ON employees(TO_CHAR(create_time, 'YYYYMMDD')); ``` - 隐式类型转换(如VARCHAR列与数值比较)会禁用索引,需保持数据类型一致。 3. **监控全表扫描原因** - 使用执行计划分析工具: ```sql EXPLAIN PLAN FOR SELECT * FROM employees WHERE status = 'INACTIVE'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); ``` - 若表数据量小(如小于1%数据块),优化器可能主动选择全表扫描。 --- ### 二、调试和提高存储过程性能 1. **性能监控工具** - 使用动态性能视图实时跟踪: ```sql SELECT sql_text, executions, elapsed_time FROM v$sql WHERE sql_text LIKE '%PROCEDURE_NAME%'; ``` 结合`V$SESSION_LONGOPS`监控长事务[^1]。 2. **代码级优化** - 减少上下文切换:批量处理代替逐行操作(如`BULK COLLECT` + `FORALL`)。 - 避免隐式游标:显式定义游标并控制`FETCH`次数。 - 参数传递优化:使用`NOCOPY`修饰符减少大型PL/SQL集合的拷贝开销。 3. **诊断工具** - 使用`DBMS_PROFILER`分析存储过程执行时间分布。 - 开启`SQL_TRACE`生成跟踪文件,结合`TKPROF`解析瓶颈点。 --- ### 三、执行计划优化SQL示例 **场景**:查询订单表时未使用索引导致全表扫描 - **原始SQL**: ```sql SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYYMM') = '202310'; ``` - **问题**:`TO_CHAR`导致索引失效,执行计划显示`FULL TABLE SCAN`[^5]。 - **优化方案**: ```sql -- 创建函数索引 CREATE INDEX idx_order_date ON orders(TO_CHAR(order_date, 'YYYYMM')); -- 改写查询条件 SELECT * FROM orders WHERE order_date >= TO_DATE('20231001', 'YYYYMMDD') AND order_date < TO_DATE('20231101', 'YYYYMMDD'); ``` --- ### 四、技术选型:Spring Cloud Alibaba vs Netflix | **组件** | **Alibaba** | **Netflix** | |------------------|--------------------------|-----------------------| | **注册中心** | Nacos(AP/CP可切换) | Eureka(AP) | | **配置中心** | Nacos(实时推送) | Spring Cloud Config | | **熔断降级** | Sentinel(流量控制细粒度) | Hystrix(维护模式) | | **网关** | Spring Cloud Gateway | Zuul 1.x(性能低) | **选型依据**: - **Alibaba优势**:国产化支持、组件集成度高(如Seata分布式事务)、社区活跃[^4]。 - **Netflix劣势**:部分组件停止更新,生态碎片化。 --- ### 五、线上服务CPU 100%排查流程 1. **定位高CPU进程** ```bash top -c # 查看进程PID及CPU占用 ``` 2. **分析线程资源** ```bash ps -mp <PID> -o THREAD,tid,time # 获取高CPU线程ID printf "%x\n" <TID> # 转换为16进制 ``` 3. **关联数据库会话** ```sql SELECT sql_id, sql_text FROM v$session WHERE paddr IN (SELECT addr FROM v$process WHERE spid = '<HEX_TID>'); ``` 4. **优化SQL或调整执行计划** - 使用`DBMS_SQLTUNE`生成优化建议。 - 检查索引有效性及统计信息更新频率[^5]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值