1 / 19

RAC Internals

RAC Internals. Julian Dyke Independent Consultant. Web Version. juliandyke.com. © 2007 Julian Dyke. Agenda. Transactions in RAC Cross Instance Consistent Reads. Introduction. System Change Number. In RAC clusters SCN must be maintained across all nodes in cluster

jyotika
Download Presentation

RAC Internals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. RACInternals Julian Dyke Independent Consultant Web Version juliandyke.com ©2007 Julian Dyke

  2. Agenda • Transactions in RAC • Cross Instance Consistent Reads

  3. Introduction

  4. System Change Number • In RAC clusters SCN must be maintained across all nodes in cluster • SCN propagation scheme differs according to version • In Oracle 9.2 and below defaults to Lamport algorithm • Lamport or SCN Scheme 2 in alert.log • SCN piggy-backed on GCS/GES messages • Recorded in redo log • Default delay of 7 seconds • In Oracle 10.1 and above uses a new algorithm • SCN Scheme 3 in alert.log • Broadcast on commit • Apparently no delay

  5. MAX_COMMIT_PROPAGATION_DELAY • Prior to Oracle 10.2 • Default value is 700 centiseconds (7 seconds) • Specifies maximum time taken for a COMMIT on one node to be reflected on other nodes in the cluster • For some applications, value must be set to 0 (Broadcast on commit) including: • E-Business suite • SAP • In Oracle 10.2 and above default value is 0

  6. LMS Background Processes • LMS background processes: • Implement cache fusion • Serve both consistent and current versions of blocks in cache of local instance to other instances • Maintain local part of Global Resource Directory • Minimum of 1 LMS process per instance • Maximum is version dependent • Oracle 9.2 10 • Oracle 10.1 20 • Oracle 10.2 36 • Prior to Oracle 10.1, could be configured using _lm_lms parameter • In Oracle 10.1 and above, initial number of LMS processes specified by gcs_server_processes parameter

  7. LMS Background Processes • Each LMS background process manages a set of blocks • Determined by hash function based on number of LMS background processes • Consequently • a block will always be handled by the same LMS process • Number of blocks served recorded in • Session / System statistics • V$CR_BLOCK_SERVER

  8. col3: 1 col2: 350 slot 0 col2: 344 col2: 340 slot 0 col1: ENG col2: 340 col3: 1 slot 1 col1: AUS col2: 99 col3: 10 slot 0 ITL1 col1: ENG col2: 352 col2: 350 col3: 10 col2: 350 col1: AUS col2: 99 col3: 10 col2: 352 col3: 1 slot 1 col2: 344 ITL1 col2: 344 ITL1 col2: 352 col3: 10 slot 1 col3: 1 col2: 340 col1: ENG slot 0 col2: 352 col1: AUS ITL1 col3: 1 col2: 340 col2: 344 col2: 344 col2: 350 col1: ENG col2: 352 col2: 99 col2: 350 col2: 99 slot 1 slot 0 col1: ENG col3: 10 col1: AUS slot 1 col1: AUS ITL1 col2: 99 col2: 340 segment 5 slot 18: state: 10wrap#: 4E7dba: 00800777 UPDATE score SET runs = runs + 6 WHERE team = 'ENG'; UPDATE score SET runs = runs + 4 WHERE team = 'ENG'; UPDATE score SET runs = runs + 2 WHERE team = 'ENG'; SELECT runs,wicketsFROM scoreWHERE team = 'ENG'; Build read consistent version of block 42 5.1 5.1 5.1 block 42 slot 0 block 42 slot 0 block 42 slot 0 uba: 800777.530.13 uba: 800777.530.12 col2: 350 col2: 344 uba: 800777.530.13 col2: 344 uba: 800777.530.12 col2: 350 col2: 340 col2: 340 uba: 800777.530.13 col2: 340 uba: 800777.530.12 col2: 350 uba: - uba: - col2: 344 uba: - seq: 530 irb 12 col3: 350 col3: 340 col3: 344 xid: 0005.018.4E7 xid: 0005.018.4E7 xid: 0005.018.4E7 xid: 0005.018.4E7 xid: 0005.018.4E7 xid: 0005.018.4E7 uba: 800777.530.12 uba: 800777.530.14 uba: 800777.530.13 uba: 800777.530.13 uba: 800777.530.14 uba: 800777.530.13 uba: 800777.530.12 uba: 800777.530.12 uba: 800777.530.13 uba: 800777.530.14 uba: 800777.530.14 uba: 800777.530.12 uba: 800777.530.12 uba: 800777.530.13 uba: 800777.530.14 uba 800777.530.13 Data Block 42 (copy) Data Block 42 (copy) Data Block 42 Data Block 42 Data Block 42 Data Block 42 Cross Instance Consistent Read Instance 1 Instance 2 Session 15 LMS0 Session 27 Undo Header 12 uba: - 13 uba 800777.530.12 14 Data Block 42 (copy) Data Block 42 Undo Block 800777

  9. V$CR_BLOCK_SERVER

  10. Light Works Rule • In theory, once a block has been written to disk, the LMS process will not attempt to read it again when responding to a consistent read request • Light Works Rule • Prevents LMS processes from going to disk when responding to CR requests for data, undo or undo segment blocks • Can prevent LMS process from completing its response to a CR request

  11. UPDATE score SET runs = 200WHERE team = 'ENG'; 22:10 22:9 Session27 Session15 LMS0 ENG 200 ENG 199 UPDATE score SET runs = 204WHERE team = 'ENG'; ENG 204 ENG 200 ENG 205 ENG 199 ENG 199 ENG 205 ENG 205 ENG 205 UPDATE score SET runs = 205WHERE team = 'ENG'; ENG 204 AUS 99 AUS 99 AUS 99 AUS 99 AUS 99 COMMIT; SELECT runs FROM score WHERE team = 'ENG'; STOP GC Read Committed Block Committed Block - Data Block on disk Block 42 UndoBlock Instance 1 Instance 2

  12. UPDATE score SET runs = 200WHERE team = 'ENG'; 22:9 22:10 Session27 LMS0 Session15 ENG 200 ENG 199 UPDATE score SET runs = 204WHERE team = 'ENG'; ENG 204 ENG 200 ENG 205 ENG 199 ENG 199 ENG 205 ENG 205 UPDATE score SET runs = 205WHERE team = 'ENG'; ENG 204 AUS 99 AUS 99 AUS 99 AUS 99 COMMIT; SELECT runs FROM score WHERE team = 'ENG'; STOP GC Read Committed Block Committed Block - Data Block in buffer cache Block 42 UndoBlock Instance 1 Instance 2

  13. GC Read Uncommitted Block • Uncommitted changes MUST be flushed to the redo log before the LMS process can ship a consistent block to another instance • Reading process must wait until redo log changes have been written to redo log by LMS process • Bad for standard RAC databases • Reads must wait for redo log writes • Worse for extended / stretch RAC clusters • Increased latency of cross site disk communications

  14. GC Read Uncommitted Block • For each block on which a consistent read is performed, a redo log flush must first be performed • Number of redo log flushes is recorded in the FLUSHES column of V$CR_BLOCK_SERVER • Redo log flush time • is recorded in the gc cr block flush time statistic for the LMS process • will increase time taken to serve consistent block • will increase time taken to perform consistent read • If LMS processes become very busy, consistent reads will experience high wait times e.g. for a full table scan gc cr multi block request

  15. UPDATE score SET runs = 200WHERE team = 'ENG'; 22:10 LMS0 Session27 Session15 ENG 200 ENG 199 UPDATE score SET runs = 204WHERE team = 'ENG'; ENG 199 ENG 204 ENG 200 ENG 204 ENG 200 ENG 199 ENG 199 ENG 199 ENG 199 ENG 205 ENG 205 ENG 205 UPDATE score SET runs = 205WHERE team = 'ENG'; ENG 204 AUS 99 AUS 99 AUS 99 AUS 99 AUS 99 AUS 99 SELECT runs FROM score WHERE team = 'ENG'; STOP GC Read Uncommitted Block Uncommitted Block - Data Block in buffer cache Block 42Copy Block 42 UndoBlock Instance 1 Instance 2

  16. UPDATE score SET runs = 200WHERE team = 'ENG'; 22:10 Session27 LMS0 Session15 ENG 200 ENG 200 ENG 200 ENG 200 ENG 199 ENG 199 ENG 199 UPDATE score SET runs = 204WHERE team = 'ENG'; ENG 204 ENG 200 ENG 199 ENG 204 ENG 204 ENG 204 ENG 204 ENG 200 ENG 200 ENG 200 ENG 199 ENG 199 ENG 205 ENG 205 ENG 199 ENG 205 ENG 199 ENG 199 ENG 205 ENG 205 ENG 205 ENG 205 UPDATE score SET runs = 205WHERE team = 'ENG'; ENG 204 ENG 204 ENG 204 AUS 99 AUS 99 AUS 99 AUS 99 AUS 99 AUS 99 AUS 99 AUS 99 SEE SLIDE NOTES FOR ADDITIONAL INFORMATION SELECT runs FROM score WHERE team = 'ENG'; STOP GC Read Uncommitted Block Uncommitted Block - Data Block on disk Block 42 UndoBlock Instance 1 Instance 2

  17. Consistent Reads in RAC • If possible, blocks will always be read from the cache of another instance • Undo blocks will be flushed to disk more frequently when: • All columns are updated • Indexed columns are updated • Single rows inserted • as opposed to using array inserts • Transactions are regularly rolled back • Rows locked using SELECT FOR UPDATE • Data blocks will be flushed to disk more frequently when: • Most transactions are read-only

  18. Consistent Reads in RAC • Consistent read response times in RAC can be reduced by: • Avoid reading uncommitted blocks on remote nodes • Partitioning • Limiting number of rows per block • Specifying SCN • Minimizing size of transactions on remote nodes • Must retain ACID properties • May be possible to use application logic to synchronize writes and reads • Increasing number of LMS processes on remote node • Should be added dynamically by kernel • Also by obvious hardware changes such as • reducing latency of interconnect • increasing disk speed

  19. Thank you for your interest For more information and to provide feedback please contact me My e-mail address is: info@juliandyke.com My website address is: www.juliandyke.com

More Related