Performance tuning for predictable Oracle Applications

This technical article, provided by the International Oracle User Group (IOUG), is a user-organized organization that enhances the level of Oracle database experts and database developers by providing high-quality information, training, networking, and support. This article picks up the "Predictable Oracle Application Performance tuning" written by free David Welch. Click here to become a member of the International Oracle user group to get thousands of technical articles and scientific literature written by Oracle users.

Introduction

We've seen many Oracle applications and e-Business suite installations with huge performance problems. We have come to the conclusion that these installations can be further improved in terms of performance. In other words, the performance is already very high and almost impossible to get the improved installation is very rare.

A controversial issue.

For the product system stack, our bottom end-to-end performance tuning approach is always quick to produce results that are faster than we think of following a broad list of memos. I propose some of the following issues for discussion:

Most of the performance improvement possibilities are at the application level: This conclusion comes from a significant comment on performance tuning on Metalink. This conclusion is not statistically relevant to our empirical performance tuning system stack.

It takes an average of two days: this is the conclusion of the book. But our experience does not support this conclusion. I think it should take at least 12 days to come up with an Oracle Application performance improvement strategy. It's very common to have a meeting on the first day of the morning. The last two days are mainly used to complete the administrative and technical level related to discovery, victory and the subsequent recommendation of the document work. It can be exaggerated to say that if a performance improvement is not documented, it is difficult to repeat similar performance improvements later. If a document is not documented as a result of a problem, it is likely that it will happen again. If a problem and its solution are not recorded to form a document, it will be very difficult to monitor it.

Extended fragmentation: This should not be an issue for online transaction processing systems. We've heard a lot about how the online transaction system handles transactions that do not have a performance impact on fragmented tables, which are exclusively key values. However, we should regularly reorganize to eliminate fragmentation, which can result in significant performance improvements. Oracle Storage Management improvements are making strides towards minimizing the impact of fragmentation.

Because buffered input and output is not a big problem, you need to tune the disk input and output: Here are two points to note. The actual cost of disk input and output is not 10,000 times times the memory buffer input output. The real ratio is close to 70. Even if your CPU seems to be offsetting the cost and does not pose any significant performance problems, the problem obviously limits the scalability of your system. Over time, we are attaching more and more importance to high memory buffer input and output, while looking for opportunities for performance improvement.

Oatablespace model and Migration toolset: Published Metalink annotations (10/03) claim that "This new model brings real-time performance improvements." "The concept of this model is to combine more than 100 Oracle application tablespaces into a table space of 10 counts." Does this lead to a potential storage space savings? Maybe. Does this lead to higher operational efficiencies? It depends on something else. We haven't explained the toolset yet. But we've already understood how table space consolidation at the whiteboard level improves performance.

Defragment your PC client: There is a lot of discussion in this book about this issue. This may be true, as the "Fat client" is popular when writing a book. But now that the Oracle application client is a "thin client" (starting with Oracle scrapping Jinitiator, we call the browser a thin client), don't expect to get a performance boost from the disk defragmentation of your PC client's hard drive.

Load module Patches: This is an often-used response to Oracle technical support for performance issues, but in many cases it is not appropriate. The reason is that patching often leads to instability. If the dependency on the patch is not fully considered, you may find that you have to load the entire patch package, and you have no intention of loading them, and the result is an impact on the stack stability of your system.