380 likes | 519 Views
Blocking It Out: Breaking (PCT)Free from Chains. Philip Rice Univ. of California Santa Cruz. Case Study: Overview of Issues. Prompted by performance problem in Identity Management batch process: Query is normally 1000+ statements per hour
E N D
Blocking It Out:Breaking (PCT)Free from Chains Philip Rice Univ. of California Santa Cruz
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 Block It Out: PCTFREE
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…] Block It Out: PCTFREE
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 | --------------------------------------------------------------------------------------------------- Block It Out: PCTFREE
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 | --------------------------------------------------------------------------------------------- Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
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… Block It Out: PCTFREE
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 + nvl(vsize(counter),0)+1 ) "NonLOB_lngth", nvl(dbms_lob.getlength(xml),0)+1 "LOB_lngth" from waveset.userobj )[...] Block It Out: PCTFREE
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); Block It Out: PCTFREE
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. Block It Out: PCTFREE
Basics: PCTFREE in Data Block • PCTFREE reserves space in data block for future growth – updates Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
PCTUSED (legacy) vs. ASSM • PCTUSED (default of 40) says to not put more rows in block until below this setting Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
Data block is a heap, rows can go anywhere Block It Out: PCTFREE
Index Block is in a structure (b-tree example) Block It Out: PCTFREE
Index entry must go in correct location Values for an indexed MOOD column-- alpha order: Block It Out: PCTFREE
Data Block Update = delete/insert for associated index, index entry moves “Mellow” is updated to “Anxious”, entry shifts to correct alpha location: Block It Out: PCTFREE
When index entry grows,space within block is shifted “Mellow” is now gone. “Happy” is updated to “Happiest”, stays in same alpha location: Block It Out: PCTFREE
Index Characteristics • When growth no longer fits, two possibilities: • Entry is at end of block • Entry is anywhere else, not at end Block It Out: PCTFREE
Entry on right end can not fit:~90-10 split to new/empty block “Greedy updated to “Greediest”: Block It Out: PCTFREE
Entry in middle can not fit:~60-40 split to new/empty block “Anxious” updated to “Argumentative”: Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 …Blocks with entries far in the past are nearly empty. Blocks with recent entries are mostly full… Block It Out: PCTFREE
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” Block It Out: PCTFREE
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 Block It Out: PCTFREE
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 Block It Out: PCTFREE
Don’t get caught by surprise! Block It Out: PCTFREE
A & Q • Acknowledgements for reference material: Tanel Poder, Tom Kyte (adapted from thread in AskTom) • A & Q Answers: Wisdom to share? Questions? Block It Out: PCTFREE