CN119003492B

CN119003492B - Database optimization method, device, equipment and storage medium

Info

Publication number: CN119003492B
Application number: CN202411482595.9A
Authority: CN
Inventors: 李燕; 谢岳松; 陈俊豪; 郭琦成; 胡松
Original assignee: Bank Of Ningbo Co ltd
Current assignee: Bank Of Ningbo Co ltd
Priority date: 2024-10-23
Filing date: 2024-10-23
Publication date: 2025-05-23
Anticipated expiration: 2044-10-23
Also published as: CN119003492A

Abstract

The present application discloses a database optimization method, device, equipment and storage medium. When executing the method provided in the embodiment of the present application, first, multiple execution commands of the same target business in the ORACLE database can be obtained, and the command competition type of the target business can be determined based on the multiple execution commands. The performance tuning method of the ORACLE database is determined according to the command competition type of the target business and the competition data corresponding to the command competition type. Then, the ORACLE database is optimized based on the performance tuning method of the ORACLE database. By analyzing the execution commands of a specific business (i.e., the target business) and the competition types between these commands, the present application can more accurately identify the optimization entry point, thereby achieving more accurate performance improvement, thereby improving the overall performance of the ORACLE database.

Description

Database optimization method, device, equipment and storage medium

Technical Field

The present application relates to the field of database technologies, and in particular, to a method, an apparatus, a device, and a storage medium for optimizing a database.

Background

In order to improve user experience, optimize performance and reduce resource consumption during operation of the ORACLE database, it is generally sought to achieve higher performance effects with lower resource configurations. Common practices for performance tuning include tuning database configuration, optimizing hardware configuration, and improving applications. However, the existing tuning methods are often based on general rules, and consideration of different service characteristics and requirements is insufficient, so that the best performance improvement cannot be achieved.

Therefore, how to formulate diversified database optimization strategies for different service features to improve the overall performance of the ORACLE database is a technical problem that needs to be solved urgently by those skilled in the art.

Disclosure of Invention

Based on the problems, the application provides a database optimization method, a database optimization device and a database storage medium, and various database optimization strategies can be formulated aiming at different service characteristics so as to improve the overall performance of an ORACLE database.

The embodiment of the application discloses the following technical scheme:

a method of optimizing a database, the method comprising:

acquiring a plurality of execution commands in an ORACLE database, wherein the execution commands come from the same target service;

Determining a command competition type of the target service based on the plurality of execution commands;

determining a performance tuning mode of the ORACLE database according to the command competition type of the target service and competition data corresponding to the command competition type, wherein the competition data comprises an index for determining the command competition type;

and performing performance optimization on the ORACLE database based on the performance tuning mode.

In one possible implementation, the command contention types include free list contention, rollback section contention, log buffer contention, and scheduling process contention.

In one possible implementation manner, the determining, based on the plurality of execution commands, a command contention type of the target service includes:

if the requested times of any free list in the ORACLE database are greater than a request threshold or the processing time of any free list is greater than a time consumption threshold, determining that the command competition type of the target service comprises the free list competition;

If the execution command comprises a rollback command and the ratio of the waiting execution time of the rollback command in a rollback section buffer area to the execution time is larger than a first duty ratio threshold, determining that the command competition type of the target service comprises the rollback section competition, wherein the waiting execution time comprises the waiting execution time of all rollback commands in the rollback section buffer area, and the execution time comprises the time from the beginning of the receipt of the first rollback command to the end of the execution of the last rollback command in the rollback section buffer area;

If the execution command includes a redo command and the difference between the size of a redo log corresponding to the redo command and the size of the storage space of the available log buffer space of the log buffer in the ORACLE database is greater than a difference threshold, determining that the command competition type of the target service includes the log buffer competition;

If the ratio of the number of executing commands to the number of all executing commands is larger than a second duty ratio threshold, determining that the command competition type of the target service comprises the scheduling process competition, wherein the executing commands are connected to different data volume instances through a multi-instance scheduler to execute the executing commands.

In one possible implementation manner, the determining a performance tuning manner of the ORACLE database according to the command contention type of the target service and contention data corresponding to the command contention type includes:

If the command competition type of the target service is the free list competition, determining a performance tuning mode of the ORACLE database, wherein the performance tuning mode comprises the steps of determining the number of newly added lists based on competition data corresponding to the free list competition and adding new free lists in the ORACLE database based on the number of newly added lists;

If the command competition type of the target service is the rollback section competition, determining a performance tuning mode of the ORACLE database, wherein the performance tuning mode comprises the steps of determining the number of newly added rollback sections based on competition data corresponding to the rollback section competition, and adding new rollback sections in the rollback section buffer based on the number of newly added rollback sections;

If the command competition type of the target service is the log buffer competition, determining a performance tuning mode of the ORACLE database, wherein the performance tuning mode comprises the steps of determining a new storage space amount based on competition data corresponding to the log buffer competition and increasing the size of the log buffer based on the new storage space;

And if the command competition type of the target service is the competition of the scheduling process, determining a performance optimization mode of the ORACLE database, wherein the performance optimization mode comprises the steps of determining the number of newly increased instance schedulers based on competition data corresponding to the competition of the scheduling process, adding new multi-instance schedulers in the ORACLE database based on the number of the newly increased instance schedulers, and the competition data corresponding to the competition of the scheduling process comprises the ratio of the number of executing commands to the number of all executing commands and the second duty ratio threshold.

In one possible implementation, the method further includes:

