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"，共同学习，共同成长！！！