`
mikixiyou
  • 浏览: 1098004 次
  • 性别: Icon_minigender_1
  • 来自: 南京
博客专栏
C3c8d188-c0ab-3396-821d-b68331e21226
Oracle管理和开发
浏览量:352836
社区版块
存档分类
最新评论

Oracle数据库的SQL性能问题分析

阅读更多

在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 SQL性能优化系列

    ORACLE SQL性能优化是数据库管理员和开发者非常关心的一个话题。为了提高数据库的性能,ORACLE 提供了多种优化技术。下面我们将详细介绍 ORACLE SQL 性能优化系列中的一些重要知识点。 一、访问表的方式 ORACLE ...

    Oracle数据库sql语句 跟踪器

    总结来说,Oracle数据库SQL语句跟踪器是一个强大的性能分析工具,它通过Toad for Oracle等工具提供深入的洞察,帮助数据库管理员和开发者有效地优化数据库性能,提升系统的整体响应速度和稳定性。通过实时监控、性能...

    oracle数据库性能优化.pdf

    总的来说,Oracle数据库性能优化是一个全面的过程,涵盖硬件配置、软件设置、SQL优化、存储策略等多个方面。需要根据系统负载、业务需求以及资源状况进行综合分析,持续监控和调整,以实现最优的数据库运行状态。...

    Oracle数据库SQL练习

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其SQL语法是进行数据操作和查询的核心工具。本资料包针对Oracle数据库的SQL使用提供了丰富的练习资源,旨在帮助用户深入理解和熟练掌握SQL语言在Oracle环境...

    Oracle数据库SQL语句的性能优化.pdf

    这本"Oracle数据库SQL语句的性能优化.pdf"很可能提供了深入的指导,旨在帮助数据库管理员和开发人员理解如何写出高效、优化的SQL查询,以最大化数据库性能。 一、SQL性能优化的重要性 在大数据时代,数据库管理系统...

    浅谈Oracle数据库SQL性能优化 (1).pdf

    在Oracle数据库SQL查询优化的过程中,包括三个阶段:解析、执行和提取数据。解析阶段检查SQL语句的语法和语义,生成执行计划并存放到共享池中。执行阶段指的是服务器进程按SQL语句执行计划执行SQL语句,执行计划涉及...

    Oracle数据库服务器IO高的分析方案.docx

    为了判断Oracle数据库服务器是否存在IO性能问题,通常需要关注以下几个指标: **知识点:** - **单个IO响应时间**:衡量一个IO请求从发出到完成所需的时间,是评估IO性能的重要指标之一。 - **标准**:一般情况下...

    oracle数据库系统性能和应用性能优化

    总的来说,Oracle数据库系统性能和应用性能优化是一个涉及多方面的复杂任务,需要对数据库架构、SQL优化、操作系统和网络有深入的理解。通过持续监控、分析和调整,可以有效地提升系统性能,满足业务需求。

    数据库性能指标,其中包括sql数据库和oracle数据库的性能指标介绍

    SQL(Structured Query Language)数据库和Oracle数据库作为两种广泛应用的关系型数据库管理系统,它们的性能指标分析对于优化数据库性能至关重要。 一、SQL数据库性能指标 1. **查询时间**:这是最基本的性能指标...

    基于ORACLE数据库的SQL性能优化.pdf

    《基于ORACLE数据库的SQL性能优化》这篇文档为我们提供了Oracle数据库SQL性能优化的全面指导,从理论到实践,结合具体案例分析了数据库逻辑结构、物理存储结构优化、使用分区等策略,并进一步讲解了SQL性能分析、...

    实现Oracle数据库SQL语句性能优化.pdf

    实现Oracle数据库SQL语句性能优化.pdf

    Oracle数据库语句性能优化方法.pdf

    本文将讨论 Oracle 数据库语句性能优化的方法,从系统设计阶段的重要性、SQL 语句处理的过程、共享语句、访问路径、表连接和干预执行计划等优化方法进行深入分析,并研究 SQL 语句的运行机制、优化要素和实践应用。...

    ORACLE_数据库性能分析篇

    Oracle数据库性能分析是数据库管理员(DBA)日常工作中至关重要的一环,特别是在大型金融机构如工商银行这样的环境中,数据库的稳定性和性能直接影响到业务的运行效率和服务质量。本文将基于提供的内容,详细阐述...

    oracle 数据库 SQL学习笔记

    Oracle数据库是全球广泛使用的...总的来说,Oracle数据库SQL学习笔记应该涵盖这些核心领域,并深入探讨每个主题,包括实例、最佳实践和问题解决策略。通过持续学习和实践,你可以成为一名熟练的Oracle数据库专业人员。

    Oracle数据库性能优化 盖国强著

    如V$视图用于实时监控数据库状态,ASH(Active Session History)和AWR(Automatic Workload Repository)报告用于长期性能分析。理解如何解读这些报告,找出性能问题,并采取相应措施是优化工作的关键步骤。 综上...

    自动生成oracle数据库表分析语句

    在Oracle数据库管理中,定期进行表分析对于优化查询性能至关重要。通过分析表和索引,Oracle可以更新统计信息,这些信息将被查询优化器用于选择最佳的执行计划。本文将详细介绍如何自动生成表分析SQL语句和索引分析...

    Oracle数据库性能及优化分析.pdf

    本文将详细分析Oracle数据库的性能优化目标和策略,为相关人员提供指导。 1. Oracle数据库优化目标 1.1 压缩数据库响应时间 响应时间是衡量数据库性能的重要指标,它指的是用户请求到获取结果之间的时间间隔。通过...

    基于Oracle数据库电信CRM经营分析系统的性能优化.pdf

    基于Oracle数据库电信CRM经营分析系统的性能优化 Oracle 数据库是当前使用最为广泛的大型数据库管理系统,对电信行业 CR M 应用的性能优化方法至关重要。作为关系型数据库,Oracle 数据库在电信 CRM 经营分析系统...

Global site tag (gtag.js) - Google Analytics