在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性能优化学习可以用到的。
ORACLE SQL性能优化是数据库管理员和开发者非常关心的一个话题。为了提高数据库的性能,ORACLE 提供了多种优化技术。下面我们将详细介绍 ORACLE SQL 性能优化系列中的一些重要知识点。 一、访问表的方式 ORACLE ...
总结来说,Oracle数据库SQL语句跟踪器是一个强大的性能分析工具,它通过Toad for Oracle等工具提供深入的洞察,帮助数据库管理员和开发者有效地优化数据库性能,提升系统的整体响应速度和稳定性。通过实时监控、性能...
总的来说,Oracle数据库性能优化是一个全面的过程,涵盖硬件配置、软件设置、SQL优化、存储策略等多个方面。需要根据系统负载、业务需求以及资源状况进行综合分析,持续监控和调整,以实现最优的数据库运行状态。...
Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其SQL语法是进行数据操作和查询的核心工具。本资料包针对Oracle数据库的SQL使用提供了丰富的练习资源,旨在帮助用户深入理解和熟练掌握SQL语言在Oracle环境...
这本"Oracle数据库SQL语句的性能优化.pdf"很可能提供了深入的指导,旨在帮助数据库管理员和开发人员理解如何写出高效、优化的SQL查询,以最大化数据库性能。 一、SQL性能优化的重要性 在大数据时代,数据库管理系统...
在Oracle数据库SQL查询优化的过程中,包括三个阶段:解析、执行和提取数据。解析阶段检查SQL语句的语法和语义,生成执行计划并存放到共享池中。执行阶段指的是服务器进程按SQL语句执行计划执行SQL语句,执行计划涉及...
为了判断Oracle数据库服务器是否存在IO性能问题,通常需要关注以下几个指标: **知识点:** - **单个IO响应时间**:衡量一个IO请求从发出到完成所需的时间,是评估IO性能的重要指标之一。 - **标准**:一般情况下...
总的来说,Oracle数据库系统性能和应用性能优化是一个涉及多方面的复杂任务,需要对数据库架构、SQL优化、操作系统和网络有深入的理解。通过持续监控、分析和调整,可以有效地提升系统性能,满足业务需求。
SQL(Structured Query Language)数据库和Oracle数据库作为两种广泛应用的关系型数据库管理系统,它们的性能指标分析对于优化数据库性能至关重要。 一、SQL数据库性能指标 1. **查询时间**:这是最基本的性能指标...
《基于ORACLE数据库的SQL性能优化》这篇文档为我们提供了Oracle数据库SQL性能优化的全面指导,从理论到实践,结合具体案例分析了数据库逻辑结构、物理存储结构优化、使用分区等策略,并进一步讲解了SQL性能分析、...
实现Oracle数据库SQL语句性能优化.pdf
本文将讨论 Oracle 数据库语句性能优化的方法,从系统设计阶段的重要性、SQL 语句处理的过程、共享语句、访问路径、表连接和干预执行计划等优化方法进行深入分析,并研究 SQL 语句的运行机制、优化要素和实践应用。...
Oracle数据库性能分析是数据库管理员(DBA)日常工作中至关重要的一环,特别是在大型金融机构如工商银行这样的环境中,数据库的稳定性和性能直接影响到业务的运行效率和服务质量。本文将基于提供的内容,详细阐述...
Oracle数据库是全球广泛使用的...总的来说,Oracle数据库SQL学习笔记应该涵盖这些核心领域,并深入探讨每个主题,包括实例、最佳实践和问题解决策略。通过持续学习和实践,你可以成为一名熟练的Oracle数据库专业人员。
如V$视图用于实时监控数据库状态,ASH(Active Session History)和AWR(Automatic Workload Repository)报告用于长期性能分析。理解如何解读这些报告,找出性能问题,并采取相应措施是优化工作的关键步骤。 综上...
在Oracle数据库管理中,定期进行表分析对于优化查询性能至关重要。通过分析表和索引,Oracle可以更新统计信息,这些信息将被查询优化器用于选择最佳的执行计划。本文将详细介绍如何自动生成表分析SQL语句和索引分析...
本文将详细分析Oracle数据库的性能优化目标和策略,为相关人员提供指导。 1. Oracle数据库优化目标 1.1 压缩数据库响应时间 响应时间是衡量数据库性能的重要指标,它指的是用户请求到获取结果之间的时间间隔。通过...
基于Oracle数据库电信CRM经营分析系统的性能优化 Oracle 数据库是当前使用最为广泛的大型数据库管理系统,对电信行业 CR M 应用的性能优化方法至关重要。作为关系型数据库,Oracle 数据库在电信 CRM 经营分析系统...