Analysis and Selection of the Optimizer Mode for Obtaining the Optimal Plan for the Execution of a Query in the ORACLE DBMS

The relevance of this topic is related to the widespread use of Oracle database management systems (DBMS) in many industries where data volumes are extremely large, which requires high system performance, reliability and fault tolerance. The gradual increase in the number of users and the increasing amount of information processed in conditions of limited resources leads to the need for optimization to achieve stable results and reduce performance incidents. In Oracle, no matter what actions are performed on the data, an optimizer is involved, whose task is to determine the optimal query execution plan. The purpose of this study is to analyze the principles of the optimizer modes, compare them, determine the advantages and disadvantages of each of them, as well as the degree of influence of various factors on the construction of an optimal query execution plan for each of the optimizer modes. Simulations have shown that response time, overhead, and runtime stability can be improved by applying the correct optimizer mode. The result of the study is to provide recommendations for choosing the optimizer mode for a specific case.