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

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

登入到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

此錯誤為觸發了Bug 7535429 (詳見本文件最下面的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

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

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

| 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

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

| 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]

To Bottom

Modified:Mar 29, 2010Type:PROBLEMStatus:MODERATEDPriority:3

Comments (0)

In this Document
Symptoms
Cause
Solution
References

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 Bug 7535429

Solution

Bug 7535429 is fixed in version 11.2.

Check for the availability of one-off Patch 7535429 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-1079786/，如需轉載，請註明出處，否則將追究法律責任。

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

相關文章