Analysis and Selection of the Optimizer Mode for Obtaining the Optimal Plan for the Execution of a Query in the ORACLE DBMS
( Pp. 92-100)

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.

Unkovskaia G.A. Analysis and Selection of the Optimizer Mode for Obtaining the Optimal Plan for the Execution of a Query in the ORACLE DBMS. Computational Nanotechnology. 2023. Vol. 10. No. 3. Pp. 92–100. (In Rus.) DOI: 10.33693/2313-223X-2023-10-3-92-100. EDN: RYVEQA

Gladkov A.K., Nikolskaya D.I. Search engine optimization research based on the database. Economy and Quality of Communication Systems. 2022. No. 4. Pp. 67–74. (In Rus.)
Millsap K., Holt D. Oracle. Performance optimization. St. Petersburg: Symbol-Plus, 2006. 464 p.
Ivanov K.K., Efremov A.A., Vashchenko I.A. The role of the optimization process in the operation of database systems. Young Scientist. 2016. No. 28 (132). Pp. 15-16. (In Rus.)
Przyjalkowski V. What are Oracle’s plans? URL: http://www.interface.ru/fset.asp?Url=/oracle/kakie.htm (data of accesses: 07.07.2023).
Esaulova E.A. Comparison of optimizers. Proceedings of the tenth regional conference on mathematics MAK-2007. Barnaul, June, 2007. AltSU, AltSTU, BSPU, GAGU, Institute of water and environmental problems (Barnaul). N.M. Oskorbin et al. (ed.). Barnaul: AltGU Publishing House, 2007. Pp. 62–63.
Green C.D. Oracle 9i database performance tuning guide and reference. Release 2 (9.2) Part Number A96533-02. URL: https://docs.oracle.com/cd/B10500_01/server.920/a96533/rbo.htm (data of accesses: 09.07.2023).
Kite T. Oracle for professionals. Transl. from English. St. Petersburg: DiaSoftYUP LLC, 2003. 672 p.
Lewis J. Oracle. Fundamentals of cost optimization. St. Petersburg: Piter, 2006. 528p.
Jahrke M., Koch J. Query optimization in database systems. Transl. from English S. Kuznetsov. 1984. URL: http://citforum.ru/database/articles/query_optimization/ (data of accesses: 09.07.2023).
Algazali S.M.M., Aivazov V.G., Kuznetsova A.V. Improving the process of finding inefficient SQL queries in the Oracle DBMS. Engineering Journal of Don. 2017. No. 4. (In Rus.) URL: https://cyberleninka.ru/article/n/sovershenstvovanie-protsessa-poiska-neeffektivnyh-sql-zaprosov-v-subd-oracle (data of accesses: 16.07.2023).
Unkovskaia G.A. Integration of the method of multi-criteria selection of alternatives based on fuzzy sets into the business processes of the banking sector. XXI Century: Resumes of the Past and Challenges of the Present plus. 2022. No. 4 (60). Pp. 63–67. (In Rus.)
Nimick R.J. Tuning problematic queries. Oracle Magazine. 2000. (In Rus.) URL: https://www.interface.ru/home.asp?artId=3776 (data of accesses: 10.07.2023).
Czuprynski J. Oracle database 11g Release 1 new features summary. Part 1. 2007. URL: https://www.databasejournal.com/oracle/oracle-database-11g-release-1-new-features-summary-part-1/ (data of accesses: 28.06.2023).
Apple R. Oracle cost based optimizer correlations. All Regis University Theses. 2013. No. 234. URL: https://epublications.regis.edu/theses/234 (data of accesses: 20.07.2023).
Hellström I. Oracle SQL & PL. SQL Optimizationfor Developers Documentation. Release 3.0.1. 2023. URL: https://oracle.readthedocs.io/_/downloads/en/latest/pdf/ (data of accesses: 16.07.2023).
Xiaoxiang Hermit. RBO and CBO of ORACLE optimizer. URL: https://www.programmersought.com/article/84476969712/ (data of accesses: 16.07.2023).
Burleson D.K. Optimizing oracle optimizer statistics. URL: http://www.dba-oracle.com/art_orafaq_cbo_stats.htm (data of accesses: 18.07.2023).
Kite T. Oracle: Effective application design. St. Petersburg: Piter. 2006. 800 p.

optimizer mode, access method, optimal plan, Oracle DBMS, performance.