ITPub博客

首页 > 数据库 > Oracle > ORACLE性能优化有时就这么简单_index

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

原创 Oracle 作者:xysoul_云龙 时间:2014-03-13 00:55:59 0 删除 编辑

性能优化有时就这么简单



一、概述



    最近有一个系统(aix6.1+oracle10.2.0.5 RACCPU每五分钟就达到百分之百,这个系统是监控系统,针对所有运行系统每五分钟取数,所以之前也没太在意, 其实在年前就有过这种情况,只是把执行频繁的语句发送给项目组,有针对系统运行环境调整了一下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.



 



查看CPU信息,这简直是直线下降,在上午10点左右添加的索引



等待会话数,更直观
 


截取添加索引前后相同时间段AWR报告,进行对比,明显改观不少



 


Snapshot Set



Begin Snap Id



Begin Snap Time



End Snap Id



End Snap Time



Elapsed Time (min)



DB Time (min)



Avg Active Users



1st



33847



11-Mar-14 14:00:24



33848



11-Mar-14 14:31:40



31.27



227.75



7.28



2nd



33868



12-Mar-14 10:33:33



33869



12-Mar-14 11:02:13



28.67



14.79



0.47



 


 

1st Per Sec



2nd Per Sec



%Diff



1st Per Txn



2nd Per Txn



%Diff



Redo size:



90,352.30



91,860.86



1.67



1,104.51



1,148.70



4.00



Logical reads:



308,580.08



19,107.48



-93.81



3,772.25



238.94



-93.67



 


1st



2nd



Event



Waits



Time(s)



Percent Total DB Time



Wait Class



Event



Waits



Time(s)



Percent Total DB Time



Wait Class



CPU time



 



5,342.3



39.09



 



CPU time



 



651.4



73.39



 



*gc buffer busy



4,559,903



3,796.7



27.78



Cluster



log file sync



121,763



151.2



17.03



Commit



*gc cr multi block request



1,007,965



209.3



1.53



Cluster



gc current block 2-way



139,251



62.9



7.08



Cluster



gc current block 2-way



358,579



187.7



1.37



Cluster



*log file parallel write



112,638



51.5



5.80



System I/O



log file sync



131,750



140.6



1.03



Commit



*gc cr block 2-way



45,035



23.5



2.65



Cluster



-log file parallel write



115,952



46.0



.34



System I/O



-gc cr multi block request



88,143



22.9



2.58



Cluster



-gc cr block 2-way



51,321



27.9



.20



Cluster



-gc buffer busy



1,290



11.4



1.28



Cluster



 


三、总结



    在目前环境中呢,作为一个运维管理DBA,经常的习惯是保证数据库不出问题,做好备份,数据库别挂了就行。也习惯于将性能问题归于SQL语句编写的不规范,只是将SQL语句发送与项目组,让其优化,可惜太多时候是肉包子打狗有去无回,他们用心的少,只要我这访问慢就找你,系统一告警领导也找你,我们就说SQL语句的问题,来来回回、绕来绕去。倒不如静下心来看看你认为有问题的SQL语句,并标明哪哪有问题,交给项目组人员修改,利人利己。大多的数据库性能问题都跟SQL有关,但却也五花八门,其实当你认真的投入进去,并收到的效果时其乐无穷,何况简单的语句,效果却很明显的呢?每天进步一点就好。



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29487349/viewspace-1110804/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
主要从事数据库相关工作,其他操作系统、中间件等也有涉及,热衷分享、开源,支持国产,期待中华民族全面的伟大复兴。近日骤然醒悟,欲在IT江湖中闯荡一番,如有幸在诸多侠客中留点踪迹,也算不虚此行。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博:文盲筱烨 微信公众号:筱烨视点

注册时间:2014-02-15

  • 博文量
    168
  • 访问量
    757238