1 / 37

Partitioning and Advanced Compression in Oracle 11g

Discover how using Partitioning and Advanced Compression in Oracle 11g can improve performance, save costs, and address the challenges of exploding data growth. Learn about the benefits, impact on table sizes, and performance gains.

hmckeown
Download Presentation

Partitioning and Advanced Compression in Oracle 11g

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. Michael R. Messina, Management Consultant Rolta-TUSC, Oracle Open World 2009 (60 min) ID#: S307992Partitioning, More Cost Savings, Better Performance, Better Management in 11g

  2. Introduction • Michael Messina • Management Consultant with TUSC • Background includes Performance Tuning, High Availability and Disaster Recovery • Using Oracle for approximately 16 years • messinam@tusc.com • www.tusc.com

  3. Audience Experience • How many have utilized Partitioning and what have been some of your experiences? • How many have utilized Table Compression and index compression (prior to 11g Advanced Compression) and what are your thoughts/experiences?

  4. Goals • Touch on industry challenges • Explosive Data Growth • Performance • Costs • Look at 2 new Partitioning Features Introduced with 11g • Examine 11g Advanced Compression and impact using 2 new partitioning features • Show how Partitioning and Advanced Compression together help address some of the industry challenges

  5. Industry Challenges • Exploding Data Growth • Got to keep up • Performance • Query Performance Degradation as data volumes increase • Backup time increases as data volumes increase • Costs - What are the True Costs? • Disk Space Purchase / Backup / Space Management / Power / Cooling • What can we do??

  6. Exploding Data Growth • If you think storing data is a challenge now, it's nothing compared to what it could be in just a few years. Data Growth of 60% is common.

  7. Performance “Storage capacity grows at over 60% per year while performance improves at less than 10% per year. This trend has existed for over 10 years and is expected to continue for the foreseeable future. “, BNET

  8. Releasing your Database Performance

  9. Performance • Ref Partitioning what is advertised • Introduced with Oracle 11g • Improves performance for parent child relationships • Partitions the child with the parent • Interval Partitioning what is advertised • Introduced with 11g • Same performance Benefits as Range partitioning

  10. Manageability • Interval Partitioning • Introduced with 11g • Defined using an interval • Works much like Range Partitioning • Partitions are created as needed eliminates need to manually add partitions. • Ref Partitioning • Introduced with 11g • Simpler partition management, child partitions created automatically when parent partitions are created

  11. True Disk Costs “The cost of managing storage hardware ranges from two to ten times the acquisition cost of the storage hardware.”, BNET ** The above costs are based on 16TB configuration.  Monash University, Cost of Storage – Direct Attached vs. SAN

  12. Compression • Index Compression since 8i • Table Compression since 9i • No Additional License Requirement • Only for direct inserts • Compression Not Maintained with updates and normal inserts • Had to re-org table to re-compress over time. • Advanced Compression 11g • Additional License Requirement • Compression Maintained with all DML activity • No re-orgs required after initial compression

  13. Compression • What can compression accomplish? • Shrink size of tables? • Shrink Size of indexes? • Improve buffer cache utilization? • Improve I/O disk visits? • Improve performance?

  14. What can we do • Reduce Size of Existing? • Can we get a 10%, 20%, 30% reduction? • Reduce Size of Future Data? • Can we impact growth by 10%, 20%, 30% • Minimize performance impact of larger data volumes? • Disk Space, Backup/Recovery, Server Resources • Can we do all this without adding significant management overhead to the DBA?

  15. Ref Partitioning • Examine Space Impact of Partitioning • Show disk space impact partitioned and un-partitioned. • Examine the true performance gain from Ref Partitioning • Demonstrate the partitioned and un-partitioned performance • Demonstrate the partitioned and compressed performance

  16. Ref Partitioning – Un-Partitioned Table Size • ORDERS (78880 rows) SUM(BYTES)/1024 --------------- 4096 • ORDER_ITEMS (499792 rows) SUM(BYTES)/1024 --------------- 16384

  17. Ref Partitioning Impact on Table Sizes • ORDERS (78880 rows) SUM(BYTES)/1024 --------------- 4736 • ORDER_ITEMS (499792 rows) SUM(BYTES)/1024 --------------- 13950 * Surprisingly we see the child table size reduced

  18. Ref - Non Partitioned Table Performance SELECT o.order_date, sum(oi.unit_price*oi.quantity) order_total FROM oe.orders o, oe.order_items oi WHERE o.order_date BETWEEN TO_DATE('01-APR-1999','DD-MON-YYYY') AND TO_DATE('30-JUN-1999','DD-MON-YYYY') AND o.order_id = oi.order_id GROUP BY order_date ORDER BY order_date ; .. 16 rows selected. Elapsed: 00:00:00.93

  19. Ref - Non Partitioned Table Performance Statistics ----------------------------------------- 1 recursive calls 0 db block gets 1967 consistent gets 1964 physical reads 0 redo size 970 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 16 rows processed

  20. Ref Partitioning Impact SELECT o.order_date, sum(oi.unit_price*oi.quantity) order_total FROM oe.orders o, oe.order_items oi WHERE o.order_date BETWEEN TO_DATE('01-APR-1999','DD-MON-YYYY') AND TO_DATE('30-JUN-1999','DD-MON-YYYY') AND o.order_id = oi.order_id GROUP BY order_date ORDER BY order_date ; .. 16 rows selected. Elapsed: 00:00:00.57 * .93 to .57 / 38% Improvement

  21. Ref Partitioning Impact Statistics ----------------------------------------- 44 recursive calls 0 db block gets 1630 consistent gets 1621 physical reads 0 redo size 896 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 16 rows processed * PIO - from 1967 to 1630 / 17% Improvement LIO – from 1964 to 1621 / 17% Improvement

  22. Interval Partitioning • Examine Space Impact of Partitioning • Show disk space impact partitioned and un-partitioned. • Examine the true performance gain from Interval Partitioning • Demonstrate the partitioned and un-partitioned performance • Demonstrate the partitioned and compressed performance

  23. Interval - Non-Partitioned Table Size • Un-Partitioned Table 889,888 Rows SUM(BYTES)/1024 --------------- 45056

  24. Interval Partitioning Impact on Table Size • Partitioned Table 889,888 Rows SUM(BYTES)/1024 --------------- 58816 * 45M to 58M represents and increase in size when table is partitioned.

  25. Interval - Non Partitioned Table Performance • Un-Partitioned Table 889,888 Rows SQL> select deptno, avg(sal) from emp where hiredate between to_date('01-JAN-1982', 'DD-MON-YYYY') and to_date('01-JAN-1983', 'DD-MON-YYYY') group by deptno ; .. Elapsed: 00:00:03.37

  26. Interval - Non Partitioned Table Statistics Statistics -------------------------------- 7 recursive calls 0 db block gets 5548 consistent gets 5674 physical reads 0 redo size 546 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed

  27. Interval Partitioning Impact • Partitioned Table 889,888 Rows SQL> select deptno, avg(sal) from emp_part where hiredate between to_date('01-JAN-1982', 'DD-MON-YYYY') and to_date('01-JAN-1983', 'DD-MON-YYYY') group by deptno ; .. Elapsed: 00:00:01.57 * 3.37 to 1.57 / 53% Improvement

  28. Interval Partitioning Impact Statistics ----------------------------------- 1 recursive calls 0 db block gets 658 consistent gets 652 physical reads 0 redo size 546 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed * LIO – 5548 to 658 / 88% Improvement PIO – 5674 to 653 / 88% Improvement

  29. Accomplished With Partitioning • Positive • Reduced logical I/O • Reduced Physical I/O • Improved elapse time • Negative • Increased the size of the table

  30. Impact of Compression on Size of Ref-Partitioned Tables • ORDERS (78880 rows) SUM(BYTES)/1024 --------------- 4352 * 8% reduction over partitioned table 5% increase on Original table. • ORDER_ITEMS (499792 rows) SUM(BYTES)/1024 --------------- 11520 * 29% reduction over partitioned table 17% reduction over Original table

  31. Impact of Ref Partitioning and Compression Together Elapsed: 00:00:00.43 Statistics ------------------------------------- 1 recursive calls 0 db block gets 413 consistent gets 407 physical reads 0 redo size 896 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 16 rows processed * .43 seconds - 53% improvement to original / 24% improvement partitioned un-compressed

  32. Impact of Compression on Size of Interval Partitioned Table • Partitioned Table 889,888 Rows SUM(BYTES)/1024 --------------- 39168 * 39% reduction on partition and uncompressed table 13% reduction from original table

  33. Impact of Interval Partitioning and Compression Together Elapsed: 00:00:00.59 Statistics --------------------------------- 1 recursive calls 0 db block gets 373 consistent gets 367 physical reads 0 redo size 546 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed * PIO - Original 5548 to 373 a 93% Imprv. Partitioned 658 to 373 a 43% Imprv. LIO - Original 5677 to 367 a 93% Imprv. Partitioned 653 to 367 a 43% Imprv.

  34. Partitioning and Compression Summary • What can partitioning accomplish • Improve Performance • Break large table into chunks reducing I/O • Minimize Management Cost • Utilize interval and Ref partitioning where new partitions are created automatically. • Manage though individual Partitions adding flexibility for Table and index Management • Improve Database backup Performance • Mark tablespaces holding older Data partitions Read-Only as it eliminates the need to backup with each full backup of the database.

  35. Partitioning and Compression Summary • What Can Compression Accomplish? • Reduce Disk Space Costs • Compress partitioned tables reducing the size of tables • Improve Performance • Compress tables to reduce I/O read operations

  36. Partitioning Conclusions • Partitioning can improve I/O utilization • Partitioning can improve performance • Partitioning increases space utilization • Compression reduces space utilization • Compression can improve performance • Compression with partitioning can improve performance more then either of them alone and can reduce space utilization. • Interval Partitioning and Ref Partitioning reduces maintenance impact for partitioning

  37. Questions/Discussion THANK YOU

More Related