精华内容
下载资源
问答
  • 2017-06-10 00:24:37

    1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databse.dbo.table_name”,因为sp_depends不能显示出该SP所使用的跨库table或view,不方便校验。 

    补充:

    这个存储过程专门用来查看一个数据库对象引用了哪些其它的数据库对象

    比如要看一个存储过程用到了哪些表及字段,就可以写:

    exec sp_depends sp_procedure

    如果是表名要加引号

    exec sp_depends 'MyTable'

    同样可以检查触发器

    这个存储过程对于检查一个存储过程是很方便的,我们使用它就可以看到存储过程使用了哪些表及字段,而不用写存储过程的脚本,去一行一行的找 


     
        2、开发人员在提交SP前,必须已经使用set showplan on分析过查询计划,做过自身的查询优化检查。

        3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点:

        a) SQL的使用规范:

        i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。

        ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。

        iii. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。

        iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。

        v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

        vi. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。

        vii. 尽量使用“>=”,不要使用“>”。 viii. 注意一些or子句和union子句之间的替换

        ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。

        x. 注意存储过程中参数和数据类型的关系。

        xi. 注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级成表级锁。

        b) 索引的使用规范:

        i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。

        ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引

        iii. 避免对大表查询时进行table scan,必要时考虑新建索引。

        iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。

        v. 要注意索引的维护,周期性重建索引,重新编译存储过程。

        c) tempdb的使用规范:

        i. 尽量避免使用distinct、order by、group by、having、join、***pute,因为这些语句会加重tempdb的负担。

        ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。

        iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。

        iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。

        v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。

        vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。

    更多相关内容
  • oracle 存储过程优化

    2018-07-13 23:33:59
    作为一名数据库开发工程师必须掌握的技能,因为工作中会遇到很多需要优化过程
  • Oracle 存储过程优化(一)

    千次阅读 2020-05-16 19:06:14
    Oracle 存储过程优化(一) 当前遇到的问题: 1、一个存储过程要跑一个多小时。 2、拎出来单独跑(只select不insert)的时候速度不慢,使用PLSQL可以很快查看到结果。 针对以上问题,在多次测试发现,使用PL/SQL工具...

    Oracle 存储过程优化(一)
    当前遇到的问题:
    1、一个存储过程要跑一个多小时。
    2、拎出来单独跑(只select不insert)的时候速度不慢,使用PLSQL可以很快查看到结果。

    针对以上问题,在多次测试发现,使用PL/SQL工具select出来的结果很快就会出来,但是当获取最后页的时候却很慢。如图一所示。
    获取最后页
    点击 获取"最后一页"时,速度超级慢。因此考虑是在显示的时候又有计算的地方。
    通过查看代码知,在select的时候有一段sql。
    如下:

       select cust_payright_typ,
           (select t1.zip_code
              from (select l.*,
                           row_number() over(partition by l.party_id, l.addr_type order by l.last_updated_ts desc) as rn 
                      from GGGG l
                     where l.updated_ts = to_timestamp('99991231', 'yyyy-mm-dd')) t2
              left join A t1
                on t1.addr_id = t2.addr_id
             where t2.party_id = a.party_id
               and t2.addr_type = 'DDDDDD'
               and t2.rn = 1) AS region_cd,
           ' ' as borrower_register_addr,
      from dual
    可以看到,在select的时候,多了一段临时查看的sql,该段sql导致查询出的数据进一步计算,导致速度迅速下降。
    修改此处为with as的形式(临时表也可),重新执行存储过程,发现速度在20秒以内。
    
    
    展开全文
  • 前言 在数据库的开发过程中,经常会遇到复杂的业务逻辑和对...下面介绍某一个MySQL存储过程优化的整个过程。 在本文中,需要被优化的存储过程如下: drop procedure if exists pr_dealtestnum; delimiter // create
  • 存储过程优化基本方法

    千次阅读 2019-05-17 14:21:00
    因为以_sp开头的存储过程默认为系统存储过程,所以首先会去master库中去找,然后再在当前数据库中找。 5,使用sp_executesql替代exec. sp_executesql可以使用参数化,从而可以重用执行计划。而exec就是纯拼sql语句...

     

    网络 已经 有了,就拿来保存下。

    1,尽量利用一些sql语句来替代一些小循环,例如聚合函数,求平均函数等。
    
    2,不再按照算法描述,以致将一条长达100多个字段的纪录分90次来更新,而是采用拼凑语句,将更新语句在循环中拼凑后,再统一更新。
    
    3,使用确定的schema, 在使用表,函数,存储过程等等时,最好加上确定的schema,这样可以使sqlserver直接找到对应目标,避免去计划缓存中搜索,而且搜
    
    索会导致编译锁定,最终影响性能。如select * from dbo.a比select * from a要好。
    
    4,自定义存储过程不要以_sp开头。因为以_sp开头的存储过程默认为系统存储过程,所以首先会去master库中去找,然后再在当前数据库中找。
    
    5,使用sp_executesql替代exec. sp_executesql可以使用参数化,从而可以重用执行计划。而exec就是纯拼sql语句。
    
    6,中间结果存放于临时表,加索引。
    
    7,少使用游标。sql是个集合语言,对于集合运算具有较高性能。而cursors是过程运算。比如对一个100万行的数据进行查询。游标需要读表100万次,而不使用游标则只需要少量几次读取。
    
    8,事务越短越好。sqlserver支持并发操作。如果事务过多过长,或者隔离级别过高,都会造成并发操作的阻塞,死锁。导致查询极慢,cpu占用率极地。
    
    9,使用try-catch处理错误异常。
    
    10,查找语句尽量不要放在循环内。
    
    -----------------
    
    sql的使用规范:
    
    1,尽量避免大事务操作,慎用holdlock字句,提高系统并发能力。
    
    2,尽量避免反复访问同一张或几张表,尤其事数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。
    
    3,尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。
    
    4,注意where字句写法,必须考虑语句顺序。应该根据索引顺序,范围大小来确定条件字句的先后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。
    
    5,不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统可能无法正确使用索引。
    
    6,尽量使用exists代替select count(1)来判断是否存在纪录。count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。
    
    7,尽量使用“>=”,不要使用“>”。
    
    8,注意一些or子句和union子句之间的替换。
    
    9,注意表之间连接的数据类型,避免不同类型数据之间的连接。
    
    10,注意存储过程中参数和数据类型的关系。
    
    11,注意insert、update操作的数据量,防止与其它应用冲突,如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级为表级
    
    锁。
    
    ------------
    
    索引的使用规范:
    
    1,索引的创建要与应用结合考虑,建议大的oltp表不要超过6个索引。
    
    2,尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引。
    
    3,避免对大表查询时进行table scan,必要时考虑新建索引。
    
    4,在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。
    
    5,要注意索引的维护,周期性重建索引,重新编译存储过程。

     

    https://www.cnblogs.com/hegx/p/6073597.html

    这个链接的方法比较好,里面的三种方法都曾用过,,,,

    https://blog.csdn.net/slowlifes/article/details/53817020

    转载于:https://my.oschina.net/qingqingdego/blog/3050816

    展开全文
  • SQL存储过程优化参考

    2010-02-21 14:57:55
    收藏的存储过程优化参考,包括: 存储过程编写经验和优化措施.doc 优化SQL语句和存储过程.pdf 希望对大家有点参考价值
  • Sybase数据库的存储过程性能优化.pdf
  • ORACLE-SQL存储过程优化

    千次阅读 2016-09-02 11:53:02
    在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT EMP_NO,EMP_NAM,DAT_IN ...

    在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法:

    1.
    表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
    例如:
    SELECT EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
    2.
    表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
    例如:
    SELECT EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

    3.
    表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
    表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
    例如:
    SELECT EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

    4.
    表明对语句块选择基于规则的优化方法.
    例如:
    SELECT EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

    5.
    表明对表选择全局扫描的方法.
    例如:
    SELECT EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

    6.
    提示明确表明对指定表根据ROWID进行访问.
    例如:
    SELECT * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
    AND EMP_NO='SCOTT';

    7.
    提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
    例如:
    SELECT BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
    WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

    8.
    表明对表选择索引的扫描方法.
    例如:
    SELECT FROM BSEMPMS WHERE SEX='M';

    9.
    表明对表选择索引升序的扫描方法.
    例如:
    SELECT FROM BSEMPMS WHERE DPT_NO='SCOTT';

    10.
    为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
    例如:
    SELECT * FROM BSEMPMS
    WHERE SAL<5000000 AND HIREDATE<SYSDATE;

    11.
    提示明确命令优化器使用索引作为访问路径.
    例如:
    SELECT SAL,HIREDATE
    FROM BSEMPMS WHERE SAL<60000;

    12.
    表明对表选择索引降序的扫描方法.
    例如:
    SELECT FROM BSEMPMS WHERE DPT_NO='SCOTT';

    13.
    对指定的表执行快速全索引扫描,而不是全表扫描的办法.
    例如:
    SELECT * FROM BSEMPMS WHERE DPT_NO='TEC305';

    14.
    提示明确进行执行规划的选择,将几个单列索引的扫描合起来.
    例如:
    SELECT * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';

    15.
    对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.
    例如:
    SELECT * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

    16.
    对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.
    例如:
    SELECT * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

    17.
    禁止对查询块的查询重写操作.

    18.
    可以将视图作为参数.

    19.
    能够对视图的各个查询进行相应的合并.
    例如:
    SELECT A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A ,(SELET DPT_NO
    ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
    AND A.SAL>V.AVG_SAL;

    20.
    对于有可合并的视图不再合并.
    例如:
    SELECT A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

    21.
    根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.
    例如:
    SELECT A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

    22.
    将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
    例如:
    SELECT BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

    23.
    将指定的表与其他行源通过合并排序连接方式连接起来.
    例如:
    SELECT * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

    24.
    将指定的表与其他行源通过哈希连接方式连接起来.
    例如:
    SELECT * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

    25.
    强制与ORACLE所选择的位置不同的表进行查询执行.
    例如:
    SELECT * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

    26.
    将指定的表作为连接次序中的首表.

    27.
    当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
    例如:
    SELECT EMP_NAM FROM BSEMPMS;

    28.
    当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
    例如:
    SELECT EMP_NAM FROM BSEMPMS;

    29.
    直接插入到表的最后,可以提高速度.
    insert into test1 select * from test4 ;

    30.
    通过在插入语句生存期内停止并行模式来启动常规插入.
    insert into test1 select * from test4 ;

     


    ORACLE SQL语句优化总结2009/02/28 00:27
       (1) 选择最有效率的表名顺序(只在基于规则的优化器中有效):
    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
       (2) WHERE子句中的连接顺序.:
    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
       (3) SELECT子句中避免使用 ‘ * ‘:
    ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
       (4) 减少访问数据库的次数:
    ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等;
       (5) 在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200
       (6) 使用DECODE函数来减少处理时间:
    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
       (7) 整合简单,无关联的数据库访问:
    如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
       (8) 删除重复记录:
    最高效的删除重复记录方法 ( 因为使用了ROWID)例子:
    DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
    FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
       (9) 用TRUNCATE替代DELETE:
    当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
       (10) 尽量多使用COMMIT:
    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
    COMMIT所释放的资源:
    a. 回滚段上用于恢复数据的信息.
    b. 被程序语句获得的锁
    c. redo log buffer 中的空间
    d. ORACLE为管理上述3种资源中的内部花费


       (11) 用Where子句替换HAVING子句:
    避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里
       (12) 减少对表的查询:
    在含有子查询的SQL语句中,要特别注意减少对表的查询.例子:
    SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
    TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
       (13) 通过内部函数提高SQL效率.:
    复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
       (14) 使用表的别名(Alias):
    当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
       (15) 用EXISTS替代IN、用NOT EXISTS替代NOT IN:
    在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
    例子:
    (高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
    (低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')
       (16) 识别'低效执行'的SQL语句:
    虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法:
    SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
    SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;
       (17) 用索引提高效率:
    索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.:
    ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
       (18) 用EXISTS替换DISTINCT:
    当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子:
    (低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
    WHERE D.DEPT_NO = E.DEPT_NO
    (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
       (19) sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行
       (20) 在java代码中尽量少用连接符“+”连接字符串!

    (21) 避免在索引列上使用NOT 通常, 
    我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

       (22) 避免在索引列上使用计算.
    WHERE子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描. 举例: 低效:


    SELECT … FROM DEPT WHERE SAL *
    12
    >
    25000; 高效:SELECT … FROM DEPT WHERE SAL >
    25000/12;


       (23) 用>=替代>
    高效:
    SELECT * FROM EMP WHERE DEPTNO >=4
    低效: SELECT * FROM EMP WHERE DEPTNO >3 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

       (24) 用UNION替换OR (适用于索引列)
    通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

       (25) 用IN来替换OR
    这是一条简单易记的规则,但是实际的执行效果还须检验,在ORACLE8i下,两者的执行路径似乎是相同的. 
    低效: SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 高效 SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

       (26) 避免在索引列上使用IS NULL和IS NOT NULL
    避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入). 然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
    低效: (索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效: (索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

       (27) 总是使用索引的第一个列:
    如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引

       (28) 用UNION-ALL 替换UNION ( 如果有可能的话):
    当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量
    低效:

    SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS
    WHERE TRAN_DATE =
    '31-DEC-95'
    UNION
    SELECT ACCT_NUM, BALANCE_AMT
    FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE =
    '31-DEC-95'
    高效: SELECT ACCT_NUM, BALANCE_AMT
    FROM DEBIT_TRANSACTIONSWHERE TRAN_DATE =
    '31-DEC-95'
    UNION
    ALL
    SELECT ACCT_NUM, BALANCE_AMT
    FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE =
    '31-DEC-95'


       (29) 用WHERE替代ORDER BY:
    ORDER BY 子句只在两种严格的条件下使用索引.
    ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
    ORDER BY中所有的列必须定义为非空.
    WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.
    例如: 表DEPT包含以下列:
    DEPT_CODE PK NOT NULL
    DEPT_DESC NOT NULL
    DEPT_TYPE NULL
    低效: (索引不被使用) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE 高效: (使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

       (30) 避免改变索引列的类型.:
    当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
    假设 EMPNO是一个数值类型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123' 实际上,经过ORACLE类型转换, 语句转化为: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123') 幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变. 现在,假设EMP_TYPE是一个字符类型的索引列. SELECT … FROM EMP WHERE EMP_TYPE = 123 这个语句被ORACLE转换为: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型

    (31) 需要当心的WHERE子句:
    某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子. 在下面的例子里, (1)‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘||'是字符连接函数. 就象其他函数那样, 停用了索引. (3) ‘+'是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.
       (32) a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.
    b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
       (33) 避免使用耗费资源的操作:
    带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
       (34) 优化GROUP BY:
    提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
    低效:SELECT JOB , AVG(SAL)FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'
    高效:SELECT JOB , AVG(SAL)FROM EMP WHERE JOB = ‘PRESIDENT'OR JOB = ‘MANAGER'
    GROUP JOB 
     

      

      一、操作符优化  

     1、IN 操作符   

      用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的,从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:  

      ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。  

      推荐方案:在业务密集的SQL当中尽量不采用IN操作符,用EXISTS 方案代替。  

      2、NOT IN操作符   

      此操作是强列不推荐使用的,因为它不能应用表的索引。   

      推荐方案:用NOT EXISTS 方案代替   

      3、IS NULL 或IS NOT NULL操作(判断字段是否为空)   

      判断字段是否为空一般是不会应用索引的,因为索引是不索引空值的。  

      推荐方案:用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。不允许字段为空,而用一个缺省值代替空值,如申请中状态字段不允许为空,缺省为申请。   

      4、> 及 < 操作符(大于或小于操作符)   

      大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。   

      5、LIKE操作符   

      LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。   

      一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。   

      6、UNION操作符   

      UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:

      select * from gc_dfys

      union

      select * from ls_jg_dfys

      这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。   

      推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。  

      select * from gc_dfys

      union all

      select * from ls_jg_dfys  

      二、SQL书写的影响  

      1、同一功能同一性能不同写法SQL的影响。  

      如一个SQL在A程序员写的为 Select * from zl_yhjbqk  

      B程序员写的为 Select * from dlyx.zl_yhjbqk(带表所有者的前缀)   

      C程序员写的为 Select * from DLYX.ZLYHJBQK(大写表名)   

      D程序员写的为 Select * from DLYX.ZLYHJBQK(中间多了空格)   

      以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同,则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。   

      2、WHERE后面的条件顺序影响   

      WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如:

      Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1

      Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'

      以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。   

      3、查询表顺序的影响   

      在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下,ORACLE会按表出现的顺序进行链接,由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。(注:如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接)   

      三、SQL语句索引的利用   

      1、操作符优化(同上)  
     
      2、对条件字段的一些优化   

      采用函数处理的字段不能利用索引,如:   

      substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’   

      trunc(sk_rq)=trunc(sysdate), 优化处理:sk_rq>=trunc(sysdate) and sk_rq
      进行了显式或隐式的运算的字段不能进行索引,如:ss_df+20>50,优化处理:ss_df>30   

      ‘X’ || hbs_bh>’X5400021452’,优化处理:hbs_bh>’5400021542’  

      sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5   

      hbs_bh=5401002554,优化处理:hbs_bh=’ 5401002554’,注:此条件对hbs_bh 进行隐式的to_number转换,因为hbs_bh字段是字符型。   

      条件内包括了多个本表的字段运算时不能进行索引,如:ys_df>cx_df,无法进行优化

      qc_bh|| kh_bh=’5400250000’,优化处理:qc_bh=’5400’ and kh_bh=’250000’  

      四、其他  

      ORACLE的提示功能是比较强的功能,也是比较复杂的应用,并且提示只是给ORACLE执行的一个建议,有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用,一般不建议开发人员应用ORACLE提示,因为各个数据库及服务器性能情况不一样,很可能一个地方性能提升了,但另一个地方却下降了,ORACLE在SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。

     

    NESTED LOOP

        对于被连接的数据子集较小的情况,nested loop连接是个较好的选择。nested loop就是扫描一个表,每读到一条记录,就根据索引去另一个表里面查找,没有索引一般就不会是 nested loops。
    一般在nested loop中, 驱动表满足条件结果集不大,被驱动表的连接字段要有索引,这样就走nstedloop。如果驱动表返回记录太多,就不适合nested loops了。如果连接字段没有索引,则适合走hash join,因为不需要索引。
    可用ordered提示来改变CBO默认的驱动表,可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

    HASH JOIN

       hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表(或数据源),利用连接键(也就是根据连接字段计算hash 值)在内存中建立hash表,然后扫描大表,每读到一条记录就来探测hash表一次,找出与hash表匹配的行。
    当小表可以全部放入内存中,其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。
        至于两个表都进行分区,其好处是可以使用parallel query,就是多个进程同时对不同的分区进行join,然后再合并。但是复杂。
    使用hash join时,HASH_AREA_SIZE初始化参数必须足够的大,如果是9i,Oracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY 为AUTO,然后调整PGA_AGGREGATE_TARGET即可。
    以下条件下hash join可能有优势:
    两个巨大的表之间的连接。
    在一个巨大的表和一个小表之间的连接。
    可用ordered提示来改变CBO默认的驱动表,可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。

    SORT MERGE JOIN

        sort merge join的操作通常分三步:对连接的每个表做table access full;对table access full的结果进行排序;进行merge join对排序结果进行合并。sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下,9i开始已经很少出现了,因为其排序成本高,大多为hash join替代了。
    通常情况下hash join的效果都比sort merge join要好,然而如果行源已经被排过序,在执行sort merge join时不需要再排序了,这时sort merge join的性能会优于hash join。
    在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下,sort merge join会比nested loops性能更佳。
    可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。

    展开全文
  • mysql存储过程优化

    2013-05-19 00:11:29
    mysql存储过程优化:使用临时表代替游标 ;巧建MySQL sum索引以提升效率
  • 存储过程性能优化技巧及性能优化实例,比较全面的讲解了存储过程
  • 本篇文章是对Oracle存储过程的编写经验与优化措施进行了详细的分析介绍,需要的朋友参考下
  • 代码如下: –代码一DECLARE @cc INT SELECT NewsId,ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex INTO #tb FROM news WITH(NOLOCK) WHERE NewsTypeId=@NewsTypeId AND IsShow=1 SET @cc = @@ROWCOUNT ...
  • Oracle存储过程优化

    千次阅读 2018-11-16 14:20:12
    Oracle存储过程优化措施 1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“user_name.table_name”。 2、开发人员在提交前,必须已经分析过查询计划,做过...
  • 一、适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。  二、介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个...
  • 一、适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。  二、介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个...
  • 一般秒杀过程如下: 如果有条件详情页数据可以放在CDN上面进行分发请求 传说一秒等于10亿ns,因此系统时间可以不用获取 以上秒杀优化是现在大多公司使用的逻辑框架,但相应的运维维护成本,团队...
  • Oracle存储过程优化思路以及思路下优化效果比较
  • 优化器基于一些条件来评估成本,包括CPU并行性和速度、I/O存储特征和通信带宽等外部条件,以及DB2注册表变量、DB2优化级别、统计信息等内部条件。而且,在系统运行时期间,这些条件中许多都在不断变化,所以选择...
  • sql2000分页存储过程优化 列出3个sql2000分页方法 对比执行时间
  • 声明变量 设置全局变量 set @a=’一个新变量’; 在函数和储存过程中使用的变量declear declear a int unsigned default 1;...这种变量需要设置变量类型 ...储存过程没有返回值,而且不能sql语句调用,只能是call调用,
  • 测试数据 测试语句 update user set age = 3 where ...添加存储过程,在mysql控制台 delimiter $$ create procedure add_test(in age1 int,in name1 char(12)) begin update user set age = age1 where name = na...
  • 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储...
  • 最近优化存储过程,发现一个存储过程要跑3个多小时,打开一看,代码却非常简单,如下: INSERT INTO table1 a ( a.idcard, a.nbr, a.flag, a.srcdate, a.datadate ) SELECT b.idcard, b.nbr, b.flag, b.srcdate, vi_...
  • 数据库存储过程优化优化存储过程有很多种方法,下面介绍最常用的7种
  • 本文旨在以一种在存储过程中循环调用子存储过程的方式来替换IN的查询方式来提高Mysql的效率。而不是用UNION等方式构造很长的单一SQL语句去执行。
  • ORACLE存储过程性能优化技巧,帮助你更好的学习oracle的方方面面的技术!

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 711,132
精华内容 284,452
关键字:

存储过程优化

友情链接: communicate.rar