ITPub博客

首页 > 数据库 > Oracle > Oracle性能优化-SQL优化(案例三)

Oracle性能优化-SQL优化(案例三)

原创 Oracle 作者:chenoracle 时间:2020-02-18 10:55:26 0 删除 编辑

Oracle 性能优化 -SQL 优化 ( 案例三 )

 

问题:

客户反馈取消记账耗时太长,10 分钟也结束不了。

问题分析:

问题重现后,远程查看主要慢在一条查询SQL

查看执行计划gl_voucher 大表全表扫描,实际上 SQL 中谓词字段上有特别高效的索引,怀疑统计信息不准确。

查看gl_voucher 统计信息记录行数 0 行,显然 oracle 认为扫描一个 0 行的表不需要走索引。

SQL> Select * from user_tables where table_name in ( GL_VOUCHER , GL_TMP_TABLE );

解决方案:

手动重新收集gl_voucher 表统计信息

Analyze table gl_voucher compute statistics;

SQL> Select * from user_tables where table_name in ( GL_VOUCHER , GL_TMP_TABLE );

再次执行速度有很大提升。

注意:

统计信息不准确经常会导致性能问题,如果数据库自带收集统计信息的任务已经停了,可以考虑使用如下存储过程定期收集统计信息,此存储过程并不适用于所有场景,根据实际情况进行调整。

--- 创建收集统计信息的存储过程:

---需要显式地赋予用户建表权限

grant   create   any   table   to  chen ;

---创建收集统计信息的存储过程

CREATE   OR   REPLACE   PROCEDURE  ANALYZE_TB AS

  OWNER_NAME   VARCHAR2 ( 100 );

  V_LOG        INTEGER ;

  V_SQL1       VARCHAR2 ( 800 );

  V_TABLENAME VARCHAR2 ( 50 );

   CURSOR  CUR_LOG IS

     SELECT   COUNT (*)   FROM  USER_TABLES WHERE  TABLE_NAME =   'ANALYZE_LOG' ;

   --1

BEGIN

   --DBMS_OUTPUT.ENABLE (buffer_size=>100000);

   --1.1

   BEGIN

     OPEN  CUR_LOG ;

     FETCH  CUR_LOG

       INTO  V_LOG ;

     IF  V_LOG =   0   THEN

       EXECUTE   IMMEDIATE   'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))' ;

     END   IF ;

   END ;

 

   SELECT   USER   INTO  OWNER_NAME FROM  DUAL ;

  V_SQL1 :=   'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES ('''   ||

            OWNER_NAME ||   ''',''ANALYZE BEGIN'',''ALL'')' ;

   EXECUTE   IMMEDIATE  V_SQL1 ;

  sys.dbms_stats.gather_schema_stats ( ownname           =>   UPPER ( OWNER_NAME ),

                                     estimate_percent =>   100 ,

                                     method_opt        =>   'FOR ALL INDEXED COLUMNS' ,

                                      cascade            =>   TRUE );

  V_SQL1 :=   'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES ('''   ||

            OWNER_NAME ||   ''',''ANALYZE END'',''ALL'')' ;

   EXECUTE   IMMEDIATE  V_SQL1 ;

   commit ;

 

   --1.2 delete tmptb statitics and lock statistics

   BEGIN

     for  x in   ( select  a.table_name ,  a.last_analyzed ,  b.stattype_locked

                 from  user_tables a ,  user_tab_statistics b

                where  a.temporary =   'Y'

                  and  a.table_name =  b.table_name

                  and   ( b.STATTYPE_LOCKED is   null   or

                     a.last_analyzed is   not   null ))   LOOP

       IF  x.last_analyzed IS   NOT   NULL   THEN

         --delete stats

        dbms_stats.delete_table_stats ( ownname =>   user ,

                                      tabname =>  x.table_name ,

                                       force     =>   TRUE );

       END   IF ;

    

       IF  x.stattype_locked IS   NULL   THEN

         --lock stats

        dbms_stats.lock_table_stats ( ownname =>   user ,

                                    tabname =>  x.table_name );

       END   IF ;

     END   LOOP ;

   end ;

EXCEPTION

   WHEN   OTHERS   THEN

     IF  CUR_LOG % ISOPEN   THEN

    

       CLOSE  CUR_LOG ;

     END   IF ;

     commit ;

end ;

 

--- 创建 job, 当天的凌晨2点开始更新统计信息,以后每2天的凌晨2点更新统计信息。 根据实际情况调整时间。

SQL >   VARIABLE  JOBNO NUMBER ;

SQL >   VARIABLE  INSTNO NUMBER ;

SQL >  

SQL >   BEGIN

   2      SELECT  INSTANCE_NUMBER INTO   : INSTNO FROM  V$INSTANCE ;

   3     DBMS_JOB.SUBMIT (: JOBNO ,

   4                      'ANALYZE_TB; ' ,

   5                      TRUNC ( SYSDATE )   +   1   +   2   /   24 ,

   6                      'TRUNC(SYSDATE)+2+2/24' ,

   7                      TRUE ,

   8                      : INSTNO );

   9      COMMIT ;

  10    END ;

  11    /

 

PL / SQL   procedure  successfully completed

欢迎关注我的微信公众号"IT小Chen",共同学习,共同成长!!!


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2676012/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle 11g OCP、Oracle 11g OCM、OCMU 用户组成员,微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    442
  • 访问量
    892752