Overview of Oracle9i Database Performance Tuning.ppt (Oracle9i Database)

1 Overview of Oracle9i Database Performance Tuning Copyright © Oracle Corporation, 2002. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Define the roles associated with the database tuning process • Describe the dependencies between tuning in different development phases • Describe service level agreements • Identify tuning goals • Identify common tuning problems • Employ tuning activities during development and production • Balance performance and safety trade-offs 1-2 Copyright © Oracle Corporation, 2002. All rights reserved. Tuning Questions • Who tunes? – – – – • • 1-3 Application designers Application developers Database administrators System administrators What to tune? How much tuning? Copyright © Oracle Corporation, 2002. All rights reserved. Tuning Phases Tuning can be divided into different phases: • Application design and programming • Database configuration • Adding a new application to an existing database • Troubleshooting and tuning 1-5 Copyright © Oracle Corporation, 2002. All rights reserved. Tuning Goals Tuning goals are usually specified in terms of: • Reducing or eliminating waits • Accessing the least number of blocks • Caching blocks in memory • Minimizing response time • Increasing throughput • Increasing load capabilities • Decreasing recovery time • Instance hit percentages 1-6 Copyright © Oracle Corporation, 2002. All rights reserved. Common Performance Problems • Bad session management – Limits scalability to a point that cannot be exceeded – Makes the system one or two orders of magnitude slower than it should be • • Bad cursor management Bad relational design – Unnecessary table joins performed – Usually a result of trying to build an object interface to relational storage 1-7 Copyright © Oracle Corporation, 2002. All rights reserved. Tuning Steps During Development • • • • • • 1-8 Tune the design Tune the application Tune memory Tune I/O Tune contention Tune the operating system Copyright © Oracle Corporation, 2002. All rights reserved. Collect a Baseline Set of Statistics A baseline set of statistics is used to: • Provide a set of statistics collected when the system was operating within the bounds set • Create a hypothesis about what has changed on the system 1-9 Copyright © Oracle Corporation, 2002. All rights reserved. Tuning Steps for a Production Database 1. 2. 3. 4. 5. 6. 1-10 Define the problem. Examine the host system and Oracle statistics. Consider some common performance errors. Build a conceptual model. Implement and measure the change. Check that the bottleneck has been resolved. Copyright © Oracle Corporation, 2002. All rights reserved. Database Server Tuning Methodology • • • • 1-12 Check alert log and trace files for errors. Check the parameter file for any diagnostic or inappropriate parameter setting. Check memory, I/O, and CPU usage. Identify processes with resource usage anomalies. Identify and tune SQL statements that are heavy consumers of CPU or I/O. Copyright © Oracle Corporation, 2002. All rights reserved. Database Server Tuning Methodology Tune response time: • Analyze system performance in terms of work done (CPU or service time) versus time spent waiting for work (wait time). • Determine which component consumes the greatest amount of the time. • Drill down to tune that component if appropriate. 1-13 Copyright © Oracle Corporation, 2002. All rights reserved. Performance Versus Safety Trade-Offs Factors that affect performance: • Multiple control files • Multiple redo log members in a group • Frequent checkpointing • Backing up datafiles • Performing archiving • Block check numbers • Number of concurrent users and transactions 1-14 Copyright © Oracle Corporation, 2002. All rights reserved. Summary In this lesson, you should have learned how to: • Create a good initial design • Define a tuning methodology • Perform production tuning • Establish quantifiable goals • List tuning problems • Decide between performance and safety 1-15 Copyright © Oracle Corporation, 2002. All rights reserved.

Overview of Oracle9i Database Performance Tuning

Nội dung