`
中华国锋
  • 浏览: 44766 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

一条SQL语句的优化学习

 
阅读更多

1.问题描述:

ECSS中有一条BI ETLSQL语句(如下),当S_ETL_I_IMG_26表的数据量达到15W, S_ETL_R_IMG_26表有150W后,这条SQL语句将会执行10多个小时.

DELETE  FROM S_ETL_R_IMG_26

WHERE EXISTS

( SELECT 'X'

FROM S_ETL_I_IMG_26

WHERE S_ETL_R_IMG_26.ROW_ID = S_ETL_I_IMG_26.ROW_ID

)

2.问题分析与处理:

经过DBA优化后,这条SQL语句在数据量达到15W以后,执行所花费的时间是在一分钟以下.

以下是DBA的详细分析和优化过程.我们大家可以好好的学习一下.

==2009-6-23 DBA更新

今天上午观察,该sql已经使用上昨天导入的outline,效率很快。该问题解决了。

==2009-6-22 DBA更新

经过2009-6-19 2100 S_ETL_R_IMG_26 exp/imp,重整以后,S_ETL_R_IMG_26目前这个表大小才56M了,缩小为原来的1/10,数据空洞已经消除了。

但是今天生产库上的该sql的执行计划还是没有变,执行效率也没有提高。

进一步分析,把生产库上的S_ETL_I_IMG_26/S_ETL_R_IMG_26两个表的数据导入开发库,在开发库的执行计划是(如下),使用hash join ,效率很快,大概4分钟就完成delete 50w的记录。

开发库执行计划:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1335637332

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost

--------------------------------------------------------------------------------

| 0 | DELETE STATEMENT | | 475K| 15M| | 475

| 1 | DELETE | S_ETL_R_IMG_26 | | | |

|* 2 | HASH JOIN RIGHT SEMI| | 475K| 15M| 10M| 475

| 3 | INDEX FULL SCAN | S_ETL_I_IMG_26_M2 | 475K| 5576K| |

| 4 | TABLE ACCESS FULL | S_ETL_R_IMG_26 | 1596K| 33M| | 160

--------------------------------------------------------------------------------

生产库执行计划:

SQL> select * from table(dbms_xplan.display_cursor('bs5h9z7kp1qa2', 0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID bs5h9z7kp1qa2, child number 0

-------------------------------------

DELETE FROM S_ETL_R_IMG_26 WHERE EXISTS ( SELECT 'X' FROM

S_ETL_I_IMG_26 WHERE S_ETL_R_IMG_26.ROW_ID = S_ETL_I_IMG_26.ROW_ID )

Plan hash value: 2166185037

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)

--------------------------------------------------------------------------------

| 0 | DELETE STATEMENT | | | | 111 (100)

| 1 | DELETE | S_ETL_R_IMG_26 | | |

| 2 | NESTED LOOPS SEMI | | 475K| 15M| 111 (0)

| 3 | INDEX FULL SCAN | S_ETL_R_IMG_26_M3 | 1596K| 33M| 109 (0)

|* 4 | INDEX FAST FULL SCAN| S_ETL_I_IMG_26_M2 | 141K| 1662K| 0 (0)

--------------------------------------------------------------------------------

于是进一步研究,为何该sql在开发/生产库上的执行计划不一样,发现是生产的参数不同引起。OPTIMIZER_INDEX_COST_ADJ这个参数在生产上为1,开发库为100,意思是在生产库上告诉优化器,使用index的代价为1,而在开发库上告诉优化器,使用index的代价为100,所以优化器在生产库上偏重走index,导致通过index full scannested loop来完成,由于S_ETL_R_IMG_26在生产库上有150万行记录,nestloop需要做150万次以上查询,故执行效率很低。

生产ecss

SQL> show parameter OPTIMIZER_INDEX_COST_ADJ;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj integer 1

开发ecssint

SQL> show parameter OPTIMIZER_INDEX_COST_ADJ

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj integer 100

===2009-6-19 DBA更新

S_ETL_R_IMG_26这个表应该有很多空间浪费, 因为S_ETL_R_IMG_26 637M 150万条记录),S_ETL_I_IMG_26 9M26万条记录),而两个表结构是一致的,这样估算,S_ETL_R_IMG_26这个表实际最多60M空间就可以了,浪费90%的空间,也有很多数据空洞。

最好作一次expimp,这样可以重建index也可以消除数据空洞。

SQL> select bytes/1024/1024 from dba_segments where segment_name='S_ETL_R_IMG_26';

BYTES/1024/1024

---------------

637

SQL> select bytes/1024/1024 from dba_segments where segment_name='S_ETL_I_IMG_26';

BYTES/1024/1024

---------------

9

SQL> select count(*) from siebel.S_ETL_R_IMG_26;

COUNT(*)

----------

1584586

SQL> select count(*) from siebel.S_ETL_I_IMG_26;

COUNT(*)

----------

266396

SQL>

SQL> desc siebel.S_ETL_I_IMG_26

Name Type Nullable Default Comments

---------------- ----------------- -------- ------- --------

ROW_ID VARCHAR2(15 CHAR)

LAST_UPD DATE sysdate

MODIFICATION_NUM NUMBER(10)

OPERATION VARCHAR2(1 CHAR)

SQL> desc siebel.S_ETL_R_IMG_26

Name Type Nullable Default Comments

---------------- ----------------- -------- ------- --------

ROW_ID VARCHAR2(15 CHAR)

LAST_UPD DATE sysdate

MODIFICATION_NUM NUMBER(10)

SQL>

对于这个参数OPTIMIZER_INDEX_COST_ADJgoogle查了一个.

OPTIMIZER_INDEX_COST_ADJ

这个初始化参数代表一个百分比,取值范围在110000之间.该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。

这些参数对于CBO的执行具有重大影响,其缺省值对于数据库来说通常需要调整。一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右。

对于大多数OLTP系统,OPTIMIZER_INDEX_COST_ADJ可以设置在1050之间。对于数据仓库和DSS系统,可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50

通常我们需要反复调整取得一个合理值。更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算.

这个参数当时是Oracle 的优化工程师过来调整为1的.调整1表示使用索引的Cost是全表扫描的Cost 1%才使用索引.

在生产环境上调整这个参数得再认真观察和评审.

这条SQL语句的优化已经不是我们增加索引所能解决的了,跟数据库的参数有非常大的关系.

分享到:
评论

相关推荐

    非常好用的SQL语句优化34条+sql语句基础

    以下是对"非常好用的SQL语句优化34条+sql语句基础"这一主题的详细解析: 1. **索引优化**:索引是提高查询速度的关键。创建合适的索引(主键、唯一索引、全文索引等)能显著提升数据检索效率。但同时要注意,过多的...

    SQL语句优化手册

    为了确保数据库系统能够高效地运行,SQL语句优化成为了提升系统性能的关键因素之一。SQL(Structured Query Language)是用于管理关系数据库的标准语言,其性能直接影响着数据库系统的整体响应时间和资源利用率。...

    一个完整程序只有一条SQL语句

    标题提到的"一个完整程序只有一条SQL语句",这展示了如何高效地利用SQL语句来完成数据操作。在这个程序中,开发者通过一条复杂的SQL语句,实现了对dataGrid的数据进行增、删、改、查(CRUD)操作。在C#环境下,...

    oracle性能优化之SQL语句优化

    SQL语句优化是性能优化的关键环节,因为它直接影响到数据的检索速度和资源的消耗。本文将深入探讨SQL语句优化,包括优化器的工作原理、优化工具、数据访问方法以及如何收集统计信息来辅助优化。 首先,Oracle的优化...

    sql语句优化心得

    这条SQL语句旨在从`hm_member_ext`表中获取指定成员(`memberId=951`)的最新扩展信息。根据日志记录,可以看到: - `Query_time`: 查询总耗时为8.416321秒。 - `Rows_examined`: 共检查了18017行数据。 这表明...

    Delphi adoquery处理多条SQL语句

    示例代码展示了如何构建一个包含多条 SQL 语句的字符串: ```delphi var sqlStr: String; begin sqlStr := 'BEGIN'; sqlStr := sqlStr + 'UPDATE Table1 SET Col1=''test'' WHERE 1=2;'; sqlStr := sqlStr + '...

    SQL语句优化,语法优化

    SQL语句优化是数据库性能提升的关键环节,尤其是在大数据量的环境下。优化SQL语句能够显著提高查询速度,减少服务器负载,提升系统整体效率。以下是一些关于SQL语句优化的重要知识点: 1. **选择最有效的表名顺序**...

    最新整理的常用sql语句及优化大全

    本资源“最新整理的常用sql语句及优化大全”涵盖了SQL的基础使用和性能优化,对于数据库管理员、开发人员或是学习者来说,都是极具价值的学习资料。 一、SQL常用语句 1. **数据查询**:`SELECT`语句是SQL中最基本...

    索引的SQL语句优化

    **降龙十八掌**,这里被比喻成18条关键的优化策略,每一条都是针对特定情境下SQL语句优化的实战技巧。它们基于一个核心原则:**建立必要的索引**。这不仅是策略的基石,也是实现高效查询的前提条件。正确的索引能够...

    一般SQL语句优化整理

    ### 一般SQL语句优化整理 #### 概述 在数据库操作中,SQL查询语句是数据检索的核心,其性能直接影响到应用系统的响应时间和资源消耗。对于一般的SQL语句优化,不仅能够提升查询效率,还能改善用户体验。本文将对...

    通过分析SQL语句的执行计划优化SQL.doc

    当一条SQL语句首次执行并解析后,其执行计划会被存储在系统全局区域SGA的共享池中,供后续相同的SQL语句复用,避免重复解析带来的资源消耗。共享SQL语句的关键在于SQL文本的严格匹配,包括空格、换行等细节。通过...

    一条sql语句关联八个表

    标题中的"一条sql语句关联八个表"涉及到的就是这种复杂的联接查询。这种查询通常用于从分布在不同表中的相关数据中提取所需信息。下面我们将深入探讨这个主题。 首先,我们了解SQL的基本联接类型: 1. **INNER ...

    SQL语句优化方法分析与探讨.pdf

    例如,建议将所有的SQL语句关键字写在一行,每条SQL语句的开头关键字保持对齐,使用绑定变量等。遵循这些标准有助于数据库查询优化器更有效地重用已有的执行计划,减少系统资源的消耗。 最后,文章提到在SQL语句...

    SQL语句优化的34条建议

    SQL语句优化是数据库性能提升的关键,以下是对标题和描述中提到的知识点的详细说明: 1. **选择最有效的表名顺序**: 在Oracle中,解析器从右到左处理FROM子句中的表,因此应将记录条数最少的表放在最后,作为基础...

    2个SQL优化语句,查询出来哪条SQL语句占据资源最多.rar

    1. **性能监控**:了解SQL语句的执行情况是优化的第一步。数据库通常提供了丰富的性能监控数据,如`V$SQL`(Oracle)和`Performance Schema`(MySQL)。 2. **资源消耗指标**:CPU时间、执行次数、总执行时间等都是...

    oracle sql语句优化

    Oracle SQL语句优化是数据库管理中的重要环节,它涉及到如何高效地执行SQL查询,从而提高数据库性能和响应速度。在Oracle数据库系统中,SQL优化主要包括以下几个方面: 1. **优化器(Optimizer)**:优化器是Oracle...

    浅析数据库系统中SQL语句的优化技术.pdf

    数据库系统中SQL语句优化技术是提高数据库性能的关键手段。优化SQL语句能直接减少对数据库资源的消耗,提升查询效率,降低系统负载。SQL语句的优化通常包含以下几个方面的知识点: 1. SQL语句优化的重要性: SQL...

    SQL语句优化在学生考勤管理系统中的应用与研究.pdf

    本文以学生考勤管理系统为背景,探讨了SQL语句优化技术的两种主要途径——传统优化技术与人工智能自动优化技术,并对基于人工智能的自动SQL语句优化技术进行了详细说明。 首先,传统SQL语句优化技术依赖于数据库...

    SQL语句优化总结

    SQL语句优化是一项重要的数据库管理技能,能够显著提高数据库查询性能,减少资源消耗,缩短响应时间。以下是对上述文件内容中提及的关键知识点的详细总结: 1. 选择最有效的表名顺序 在使用基于规则的优化器时,...

    oracle一条sql语句分页

    需要注意的是,ROWNUM的计算是在SQL语句执行的过程中完成的,并且一旦为某一行分配了ROWNUM值后,这个值就不会再改变。这意味着,即使对表进行排序操作,ROWNUM的值也不会受到影响。 #### 2. 使用 ROWNUM 实现简单...

Global site tag (gtag.js) - Google Analytics