The method comprises the steps of inquiring hit rates of all execution commands in a global area SGA of an ORACLE database, wherein the SGA comprises a library cache area and a dictionary cache area, the hit rates comprise the library cache area hit rate and the dictionary cache area hit rate, the library cache area hit rate comprises a ratio of the number of first hit commands to the number of all execution commands, the dictionary cache area hit rate comprises a ratio of the number of second hit commands to the number of all execution commands, the first hit commands comprise the execution commands of which database execution items exist in the library cache area, the second hit commands comprise the execution commands of which database execution items exist in the dictionary cache area, and the database execution items comprise compiled versions, analysis results and/or execution plans of the execution commands.

If the hit rate of the library cache area is smaller than a hit rate threshold, determining the amplification amount of the library cache area based on the hit rate of the library cache area, and amplifying the library cache area based on the amplification amount of the library cache area;

If the hit rate of the dictionary cache is smaller than the hit rate threshold, determining the amplification amount of the dictionary cache based on the hit rate of the dictionary cache, and amplifying the dictionary cache based on the amplification amount of the dictionary cache.

In one possible implementation, the rollback section includes a public rollback section and a private rollback section.

An optimization apparatus for a database, the apparatus comprising:

the system comprises an acquisition unit, a target service and a processing unit, wherein the acquisition unit is used for acquiring a plurality of execution commands in an ORACLE database;

a first determining unit configured to determine a command contention type of the target service based on the plurality of execution commands;

The second determining unit is used for determining a performance tuning mode of the ORACLE database according to the command competition type of the target service and competition data corresponding to the command competition type, wherein the competition data comprises an index used for determining the command competition type;

And the performance optimization unit is used for performing performance optimization on the ORACLE database based on the performance optimization mode.

An optimization device for a database comprises a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the database optimization method as described above when executing the computer program.

A computer readable storage medium having instructions stored therein which, when executed on a terminal device, cause the terminal device to perform a database optimization method as described above

Compared with the prior art, the application has the following beneficial effects:

The application provides a database optimization method, a database optimization device, database optimization equipment and a storage medium. Specifically, when the method for optimizing the database provided by the embodiment of the application is executed, a plurality of execution commands closely related to the target service can be first identified from the ORACLE database. By analyzing the execution commands, the command competition type, namely the command set which affects each other and competes for resources in the running process, can be accurately identified. Then, according to the determined command competition type and the corresponding competition data, a targeted performance tuning strategy can be formulated, wherein the competition data comprises indexes for accurately judging the competition relationship among commands. These competing data provide insight into the command competition pattern, helping us know which operations or transactions collide more frequently when, resulting in performance bottlenecks. And finally, based on the acquired performance tuning mode, the ORACLE database is optimized in a targeted manner.

Compared with the tuning method based on the general rule, the method can better cope with the difference of different service characteristics and requirements, and realize more accurate resource utilization and efficiency improvement. Meanwhile, by identifying the command competition type between executing commands, the optimization method can diagnose the bottleneck of the ORACLE database, and invalid resource increase is avoided. Compared with the method of blindly improving the resource allocation to achieve the performance improvement, the method can more scientifically allocate the resources, effectively reduce unnecessary hardware or software allocation, save the cost and simultaneously ensure the performance.

In addition, by continuously acquiring and analyzing the execution command, the method can monitor and respond to the change of the service mode, adjust the optimization strategy, and ensure that the database performance is continuously optimized to adapt to the dynamic service environment. In contrast, conventional static tuning methods may not be able to quickly address challenges presented by changes in business needs.

Drawings

In order to more clearly illustrate this embodiment or the technical solutions of the prior art, the following description will briefly explain the drawings used in the embodiments or the description of the prior art, and it is obvious that the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained according to these drawings without inventive effort for a person skilled in the art.

FIG. 1 is a method flowchart of a database optimization method according to an embodiment of the present application;

Fig. 2 is a schematic structural diagram of an optimizing apparatus for a database according to an embodiment of the present application.

Detailed Description

In order to facilitate understanding of the technical solution provided by the embodiments of the present application, the following description will first explain the background technology related to the embodiments of the present application.

The ORACLE database is a relational database management ORACLE database developed by ORACLE corporation. It is an enterprise-level database, ORACLE database, widely used to store and manage data for a variety of large organizations. ORACLE databases are known for their stability, security, and performance, and are used by a wide variety of industries, including finance, the internet, government, manufacturing, and the like.

In the operation process of the ORACLE database, optimizing the database performance becomes one of the key tasks in order to ensure smooth and efficient user experience, and simultaneously realize efficient utilization of resources and reasonable control of cost. The pursuit of achieving higher performance effects with lower resource configurations is a core goal of database management, which requires not only fine ORACLE database tuning, but also comprehensive consideration of hardware environment, software configuration and application logic.

For performance tuning, methods are generally adopted including operations in terms of adjusting database configuration, optimizing hardware configuration, improving application programs, and the like. By adjusting database parameters such as memory allocation, cache size, network connection strategy and the like, the data access speed and the query efficiency can be effectively improved. For hardware configuration, upgrades to higher speed storage and more powerful processors, or optimizing the network architecture, can significantly improve data processing speed and ORACLE database response time. In addition, through code optimization, algorithm optimization and data structure adjustment, an application program can interact with a database more efficiently, and the method is a great way for improving overall performance.

However, the current methodology is often limited by a set of general rules applicable to most scenarios, ignoring specific differences in service characteristics, data size, access patterns, etc., resulting in a failure to achieve optimal performance improvement in the ideal in the face of complex and varied service requirements. The optimal strategy of 'one-time cutting' lacks enough flexibility and pertinence and may cause problems such as ORACLE database performance bottleneck in practical application.

