- A Brief History of Oracle Performance Tuning
- Moving Beyond a Symptomatic Approach
- Stage 1: Minimizing the Application Workload
- Stage 2: Reducing Contention and Bottlenecks
- Stage 3: Reducing Physical IO
- Stage 4: Optimizing Disk IO
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Stage 1: Minimizing the Application Workload
Our first objective is to minimize the application's demands on the database. We want the database to satisfy the application's data requirements with the least possible processing. In other words, we want Oracle to work smarter, not harder.
Broadly speaking, we use two main techniques to reduce application workload:
- Tuning the application code—This might involve changing application code—C#, Ruby or Java—so that it issues fewer requests to the database (by using a client-side cache, for instance). However, more often this will involve rewriting application SQL and/or PL/SQL.
- Modifying the physical implementation of the application's databases—This might involve indexing, denormalization, or partitioning.
Chapters 4 through 14 cover in detail the various techniques we can use to minimize application workload. Specifically
- Structuring an application to avoid overloading the database—Applications can avoid making needless requests of the database and can be architected to minimize lock and other contention.
- Using best practices when communicating with the Oracle server—The programs that communicate with Oracle can be designed and implemented to minimize database round trips and unnecessary requests.
- Optimizing the physical database design—This includes indexing, denormalization, partitioning, and other ways of physically structuring data to reduce the work required to execute SQL requests.
- Optimizing the Oracle query optimizer—By correctly configuring the collection of optimizer statistics, over-riding optimizer plans when necessary, and instituting ongoing monitoring of SQL performance.
- Tuning the performance of individual SQL statements—This might involve changing the SQL execution plan using hints, stored outlines, profiles, and SQL rewrites.
- Using parallel SQL capabilities—This allows you to apply multiple processes to the SQL execution.
- Tuning and using PL/SQL programs—You can use PL/SQL used in certain circumstances to improve application performance, and PL/SQL programs present unique tuning issues and opportunities.
These techniques not only represent the logical place to start in our tuning efforts, but they also represent the techniques that provide the most dramatic performance improvements. It's not at all uncommon for SQL tuning to result in performance improvements of 100 or even 1,000 times: improvements that you rarely see when tuning contention, optimizing memory, or adjusting physical disk layout.