How to manage Oracle databases

Overview: Oracle DatabaseIs an oldest commercial relational database management system. It is the core product of Oracle, a professional database manufacturer. It is also the most widely used, most powerful, object-oriented database system with a client/server architecture. The application scope of Oracle databases has been expanded, and Oracle databases have been managed to better serve people. How can we manage Oracle databases?

As a general database management system, Oracle has a complete data management function, these functions include storing large amounts of data, defining and operating data, concurrency control, security control, Integrity Control, fault recovery, and advanced language interfaces. Oracle is also a distributed database system that supports various distributed functions, especially Internet applications. As an application development environment, Oracle provides a set of user-friendly and functional database development tools, allowing users to have a good application development environment. Oracle uses PL/SQL to perform various operations, which are open, portable, and flexible. In particular, Oracle supports object-oriented functions, such as classes, methods, attributes, and other concepts, making Oracle products an object/relational database management system.

For database administrators, the pressure on their work is huge, because in addition to the huge daily workload, they also need to face the sudden occurrence of various problems of responsibility. When a problem occurs, you need to analyze the problem immediately, and then take relative optimization measures to solve these sudden problems. The successful completion of these tasks is inseparable from the support of application monitoring software. Application monitoring software can help database administrators collect data from databases and operating systems and display the data in a graphical manner.

The Mocha Business Service Management (Mocha BSM) application monitoring module provides visual monitoring, fault locating, and statistical reports for Oracle databases.

Moka software has nearly ten years of experience in Oracle system management, related development and maintenance. It provides the most management-valuable indicators, refined to the monitoring of each application element, and fundamentally pays attention to the running status of Oracle. Oracle is monitored automatically to implement Platform-level Fault Warning, performance warning, and configuration change warning. This allows administrators to easily and quickly analyze problems and take corresponding solutions.

Performance Tuning for Oracle databases

The following is an example of how to improve the performance of Oracle databases by adjusting the sharing pool with the help of Mocha BSM:

The adjustment of the Sharing pool mainly includes three aspects: Library speed, data dictionary cache, and conversation information. Because of the algorithm used by Oracle to manage data in the shared pool, the data in the data dictionary cache remains longer than the data in the cache. Therefore, you can increase the data dictionary cache hit rate by adjusting the database cache speed to an acceptable hit rate.

In general, the ratio of the total number of cache hits to the total number of accesses should be close to zero. If the hit rate is 0.001%, the cache hit rate is very high. If the hit rate is close to or greater than 5%, measures should be taken immediately to reduce such miss.

Measure 1:Increase the value of the initialization parameter shared_pool_size to increase the amount of memory available in the library cache. To achieve better results, you may also need to increase the value of the initialization parameter open_cursors to increase the number of cursors allowed by the conversation. It should be noted that too much memory allocated to the cache may cause page adjustment or swap.

Measure 2:Write equivalent SQL statements, and try to share SQL statements with PL/SQL blocks in an SQL zone to reduce high-speed cache miss. This is what the Application Designer should do: SQL statements or PL/SQL block text must be equivalent to each character, including case and space.

Effective management of Suse Linux using Mocha BSM

You can use the Dynamic Performance Table V $ LIBRARYCACHE to check and adjust the cache activity of all databases since the Instance was started.

The following columns in the V $ LIBRARYCACHE table reflect the cache miss in the invocation phase:

PINS column. Its value shows the number of times it is executed in the library cache;

RELOADS column. Its value shows the number of cache hits in the execution phase.

Check hit rate

With Mocha BSM, one of the more than 60 performance metrics of Oracle DB: the database cache hit rate, you can intuitively see that the current Oracle DB database cache hit rate is 97.79%, and according to your needs, you can also set an alarm threshold for the database cache hit rate. Once the monitored Oracle DB database cache hit rate exceeds the set threshold, Mocha BSM immediately sends an alarm to the database administrator (methods include: email, SMS, phone, desktop sound and light ).

There are a lot of Oracle databases. For example, Oracle is more stable than SQL Server. Oracle's data import tool sqlload.exe is more powerful than SQL Server's Bcp. Oracle can import text file data according to conditions. The disadvantage is that it is expensive, therefore, it is of great significance to manage Oracle databases,