Foreseeable procedural performance tuning of Oracle Applications)

This technical article is provided by the "International Oracle user group" (ioug). It is an organization composed of user groups. This organization provides high-quality information, training, networking, and support, to improve the level of Oracle Database experts and database developers. This article is excerpted from the foreseeable procedural performance tuning of Oracle Applications by David Welch. Click here to become a member of the "International Oracle user group" to get thousands of technical articles and scientific documents written by oracle users.

Introduction

We have seen a lot of Oracle applications and e-Commerce Suite installation with huge performance problems. We have concluded that these installations can be further improved in terms of performance. In other words, it is rare to install an installation that has high performance and can hardly be improved.

Controversial issues

For the product system stack, our bottom-end peer performance tuning method is always quick to produce results, faster than what we think follows a broad memo list. I have raised the following questions:

Most performance improvements are possible at the application level: this conclusion comes from a significant comment on performance tuning on Metalink. This conclusion has no statistical relationship with our empirical performance tuning System stack.

It takes two days on average: This is the conclusion in the book. However, our experience does not support this conclusion. I think it takes at least 12 days to get an Oracle application performance improvement policy. It is very common to have a meeting on the first day of the morning. The last two days are mainly used to complete administrative and technical-level discovery, victory, and follow-up recommendation documents. It can be exaggerated to say that if a performance improvement is not documented to form a document, it is difficult to repeat similar performance improvements in the future. If you do not record the problem to form a document, it is likely to happen again. If a problem and its solution are not documented to form a document, it will be very difficult to monitor it.

