An Oracle performance tuning in combat

Project situation: is a large company's internal office system, the system has two and the general enterprise application is not the same characteristics: first, the user is very much, the number of personnel reached about 2W, the other is the form of hierarchical management, each branch of data management.

Our positioning: We are as a business platform provider to participate in this project, we provide the bottom of the development platform, system integrators on this basis for two times development.

Many problems have been encountered in the process of project development and deployment, and many problems have been reflected.

One, what's going on, running slower than the cat

After the project is developed, it is deployed on the IBM AIX compact machine, 32G RAM, 16 CPUs. The application server uses weblogic9.2, and the database is oracle10.0.2. The system was found to be running very slowly, even slower than Tomcat in the development environment. Then began to troubleshoot the reason, the first is to monitor the SQL, the priority is the database access performance bottlenecks. By monitoring, it is found that many businesses need to execute a large number of SQL statements, look at the relevant code written by the customer, and find that a large number of SQL loops are executed while querying the data. The main reason is that they loop through our API in the code, one of the most typical examples is to look up user name through the user ID, instead of saving the user name in the business form, but looking up the user name to the page by the user ID at the time of the query, almost every query is n+1.

In addition, because the platform uses the hibernate, the OO programming is very straightforward, causes the developer completely ignores the corresponding database operation to bring the pressure. Many of the business logic is done directly by the PO overlay, which can be distributed to the PO in a small amount of logic complete with little SQL, resulting in a large number of PO interactions and SQL statements.

Start optimizing SQL, optimizing while adding a large number of business caches. But after the optimization of the slow running phenomenon still exists, performance has a certain increase but not very obvious. Continue optimization, which takes into account how much frequently accessed data uses the memory database. However, after optimization, the effect on Tomcat is obvious, and the problem remains with the deployment to the production environment. Then consider the configuration of WebLogic, as a development platform provider, we just provide support for system development, for application server and database server just do basic configuration system can run. But on this issue system integrators are convinced that the problem with our platform is not put, and there is a very serious problem: they are using pirated weblogic, so there is no corresponding technical support.

Solve the problem: the last is to find a BEA once developer, the problem is very simple, field deployment of the WebLogic by default is run on 32-bit machines, and 64-bit machine exists some incompatibility. The problem was solved by replacing the corresponding jar package, mainly IO. When the replacement is complete, the speed is increased to 30%. The developer said that if there were no lisence, the replacement jar packages would not be available at all.

Second, the memory is exhausted

The speed of access problem solved, the system used quickly up, immediately encountered a new problem: the memory ran out. Serious to almost every day out of the memory once. This problem occurs frequently at the client site.

Local testing, Tomcat,sun JDK monitors memory usage through Jprofiler. In the case of concurrent access to the portal, memory does exist, 100 concurrency, memory usage immediately rose by about 150m, continue concurrent 100, and then grow 150m. But soon there will be a GC happening at peak time, memory usage is stable in 200m, in memory a lot of char[] array objects. Fatigue testing, the memory usage curve did not appear gradually rising leakage. For WebLogic and Jrocket tests, GC occurs more frequently and memory usage is stable.