1 / 18

Performance Tuning

Performance Tuning. Compiled from: Oracle Database Administration, Session 13, Performance, Harvard U Oracle Server Tuning Accelerator, David Scott, Intec. Case Study #1. Define the BUSINESS problem The GUI is too slow; a loss in productivity $$$ Measure the pain

teneil
Download Presentation

Performance Tuning

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. 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.

More Related