記一次gc buffer busy等待事件的處理

我們先來看看gc buffer busy 的定義：
gc buffer busy
This wait event, also known as global cache buffer busy prior to Oracle 10g,
specifies the time the remote instance locally spends accessing the requested data block.
This wait event is very similar to the buffer busy waits wait event in asingle-instance database and are often the result of:
在10g 之前。這個等待事件叫做 global cache buffer busy，產生的原因和單例項的 buffer busy waits 類似就是一個時間點節點a的例項向節點b請求block的等待。
產生原因：
1. Hot Blocks -
multiple sessions may be requesting a block that is either not in buffer cache or is in an incompatible mode.
Deleting some of the hot rows and re-inserting them back into the table may alleviate the problem.
Most of the time the rows will be placed into a different block and reduce contention on the block.
The DBA may also need to adjust the pctfree and/or pctused parameters for the table to ensure the rows are placed into a different block.
熱塊
可能有多個session 請求這個資料塊，有可能是資料塊還沒有讀入 data buffer cache(某個session正在讀入)，或者session 間存在衝突的模式
一個會話在delete 熱塊的熱行並且重新插入的時候，有可能出現這個問題。
解決的辦法是修正 (擴大)pctfree 引數。來打散這個熱塊。
2.Inefficient Queries ˆ
as with the gc cr request wait event,
the more blocks requested from the buffer cache the more likelihood of a session having to wait for other sessions.Tuning queries to access
fewer blocks will often result in less contention for the same block.
低效的查詢
越多的資料塊請求到buffer cache 中，那麼越可能造成別的會話等待。優化查詢(sql)，讀入更少的lock。

下面來看看遇到gc buffer busy是的資料庫情況：
環境：
作業系統：AIX5.3 20C40G
資料庫版本：10.2.0.5 RAC
現象：
從v$session或者v$session_wait檢視中，可以查詢到大量的gc buffer busy 等待。系統執行緩慢。
SID SERIAL# SPID EVENT STATUS BLOCKING_SESSION SQL_ID
---------- ---------- ------------ ------------------------------------------------------------ -------- ---------------- -------------
807 1000 2007276 gc buffer busy ACTIVE 5asgkgnjjhzac
670 827 364556 gc buffer busy ACTIVE 5asgkgnjjhzac
1013 1792 237622 gc buffer busy ACTIVE 7j4dakq0798zs
805 634 1446244 gc buffer busy ACTIVE 75kwpag9a99az
1358 44586 1118556 gc buffer busy ACTIVE 5asgkgnjjhzac
784 1697 2044138 gc buffer busy ACTIVE c0xgjncf7tab6
894 1410 487530 gc buffer busy ACTIVE 7j4dakq0798zs
801 3319 2445606 gc buffer busy ACTIVE 5asgkgnjjhzac
1280 57943 929998 gc buffer busy ACTIVE 7j4dakq0798zs
1119 119 2089450 gc buffer busy ACTIVE 5asgkgnjjhzac
1359 36831 840082 gc buffer busy ACTIVE 5asgkgnjjhzac

仔細檢查發現，這些gc buffer busy 全部是由2個SQL 引起的，SQL ID分別為：5asgkgnjjhzac 和 7j4dakq0798zs 。
檢查2個SQL的執行計劃：
5asgkgnjjhzac
Plan hash value: 1554161953
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 2 | 722 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CERTIFICATE_DETAIL | 2 | 650 | 5 (0)| 00:00:01 |
| 4 | INDEX RANGE SCAN | IDX_CER_DETAIL_01 | 3 | | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| CERTIFICATE_TITLE | 1 | 36 | 2 (0)| 00:00:01 |
| 6 | INDEX UNIQUE SCAN | PK_CERTIFICATE_TITLE | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Plan hash value: 2556006691
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 61633 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 2 | 726 | 61633 (4)| 00:12:20 |
| 3 | TABLE ACCESS FULL | CERTIFICATE_DETAIL | 4 | 1308 | 61628 (4)| 00:12:20 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 4 | TABLE ACCESS BY INDEX ROWID| CERTIFICATE_TITLE | 1 | 36 | 2 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | PK_CERTIFICATE_TITLE | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

這個SQL產生了兩個執行計劃，這兩個執行計劃的區別就在於CERTIFICATE_DETAIL表，一個是走索引，一個是全表掃描。後又查詢了這個表的統計分析，見下表，從主鍵看，這個表有8144325行，看來走索引和全表掃描的效能將會差別很大，產生gc buffer busy的原因可能是這個索引的問題。
INDEX_NAME INDEX_TYPE STATUS NUM_ROWS COLUMN_NAME
------------------------------ --------------------------- -------- ---------- ------------------------------
INDEX_45 FUNCTION-BASED NORMAL VALID 7995680 SYS_NC00066$
INDEX_CER_WRITEOFFDETAIL NORMAL VALID 4898206 WRITEOFFDETAILIDS
INDEX_CER_LEDGERCOMPANYCODE NORMAL VALID 7654900 LEDGERCATEGORY_COMPANYCODE
PK_CERTIFICATE_DETAIL NORMAL VALID 8144325 ID
INDEX_44 NORMAL VALID 7750769 CERTIFICATE_ID
INDEX_CER_COSTCENTER NORMAL VALID 3249340 COST_CENTER
INDEX_CER_LEDGERNO NORMAL VALID 8118682 LEDGER_NO
接下來看看7j4dakq0798zs的執行計劃，表 CERTIFICATE_DETAIL依然走了全表掃描，看來問題可以確定了。

Plan hash value: 1109848256
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 83037 (100)| |
| 1 | HASH UNIQUE | | 4 | 1364 | 83037 (4)| 00:16:37 |
| 2 | NESTED LOOPS | | 4 | 1364 | 83036 (4)| 00:16:37 |
| 3 | TABLE ACCESS FULL | CERTIFICATE_DETAIL | 4 | 264 | 83028 (4)| 00:16:37 |
| 4 | TABLE ACCESS BY INDEX ROWID| CERTIFICATE_TITLE | 1 | 275 | 2 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | PK_CERTIFICATE_TITLE | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

問題解決：
在表 CERTIFICATE_DETAIL 的相關列上建索引後，從v$session檢視監控中可以看到gc buffer busy等待事件消失，等待事件個數也不斷減少，一會後，資料庫恢復正常。

至於索引丟失，可能是。。。。。。

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

記一次gc buffer busy等待事件的處理

相關文章