In order to solve the problem, the embodiment of the application provides a database optimization method, a database optimization device, a database optimization equipment and a database storage medium, wherein a plurality of execution commands aiming at the same target service are extracted from an ORACLE database, and then the commands are analyzed to identify command competition types. And then, determining a corresponding performance tuning strategy by combining the command competition type of the target service and related competition data, wherein the competition data comprises a key index for identifying the command competition type. Finally, based on the performance tuning strategy, the ORACLE database is optimized. Compared with a general rule tuning method, the method provided by the application can be used for more accurately coping with different service characteristics, and realizing the optimal utilization of resources and the improvement of efficiency. The bottleneck of the ORACLE database can be effectively diagnosed by identifying the command competition type among the execution commands, and invalid resource increase is avoided, so that scientific resource allocation is realized, unnecessary hardware or software configuration is reduced, cost is saved, and performance is ensured. Meanwhile, by continuously acquiring and analyzing the execution command, the method can flexibly adjust the optimization strategy, adapt to the change of the service mode, ensure the continuous optimization of the database performance and overcome the defect of the traditional static optimization method in rapidly coping with the change of the demand.

The following description of the embodiments of the present application will be made clearly and completely with reference to the accompanying drawings, in which it is apparent that the embodiments described are only some embodiments of the present application, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.

Referring to fig. 1, the method is a method flowchart of a database optimization method provided in an embodiment of the present application, and as shown in fig. 1, the database optimization method may include steps S101 to S104:

s101, acquiring a plurality of execution commands in an ORACLE database.

Acquiring multiple execution commands in ORACLE refers to extracting a series of structured query language (Structured Query Language, SQL) execution instructions from a database that are related to a particular service. This may be accomplished by querying an ORACLE database view (e.g., an SQL statement information view or an SQL region information view) using a database query tool or script to obtain an execution history, thereby screening out commands related to the same target business for subsequent performance analysis and optimization.

It should be noted that these specific execution commands (e.g., database query, update, insert, etc.) are designed and executed around a specific business requirement, goal, or process (i.e., a goal business). These commands may serve different but interrelated functions such as order management, inventory control, financial reporting, customer relationship management, marketing analysis, etc. Together they support business decisions, promote efficiency, improve customer service, or achieve other critical business goals.

For example, in an e-commerce business, specific execution commands may include updating inventory status, processing orders, calculating discounts, sending shipping notices, and the like. These operations are all focused on a unified business framework, which aims to improve the automation degree of the transaction flow and the satisfaction of customers.

S102, determining the command competition type of the target service based on the plurality of execution commands.

Determining the command contention type of the target service based on the plurality of execution commands refers to analyzing the execution of the commands to identify their contention patterns on resource usage. This includes checking whether there is a conflict or resource contention between commands, providing a basis for subsequent performance optimization. By this analysis, specific factors affecting the performance of the service can be more clearly understood.

In one possible implementation manner, the determining, based on the plurality of execution commands, a command contention type of the target service includes A1-A4:

A1, if the requested times of any free list in the ORACLE database are larger than a request threshold or the processing time of any free list is larger than a time consumption threshold, determining that the command competition type of the target service comprises the free list competition.

In the context of an Oracle database, free List is a data structure used to store allocated but unused memory blocks. When a process performs a memory allocation, it looks up the free list to find the appropriate free space. If the number of times any free list is requested in the database increases significantly (above the request threshold) or the processing of any free list takes significantly longer (above the time-consuming threshold), this generally means that a large number of memory allocation operations are occurring simultaneously and during this process the free list processing performance is degraded or the load is excessive.

In this case, it can be concluded that the command competition type of the target service includes "free list competition". This means that multiple concurrent requests or tasks may be competing for the same limited resources, i.e., free memory blocks in the free list, while performing a particular business operation. Such contention can lead to performance problems such as prolonged response time, transaction blocking, and even impact on the overall performance and stability of the ORACLE database.

It should be noted that, the time consumed for processing the free list refers to the time consumed for each activity related to operating the free list in database management.

In one possible implementation, the request threshold typically suggests starting from tens of thousands to hundreds of thousands of requests as the threshold, with the specific number depending on the number of concurrent users of the ORACLE database, the average complexity of each request, and the ORACLE database capabilities. For example, for a medium-scale application, it may be initially set to receive 50000 requests per minute as a threshold for triggering free list contention. The application does not limit the size of the request threshold, and the user can adjust the size of the request threshold according to actual demands.

Likewise, the time-consuming threshold should also be set based on the maximum latency that the ORACLE database can tolerate. Under high load conditions, delays of possibly several milliseconds per second are considered undesirable. The application does not limit the size of the time consumption threshold, and the user can adjust the size of the time consumption threshold according to actual demands.

A2, if the execution command comprises a rollback command and the ratio of the waiting execution time to the execution time of the rollback command in the rollback section buffer area is greater than a first duty ratio threshold, determining that the command competition type of the target service comprises the rollback section competition.

In the ORACLE database, a "rollback" is a critical component for storing and tracking the operational state of a transaction that allows the database to be restored to a state prior to the start of the transaction under certain circumstances. If the target service execution command contains a rollback instruction, and at a specific point in time, the average waiting execution time of the rollback command is observed to be greater than the preset first duty cycle threshold by a ratio of the average waiting execution time to the execution time, this generally means that the rollback operation encounters a significant bottleneck in one or more rollback segment buffers. That is, the ORACLE database discovers a degree of resource contention or congestion in these buffers when processing rollback requests. In this case, it may be determined that the command contention type of the target service includes "backoff segment contention".

