Oracle效能優化-SQL優化(案例三)

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/，如需轉載，請註明出處，否則將追究法律責任。