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.