It should be noted that the rollback command is a series of commands for revoking or rollback database transactions;

Rollback segment buffer whenever a transaction completes an operation, the ORACLE database will record update information related to the transaction in the rollback segment. When a transaction needing rollback occurs subsequently, the ORACLE database acquires relevant data from the buffers to perform rollback operation, namely, the rollback section buffers execute rollback commands through a plurality of rollback sections;

wait for execution time refers to the sum of the time between all rollback commands being committed to execution start, i.e., the time that the commands are queued in the buffer. For example, there are three rollback commands, the first rollback command has a time of 1 millisecond from being submitted to the start of execution, the second rollback command has a time of 2 milliseconds from being submitted to the start of execution, and the third rollback command has a time of 1.5 milliseconds from being submitted to the start of execution, then the wait execution time is 4.5 milliseconds;

the execution time is the period of time from the receipt of the first rollback command until the completion of execution of the last rollback command.

Thus, when the ratio of the waiting execution time and the execution time of the rollback command in the rollback section buffer exceeds the first duty threshold, it may be determined by detection and analysis that the command contention type for this scenario belongs to the "rollback section contention". Such identification facilitates timely action to optimize ORACLE database performance, improve efficiency of rollback operations, and prevent data consistency problems and other potential ORACLE database stability risks due to delays in rollback operations.

It should be noted that, the present application does not specifically limit the size of the first duty ratio threshold, and the user can adjust the size of the first duty ratio threshold according to actual requirements.

In one possible implementation, the rollback sections include a public rollback section and a private rollback section.

The common rollback segment is a shared resource that is automatically managed by the DBMS_REDO packet. When a transaction is created that performs COMMIT or ROLLBACK (even though the transaction has been in an auto COMMIT mode), the ORACLE database uses the common ROLLBACK segment to store the updated information for the transaction. This ensures that any transaction that needs rollback can access the required change data. The common rollback section is suitable for use in environments where concurrency is low because it can be shared between most concurrent transactions, thereby reducing memory usage.

In contrast, the private rollback section is proprietary to each database user or specified client ID (CLIENT IDENTIFIER, CID). This means that each user or CID has its own independent rollback section. This arrangement, while increasing the use of memory, provides better performance and isolation because each transaction has its own rollback section. Especially for applications requiring high concurrency processing, private rollback segments can significantly reduce latency and avoid data collisions between transactions.

It should be noted that the choice of public or private rollback sections is mainly dependent on the requirements of the application and the load situation of the ORACLE database:

The common rollback section is suitable for scenes with low concurrency and low performance requirements, and particularly in an ORACLE database of multiple users, the shared resources can be effectively utilized.

The private rollback section is more suitable for high concurrency environments or scenarios with stringent requirements for transaction isolation and performance, although it increases memory overhead, it is very advantageous for applications requiring tightly controlled concurrency and data consistency.

A3, if the execution command comprises a redo command, and the difference between the size of the redo log corresponding to the redo command and the size of the available log buffer space storage space of the log buffer in the ORACLE database is greater than a difference threshold, determining that the command competition type of the target service comprises the log buffer competition.

In the ORACLE database, log buffer is a key component for temporarily storing online redo log records. When write operations are performed to the database table space, these operations are first recorded to the log buffer and then written to the actual redo log file periodically.

If the "execute command includes a redo command", this generally means that an operation such as a data update or a transaction commit is being performed on the database. Each such command may produce a redo log record. In the ORACLE database, when such a redo command is executed, if the difference between the size of the redo log record and the available storage space in the log buffer (i.e., the amount of space remaining available for storing new log information) is greater than a preset "difference threshold", then a situation in which there is a log buffer race may be identified. That is, when such a difference exceeds a "difference threshold," the ORACLE database may detect a potential performance bottleneck or resource pressure, thereby marking one of the "command race types" of the target traffic as "log buffer race".

In one possible implementation, the difference threshold may be set at 50% or higher, depending on the size of the log buffer and the specific requirements of the database. For example, if the log buffer size is 16 Megabytes (MB), the difference threshold may be set to 8MB (i.e., 50% of the log buffer space). The application does not limit the size of the difference threshold, and the user can adjust the size of the difference threshold according to actual demands.

It should be noted that the redo log stores all modifications made to the data page since the last write operation. It records all row update, add or delete operations. Upon a database restart or failure recovery, the redo log is used to reapply the modifications to restore the database to a state prior to the failure.

And A4, if the ratio of the number of executing commands to the number of all executing commands is larger than a second duty ratio threshold, determining that the command competition type of the target service comprises the scheduling process competition.

In the ORACLE database, if it is monitored that the proportion of the number of commands being actually executed to the total number of commands has exceeded the second duty cycle threshold, it indicates that the command execution of the system has a higher degree of competition, i.e. "scheduling process competition". Scheduling process contention refers to conflicts that occur in the ORACLE database when tasks (also called commands or jobs) acquire and use system resources (e.g., processors, memory, I/O devices, etc.), particularly in a multitasking or concurrent environment. In this case, due to the limited resources, various factors such as priority, execution speed, waiting time between tasks running at the same time may cause some tasks to be not supported by sufficient resources for a long time, while other tasks may become more efficient because they are preferentially processed. This not only affects the efficiency and response time of task execution, but may also lead to uneven allocation of system resources, affecting overall performance and stability.

