SQL最佳化案例一則--複合索引沒有被使用

sky_dizzy001發表於2014-02-09



左右接收到某生產系統系統中介軟體報警,從中介軟體現象初步判斷為中介軟體到資料庫的連線耗盡或者資料庫壓力過大。



登入到192.168.1.105伺服器後,透過topas發現CPU資源耗盡,CPU idle為0。主機組將CPU由2個動態增加到4個,稍後發現資料庫異常宕掉,其後臺alert日誌如下

Detected change in CPU count to 8



Errors in file /software/oracle/app/oracle/product/diag/rdbms/scdb/scdb/trace/scdb_ckpt_389312.trc  (incident=89):



ORA-00600: internal error code, arguments: [ksrpubwait:deadlock], [parameters to cluster db instances - broadcast channel], [1], [1], [0], [], [], [], [], [], [], []



Incident details in: /software/oracle/app/oracle/product/diag/rdbms/scdb/scdb/incident/incdir_89/scdb_ckpt_389312_i89.trc



Errors in file /software/oracle/app/oracle/product/diag/rdbms/scdb/scdb/trace/scdb_ckpt_389312.trc:



ORA-02097: parameter cannot be modified because specified value is invalid



ORA-00600: internal error code, arguments: [ksrpubwait:deadlock], [parameters to cluster db instances - broadcast channel], [1], [1], [0], [], [], [], [], [], [], []



CKPT (ospid: 389312): terminating the instance due to error 2097



此錯誤為觸發了 (詳見本文件最下面的NoteID 756128.1)



手動啟動資料庫, 連線正常,但CPU資源仍然耗盡。



檢視等待事件



select event,count(*) from v$session_wait group by event order by 1;



在不同的時間段,發現會有少量的latch free事件,但持續時間很短。透過AWR報告,此語句耗CPU最多



select b.id, b.actual_name, c.short_name, d.name, b.duty,



nvl(a.total_credit_hour,0) as total_credit_hour,a.can_achieve    from



user_credit_hour a ,user_info b, organ c, organ_dept d   where



a.user_id=b.id and b.organ_id=c.id and c. organ_degree=0  and



b.dept_id=d.id   and rownum <=10 order by a.total_credit_hour desc



檢視其執行計劃如下:



PLAN_TABLE_OUTPUT



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



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



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



|   0 | SELECT STATEMENT       |                  |       |       |  3762 (100)|          |



|   1 |  SORT ORDER BY         |                  |    10 |   780 |  3762   (1)| 00:00:46 |



|*  2 |   COUNT STOPKEY        |                  |       |       |            |          |



|*  3 |    HASH JOIN           |                  |   217 | 16926 |  3761   (1)| 00:00:46 |



|*  4 |     HASH JOIN          |                  |    75 |  4875 |   184   (2)| 00:00:03 |



|   5 |      NESTED LOOPS      |                  |    75 |  3600 |   177   (2)| 00:00:03 |



|*  6 |       TABLE ACCESS FULL| ORGAN            |     1 |    19 |     3   (0)| 00:00:01 |



|*  7 |       TABLE ACCESS FULL| USER_INFO        |    75 |  2175 |   174   (2)| 00:00:03 |



|   8 |      TABLE ACCESS FULL | ORGAN_DEPT       |  3089 | 52513 |     7   (0)| 00:00:01 |



|   9 |     TABLE ACCESS FULL  | USER_CREDIT_HOUR | 73403 |   931K|  3575   (1)| 00:00:43 |



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



 


Predicate Information (identified by operation id):



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



 


   2 - filter(ROWNUM<=10)



   3 - access("A"."USER_ID"="B"."ID")



   4 - access("B"."DEPT_ID"="D"."ID")



   6 - filter("C"."ORGAN_DEGREE"=0)



   7 - filter("B"."ORGAN_ID"="C"."ID")



 


透過執行計劃,此語句裡幾張表都是在做全表掃描,最大的一張為如下:



SQL> select count(*) from DB_SC.USER_CREDIT_HOUR;



  COUNT(*)



----------



     73412



查詢該表上的索引資訊,總共有兩個索引



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



 


OWNER                          INDEX_NAME                     INDEX_TYPE



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



DB_SC                       USER_CREDIT_HOUR_PK            NORMAL



DB_SC                       USER_CREDIT_HOUR_MAIN_INDEX    NORMAL



索引定義如下:



SQL> select dbms_metadata.get_ddl('INDEX','USER_CREDIT_HOUR_MAIN_INDEX','DB_SC') from dual;



 


DBMS_METADATA.GET_DDL('INDEX','USER_CREDIT_HOUR_MAIN_INDEX','DB_SC')



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



  CREATE INDEX "DB_SC"."USER_CREDIT_HOUR_MAIN_INDEX" ON "DB_SC"."USER_CREDIT_HOUR" ("YEAR", "USER_ID")



  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS



  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645



  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)



  TABLESPACE "TS_STUDY_IDX"



 


