記一次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 |
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
| 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等待事件消失,等待事件個數也不斷減少,一會後,資料庫恢復正常。
至於索引丟失,可能是。。。。。。
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: 1109848256INDEX_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依然走了全表掃描,看來問題可以確定了。
------------------------------------------------------------------------------------------------------
| 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處理GC
- gc buffer busy acquire問題處理GCUI
- rac 遭遇GC BUFFER BUSY 處理思路GC
- 【等待事件】buffer busy waits事件AI
- gc current/cr block busy等待事件GCBloC事件
- gc buffer busyGC
- Buffer Cache以及buffer busy waits/gc相關事件AIGC事件
- buffer busy wait 等待事件說明AI事件
- 一次gc buffer busy問題的診斷GC
- buffer busy wait 等待事件說明(轉)AI事件
- gc buffer busy的優化GC優化
- 【TUNE_ORACLE】等待事件之“buffer busy waits”Oracle事件AI
- gc buffer busy的最佳化GC
- wait event:gc buffer busyAIGC
- Oracle資料庫buffer busy wait等待事件 (2)Oracle資料庫AI事件
- Oracle資料庫buffer busy wait等待事件 (1)Oracle資料庫AI事件
- 分析解決11gR2 雙節點RAC環境下的gc cr block busy/gc buffer busy acquire等待GCBloCUI
- GC BUFFER BUSY問題的診斷GC
- 等待事件_buffer_busy_waits_and_read_by_other_session(1)事件AISession
- 等待事件_buffer_busy_waits_and_read_by_other_session(2)事件AISession
- 等待事件_buffer_busy_waits_and_read_by_other_session(3)事件AISession
- 等待事件_buffer_busy_waits_and_read_by_other_session(4)事件AISession
- 等待模擬-BUFFER BUSY WAITAI
- 模擬產生CBC LATCH與buffer busy wait等待事件AI事件
- 轉載經典文章 buffer busy wait 等待事件說明AI事件
- oracle buffer busy waits等待的含義OracleAI
- RAC遇到GC Buffer Busy的解決方法2GC
- RAC遇到GC Buffer Busy的解決方法1GC
- gc current request等待時間處理GC
- buffer busy waits 平均等待時間AI
- GC Buffer Busy Waits in RAC: Finding Hot BlocksGCAIBloC
- gc cr request等待事件GC事件
- 【等待事件】Buffer Exterminate事件
- Oracle Dba必須瞭解的buffer busy waits等待OracleAI
- Cache Buffer Chain Latch等待事件AI事件
- log file sync等待事件處理思路事件
- 解決gc current request等待事件GC事件
- gc 等相關等待事件描述GC事件