It should be noted that in the ORACLE database, each specific task or operation (collectively, "execute command") does not interact directly with the resources of the entire ORACLE database, but is coordinated and distributed by an intermediate management component, i.e., a "multi-instance scheduler". This scheduler is responsible for assigning specific tasks to the most appropriate data processing units or instances for execution based on factors such as the current state of the system, resource availability, and task requirements. These "data volume instances" refer to either physical or logical computing nodes capable of independently processing data operations, which may be distributed in different locations on a network or may be on the same server.

It should also be noted that the "rollback section buffer executes rollback commands through multiple rollback sections" and "execution commands connect to different data volume instances through a multi-instance scheduler to execute the execution commands" do not conflict, as the operation of the rollback sections and the scheduling of the execution commands are two independent layers. The rollback section is used for transaction management and data recovery, while the scheduler executing the commands is responsible for resource allocation and task execution. In the ORACLE database, commands need to be executed, and success or failure of a command may trigger a rollback operation. Thus, even though commands are executed concurrently and distributed across multiple instances, there is still a need for an efficient rollback mechanism to ensure data consistency. Each execution command may still be distributed to the appropriate instance for execution by the multi-instance scheduler, while the corresponding rollback operation (using the rollback segments) may still be performed independently and accurately if rollback is required.

In one possible implementation manner, the size of the second duty ratio threshold may be, but is not limited to being set to 50%, and the present application does not specifically limit the size of the second duty ratio threshold, and the user may adjust the size of the second duty ratio threshold according to actual needs.

And S103, determining a performance tuning mode of the ORACLE database according to the command competition type of the target service and the competition data corresponding to the command competition type.

In the ORACLE database, the command competition type aiming at the target service and the competition data related to the command competition type can be used for formulating a corresponding ORACLE database performance tuning strategy. The competition data refers to specific indexes for analyzing and identifying command competition types, and the indexes help identify bottleneck of the ORACLE database, so that targeted optimization is performed to improve the overall performance of the ORACLE database.

In one possible implementation manner, the determining a performance tuning manner of the ORACLE database according to the command contention type of the target service and contention data corresponding to the command contention type includes B1-B4:

And B1, if the command competition type of the target service is the free list competition, determining a performance tuning mode of the ORACLE database to be that the number of newly added lists is determined based on competition data corresponding to the free list competition, and adding new free lists into the ORACLE database based on the number of newly added lists.

In the ORACLE database, if the command competition type of the target service is determined to be free list competition, a certain performance tuning strategy can be adopted:

The number of new listings is determined based on the competition data by first collecting and analyzing competition data related to free-listing competition, which data typically contains the number of requests and a threshold of requests (or a threshold of processing time and time consumption), and from this data, scaling or rules of thumb can be used to infer the number of new listings needed.

Adding new free lists in the ORACLE database based on the number of new lists, then, based on the results of the above analysis, if the requested number of times continues to be above a threshold, or the processing time is significantly increased, it may be decided to create additional free lists to relieve the stress. The addition of new lists spreads the concentrated pressure of requests and increases the flexibility and efficiency of the database response system by increasing the resource capacity.

By the method, the ORACLE database can more effectively cope with free list competition problems caused by high concurrent application or large data volume operation, so that the overall performance and stability are improved. Adjusting the number of free lists is a dynamic process that is performed on the basis of monitoring system status and constantly optimizing demand to maximize resource usage efficiency.

And B2, if the command competition type of the target service is the rollback section competition, determining a performance tuning mode of the ORACLE database to be that the number of newly increased rollback sections is determined based on competition data corresponding to the rollback section competition, and adding new rollback sections into the rollback section buffer based on the number of newly increased rollback sections.

If the command race type of the target service is identified as a rollback segment race, this typically indicates that the rollback segment resource requirements in the ORACLE database are too high when processing the transaction rollback operation. Performance tuning strategy of ORACLE database in this context, optimization measures based on the following steps are taken:

the backoff segment contention data is determined by first determining a ratio of the wait time to the execution time and a first duty cycle threshold.

The number of rollback segments is optimized based on the competition data, and then the number of newly added rollback segments required can be calculated according to the competition degree and the load of the ORACLE database.

Adding new rollback sections after dead reckoning, the next step is to increase the number of new rollback sections in the rollback section buffer. This operation aims to improve the processing efficiency of the rollback operation, reduce the latency due to resource limitations, and thereby improve the overall database response speed and transaction processing capacity.

By the aid of the optimization mode, performance of the database in transaction rollback processing can be effectively improved, the mechanism that the database can rapidly and efficiently finish rollback when transaction failure occurs and the influence on system performance is reduced.

In one possible implementation, the number of rollback segments is generally set as follows:

And B3, if the command competition type of the target service is the log buffer competition, determining a performance tuning mode of the ORACLE database to be that the newly increased storage space amount is determined based on competition data corresponding to the log buffer competition, and the size of the log buffer is increased based on the newly increased storage space.

In the ORACLE database, if the command race type of the target service is identified as log buffer race, this means that the database encounters a bottleneck in processing the redo log record. In this case, the policy of performance tuning is to decide how to increase the storage capacity of the log buffer by evaluating specific competing data.