SQL> select dbms_metadata.get_ddl('INDEX','USER_CREDIT_HOUR_PK','DB_SC') from dual;



DBMS_METADATA.GET_DDL('INDEX','USER_CREDIT_HOUR_PK','DB_SC')



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



  CREATE UNIQUE INDEX "DB_SC"."USER_CREDIT_HOUR_PK" ON "DB_SC"."USER_CREDIT_HOUR" ("ID")



  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS



  STORAGE(INITIAL 4194304 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645



  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)



  TABLESPACE "TS_STUDY_IDX"



在執行計劃中沒有使用複合索引,手動建立一個單鍵索引(USER_CREDIT_HOUR_USER_ID)後, 其執行計劃如下:



DBMS_METADATA.GET_DDL('INDEX','USER_CREDIT_HOUR_USER_ID','DB_SC')



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



 


  CREATE INDEX "DB_SC"."USER_CREDIT_HOUR_USER_ID" ON "DB_SC"."USER_CREDIT_HOUR" ("USER_ID")



  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS



  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645



  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)



  TABLESPACE "TS_STUDY_IDX"



其執行計劃為走索引



PLAN_TABLE_OUTPUT



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



Plan hash value: 3736789896



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



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



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



|   0 | SELECT STATEMENT               |                          |    10 |   780 |   483   (1)| 00:00:06 |



|   1 |  SORT ORDER BY                 |                          |    10 |   780 |   483   (1)| 00:00:06 |



|*  2 |   COUNT STOPKEY                |                          |       |       |            |          |



|   3 |    NESTED LOOPS                |                          |       |       |            |          |



|   4 |     NESTED LOOPS               |                          |   217 | 16926 |   482   (1)| 00:00:06 |



|*  5 |      HASH JOIN                 |                          |    75 |  4875 |   184   (2)| 00:00:03 |



|   6 |       NESTED LOOPS             |                          |    75 |  3600 |   177   (2)| 00:00:03 |



|*  7 |        TABLE ACCESS FULL       | ORGAN                    |     1 |    19 |     3   (0)| 00:00:01 |



|*  8 |        TABLE ACCESS FULL       | USER_INFO                |    75 |  2175 |   174   (2)| 00:00:03 |



|   9 |       TABLE ACCESS FULL        | ORGAN_DEPT               |  3089 | 52513 |     7   (0)| 00:00:01 |



|* 10 |      INDEX RANGE SCAN          | USER_CREDIT_HOUR_USER_ID |     3 |       |     1   (0)| 00:00:01 |



|  11 |     TABLE ACCESS BY INDEX ROWID| USER_CREDIT_HOUR         |     3 |    39 |     4   (0)| 00:00:01 |



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



   2 - filter(ROWNUM<=10)



   5 - access("B"."DEPT_ID"="D"."ID")



   7 - filter("C"."ORGAN_DEGREE"=0)



   8 - filter("B"."ORGAN_ID"="C"."ID")



  10 - access("A"."USER_ID"="B"."ID")



 


附錄:



NoteID 756128.1



ORA-600 [Ksrpubwait:Deadlock] ORA-2097 Instance crashes when CPU Count is Dynamically Changed [ID 756128.1]



wps_clip_image-18808To Bottom



wps_clip_image-26093

 


 


Modified:Mar 29, 2010wps_clip_image-20819Type:PROBLEMwps_clip_image-19134Status:MODERATEDwps_clip_image-29146Priority:3



wps_clip_image-12254Comments (0)wps_clip_image-21365



wps_clip_image-10428wps_clip_image-7666



wps_clip_image-32664wps_clip_image-30079



wps_clip_image-26419wps_clip_image-27664



wps_clip_image-4860wps_clip_image-29537



 


wps_clip_image-12707

 


In this Document
 
 
 
 



 


Platforms: 1-914CU;



This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.



Applies to:



Oracle Server - Enterprise Edition - Version: 11.1.0.7 to 11.1.0.7 - Release: 11.1 to 11.1
Information in this document applies to any platform.



Symptoms



ORA-600 [ksrpubwait:deadlock], [parameters to cluster db instances - broadcast channel]
ORA-2097: parameter cannot be modified because specified value is invalid
Instance crashes.
This happens everytime when CPU is dynamically changed.



STACK TRACE:
ksrpubwait ksrpublish kspbcast kspset0 kxfpCPUCountUpdate ksbcpcb



Cause



This caused by



Solution



is fixed in version 11.2.



Check for the availability of one-off for your platform on MetaLink.



 



Possible Workarounds:



1) Explicitely setting CPU_COUNT to new value.



2) Start the same instance from pfile created from the same spfile can avoid the problem.



3) Set explicitely PARALLEL_MAX_SERVERS to the current value.



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29468144/viewspace-1078413/,如需轉載,請註明出處,否則將追究法律責任。

相關文章