Transcript Row Migration can Aggravate Contention on Cache Buffer

Row Migration can Aggravate
Contention on Cache Buffer
Chains Latch
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Who Am I?
• Oracle Database Specialist
– Independent consultant
• System Performance
tuning
– PeopleSoft ERP
– Oracle RDBMS
• Book
– www.psftdba.com
• UKOUG
– PeopleSoft Technology SIG
Committee
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
2
Resources
• If you can’t hear me say so now.
• Please feel free to ask questions as we go
along.
• The presentation is available from
• UKOUG Library
• www.go-faster.co.uk
• See also http://blog.go-faster.co.uk
– Tip: Print in ‘black and white’ for white
background.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
3
Background Reading
• Blog Entry:
– http://blog.go-faster.co.uk/2008/12/rowmigration-can-aggrivate-contention.html
– New slides added since presentation to
UKOUG DBMS SIG marked with *
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
4
A War Story
• Earlier
– I showed you how to build a spreadsheet to
graph AWR stats
• Now
– I want to show you a example of how it was
used in the real world.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
5
System Overview
• Packaged Training Management
Application.
– From time to time, users would report that the
system would ‘grind to a halt’
• No access to OEM.
• But we did have AWR running on a 15
minute snapshot.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
6
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
7
System Overview
• System stable for weeks at a time.
• User performance complaints corresponded
with spikes on Cache Buffer Chains Latch
• Couldn’t really get a picture of what the
users were doing that was different.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
8
Logons
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
9
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
10
Correlation?
• By eye, I could see that over several days
the spikes in the CBCL wait event matched
the number of logons.
• So I graphed one against the other.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
11
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
12
Cache Buffer Chains Latch
• Whenever more than 90 users.
• Addition 1 sec/sec of wait per additional
user.
– The system isn’t scaling any more.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
13
*Cache Buffer Chains Latch
• Usually caused by hot blocks.
– Migrated rows cause additional block accesses
• AWR report: table fetch continued row
• Have to be a lot of this to cause this much trouble
– I believe (but I cannot now prove) that when
rows migrated out of a block, others were
inserted into space left behind.
– So data blocks had lots of pointers to migrated
rows.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
14
Root Cause
• Row Migration
– XML structure in a CLOB
– Starts small, gets larger
– <3964 bytes stored in-line in data block
• Need to
– Set appropriate value for PCTFREE.
– Rebuild tables and indexes.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
15
USER_TABLES.CHAIN_CNT
• Not populated by dbms_stats.
– Also USER_TABLES.AVG_FREE_SPACE
• Must use ANALYZE.
– ANALYZE TABLE <table> LIST CHAINED ROWS INTO
<table>
– http://blog.go-faster.co.uk/2009/01/detectingand-fixing-row-migration.html
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
16
Rebuilding Tables
• Where tables have history that will not be
updated further
– Packed the history into the minimum number of
blocks
• ALTER TABLE MOVE … PCTFREE <small value>
– But new rows in new blocks need space to
grow
• ALTER TABLE … PCTFREE <reasonable value>
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
17
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
18
Result
• After rebuilding tables, CBCL contention
all but disappeared.
– And it hasn’t come back.
• db_file_sequential_read also fell, probably
because fewer blocks forced out to load
migrated rows.
– Possibly because we rebuilt the indexes.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
19
Caveat
• I am NOT asserting that
– Row caching always causes latch contention
– Latch contention always caused by row
caching.
• However, in our specific case it did appear
that fixing one resolved the other.
• Your mileage may vary.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
20
Conclusion
• It is important to
– Understand what your application is doing to
the database.
– Set physical attributes of tables appropriately.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
21
*Explicit Lob Segments
• We could have defined explicit LOB
segments to hold all lob column data
– We didn’t try that.
– Application usually queried all columns
– So would always have accessed addition data
blocks.
– Our CLOBs mostly small enough to fit in-line.
Row Migration can Aggravate Contention
on Cache Buffer Chains Latch
©2009 www.go-faster.co.uk
22
Questions?