Specifically, it may be determined first that the competing data of the log buffer competition corresponds to a difference between the size of the redo log and the size of the available log buffer space (the larger the difference, which means the more log records waiting to be written to disk, the further approaching the space constraint of the log buffer), and a difference threshold. The amount of newly added storage space is then determined based on the competing data of the log buffer, which typically involves evaluating the size of the existing log buffer, and calculating an appropriate new capacity against the current usage level, the expected peak load, and the traffic demand. By increasing the part of storage, the queuing pressure of the redo log can be reduced, and the processing efficiency and the response speed of the database are improved. And finally, increasing the size of the log buffer area based on the newly added storage space. The whole process is generally accompanied by monitoring the running state of the database, so that the performance of the new configuration can be effectively improved after the new configuration is put into production, and new problems are not introduced.

And B4, if the command competition type of the target service is the scheduling process competition, determining a performance optimization mode of the ORACLE database, namely determining the number of newly increased multi-instance schedulers based on competition data corresponding to the scheduling process competition, and adding new multi-instance schedulers into the ORACLE database based on the number of the newly increased multi-instance schedulers.

When the command contention type of the target service is determined to be a scheduled process contention, this means that resource allocation or management of the multi-instance scheduler is bottleneck in processing a large number of execution commands. In particular, this type of contention means that the ratio between the command being executed and the total number of all commands exceeds a second predetermined duty cycle threshold. This indicates that the scheduling architecture of the current system may not be able to efficiently and timely handle incoming concurrent requests.

In this case, the performance tuning is performed in such a way that this contention pressure is alleviated by increasing the number of multi-instance schedulers. The logic of such a strategy is based on the ability of the lifting system to distribute and manage execution tasks, by introducing additional scheduler instances, more parallel processing capacity can be provided, waiting queues are reduced, and ultimately overall response speed and performance are improved.

The specific steps of the newly added instance scheduler are as follows:

the number of new schedulers is determined by first calculating the number of new schedulers needed based on the contention data corresponding to the contention of the scheduling process (i.e. the ratio of the number of executing commands to the number of all executing commands) and the known second duty cycle threshold. This calculation process typically requires consideration of the current load situation of the database, the target performance improvement level, and other resource constraints.

Adding new schedulers, namely after determining the number of the newly added schedulers, actually deploying the new multi-instance schedulers in the ORACLE database.

Optimizing resource allocation while adding schedulers, it is also desirable to optimize resource allocation policies based on the overall needs of the system and the existing resource conditions, ensure that each scheduler operates efficiently, and process tasks allocated to them efficiently.

By the method, the problem caused by competition of the scheduling process can be directly solved, space can be reserved for future service growth, and long-term performance stability and expandability are realized.

And S104, performing performance optimization on the ORACLE database based on the performance optimization mode.

Performing performance optimization on the ORACLE database based on the performance tuning mode means that determined and targeted strategies and technical means are adopted to improve the performance of the ORACLE database.

For example, for the case of scheduling process contention, the number of multi-instance schedulers may be increased to enable more efficient parallel processing, reduce task queuing time, and further improve system throughput and overall performance.

Through continuous monitoring, analysis and adjustment to the database, the aim of performance tuning is to ensure that the database can efficiently process daily business load, and simultaneously can maintain good performance and stability when handling sudden high load, and finally achieve the purposes of improving user experience, optimizing resource use efficiency and reducing operation cost.

In one possible implementation, the method further comprises C1-C3:

c1, querying hit rates of all the execution commands in a global area (System Global Area, SGA) of the ORACLE database.

After all execution commands of the target task in the ORACLE database are executed, the memory (the memory mainly refers to SGA) of the ORACLE database can be optimized. Then the hit rate of the execution command in the SGA of the ORACLE database needs to be measured and analyzed first, specifically involving two key components, the library cache and the dictionary cache.

Overall hit rate-first calculated is the overall hit rate of all execution commands in the SGA. This is determined by comparing the number of total executed commands to the number of move commands in the hit SGA, i.e. the ratio of the number of commands that need to be accessed from the outside (e.g. disk) without finding the relevant execution item in the cache to the total number of executed commands.

Library cache hit Rate for library cache this query concerns whether the compiled version of the execute command, the resolution result, or the execution plan is present in the internal cache without additional I/O operations. By "first hit commands" are meant those execution commands in their compiled version, resolved results, or execution plan in the library cache region.

Dictionary cache hit ratio-similar to library cache hit ratio, queries directed to dictionary cache are focused on those associated data structures or information in the cache, thus requiring a process of retrieval from an external resource for each execution. Here, the "second hit command" refers to an execution command existing in the dictionary cache area.

The core objective of the overall task is to evaluate the impact on performance of the current SGA configuration, especially the library cache and dictionary cache. Low hit rates may suggest that these caches are not sufficiently large and require optimization or expansion to reduce I/O operations, improving data retrieval speed and overall system performance. Through the detailed hit rate analysis, a database manager can pertinently adjust a caching strategy, such as measures of increasing the cache size, optimizing data preloading and the like, so as to improve the response speed and efficiency of the database.

And C2, if the hit rate of the library cache area is smaller than a hit rate threshold, determining the amplification amount of the library cache area based on the hit rate of the library cache area, and amplifying the library cache area based on the amplification amount of the library cache area.

If the hit rate of the bank cache (i.e., the proportion of the execution command hit cache) is below a predetermined hit rate threshold, then the amount of the bank cache that needs to be amplified is determined by comparing the current bank cache hit rate to the hit rate threshold. This amount of expansion is calculated based on the difference between the current hit rate and the threshold. The library cache area is then correspondingly physically or configurationally expanded based on the calculated amplification. This may include increasing memory size, upgrading hardware, or adjusting cache policies.

