Slide 1

Steve Lewis DBA Express Scripts Steven.lewis@express-scripts.com Slide 2 Slide 3 Why Tune Performance ? Slide 4 ORACLE is: Unbreakable Slide 5 Why Tune Performance ? ORACLE is: Unbreakable Easily Administered Slide 6 Why Tune Performance ? ORACLE is: Unbreakable Easily Administered Tunes Itself Slide 7 Why Tune Performance ? 2 Reasons ~ Slide 8 Why Tune Performance ? 2 Reasons 1. Application Sensitivity Slide 9 Why Tune Performance ? 2 Reasons 1. Application Sensitivity Get off the hot seat Slide 10 Why Tune Performance ? 2 Reasons 1. Application Sensitivity Get off the hot seat 2. Increase Capacity ~ Slide 11 Increase Capacity - Hardware Hardware Costs $$$s (CPUs / server) Ancillary Research / Current Hardware / Load Forecast Floor space / Hosting fees Electricity Slide 12 Increase Capacity - Hardware Software Costs OS : server + CPUs Apps / Utilities : server + CPUs Yearly Maintenance Slide 13 Increase Capacity - Hardware Environment Costs (70 vs. 35) Network maintenance SAN maintenance Software Releases Software Patching / Backout Slide 14 Increase Capacity - Hardware Application Performance Cost ~ Slide 15 Increase Capacity - Hardware Application Performance Cost Runs Hotter Hardware orders typically trail resource problems and have a long lead time Application Customer Management MML fire drill App Team DBA Team Network Team San Team SA s Slide 16 Increase Capacity Tuning vs. Hardware Slide 17 Increase Capacity - Tuning When ? AWR Proactive (15 top queries = 2x) Instance Load, I/O, CPU, Network Traffic OEM / Grid Control CPU Thresholds [High Level view] Slide 18 Increase Capacity - Tuning CPU Thresholds Slide 19 Slide 20 Slide 21 ~ Slide 22 Standard Deviation Slide 23 3 Sigma zz Percentage within Range 0.67450% 168.27% 1.64590% 1.96095% 295.45% 2.57699% 399.73% Slide 24 Standard Deviation = 5 Slide 25 Standard Deviation = 6 Slide 26 Standard Deviation = 1 Slide 27 Case 1 Select lname, fname, address, zip From customer Where lname = SMITH; Slide 28 Case 1 Select lname, fname, address, zip From customer c Where lname = SMITH; /*+ parallel (c, 8) */ Slide 29 Case 1 Select lname, fname, address, zip From customer c Where lname = SMITH; NOT /*+ parallel (c, 8) */ Slide 30 Case 2 Select customer_nbr, order_nbr, order_date From orders Where order_date > sysdate -1; Slide 31 Case 2 Select customer_nbr, order_nbr, order_date From orders Where order_date > sysdate -1; 1 Million orders going back 3 years No Index on order_date Slide 32 Case 3 Select /*+ index (e, employee_loc) */ employee_nbr, address, location From employee Where location = HOUSTON; Plan showed table scan Added location index Forced index with hint 100,000 employees; in Dallas, in Houston Slide 33 Case 3 Table Scan is the worst performance killer. Slide 34 Case 3 Table Scan is the worst performance killer. If < 1% of table, use keyed read If > 1% of table, use table scan 1% keyed read = 100% table scan Table scan = 100x more efficient than a keyed read Worst performance killer is using an index when you should not - Up to a 100x resource mistake Slide 35 Case 3 Select /*+ index (e, employee_loc) */ employee_nbr, address, location From employee Where location = HOUSTON; Plan showed table scan Added location index Forced index with hint 100,000 employees; in Dallas, in Houston 50x Mistake Slide 36 Case 4 Select /*+ first_rows hash(e) full(l) */ employee_nbr, address, e.location, property_taxes Fromemployee e, location l Where e.location = l.location; Slide 37 Strange Cases 1. Oracle Partition gather_stats 2. Packaged Apps 3. Non-AWR resources Slide 38 Barriers to Performance Tuning for Capacity 1. Pressure for quick decisions and solutions that may not make long term sense. You stage future trouble. 2. All Technical barriers can be overcome. 3. Political Barriers Customer Complaints + MMLs = Hardware 2x Tuning (MMLs + Complaints) = why spend time tuning ? May be adding hardware in other areas Strong Technical leaders and middle management advocates Ive been fortunate in this area Slide 39 Conclusion 2 ways to address dramatic (2x) load increases Tuning and Hardware 2 different reasons for performance tuning Application Sensitivity Database Capacity Monitoring for max capacity warnings AWR CPU mean + 3 sigma Tuning cases and performance pitfalls Barriers to Performance Tuning for Capacity Slide 40 Questions ?