1 / 29

Oracle BIWA SIG Basics

Oracle BIWA SIG Basics. Worldwide association of 2000 professionals interested in Oracle Database-centric business intelligence, data warehousing, and analytical products, features and options. Membership is FREE Open forum to foster success in use and development of Oracle BIWA products.

fletcher
Download Presentation

Oracle BIWA SIG Basics

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. Oracle BIWA SIG Basics • Worldwide association of 2000 professionals interested in Oracle Database-centric business intelligence, data warehousing, and analytical products, features and options. • Membership is FREE • Open forum to foster success in use and development of Oracle BIWA products. • BIWA’s goals include sharing: “best practices” and “novel and interesting use cases” of Oracle BIWA-centric technology. • See Mission Statement and Charter at oraclebiwa.org.

  2. Next Oracle BIWA SIG Conference BIWA Training Daysat COLLABORATE 10 – IOUG Forum • “Get Analytical with BIWA Training Days” • April 18-22, 2010 • Mandalay Bay Convention Center, Las Vegas, Nevada • REGISTER with Offer Code “BIWA2010” for IOUG Member Discount Rate • See oraclebiwa.org for detailsand links

  3. Top 5 Tips on:Reducing Storage Cost while Improving Performance Jean-Pierre Dijcks Data Warehouse Product Management

  4. Agenda • Tip 1: Appropriate Hardware • Tip 2: Tier your Storage • Tip 3: Partition your Data • Tip 4: Compress your Data • Tip 5: Think, Plan and Design • Q&A

  5. Agenda • Tip 5: Think, Plan and Design • Tip 1: Appropriate Hardware • Tip 2: Tier your Storage • Tip 3: Partition your Data • Tip 4: Compress your Data • Software Forces a Paradigm Shift • Q&A

  6. Tip #5: Think, Plan and Design • Understand the requirements • Data retention rates • What to do with the older data • What are you doing with the newer data • Performance requirements for all data (not just the latest stuff) • Plan for the worst (kind of) • What is the performance goal and can you still achieve that in 6 months or 2 years • What is the data retention rate and can you deal with this at double your data size? • Design the system to still work tomorrow

  7. Tip #5: Think, Plan and Design • Understand or learn about the trends • Hardware • Low-price commodity servers • High refresh rates of components (CPUs etc.) • Ever growing sizes, speeds at ever dropping prices • Software • More aligned with hardware • Push down into storage of data intensive tasks • Consolidation and more workloads are thrown at software

  8. Tip #5: Think, Plan and Design • An interpretation of the meaning of these trends is: • We will see self-provisioning of vast resources by (end) users • This will be achieved by a flexible grid of resources being made available • More people will get and use more compute power • More and more workloads are run on the “same” hardware • Integrated software services will provide the value add for these users and make consolidation work… This has major implications for all of us… I think…

  9. Tip #1: Balance your HardwareDriver: Flexibility in Performance • Solid State Disks • Flash Cards and Disks • Memory • Etc… Higher Upward Speed Cost ILM Lower Downward • 2TB 10K RPM SATA Disks • Other high capacity media

  10. Tip #1: Balance your HardwareDriver: Flexibility in Performance Memory < 10% of your data Flash Technology 60% of yourqueries SAS drives < 50% of your data 35% of yourqueries SATA drives 100% of your data Off-line Data Archives 5% of your queries Disclaimer: Illustration purposes only!

  11. Tip #2: Tier your StorageDriver: Cost and Performance Memory SpeedandCost 0.25 5 Flash Technology 0 10 SAS drives 0 85 SATA drives 99.75 0 => cost indicator % of capacity % of capacity Performance Capacity Disclaimer: Illustration purposes only!

  12. Tip #2: Tier your StorageDriver: Cost and Performance Memory SpeedandCost 0.5 1 Flash Technology 1.5 5 SAS drives 0 94 SATA drives 98 0 => cost indicator % of capacity % of capacity Balanced Perform Balanced Capacity Disclaimer: Illustration purposes only!

  13. Tip #1 and Tip #2Balance and Flexibility Very diverse workloads + same hardware = need for flexibility • Create a grid of compute and storage resources • Allow for a hierarchy of storage solutions within the grid • Balance the hardware to: • Achieve acceptable performance for the majority workload • Achieve great performance for mission critical actions • Achieve a reasonable price / performance balance • Do not size just for performance, nor just for capacity

  14. Tip #3: Partition your DataImpact: Performance and Ease of Maintenance • Maintenance: • Easier to work on smaller chunks of data • Allows specification of separate management and performance strategies on a smaller chunk • Performance: • In maintenance operations (as shown above) • By reducing the data volume to scan • A potential way of allowing parallel operations to optimize data processing

  15. The Concept of PartitioningSimple yet powerful SALES SALES SALES Europe USA Jan Feb Jan Feb Large Table Difficult to Manage Partition Divide and Conquer Easier to Manage Improve Performance Composite Partition Higher Performance More flexibility to match business needs

  16. Only the 2 relevant partitions are read Partition for PerformancePartition Pruning Sales Table Q: What was the total sales amount for May 20 and May 21 2009? 5/17 5/18 Select sum(sales_amount) From SALES Where sales_date between to_date(‘05/20/2009’,’MM/DD/YYYY’) And to_date(‘05/22/2009’,’MM/DD/YYYY’); 5/19 5/20 5/21 5/22

  17. Select sum (sales_amount) From Sales s, Customer c Where s.cust_id = c.cust_id; Sub part 1 Sub part 1 Sub part 1 Sub part 1 Sub part 2 Sub part 2 Sub part 2 Sub part 2 Sub part 3 Sub part 3 Sub part 3 Sub part 3 Sub part 4 Sub part 4 Sub part 4 Sub part 4 Parallel ProcessingPartition Wise Join Sales Customer Range partition May 18th 2008 A large join is divided into multiple smaller joins, each joins a pair of partitions in parallel Both tables have the same degree of parallelism and are partitioned the same way on the join column (cust_id)

  18. Exadata Storage IndexTransparent I/O Elimination with No Overhead Exadata Storage Indexes maintains summary information about table data in memory Stores MIN and MAX values of filter columns Typically one index entry for every MB of disk Eliminates disk I/Os if MIN and MAX can never match “where” clause of a query “Negative index” Completely automatic and transparent MIN ship_date = ’19-SEP-2009’ MAX ship_date = ’07-OCT-2009’ MIN ship_date = ’01-OCT-2009’ MAX ship_date = ’03-NOV-2009’ Select * from orders where ship_date < ’30-SEP-2009’ Only first set of rows can match 18

  19. Tip #3: Partition your DataImpact: Performance and Ease of Maintenance Breaking up a large data set delivers both performance and ease of maintenance • This does not mean “Apply Partitioning”, there is more that Oracle can do to allow better performance: • Partitioning – of course… To improve scan speeds and maintenance operations • Storage Indexes – To improve Scan Speeds • Smart Scans – To reduce data moved around

  20. Tip #3: Partition your DataDriver: Flexibility in Performance Improve scan rates to leverage slower storage tiers = Downward Mobility 95% of yourqueries < 75% of your data SAS drives SATA drives 100% of your data Off-line Data Archives 5% of your queries Disclaimer: Illustration purposes only!

  21. Tip #4: Compress your DataImpact: Cost and Performance Compression in Oracle: • “Data Warehouse” compression • 2 – 3x compression ratio • Included in DB license • “OLTP” compression • 3 – 4x compression ratio • Database Option – Advanced Compression • Exadata Hybrid Columnar Compression • 10 – 50x compression ratio • Included in Oracle Exadata license

  22. Tip #4: Compress your DataHybrid Columnar Compression Data is grouped by columnand then compressed Query Mode for data warehousing Optimized for speed 10X compression typical Scans improve proportionally Archival Mode for infrequently accessed data Optimized to reduce space 15X compression is typical Up to 50X for some data

  23. Tip #4: Compress your DataUsage Matrix • Apply compression on a per Partition or higher level • Change compression over the lifetime of a Partition or Table • Both EHCC and DW compression will start to “decompress” when data is updated

  24. Tip #4: Compress your DataApplying Compression across Partitions Day 1 Day 2 Day 8 Day 9 Day 10 Month 7 Month 8 OLTP Compression EHCCQuery Mode EHCCArchive Mode

  25. Tip #4: Compress your Data Improving performance and lowering cost Move more data onto high performance storage tiers = Upward Mobility Memory < 30% of your data Flash Technology 75% of yourqueries SAS drives 100% of your data 25% of yourqueries SATA drives Disclaimer: Illustration purposes only!

  26. Software forces a Paradigm ShiftApplying software changes the balance • Adding software into the mix fundamentally changes the way we use and think about storage • Software driven partitioning of data changes the cost per scanned TB in relation to total data volume • Compression changes the cost per TB stored significantly • Compression changes the cost per scanned TB in relation to non-compressed data

  27. Compounding ReturnsLess Storage, Better Performance 10 TB of user data Requires 10 TB of IO 1 TB with compression 100 GB with partition pruning Sub-second Response times 20 GB with Storage Indexes 5 GB with Smart Scans

  28. ConclusionSoftware and Hardware is the Solution With today’s technology you can improve performance while reducing storage cost • Building a hierarchy of storage solutions allows you to be: • More Flexible • Deliver better performance for lower cost • Partitioning and compression are technologies that change the hardware status-quo • Partitioning allows slower HW deliver better performance • Compression allows faster HW to hold more data and delivers better performance

  29. Q & A

More Related