Oracle性能优化有时就这么简单_index

性能优化有时就这么简单

一、概述

最近有一个系统(aix6.1+Oracle10.2.0.5 RAC)CPU每五分钟就达到百分之百,这个系统是监控系统,针对所有运行系统每五分钟取数,所以之前也没太在意, 其实在年前就有过这种情况,只是把执行频繁的语句发送给项目组,有针对系统运行环境调整了一下SGA等大小,只是不告警了(cpu使用超过百分之九十就告警),也就没管。可这几天有出现该问题,还是继续将执行频繁的sql发送给项目组,由于所有受监控的系统都会每五分钟向该系统发数,也就理所当然的认为系统就这个特点,也就没理会。可是,这两天,告警短信也每五分钟就发送一次,随之的恢复短信,自己看着不烦,领导看着也烦啊,那就看看吧(虽然对sql语句不感冒吧)

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

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

二、 处理过程

通过提取一段时间内的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.
 

相关推荐