Oracle效能最佳化有時就這麼簡單_index

Oracle效能最佳化有時就這麼簡單_index

效能最佳化有時就這麼簡單

一、概述

最近有一個系統（aix6.1+Oracle10.2.0.5 RAC）CPU每五分鐘就達到百分之百，這個系統是監控系統，針對所有運行系統每五分鐘取數，所以之前也沒太在意， 其實在年前就有過這種情況，只是把執行頻繁的語句發送給項目組，有針對系統運行環境調整了一下SGA等大小，只是不警示了（cpu使用超過百分之九十就警示），也就沒管。可這幾天有出現該問題，還是繼續將執行頻繁的sql發送給項目組，由於所有受監控的系統都會每五分鐘向該系統發數，也就理所當然的認為系統就這個特點，也就沒理會。可是，這兩天，警示簡訊也每五分鐘就發送一次，隨之的恢複簡訊，自己看著不煩，領導看著也煩啊，那就看看吧（雖然對sql語句不感冒吧）

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

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

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

二、 處理過程

通過提取一段時間內的AWR，發現下面這條語句執行次數最頻繁，當然也是消耗cpu資源最多的。

SELECT V1400, H, L, A, to_char(HTIME, 'yyyy-mm-dd hh24:mi:ss'), to_char(LTIME, 'yyyy-mm-dd hh24:mi:ss') FROM XXXDBA.ORACLETABLEAVAILABLE WHERE RESOURCEID = :1 AND TIME = to_date(:2, 'yyyy-mm-dd')

第一眼看去有綁定變數，而且截取幾個時間段的AWR發現每次執行的語句列名也不完全相同，就想，讓項目組的人去看吧。閑來無事，就查看了一下該表的資訊

SQL> desc XXXDBA.ORACLETABLEAVAILABLE

 Name            Null?    Type

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

 RESOURCEID    NOT NULL VARCHAR2(128)

 TIME          NOT NULL DATE

 V0000                  NUMBER

 V0005                  NUMBER

 V0010                  NUMBER

 V0015                  NUMBER

 V0020                  NUMBER

 V0025                  NUMBER

 V0030                  NUMBER

 V0035                  NUMBER

 V0040                  NUMBER

………………………………..

V2355                  NUMBER

 H                      NUMBER

 L                      NUMBER

 A                      NUMBER

 HTIME                  DATE

 LTIME                  DATE

 

第一反應是感覺這個表會不會定時或者根據條件更新列啊，先不管了，看一下執行計畫再說，發現執行一次竟然那麼長時間，而且走的全表掃描

SQL> explain plan for SELECT V0800,H,L,A,to_char(HTIME,'yyyy-mm-dd hh24:mi:ss'),to_char(LTIME,'yyyy-mm-dd hh24:mi:ss') FROM XXXDBA.ORACLETABLEAVAILABLE WHERE RESOURCEID = :1 AND TIME = to_date(:2,'yyyy-mm-dd');

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1457290298

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

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

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

|  0 | SELECT STATEMENT  |                          |    1 |    65 | 18311  (1)| 00:03:40 |

|*  1 |  TABLE ACCESS FULL| ORACLETABLEAVAILABLE |    1 |    65 | 18311  (1)| 00:03:40 |

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

Predicate Information (identified by operation id):

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

  1 - filter("RESOURCEID"=:1 AND "TIME"=TO_DATE(:2,'yyyy-mm-dd'))

13 rows selected.

 

查看該表行數

SQL> select count(*) from XXXDBA.ORACLETABLEAVAILABLE;

  COUNT(*)

----------

    326796

 

查看是否有相關索引，竟然沒有索引，按理說該表不算太大，但執行次數太多，加個索引會快些吧

SQL> select owner,index_name,index_type,table_name from dba_indexes where table_name='ORACLETABLEAVAILABLE';

no rows selected

 

通過詢問項目組人員，得知，該表收集受監聽系統時間點資料，更做相應更新操作，列名不變，查詢語句中條件陳述式列RESOURCEID是唯一的，建議項目組添加索引，索引資訊如下

SQL>  select owner,index_name,index_type,table_name from dba_indexes where table_name='ORACLETABLEAVAILABLE';

OWNER                          INDEX_NAME                    INDEX_TYPE                  TABLE_NAME

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

XXXDBA                P_ORACLETABLEAVAILABLE    NORMAL                      ORACLETABLEAVAILABLE

SQL> select dbms_metadata.get_ddl('INDEX','P_ORACLETABLEAVAILABLE','XXXDBA') from dual;

DBMS_METADATA.GET_DDL('INDEX','P_ORACLETABLEAVAILABLE','XXXDBA')

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

  CREATE UNIQUE INDEX "XXXDBA"."P_ORACLETABLEAVAILABLE" ON "XXXDBA"."ORACLETABLEAVAILABLE" ("RESOURCEID", "TIME")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "PERFORMANCE"

 

再次查看執行計畫，這速度，杠杠的

SQL> explain plan for SELECT V0900,H,L,A,to_char(HTIME,'yyyy-mm-dd hh24:mi:ss'),to_char(LTIME,'yyyy-mm-dd hh24:mi:ss')

  2  FROM XXXDBA.ORACLETABLEAVAILABLE

  3  WHERE RESOURCEID = :1 AND TIME = to_date(:2,'yyyy-mm-dd')

  4  ;

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1279632247

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

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

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

|  0 | SELECT STATEMENT            |                            |    1 |    65 |    3  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| ORACLETABLEAVAILABLE  |    1 |    65 |    3  (0)| 00:00:01 |

|*  2 |  INDEX UNIQUE SCAN        | P_ORACLETABLEAVAILABLE |    1 |      |    2  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  2 - access("RESOURCEID"=:1 AND "TIME"=TO_DATE(:2,'yyyy-mm-dd'))

14 rows selected.
 

更多詳情見請繼續閱讀下一頁的精彩內容：  

1 2 下一頁