And C3, if the hit rate of the dictionary cache area is smaller than a hit rate threshold, determining the amplification amount of the dictionary cache area based on the hit rate of the dictionary cache area, and amplifying the dictionary cache area based on the amplification amount of the dictionary cache area.

Similarly, when the hit rate of a dictionary cache is also below the hit rate threshold, which is also set, it means that the cache may not be able to efficiently store and quickly access its internal data structures or information. At this time, an expansion amount is determined based on the result of comparing the hit rate of the dictionary cache area with the threshold value thereof by the same method. The size of the dictionary cache area is then adjusted or enlarged based on this expansion amount. This helps to improve database execution efficiency, particularly for tasks involving frequent lookups and access to dictionary cache area data.

It should be noted that, the application does not specifically limit the size of the hit rate threshold, and the user can adjust the size of the hit rate threshold according to actual requirements.

In the ORACLE database, the SGA is a memory area storing various shared data, and includes a shared pool, a Java pool, a redo log buffer, a big pool, and a PGA (program global area). It has a critical impact on database performance because most database operations are performed in caches executing within the SGA.

Based on the content of S101-S104, it is known that multiple execution commands of the same target service can be acquired from the ORACLE database, and the command competition type of the service is determined according to the commands. And then, according to the command competition type of the target service and the corresponding competition data, a corresponding performance tuning strategy is formulated, wherein the competition data comprises indexes for identifying the command competition type. Finally, the ORACLE database is optimized based on the determined performance tuning strategy. Compared with the traditional tuning strategy based on the general rule, the optimization method provided by the application has the remarkable advantages of high flexibility and pertinence. The performance bottleneck of the ORACLE database can be accurately identified by deeply analyzing the competition relationship between the execution commands under the specific service environment, so that more accurate resource allocation and optimization strategies are implemented. The application effectively avoids the invalid utilization of resources, saves the cost, ensures the continuous optimization of the database performance and adapts to the service requirement of dynamic change. And the optimization scheme can be adjusted in real time according to actual running conditions, so that efficient running under different conditions is ensured, and better performance improvement and resource utilization efficiency are finally realized.

Referring to fig. 2, fig. 2 is a schematic structural diagram of an optimizing apparatus for a database according to an embodiment of the present application. As shown in fig. 2, the database optimizing apparatus includes:

An obtaining unit 201, configured to obtain a plurality of execution commands in an ORACLE database, where the plurality of execution commands are from the same target service;

A first determining unit 202, configured to determine a command contention type of the target service based on the plurality of execution commands;

A second determining unit 203, configured to determine a performance tuning manner of the ORACLE database according to a command contention type of the target service and contention data corresponding to the command contention type, where the contention data includes an index for determining the command contention type;

And the performance optimization unit 204 is configured to perform performance optimization on the ORACLE database based on the performance tuning manner.

In one possible implementation manner, the first determining unit 202 specifically includes:

a third determining unit, configured to determine that a command contention type of the target service includes the free list contention if a requested number of times of any free list in the ORACLE database is greater than a request threshold or a processing time of any free list is greater than a time consumption threshold;

a fourth determining unit, configured to determine that a command contention type of the target service includes the rollback section contention if the execution command includes a rollback command and a ratio of a waiting execution time of the rollback command in a rollback section buffer is greater than a first duty ratio threshold, where the waiting execution time includes a waiting execution time of all rollback commands in the rollback section buffer, and the execution time includes a time from when the rollback section buffer receives a first rollback command to when a last rollback command is executed;

A fifth determining unit, configured to determine that a command competition type of the target service includes a log buffer competition if the execution command includes a redo command and a difference between a size of a redo log corresponding to the redo command and a size of an available log buffer space of a log buffer in the ORACLE database is greater than a difference threshold;

And a sixth determining unit, if the ratio of the number of executing commands to the number of all executing commands is greater than a second duty ratio threshold, determining that the command contention type of the target service includes the scheduling process contention, wherein the executing commands are connected to different data volume instances through a multi-instance scheduler to execute the executing commands.

In one possible implementation manner, the second determining unit 203 specifically includes:

A seventh determining unit, configured to determine, if the command contention type of the target service is the free list contention, that the performance tuning manner of the ORACLE database is to determine a new number of lists based on contention data corresponding to the free list contention, and add a new free list to the ORACLE database based on the new number of lists;

An eighth determining unit, configured to determine, if the command contention type of the target service is the backoff segment contention, a performance tuning manner of the ORACLE database, where the performance tuning manner is to determine a new number of rollback segments based on contention data corresponding to the backoff segment contention, and add a new rollback segment to the rollback segment buffer based on the new number of rollback segments;

a ninth determining unit, configured to determine, if the command contention type of the target service is the log buffer contention, that the performance tuning manner of the ORACLE database is to determine an amount of newly added storage space based on contention data corresponding to the log buffer contention, and to increase the size of the log buffer based on the newly added storage space;

And a tenth determining unit, configured to determine, if the command contention type of the target service is the scheduling process contention, that the performance tuning manner of the ORACLE database is to determine the number of newly increased instance schedulers based on contention data corresponding to the scheduling process contention, and add new multi-instance schedulers to the ORACLE database based on the number of newly increased instance schedulers, where the contention data corresponding to the scheduling process contention includes a ratio of the number of executing commands to the number of all executing commands and the second duty ratio threshold.

In one possible implementation, the apparatus further includes:

