How many times have you been called for a Data Warehouse running task that is taking too long? The underlying SQL statement has been running for some hours now and the SLA to the business is under risk! Does it sound familiar?
If you have ever worked as a DBA, Support Engineer, or even a Developer for a production data warehouse then the above situation must sound quite familiar. Actually in real-world large data warehouses solving SQL performance problems is a daily task. A task that quite often we must perform during "anti-social" hours (e.g., after midnight or very early in the morning, or during weekends etc.).
Recently, I gave a presentation to our DW development and application support team regarding SQL tuning techniques for solving such everyday performance problems for our Coorporate Data Warehouse. I have uploaded my presentation titled "Oracle SQL Tuning for Day to Day Data Warehouse Support" at slideshare, in order to share it with other DW professionals and DBAs that deal with similar problems.
The presentation describes the most popular methods employeed today by DW staff, in order to cope with such performance problems and provides best-practice recommendation based on our real-world experience. Some of the topics described in the presentation have been described in detail in previous posts of this blog, like "how we can detect a change in the execution plan", "how to use Active Session History (ASH) and SQL Monitoring to detect the most time-consuming operation in a plan", "the relation between join ordering and join hints" etc. We provide also a set of scripts for performing the methods presented that are available at our github repository.
Enjoy!
If you have ever worked as a DBA, Support Engineer, or even a Developer for a production data warehouse then the above situation must sound quite familiar. Actually in real-world large data warehouses solving SQL performance problems is a daily task. A task that quite often we must perform during "anti-social" hours (e.g., after midnight or very early in the morning, or during weekends etc.).
Recently, I gave a presentation to our DW development and application support team regarding SQL tuning techniques for solving such everyday performance problems for our Coorporate Data Warehouse. I have uploaded my presentation titled "Oracle SQL Tuning for Day to Day Data Warehouse Support" at slideshare, in order to share it with other DW professionals and DBAs that deal with similar problems.
The presentation describes the most popular methods employeed today by DW staff, in order to cope with such performance problems and provides best-practice recommendation based on our real-world experience. Some of the topics described in the presentation have been described in detail in previous posts of this blog, like "how we can detect a change in the execution plan", "how to use Active Session History (ASH) and SQL Monitoring to detect the most time-consuming operation in a plan", "the relation between join ordering and join hints" etc. We provide also a set of scripts for performing the methods presented that are available at our github repository.
Enjoy!
Very informative article. Thanks for sharing this valuable information
ReplyDeleteOracle ATG Services