Tuning a Very Large Data Warehouse

1. Tuning a Very Large Data Warehouse Pichai Bala

2. About Me • Working in the IT industry for the past 17 years • Working in Oracle since 1993. • Working in Data Warehouse and BI since 2003

3. Disclaimer The views expressed in this presentation are mine and does not represent the organization I work for or the organizations I had worked for in the past. Please always test and validate the ideas presented here in a test environment first.

4. A Day in the life of a DBA

5. How about this one?

6. What the chaos mean for the DBA? • Free buffer waits • enq: KO - fast object checkpoint • enq: TM - contention • db sequential read • CPU time • Logical I/O • Physical I/O

7. What it means to the End Users? • ETL Load/Batch Job Delays • Reporting Delays • Decision Making Delays • Business Analytics Delays • Customer Intelligence Delays • Planning and Forecasting Delays • Key Performance Metrics Delays

8. Data Warehouse is now in Death Bed

9. But, Why?

10. Data Warehouse Vicious Cycle • Data gets deployed • Gains User Acceptance • More Users and More Demands and Needs • Existing Data Grows and New Data gets Deployed …and gets into the Death Spiral

12. Possible Causes…. • Lack of proper and meaningful maintenance • Human errors • Poor Design • Bad SQLs by developers, users • Poor monitoring and scheduling etc..

13. Tuning Strategy • Keep it Simple • Low Intensity Changes with low impact but with high performance benefits • Localized changes • No change in logic • Easy to understand, test and deploy

14. Reduce Wastage • Reduce CPU • Reduce Logical IO • Reduce Physical IO • Reduce UNDO • Reduce Direct Path Reads

15. How it can be done? • Server Tuning • Instance Tuning and Maintenance • Database Tuning and Maintenance • Table Reorganizations/Redefinitions • New Indexes • Regular Statistics Collection • Views • SQL/PLSQL Code Changes • Working with other teams • Educating/Training the users

16. Instance/Database Tuning • SGA Max Size • DB Cache Size • Shared Pool • Large Pool • No. of DB Writers • Redo Log File Size • Typical Init.ORA parameters like QUERY_REWRITE, BITMAP_MERGE_JOIN

17. SQL/PLSQL Tuning • Avoid Clutter • Use Indexes when appropriate • Full Table Scan is not bad • Revisit the code • Cunning code is not always necessary • Work with other teams and business to reduce complexity in code • Avoid Hints

18. Query Results can be wrong • In 10G use ORDER BY whenever GROUP BY is used • Hidden parameter can be enabled with the help of Oracle Support

19. Pillars of the Data Warehouse • Partitioning • Parallelism • Aggregations • Compression • Materialized Views • Read Only Tablespaces • Data Archival

20. Partitioning • Range Partitioning • List Partitioning • Range List Partitioning • Range Hash Partitioning • Hash Partitioning Caveat: Joins beware.

21. Parallelism • Tables can be built parallel • Parallel Indexes • Parallel Hints while loading or querying. • Alter table <xxx> move … parallel (degree 8) …; • Alter table <xxx> split … parallel( degree 4) …; • Create table <xxx> parallel(degree 4)… • Sufficient LARGE_POOL helps greatly

22. Aggregations • Aggregations and MVs are the soul of any DSS • Most BI tools supports Aggregation Awareness • Have multiple aggregations • Aggregations help users with adhoc queries • Daily, Monthly and Yearly Aggregations are very common in most DSS

23. Compression • Saves Disk Space by 40 to 50% • Reduces Logical IO • Reduces Physical IO • Reads will be fast • DMLs will be slow • Compress Table as well as Index • Caveat : You can’t uncompress after the table is compressed ORA-01735: invalid ALTER TABLE option

24. Materialized View • Fast Refresh may be very slow • From 10G MV can be parallel • MVs can be partitioned • MV_CAPABILITY results can be misleading. • ALTER MATERIALIZED VIEW <mv_name> parallel (degree 4 ); For MV Fast Refresh to be successful a Complete Refresh should happen before

25. Exchange Partitions • Very Useful • Dictionary update only • Can’t Exchange a table with bitmap indexes with a partition Partition exchange has issue with BITMAP indexes with the ora error for mismatch indexes 0RA-14098

26. READONLY Tablespaces • Data Warehouse has time variant non-volatile data • Say Range Partition on TIME, and making historic tablespaces READONLY helps Database Checkpoint process

27. Data Archival • With various regulatory and internal requirements data needs to be retained for 2 to 30 years. • Data growth is exponential • Archival is needed to start it small and keep it small • Saves $$$ in Database licenses and maintenance. • Helps the optimizer to get results faster from a smaller set

28. Rolling Partitions • If design permits instead of creating new partitions every time the same partition can be reused again and again. • Like SUNDAY can be reloaded again on the same partition next Sunday. • Rolling Partitions by HOUR or by DAY of the WEEK can be considered • Helps Data Retention Strategies too.

29. Case of HUGE UNDO • More than 30G of UNDO was getting generated for a 1.5G table Fix the code and fix the problem.

30. Misleading V$lock • Blocking locks won’t show in v$lock but locks would exist • Use x$kgllk or x$kglpn to identify and kill the blocking sessions.

31. Package Invalidations • Package gets invalidated but can’t recompile itself because of sessions holding them invisibly • Coding and deployment standards can help

32. ORA-02049: timeout: distributed transaction waiting for lock • Flush the Shared Pool, the failures go away • From 10G you can avoid bounces by flushing buffer_cache and shared_pool

33. Again?

34. Stuck in traffic? Meet the new supercar based on Ferrari that could fly you out of jams.*Only £500,000.

35. Flying Ferrari