Extended fragmentation: this should not be a problem for online transaction processing systems. We have heard many statements that the "online transaction processing system" does not affect the performance of tables with severe fragments (which are completely unique in key values. However, we should reassemble it on a regular basis to eliminate fragments, which will bring huge performance improvements. Oracle storage management improvement is moving towards minimizing the impact of fragmentation.

Because the buffer input and output are not a big problem, you need to optimize the disk Input and Output Performance: There are two points to describe. The actual overhead of disk input and output is not 10 thousand times that of memory buffer input and output. The actual ratio is close to 70. Even if your CPU seems to be offsetting this price and does not bring any significant performance problems, this issue will obviously limit the scalability of your system. As time passes, we pay more and more attention to high memory buffer input and output while looking for opportunities for performance improvement.

Oatablespace model and migration tool set: the released Metalink note (10/03) claims that "this new model has improved performance in real time ." The concept of this model is to combine more than 100 Oracle Application tablespaces into a table space with 10 counts. Will this lead to potential storage space savings? Maybe. Will this result in higher operational efficiency? It depends on other things. We have not explained this toolset. However, we have understood how table space merging at the whiteboard level improves performance.

Disk fragmentation for your PC Client: There are a lot of discussions about this in this book. This may be correct, because "fat clients" are popular in writing this book ". But now, the Oracle Application client is a "thin client" (since Oracle abolished JInitiator, we call the browser a thin client ), do not expect to improve performance from disk fragmentation on your PC Client hard drive.

Loading module Patch: This is a frequent countermeasure for Oracle technical support for performance problems. In fact, it is not suitable in many cases. The reason is that patching often causes instability. If you do not fully consider the patch dependency, you may find that you have to load the entire patch, and you have no plans to load them, the result is that it has an impact on the stability of your system's stack.

Project Management

Project management is critical. The implementation of Oracle application performance is technically and administratively. A person must be the project manager. Different priorities must be given by the functional area. If possible, business units can calculate their financial expenses due to the delay in Talent Selection, and then multiply the number of users and their income per minute. One of the overhead for improving application performance is to record documents. At the same time, a large number of paper documents need to be recorded. Users' desires must be managed because not all regions produce the same dramatic results. There must be a manager to divide different priorities. In some cases, you may even need to filter access from the performance team. On the one hand, users will frequently propose ideas and requirements that will cause underlying performance problems. On the other hand, interacting with users may impede your work progress. Success also results in exposure of the next layer of performance problems.

What do users cannot tell you?

The bottom-up method of a user reveals that a single package consumes about 25% of the input and output resources. For another user, a single query may cause TB of buffer input and output per week. Performance Tuning reduces the buffer overhead to 0.06%. The problem is that it will consume CPU resources, and whether to expand the CPU in that case requires careful consideration. No one knows that the system stack is offsetting this cost.

The secret about performance tuning is found in the Oracle Performance Tuning guide. As a team, we have discovered this secret for many years. For performance problems of beta-level or product systems, you should start to diagnose the underlying stack of the system. Unfortunately, performance diagnostics often only focus on four parts in the middle of the system stack. They are:

* Logical database structure
* Database operations
* Access path (SQL)
* Memory Allocation

However, we can often find Major performance problems at the bottom layer of Oracle, as shown below:

* Input/output and physical database structure
* Resource Competition
* Underlying operating system platform

 

Treasure Map

In terms of Oracle performance optimization, the treasure map is the V $ sqlarea view. If I am an IT manager, I will remember the name of this view. In addition, whenever I encounter my database administrator in the lobby, I will ask them how many times they will query this view this week.

Metalink comment 235146.1 provides some examples for querying this view. For example:

Select SQL _text, executions, buffer_gets, disk_reads, rows_processed,
Sorts, address, first_load_time, hash_value, Module
From v $ sqlarea
Where executions> 0
Order by reads_per DESC

Recently, more and more Oracle 9i versions are added to the module column, which reveals the module name of the Oracle application.

Statistical Package

In many large enterprises, the use of statistical packages is still ignored. This may be a report of coercion. Do not try to read the output results to get all the information. Even the first page is enough to tell you where the remaining 10% of the Report is worth your attention. The statistical package of Oracle 9.2 contains the CPU and consumption time columns. Previously, in order to sort long-running SQL statements to the top, we had to enable "tracing", connect tracing files, and deliver them to the tkprof program for processing. This is unrealistic for large enterprises that simply "track" to process up to 10 Gb of data.

Let users participate in Performance Tuning

Those who write this suggestion (that is, to involve users in performance tuning) should be praised for their creativity. Let your users participate in performance diagnosis. Buy an Oracle Application to evaluate your PC and use it for users. Do not use a notebook that is similar to a personal computer, because in the same standard, the notebook does not have the same performance characteristics as the personal computer. The configuration list is as follows:

* 750 MB CPU
* 256 MB memory
* Windows 2000 Enterprise Edition (Fourth Edition)
* Use an independent Logical Disk
* JInitiator-locked version
* Standard software, such as Office 2003

The following configurations are not required for personal computers used for evaluation:

* Wallpaper
* Screen
* Tool bar
* Resident program

Sending the rating to the user's desktop on a personal computer carries performance issues. Connect your computer to the LAN for a period of time. Then, the user's computer is put into the computer room and connected to the middle layer, allowing the user to do more work on it. The use of personal computers for evaluation eliminates users' subjectivity on the Performance of Oracle applications, and also eliminates your subjectivity in the face of user complaints about performance issues.

Index count and Performance

Back in 1970s, the developer guide basically said not to create four to five indexes on a table. Note the following in the developer guide:

Oracle does not limit the number of indexes created on a table. However, you need to consider the performance improvements brought about by indexes and the actual needs of your database application to determine which columns need to be indexed.

Fact: Each Oracle application table may contain more than 30 indexes. If we add an index to reduce the input and output of SQL statements that are frequently needed, we will add this index without considering the high index count issue.

CPU

Reduce the width of the concurrency management pool, so far we have not found that this will block the task. We often see that reducing the width of the concurrency management pool actually increases the throughput of the batch processing task, which also makes the CPU less busy. Many tasks that contain peer processes must be completed. If the pool width of a task is too narrow, the required task may never be processed, blocking the overall task.

We have dealt with Oracle application installation team and trainers who like to increase the width of the concurrency management pool while ignoring the impact on CPU. This setting remains available until the product is released. In the training and testing environments, the door to security issues is open, and the installer increases the concurrency management pool width to expect their batch processing tasks to be completed as soon as possible. They may not take into account the impact on the CPU, and the CPU may be fully occupied.

The CPU running queue should not be twice as deep as your CPU count. If the CPU is frequently used up in a day, you must discard some settings. The first place to find the settings that need to be abandoned should be the concurrency management pool.

Summary:
Oracle routine maintenance and performance tuning are not simply a technology. It is more important to specify a scientific and rigorous management and maintenance plan. You must optimize and maintain all the difficulties recorded in the process to form documents, accumulated knowledge and experience, so as not to make the same mistake twice;
Record the running logs, when the system performance is poor and the speed is slow; then analyze and find out the cause and specify the solution;
Optimization is divided into two parts:
I. application Layer, including logical database structure, database operations, access paths (SQL), and memory allocation. optimization methods include decomposing large tables, modifying key table structures, analyzing SQL statements at the application layer, and optimizing them to achieve optimal execution. configuring parameters, appropriately allocating memory, regular analysis, and re-Indexing, move the table to remove fragments;
II. system layer, including input/output and physical database structure, resource competition, and underlying operating system platform. select an appropriate file system based on the scale of the system application, this can reduce the number of I/O operations. The operating system supports large-scale throughput, the window is a micro-River, and Linux/Unix is a macro kernel, the speed and operation performance of inter-process communication in the system are different.

According to your needs-> specify the O & M plan-> analyze the running log-> more the running plan-> analyze the running log... such an iterative process.