1 / 37

Blocking It Out: Breaking (PCT)Free from Chains

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

lalo
Download Presentation

Blocking It Out: Breaking (PCT)Free from Chains

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. Blocking It Out:Breaking (PCT)Free from Chains Philip Rice Univ. of California Santa Cruz

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. Basics: PCTFREE in Data Block • PCTFREE reserves space in data block for future growth – updates Block It Out: PCTFREE

  14. 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

  15. 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

  16. PCTUSED (legacy) vs. ASSM • PCTUSED (default of 40) says to not put more rows in block until below this setting Block It Out: PCTFREE

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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

  22. Data block is a heap, rows can go anywhere Block It Out: PCTFREE

  23. Index Block is in a structure (b-tree example) Block It Out: PCTFREE

  24. Index entry must go in correct location Values for an indexed MOOD column-- alpha order: Block It Out: PCTFREE

  25. 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

  26. 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

  27. 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

  28. Entry on right end can not fit:~90-10 split to new/empty block “Greedy updated to “Greediest”: Block It Out: PCTFREE

  29. Entry in middle can not fit:~60-40 split to new/empty block “Anxious” updated to “Argumentative”: Block It Out: PCTFREE

  30. 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

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

  36. Don’t get caught by surprise! Block It Out: PCTFREE

  37. 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

More Related