Performance Tuning

1. Performance Tuning Compiled from: Oracle Database Administration, Session 13, Performance, Harvard U Oracle Server Tuning Accelerator, David Scott, Intec

2. Case Study #1 • Define the BUSINESS problem • The GUI is too slow; a loss in productivity $$$ • Measure the pain • Screen XYZ in the GUI takes 47 seconds • 3000 users use this screen at each login • Most other screens are fine • Identify the problem component • Stopwatch timing of XYZ response is 47 seconds • Same SQL query takes 47 seconds • Query identified as culprit

3. Case Study #1 (cont’d) • Find the root cause • Inefficient query • Investigate and weigh solutions • Multiple versions of query compared • Results must match original • Fix the problem • Replace query in Screen XYZ with tuned version • Measure the results • Query returns in 2 seconds • ROI: 3000 users * 45 seconds saved * Avg salary @ $10/hour * 260 business days/year = $97,500

4. Performance Tuning • Trade-offs Between Response Time and Throughput • Goals for tuning vary, depending on the needs of the application • OLTP vs. OLAP applications (which one requires which performance?)

5. Performance Definition • Response time = service time + wait time • We can increase performance two ways: • by reducing service time • by reducing wait time

6. Performance Definition • System throughput equals the amount of work accomplished in a given amount of time • Two techniques of increasing throughput exist • Get more work done with the same resources (reduce service time) • Get the work done quicker by reducing overall response time (reduce wait time)

7. Performance Definition • The service time for a task may stay the same, but wait time increases as contention increases • If many users are waiting for a service that takes 1 second, then the tenth user must wait 9 seconds for a service that takes 1 second

8. Where’s the Wait? Database Network ClientApplication CPU Disk

9. Critical Resources • Resources such as CPUs, memory, I/O capacity, and network bandwidth are key to reducing service time • Adding resources can give higher throughput and swifter response times

10. SQL Processing Architecture

11. Parser • The parser performs two functions: • Syntax analysis: This checks SQL statements for correct syntax • Semantic analysis: Checks that the current database objects and object attributes are correct

12. Optimizer • The optimizer is the heart of the SQL processing engine. The Oracle server provides two methods of optimization: rule-based optimizer (RBO) and cost-based optimizer (CBO). • Note: As of Oracle 10g, RBO is no longer supported.

13. Row Source Generator • The row source generator receives the optimal plan from the optimizer • It outputs the execution plan for the SQL statement • A set of rows returned by an execution step is called a row source • The execution plan is a collection of row sources, structured in the form of a tree

14. SQL Execution • The combination of steps required to execute a statement is called an execution plan • An execution plan includes an access method for each table that the statement accesses and an ordering of the tables (the join order)

15. Example 1 • How will the following SQL statement be processed? • SELECT * • FROM BLLIM.Project; • Does it matter if an index exists?

16. Example 2 • How will the following SQL statement be processed? • SELECT * • FROM BLLIM.Project • WHERE status = ‘C’; // ‘C’ for complete • Does it matter if an index exists?

17. Example 3 • How will the following SQL statement be processed? • SELECT * • FROM BLLIM.Project • WHERE status = ‘C’; // ‘C’ for complete • Does it matter if an index exists? • What if the distribution of status is the following (skewed)? • 70 projects have ‘C’, 10 have ‘I’, and 20 have ‘A’

18. Example 4 • How will the following SQL statement be processed? • SELECT pjTitle • FROM BLLIM.Project • WHERE status = ‘C’; // ‘C’ for complete • Does it matter if an index exists? • Assume that you have an index for status and another one for pjTitle.