在Oracle 10.2.0.4数据库中,有一个SQL执行缓慢,超过数分钟无结果,等待事件又是空闲的SQL*Net message事件,最后只好强行中断。
这个SQL是一个普通的UPDATE语句,where子句中多张表关联,关联的表都是临时表。
update t_fund_product_info
set is_valid = 'N'
where prdt_id not in
(select a.prdt_id
from tmp_crm_DX_PRDT_FOR_INFO a, tmp_crm_PRDT_CATA_FOR_INFO b
where a.prdt_type = '501040'
and a.prdt_id = b.prdt_id
and b.prdt_key in (select distinct prdt_id
from tmp_crm_PRDT_CHANNEL
where dg_ch = 'XX商城'));
这个SQL语句,多次执行都没有出来结果。
在PL SQL DEV中F5得到的执行计划是这样:
UPDATE STATEMENT, GOAL = ALL_ROWS
UPDATE SCOTT T_FUND_PRODUCT_INFO
FILTER
TABLE ACCESS FULL SCOTT T_FUND_PRODUCT_INFO
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL SCOTT TMP_CRM_DX_PRDT_FOR_INFO
TABLE ACCESS FULL SCOTT TMP_CRM_PRDT_CATA_FOR_INFO
TABLE ACCESS FULL SCOTT TMP_CRM_PRDT_CHANNEL
虽然都是全表扫描,但表也不大,都是几千条记录。 NESTED LOOPS (嵌套连接)也没有什么问题。
(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1755048
)
我在表t_fund_product_info的字段prdt_id上建立主键,执行就OK。得到的执行计划是这样:
UPDATE STATEMENT, GOAL = ALL_ROWS
UPDATE SCOTT T_FUND_PRODUCT_INFO
HASH JOIN RIGHT ANTI
VIEW SYS VW_NSO_1
HASH JOIN
HASH JOIN
TABLE ACCESS FULL SCOTT TMP_CRM_PRDT_CHANNEL
TABLE ACCESS FULL SCOTT TMP_CRM_PRDT_CATA_FOR_INFO
TABLE ACCESS FULL SCOTT TMP_CRM_DX_PRDT_FOR_INFO
TABLE ACCESS FULL SCOTT T_FUND_PRODUCT_INFO
这个执行符合我的要求,是HASH JOIN RIGHT ANTI(哈希反连接)。
这个库新建的,表刚刚新建。检查user_tables,确认所有的表都没有被分析过。插一句,数据库的配置没有问题。于是,手工分析一下这四张表。
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
1 TMP_CRM_DX_PRDT_FOR_INFO 3624 103 2012-12-27 14:33:22
2 TMP_CRM_PRDT_CATA_FOR_INFO 2149 20 2012-12-27 14:05:02
3 TMP_CRM_PRDT_CHANNEL 6695 58 2012-12-27 14:05:02
4 T_FUND_PRODUCT_INFO 875 13 2012-12-27 14:05:02
再将主键删除,使用alter session set events='10046 trace name context forever,level 12'分析,得到执行计划没变化。如下:
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T_FUND_PRODUCT_INFO (cr=81443 pr=0 pw=0 time=455192 us)
0 FILTER (cr=81443 pr=0 pw=0 time=455183 us)
875 TABLE ACCESS FULL T_FUND_PRODUCT_INFO (cr=15 pr=0 pw=0 time=903 us)
875 NESTED LOOPS (cr=81428 pr=0 pw=0 time=451275 us)
916 NESTED LOOPS (cr=57242 pr=0 pw=0 time=277376 us)
875 TABLE ACCESS FULL TMP_CRM_DX_PRDT_FOR_INFO (cr=46157 pr=0 pw=0 time=201112 us)
916 TABLE ACCESS FULL TMP_CRM_PRDT_CATA_FOR_INFO (cr=11085 pr=0 pw=0 time=74469 us)
875 TABLE ACCESS FULL TMP_CRM_PRDT_CHANNEL (cr=24186 pr=0 pw=0 time=172016 us)
这个执行很快结束,并且成功。
再将主键恢复,继续alter session set events='10046 trace name context forever,level 12'分析执行,得到执行计划如下:
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T_FUND_PRODUCT_INFO (cr=207 pr=0 pw=0 time=10225 us)
0 HASH JOIN ANTI (cr=207 pr=0 pw=0 time=10223 us)
875 TABLE ACCESS FULL T_FUND_PRODUCT_INFO (cr=16 pr=0 pw=0 time=7102 us)
912 VIEW VW_NSO_1 (cr=191 pr=0 pw=0 time=7175 us)
912 HASH JOIN (cr=191 pr=0 pw=0 time=6260 us)
1351 HASH JOIN (cr=130 pr=0 pw=0 time=4500 us)
1595 TABLE ACCESS FULL TMP_CRM_DX_PRDT_FOR_INFO (cr=107 pr=0 pw=0 time=28 us)
2149 TABLE ACCESS FULL TMP_CRM_PRDT_CATA_FOR_INFO (cr=23 pr=0 pw=0 time=21 us)
1032 TABLE ACCESS FULL TMP_CRM_PRDT_CHANNEL (cr=61 pr=0 pw=0 time=30 us)
在分析前和分析后,无主键和有主键情况下,执行计划其实是一致的。但是没有主键,没有分析的情况下就是慢,慢到执行不出结果。
虽然问题解决了,但是我还是有疑问。
为什么在没有分析没有主键时,怎么执行都是很慢?
为什么在没有分析但有主键,执行就很快,执行计划采用了哈希反连接?
为什么在各个表分析以后,不管有没有主键,执行能成功了?
难道是在没有分析情况下,SQL生成执行计划很消耗时间?可是等待事件却又是空闲?
我怀疑,这会不会是网络异常中断导致的?因为这个网络设置是这样,在一定时间内,客户端和数据库服务器没有交互,它们之间的网络连接就会中断。
分享到:
相关推荐
Oracle数据库SQL性能优化学习可以用到的。
本问由藏经阁http://www.zangjing.net提供. Oracle数据库性能分析,掌握Oracle数据库处理SQL语句原理和Oracle数据库性能分析基本方法。
ORACLE SQL性能优化系列 ORACLE SQL性能优化系列 ORACLE SQL性能优化系列
浅谈Oracle数据库SQL性能优化.pdf
oracle数据库性能优化.pdforacle数据库性能优化.pdforacle数据库性能优化.pdforacle数据库性能优化.pdforacle数据库性能优化.pdf
浅谈Oracle数据库SQL性能优化 (1).pdf
非DBA使用的Oracle优化总结,也是平时开发中应该注意的细节,更是提高数据库性能的必要手段.
sql性能优化
Oracle数据库SQL语句的性能优化.pdf
Oracle数据库SQL语句性能调整 讲解的非常不错
目录 全面认识Oracle数据库 1 Oracle的数据倒入EXCEL中的方法 3 Oracle 9i如何设置时间间隔型数据 3 ...提高Oracle数据库系统Import的性能 96 Oracle数据库的四种启动方式 97 oracle数据库优化基础 97
数据库性能指标,其中包括sql数据库和oracle数据库的性能指标介绍
实现Oracle数据库SQL语句性能优化.pdf
1.9 减少访问数据库的次数 7 1.10 使用DECODE函数来减少处理时间 8 1.11 删除重复记录 8 1.12 用TRUNCATE替代DELETE 9 1.13 尽量多使用COMMIT 9 1.14 计算记录条数 9 1.15 用WHERE子句替换HAVING子句 9 1.16 减少...
内容包括: 第一章:oracle资源管理 第二章:oracle任务调度 第三章:AWR及顾问框架 第四章:自动维护任务 第五章:预警及诊断系统 第六章:Optimizer策略与...信誉第一,如有任何问题,可以给我发私信或者评论区留言
Oracle 数据库 性能测试 定义出程序中 执行时间较长的sql语句
oracle数据库性能情况查询脚本
试析Oracle数据库SQL语句的性能优化.pdf
关于如何提高ORACLE数据库SQL语句性能,对语句进行优化。