Golden rules of RAC diagnostics

By Riyaj Shamsudeen

©OraInternals Riyaj Shamsudeen

Me

n n n n

n n n n n

20+ years using Oracle products/DBA OakTable member, Oracle ACE Director Certified DBA -7.0,7.3,8,8i,9i &10g Specializes in RAC, performance tuning, EBS, & Exadata Chief DBA with OraInternals Co-author a few books Email: rshamsud@orainternals.com Blog : orainternals.wordpress.com Web: www.orainternals.com
©OraInternals Riyaj Shamsudeen 2

Golden rules of RAC diagnostics

1. Beware of top event tunnel vision. 2. Eliminate infrastructure as an issue. 3. Identify problem instance(s). 4. Review Send side metrics, and not just Receive side metrics. 5. Use histograms to identify any abnormalities, and not just averages.

©OraInternals Riyaj Shamsudeen

Top event tunnel vision

n

In RAC, the root cause can be hidden in other node and might not show up in the Top-5 wait events. Impact of side effect events can be much, much worse then actual root cause event itself. Performance of the instances are intertwined. Performance of background processes in one node, can affect the application performance in the all other nodes.

n

n n

©OraInternals Riyaj Shamsudeen

Top event tunnel vision

..2

Example 1

©OraInternals Riyaj Shamsudeen

5

Top event tunnel vision

..3

Example 2

©OraInternals Riyaj Shamsudeen

6

Top event tunnel vision

..4

Example 3

©OraInternals Riyaj Shamsudeen

7

Problem node

Example 3 (continued)

©OraInternals Riyaj Shamsudeen

8

gc buffer busy
A BL request for file #5, block #8 is pending. gc buffer busy acquire

FG2 gc current request charge time to ‘gc current block 2-way’ Wire

FG1
Open request File #5, blk #8

Flush request

Post

LGWR gc buffer busy release

FG3

©OraInternals Riyaj Shamsudeen

Inst 2

LMS

Block send

Inst 1

gc buffer busy

n

Event ‘gc buffer busy [acquire|release]’ can be the symptoms for all the RAC performance root causes discussed earlier. Gc buffer busy wait simply means that there is a pending request for a BL lock for that block already. All other process wait for the BL lock to be available and charge the time to gc buffer busy event.

n

n

©OraInternals Riyaj Shamsudeen

Gc buffer busy – Objects related
Following four pages only applicable if there are no other events indicating systemic issues. n ASH or trace files can be used to identify the blocks suffering from excessive ‘gc buffer busy’ waits. Only use this method if there is no other issue affecting background processes. To understand gc buffer busy waits: n Identify the object and object type n Identify the block type ash_gcwait_to_obj.sql can be used to identify the object name and type. 11

n

n

n

©OraInternals Riyaj Shamsudeen

Gc buffer busy – Identify object

@ash_gcwait_to_obj.sql INST_ID EVENT 1 gc buffer busy acquire 2 gc buffer busy acquire 2 gc buffer busy release OWNER RS RS RS OBJECT_NAME T_GEN_INS_01_N1 T_GEN_INS_01_N1 T_GEN_INS_01 OBJECT_TYPE INDEX INDEX TABLE CNT 3 10 4

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

©OraInternals Riyaj Shamsudeen

12

Gc buffer busy – Identify block

@ash_gcwait_to_block.sql INST_ID EVENT 2 gc buffer busy acquire 2 gc buffer busy acquire 2 gc buffer busy acquire 1 gc buffer busy acquire 2 gc buffer busy release 1 gc buffer busy acquire 2 gc buffer busy release 2 gc buffer busy release CURRENT_FILE# CURRENT_BLOCK# 4 4 4 4 4 4 4 4 103582 103607 103603 103582 103615 103583 103586 103613 CNT 4 3 3 2 2 1 1 1 ---------- ------------------------------ ------------- -------------- ----------

In this example, many different blocks are involved in ‘gc buffer busy’ waits. A symptom of heavy insert load.
Demo: ash_gcwait_to_block.sql
©OraInternals Riyaj Shamsudeen

13

Gc buffer busy – Identify block type n You might need to dump the block to identify the type and contents of the block.
103582 block max 103582;

alter system dump datafile 4 block min

n

In this example, it is a leaf block of an index.
0x0101949e csc: 0x00.11c65d1 itc: 9 flg: E typ: 2 - INDEX

Block header dump:

Object id on Block? Y seg/obj: 0x13266 brn: 0 inc: 0 Itl 0x01 0x02 0x03 0x04 0x05

bdba: 0x1019498 ver: 0x01 opc: 0 exflg: 0 Xid Uba 0x00c00a1f.058a.01 0x00c00a37.058a.56 0x00c01232.045c.18 0x00c005d1.0402.12 0x00c00849.0446.05 Flag -BU------------Lck 1 55 90 73 75 Scn/Fsc fsc 0x0000.011c65d4 fsc 0x0000.00000000 fsc 0x0000.00000000 fsc 0x0000.00000000 fsc 0x0000.00000000

0x0008.001.0000144f 0x0008.015.00001449 0x0004.008.000016c0 0x0002.005.000015c1 0x000a.01c.0000158b

Leaf block dump ===============

Demo: dump_blocks.sql, tracefile.sql
©OraInternals Riyaj Shamsudeen

14

Object type Table

Block type Segment header Segment blocks

Possible issue(s) Freelists, freelist groups, deletes from one node,ASSM bugs etc Heavy insert workload, heavy update to few blocks, SQL performance issues scanning few objects aggressively. Numerous inserts on indexed columns, sequence generated keys Insert + numerous index scans, concurrent inserts and deletes etc Numerous short transactions Long pending transaction and CR block generation Sequences with nocache or order set and excessive access to sequence.
©OraInternals Riyaj Shamsudeen

Index

Leaf block Root block/ branch block

Undo

Undo header block Undo block

Dictionary SEQ$ (object)

15

Top 5 rules of RAC diagnostics

1. Beware of top event tunnel vision. 2. Eliminate infrastructure as an issue. 3. Identify problem instance(s). 4. Review Send side metrics, and not just Receive side metrics. 5. Use histograms to identify any abnormalities, and not just averages.

©OraInternals Riyaj Shamsudeen

Eliminate infra-structure as an issue

n

Wait events ‘gc cr block 2-way’ , ‘gc cr block 3-way’ , ‘gc current block 2-way’, and ‘gc current block 3-way’, ‘gc cr grant 2-way’, ‘gc cr grant 2-way’. Concurrency or congestion issues are not factored in to these events. The lowest wait time that you can possibly get for a block or a message transfer. A wait time of