The system comprises a query unit, a query unit and a database cache unit, wherein the query unit is used for querying the hit rate of all execution commands in a global area SGA of the ORACLE database, the SGA comprises a library cache area and a dictionary cache area, the hit rate comprises the library cache area hit rate and the dictionary cache area hit rate, the library cache area hit rate comprises the ratio of the number of first hit commands to the number of all execution commands, the dictionary cache area hit rate comprises the ratio of the number of second hit commands to the number of all execution commands, the first hit commands comprise the execution commands of database execution items in the library cache area, the second hit commands comprise the execution commands of database execution items in the dictionary cache area, and the database execution items comprise compiled versions, analysis results and/or execution plans of the execution commands.

A first integration unit configured to determine an amplification amount of the library cache region based on the library cache region hit rate if the library cache region hit rate is less than a hit rate threshold, and amplify the library cache region based on the amplification amount of the library cache region;

And the second synthesis unit is used for determining the amplification amount of the dictionary cache area based on the hit rate of the dictionary cache area and amplifying the dictionary cache area based on the amplification amount of the dictionary cache area if the hit rate of the dictionary cache area is smaller than a hit rate threshold.

In addition, the embodiment of the application also provides a database optimizing device which comprises a memory, a processor and a computer program stored in the memory and capable of running on the processor, wherein the processor realizes the database optimizing method when executing the computer program.

In addition, the embodiment of the application also provides a computer readable storage medium, wherein the computer readable storage medium stores instructions which, when run on terminal equipment, cause the terminal equipment to execute the database optimization method.

The embodiment of the application provides an optimizing device for a database, which firstly utilizes an obtaining unit 201 to obtain a plurality of execution commands from the same target service from an ORACLE database, and utilizes a first determining unit 202 to determine the command competition type of the target service based on the plurality of execution commands. The second determining unit 203 determines a performance tuning manner of the ORACLE database according to the command contention type of the target service and contention data corresponding to the command contention type, where the contention data includes an index for determining the command contention type. The ORACLE database is then performance optimized based on the determined performance tuning approach using the performance optimization unit 204. Compared with the tuning method based on the general rule, the method and the system can more accurately cope with different service characteristics and realize efficient resource utilization. By identifying "command race types" between executing commands, ORACLE database bottlenecks can be diagnosed, avoiding inefficient resource increases. Compared with the blind improvement of resource allocation, the application scientifically allocates resources, effectively reduces unnecessary hardware and software cost and ensures performance. In addition, the continuous acquisition and analysis of the execution command enables the method to monitor the service mode change, adjust the optimization strategy in time and ensure continuous optimization of the database performance in a dynamic environment.

The method, the device, the equipment and the storage medium for optimizing the database provided by the application are described in detail. In the description, each embodiment is described in a progressive manner, and each embodiment is mainly described by the differences from other embodiments, so that the same similar parts among the embodiments are mutually referred. For the device disclosed in the embodiment, since it corresponds to the method disclosed in the embodiment, the description is relatively simple, and the relevant points refer to the description of the method section. It should be noted that it will be apparent to those skilled in the art that various modifications and adaptations of the application can be made without departing from the principles of the application and these modifications and adaptations are intended to be within the scope of the application as defined in the following claims.

It should be understood that in the present application, "at least one (item)" means one or more, and "a plurality" means two or more. "and/or" is used to describe an association relationship of an associated object, and indicates that three relationships may exist, for example, "a and/or B" may indicate that only a exists, only B exists, and three cases of a and B exist simultaneously, where a and B may be singular or plural. The character "/" generally indicates that the context-dependent object is an "or" relationship. "at least one of" or the like means any combination of these items, including any combination of single item(s) or plural items(s). For example, at least one of a, b or c may represent a, b, c, "a and b", "a and c", "b and c", or "a and b and c", wherein a, b, c may be single or plural.

It is further noted that relational terms such as first and second, and the like are used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Moreover, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises an element.

Claims

1. A method of optimizing a database, the method comprising:

performing performance optimization on the ORACLE database based on the performance tuning mode;

The command competition type comprises free list competition, rollback section competition, log buffer zone competition and scheduling process competition;

the determining, based on the plurality of execution commands, a command contention type of the target service includes:

2. The method according to claim 1, wherein the determining the performance tuning manner of the ORACLE database according to the command contention type of the target service and the contention data corresponding to the command contention type includes:

3. The method according to claim 1, wherein the method further comprises:

Querying hit rates of all execution commands in a global area SGA of the ORACLE database, wherein the SGA comprises a library cache area and a dictionary cache area, the hit rates comprise the library cache area hit rate and the dictionary cache area hit rate, the library cache area hit rate comprises a ratio of the number of first hit commands to the number of all execution commands, the dictionary cache area hit rate comprises a ratio of the number of second hit commands to the number of all execution commands, the first hit commands comprise the execution commands of which database execution items exist in the library cache area, the second hit commands comprise the execution commands of which database execution items exist in the dictionary cache area, and the database execution items comprise compiled versions, analysis results and/or execution plans of the execution commands;

4. The method of claim 1, wherein the rollback section comprises a public rollback section and a private rollback section.

5. An optimization apparatus for a database, the apparatus comprising:

The performance optimization unit is used for performing performance optimization on the ORACLE database based on the performance tuning mode;

The first determining unit 202 specifically includes:

6. An optimization device for a database, comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the method for optimizing a database according to any one of claims 1-4 when executing the computer program.

7. A computer readable storage medium, characterized in that the computer readable storage medium has stored therein instructions, which when run on a terminal device, cause the terminal device to perform the method of optimizing a database according to any of claims 1-4.