Transcript Adventure with Chained Rows (slides -

Blocking It Out:
Breaking (PCT)Free from Chains
Philip Rice
Univ. of California Santa Cruz
DBA group: July, 2009
1
Case Study: Overview of Issues

Prompted by performance problem in
Identity Management batch process:
 Query is normally 1000+ statements per hour
 Slowed to 3 minutes each execution instead of
normal 0.5 seconds
 Culprit: CHAIN_CNT = 25% of table, other stats OK
 CBO in 9.2 never complained, 10.2 gave very
bad plan, soon after upgrade
 Focus became re-learning Block Mgmt details
(esp. PCTFREE), for valid performance fix
UCSC: July, 2009
Block It Out: PCTFREE
2
Problem SQL
SELECT userobj.id, name, '', '', summary, '’
FROM waveset.userobj
WHERE name!='LASTMODIFIED’
and name NOT LIKE '#DEL#%’
and userobj.id IN
( select id
from waveset.userattr
where attrname='ADMINROLES’
and attrval='#ID#4A4CA66222610BC0:14D921A:113E5993591:-7F74' )
ORDER BY name;
[Summary of Plan on next slides…]
UCSC: July, 2009
Block It Out: PCTFREE
3
Execution Plan: fast

Sub-select gets ~12 rows from 1M+ row table
using an Index Range Scan
 Outer select uses Index Unique Scan on the table
that became (more recently) faulty with high
CHAIN_CNT value: 162K rows
--------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
9 | 5148 |
4 (25)| 00:00:01 |
|
1 | SORT ORDER BY
|
|
9 | 5148 |
4 (25)| 00:00:01 |
|
2 |
NESTED LOOPS
|
|
9 | 5148 |
3
(0)| 00:00:01 |
|
3 |
TABLE ACCESS BY INDEX ROWID| USERATTR
|
1 |
92 |
2
(0)| 00:00:01 |
|* 4 |
INDEX RANGE SCAN
| USERATTR_ATTRNAME |
3 |
|
1
(0)| 00:00:01 |
|* 5 |
TABLE ACCESS BY INDEX ROWID| USEROBJ
|
65 | 31200 |
1
(0)| 00:00:01 |
|* 6 |
INDEX UNIQUE SCAN
| USEROBJ_ID
|
1 |
|
1
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
UCSC: July, 2009
Block It Out: PCTFREE
4
Execution Plan: 10.2 CBO gets sick

Faulty plan flips the inner/outer access:
 CHAIN_CNT table does Index Full Scan (on
“name” index: not equal and not like) and
Access by RowID on 162K rows (*very* slow)
 Nested Loops with Index Unique Scan (on PK
index) -- access on 1M+ row table is still fast
--------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
541K|
294M| 40880
(1)| 00:05:11 |
|
1 | NESTED LOOPS
|
|
541K|
294M| 40880
(1)| 00:05:11 |
|
2 |
TABLE ACCESS BY INDEX ROWID| USEROBJ
|
163K|
74M| 40863
(1)| 00:05:11 |
|* 3 |
INDEX FULL SCAN
| USEROBJ_NAME |
163K|
|
136
(4)| 00:00:02 |
|* 4 |
INDEX UNIQUE SCAN
| USERATTR_PK |
3 |
267 |
1
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------
UCSC: July, 2009
Block It Out: PCTFREE
5
CBO Considerations

App from vendor generates SQL,
no bind variables
 Want to fix without long term CBO tweaks:
 Avoid stats locking, stats setting
 Can not use SQL Profile or query rewrite,
each statement unique
 Temp fix put in place with analyze command
(9i behavior) instead of dbms_stats
 Focus on fix for Chained/Migrated rows
UCSC: July, 2009
Block It Out: PCTFREE
6
Basics: Chaining
Chained Row: won’t fit in data block, needs pointer
to another block
 Chained Example: 5 columns, varchar2(4000),
each mostly full – 20K won’t fit in 8K block
 LOB in-line storage: up to 4000 bytes, can
contribute to problem of not fitting
 Options to handle Chaining:
 Use different tablespace with larger block size,
multiple of OS block
 For LOB: out of line storage

UCSC: July, 2009
Block It Out: PCTFREE
7
Basics: Migration (credit to Tanel Poder
)

Row Migration: row that originally fit into one
block is updated, overall row length increases,
block free space is already completely filled
 Entire row migrates to new block, pointer stays
in original block
 Corresponding index entries are not updated
with new location of row, uses pointer in original
data block instead – index maintenance trade-off
 Index Range Scan is significant problem, FTS is
much less so because all blocks are read
UCSC: July, 2009
Block It Out: PCTFREE
8
Chains: Set Me Free!

DBA_TABLES.CHAIN_CNT: chained and/or
migrated count
 Which do we have?
Need to look at current row conditions
 Faulty table had LOB column:
look at LOB and non-LOB lengths
 Get max/min/avg size of LOB and non-LOB,
and max/min/avg size of total row,
determine Chained or Migrated…
UCSC: July, 2009
Block It Out: PCTFREE
9
Row Length Info (credit to Robert James Hanrahan)
Use vsize and dbms_lob functions to capture
column lengths for each row:
WITH length_of_each_row AS (
select
3+(
nvl(vsize(id),0)+1 +
nvl(vsize(name),0)+1 +
nvl(vsize(lockinfo),0)+1 + nvl(vsize(modified),0)+1 +
nvl(vsize(summary),0)+1 + nvl(vsize(attr1),0)+1 +
nvl(vsize(attr2),0)+1 +
nvl(vsize(attr3),0)+1 +
nvl(vsize(attr4),0)+1 +
nvl(vsize(attr5),0)+1 +
) "NonLOB_lngth",
nvl(dbms_lob.getlength(xml),0)+1 "LOB_lngth"
nvl(vsize(counter),0)+1
from waveset.userobj )
[...]
UCSC: July, 2009
Block It Out: PCTFREE
10
Row Length Info (credit to Robert James Hanrahan)
SELECT
round(avg("NonLOB_lngth"))
"avg_NonLOB_lngth",
round(avg("LOB_lngth"))
"avg_LOB_lngth",
round(avg("NonLOB_lngth"+"LOB_lngth")) "avg_Total_lngth",
max("NonLOB_lngth")
"max_NonLOB_lngth",
max("LOB_lngth")
"max_LOB_lngth",
max("NonLOB_lngth"+"LOB_lngth")
"max_Total_lngth",
min("NonLOB_lngth")
"min_NonLOB_lngth",
min("LOB_lngth")
"min_LOB_lngth",
min("NonLOB_lngth"+"LOB_lngth")
"min_Total_lngth"
FROM (length_of_each_row);
UCSC: July, 2009
Block It Out: PCTFREE
11
Row Length Results
avg_NonLOB_lngth 562
avg_LOB_lngth 1030
avg_Total_lngth 1592
max_NonLOB_lngth 726
max_LOB_lngth 2169
max_Total_lngth 2826
min_NonLOB_lngth 152
min_LOB_lngth
244
min_Total_lngth 396
Nothing beyond 8K block size, all are migrated.
LOB is consistently 2/3 of total, not worth
doing out of line.
UCSC: July, 2009
Block It Out: PCTFREE
12
Basics: PCTFREE in Data Block

PCTFREE reserves space in data block for
future growth – updates
UCSC: July, 2009
Block It Out: PCTFREE
13
Fix: PCTFREE





PCTFREE of 50 allows average length to double
CHAIN_CNT showed a quarter of table with
migration, 42K out of 162K rows
Allow for max growth on multiple rows within one
block
What is growth pattern? Start very small?
How much from LOB vs. non-LOB?
Our situation: assume close to double over time
(LOB proportional), need PCTFREE of ~40-50
UCSC: July, 2009
Block It Out: PCTFREE
14
Fix Method: PCTFREE

Steps for fix:
 Drop indexes
 Rename table (no dependencies found)
 CTAS to original table name with PCTFREE
of 40: handle existing rows
 Then PCTFREE of 50 for new rows
 Can drop renamed table, purge later
 Recreate indexes on CTAS table,
PCTFREE of 20
 Monitor CHAIN_CNT in future
UCSC: July, 2009
Block It Out: PCTFREE
15
PCTUSED (legacy) vs. ASSM

PCTUSED (default of 40) says to not put more
rows in block until below this setting
UCSC: July, 2009
Block It Out: PCTFREE
16
PCTUSED vs. ASSM





PCTUSED uses Free List Management
PCTFREE + PCTUSED < 100
PCTUSED is moot point when Auto Segment
Space Management (ASSM) is used at
Tablespace level
 Default in recent releases
 PCTUSED is NULL then in dictionary
ASSM is only in LMT – Locally Managed Tblpsc
ASSM allows Segment Shrink feature, but
Shrink does not pertain to our Migration problem
UCSC: July, 2009
Block It Out: PCTFREE
17
ASSM in Data Block

Status for freeness – 6 possibilities
 0 = unformatted
 1 = logically full
 2 = 0-25% free
 3 = 25-50% free
 4 = 50%-75% free
 5= 75-100% free
UCSC: July, 2009
Block It Out: PCTFREE
18
ASSM in Data Block

When a block is 90% full, Freeness State will be
set to 0-25% free
 If the block freeness drops below PCTFREE,
freeness state will go to "FULL" regardless of
percentage of free space in your block -PCTFREE is what matters
UCSC: July, 2009
Block It Out: PCTFREE
19
Index Considerations

PCTUSED was never an option for Indexes
 PCTUSED and ASSM can be considered for
data blocks, but does not pertain to index blocks
 Index block is horse of a different stripe!
 Data/Index block can use same tablespace
building material, but they have different DNA
UCSC: July, 2009
Block It Out: PCTFREE
20
Index Characteristics (adapted from AskTom)

PCTFREE is used *only* when index is created
 After creation, index block is allowed to be
completely filled as part of normal operation, so
PCTFREE becomes meaningless at that point
UCSC: July, 2009
Block It Out: PCTFREE
21
Data block is a heap,
rows can go anywhere
UCSC: July, 2009
Block It Out: PCTFREE
22
Index Block is in a structure (b-tree example)
UCSC: July, 2009
Block It Out: PCTFREE
23
Index entry must go in correct location
Values for an indexed MOOD column
-- alpha order:
Angry Apathetic Greedy
Happy Hopeful Mellow Overjoyed
UCSC: July, 2009
Block It Out: PCTFREE
24
Data Block Update = delete/insert for
associated index, index entry moves
“Mellow” is updated to “Anxious”, entry shifts to
correct alpha location:
Angry Apathetic Greedy
Angry Anxious Apathetic
Greedy
Happy Hopeful Mellow Overjoyed
Happy Hopeful
UCSC: July, 2009
Block It Out: PCTFREE
Overjoyed
25
When index entry grows,
space within block is shifted
“Mellow” is now gone.
“Happy” is updated to “Happiest”, stays in same
alpha location:
Angry Anxious Apathetic
Greedy
Happy Hopeful
Overjoyed
Happiest Hopeful
Overjoyed
UCSC: July, 2009
Block It Out: PCTFREE
26
Index Characteristics

When growth no longer fits, two possibilities:
 Entry is at end of block
 Entry is anywhere else, not at end
UCSC: July, 2009
Block It Out: PCTFREE
27
Entry on right end can not fit:
~90-10 split to new/empty block
“Greedy updated to “Greediest”:
Angry Anxious Apathetic
Greediest
Happiest Hopeful Overjoyed
UCSC: July, 2009
Block It Out: PCTFREE
28
Entry in middle can not fit:
~60-40 split to new/empty block
“Anxious” updated to “Argumentative”:
Angry Argumentative
Apathetic Greedy
Happiest Hopeful Overjoyed
UCSC: July, 2009
Block It Out: PCTFREE
29
Index Characteristics

Split is very high cost operation (Doc 30405.1)
 High PCTFREE (used at index creation only!)
helps minimize future splits
 How do table values arrive (i.e. inserts)?
Two possibilities:
 Random order
 Sequential
UCSC: July, 2009
Block It Out: PCTFREE
30
Index Characteristics: Sequential

Value is always GREATER THAN all values that
existed at index creation time,
aka "a monotonically increasing value”
-- examples:
 Primary Key using Oracle Sequence
 Activity date column using SYSDATE
 Inserts on right side can cause buffer busy waits
 Monotonically decreasing value is same effect
UCSC: July, 2009
Block It Out: PCTFREE
31
Many Deletes after Sequential Arrival

Index space is *not* reused unless block
becomes totally empty, because no values
would logically fit into that data structure
09-AUG-1974
17-OCT-1989
…Blocks with entries far in the past are nearly empty.
Blocks with recent entries are mostly full…
04-JUL-2009
05-JUL-2009
06-JUL-2009
07-JUL-2009
08-JUL-2009
09-JUL-2009
10-JUL-2009
11-JUL-2009
UCSC: July, 2009
Block It Out: PCTFREE
32
Index Characteristics with many deletes

For many deletes on sequential (monotonic):
 Rebuild *might* make sense
 Coalesce ("online friendly") better
 For data arriving in random order:
 Index rebuild could mean unnecessary splits,
unless sufficient PCTFREE is included
 Natural “puffiness”
UCSC: July, 2009
Block It Out: PCTFREE
33
Table/Index Scenarios

If table is empty when index is created, and data
filled in later, PCTFREE on index has no bearing
because index will fill blocks completely as part of
normal operation
 If adding an index to a populated table that is on
monotonic sequence, PCTFREE = 0 is acceptable
 If adding an index to a populated table that is
random insert/update/delete, no perfect number
for PCTFREE, not related to value for data block
UCSC: July, 2009
Block It Out: PCTFREE
34
Final Points

Larger index block size can help reduce Height,
but will not help data block migration
 PCTFREE choice for our Problem:
 Data block:
40 during migration fix,
50 after that: more blocks to read in FTS,
but minor compared to faulty CBO plan
 Index block:
20 (indexes recreated as part of fix)
-- Will prevent block splits for a while
-- Index block splits won’t affect CHAIN_CNT
UCSC: July, 2009
Block It Out: PCTFREE
35
Don’t get caught by surprise!
UCSC: July, 2009
Block It Out: PCTFREE
36
A&Q


Acknowledgements for reference material: Tanel Poder,
Tom Kyte (adapted from thread in AskTom)
A&Q
Answers: Wisdom to share?
Questions?
UCSC: July, 2009
Block It Out: PCTFREE
37