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

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数据库性能分析

    为了有效地进行Oracle数据库性能分析,识别导致性能问题的SQL语句是非常关键的。以下是一些常用的工具和技术: - **AWR(Automatic Workload Repository)**: AWR提供了关于系统活动的重要信息,可以帮助识别高CPU或I...

    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数据库性能

    本文将深入探讨“用智能优化限制提高Oracle数据库性能”的核心知识点,通过解析智能优化、Oracle数据库性能提升策略,以及具体的SQL执行计划调整技巧,为读者提供一份全面的指南。 ### 智能优化:提升Oracle数据库...

    实现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

    本文从数据库系统级和SQL语句级两个方面进行了深入的分析,旨在帮助数据库管理员和性能分析人员获得真实可靠的系统响应时间和会话活动的响应时间。 一、数据库系统级响应时间的分析 在网络数据库中,为了提高性能...

Global site tag (gtag.js) - Google Analytics