Artificial Intelligence-Based Performance Optimization of Oracle PL/SQL Queries
Abstract views: 23
/ 
PDF downloads: 9
DOI:
https://doi.org/10.5281/zenodo.17992614Keywords:
Oracle, PL/SQL, Performance Optimization, AI, SQL AnalysisAbstract
This study aims to develop an artificial intelligence–based optimization system to analyze and improve the performance of slow-running queries in Oracle PL/SQL and Forms-based applications. Performance data from Oracle queries were collected using SQL_TRACE and EXPLAIN PLAN and analyzed in a Python environment. A dataset was constructed through feature selection based on metrics such as execution time, logical reads, and I/O operations. Random Forest and XGBoost algorithms were applied to identify factors contributing to query slowness, with historical performance records used for model training and evaluation through standard performance metrics. The system was further refined and validated using real-world queries to enhance its recommendation capability. Results indicate substantial improvements: execution time reduced by 82.4%, consistent read rate by 84.8%, physical read rate by 90.9%, and total Oracle cost by 97%. In model comparison, XGBoost achieved superior classification accuracy with 96.1% accuracy and F1-score, while Random Forest provided faster prediction times. This research introduces a novel AI-driven system for diagnosing and optimizing Oracle PL/SQL performance issues, offering decision support for database administrators and contributing to improved query efficiency.
References
Abar, H. (2020). Prediction of gold prices using XGBoost and MARS methods. EKEV Academy Journal, 83, 427–446.
Asokan, E. (2025). The role of AI in predictive database performance tuning. International Journal of Scientific Research in Computer Science, Engineering and Information Technology, 11(2), 356–369. https://doi.org/10.32628/CSEIT25112365
Buldu, B., & Yıldız, M. (2021). Performance comparison of XGBoost and random forest algorithms for network-based intrusion detection. International Marmara Sciences Congress, 733–739.
Chen, T., & Guestrin, C. (2016). XGBoost: A scalable tree boosting system. KDD 2016. https://doi.org/10.1145/2939672.2939785
Chicco, D., & Jurman, G. (2020). The advantages of the Matthews correlation coefficient (MCC) over F1 score and accuracy in binary classification evaluation. BMC Genomics, 21(6). https://doi.org/10.1186/s12864-019-6413-7
Dhanaraj, R. K., et al. (2021). Random Forest Bagging and X-Means Clustered Antipattern Detection from SQL Query Log for Accessing Secure Mobile Data. Wireless Communications and Mobile Computing, Article ID 2730246. https://doi.org/10.1155/2021/2730246
Doğanlı, B. (2025). Customer segmentation and behavior analysis: Examining income and spending behaviors using random forest. Business Economics and Management Research Journal, 8(1), 52–66. https://doi.org/10.58308/bemarej.1646966
Erdem, Ş., & Bakır, M. A. (2023). Detection of financial failure in the machinery and equipment manufacturing sector using isolation forest and resampling methods. Verimlilik Dergisi, 57(4), 719–734. https://doi.org/10.51551/verimlilik.1301865
Imani, M., Beikmohammadi, A., & Arabnia, H. R. (2025). Comprehensive analysis of Random Forest and XGBoost performance with SMOTE, ADASYN, and GNUS under varying imbalance levels. Technologies, 13(3), 88. https://doi.org/10.3390/technologies13030088
Karakaya, İ. (2025). Evaluation of Machine Learning and Ensemble Learning Models for Classification Using Delivery Data. Productivity for Logistics, 89–104. https://doi.org/10.51551/verimlilik.1526436
Marcus, R., & Narayanan, P. (2020). Bao: Learning to steer query optimizers. SIGMOD 2020. https://doi.org/10.1145/3448016.3452838
Oracle Corporation. (2023). Using SQL Trace and TKPROF (Oracle Database Reference Guide). https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/performing-application-tracing.html
Shao, Z., Ahmad, M. N., & Javed, A. (2024). Comparison of Random Forest and XGBoost classifiers using integrated optical and SAR features for mapping urban impervious surface. Remote Sensing, 16(4), 665. https://doi.org/10.3390/rs16040665
Şahinaslan, E., & Şahinaslan, Ö. (2022). Microsoft SQL sunucusunda veritabanı kurtarma teknikleri. International Journal of Innovative Engineering Applications, 6(1), 158–169. https://doi.org/10.46460/ijiea.1070325
Şahinaslan, E., Şahinaslan, Ö., & Dalyan, H. (2022). Naive Bayes sınıflandırıcısı kullanılarak YouTube verileri üzerinden çok dilli duygu analizi. Avrupa Bilim ve Teknoloji Dergisi, (38), 320–327. doi: 10.17671/gazibtd.999960
Şahinaslan, E., & Şahinaslan, Ö. (2023). Gümrük beyannamesi sürecinde öğrenmeye dayalı algoritmaların etkinliğinin incelenmesi. Uluslararası Mühendislik Araştırma ve Geliştirme Dergisi, 15(2), 410–421. doi: 10.26650/acin.1057060
Tartuk, M., Nurdağ, T. F., Acar, V., Erdem, S., Akay, F., & Abut, F. (2022). Forecasting call center arrivals using XGBoost combined with consecutive and periodic lookback. Eastern Anatolian Journal of Science, 8(1), 20–25.
Veranyurt, Ü., Deveci, A., Esen, M. F., & Veranyurt, O. (2020). Disease classification using machine learning techniques: Application of Random Forest, K-Nearest Neighbour and AdaBoost algorithms. International Journal of Health Management and Strategies Research, 6(2), 275–286.
Yang, Z., & Chiang, W.-L. (2022). Balsa: Learning a query optimizer without expert demonstrations. SIGMOD 2022. https://doi.org/10.1145/3514221.3517885
Yangın, G. (2019). Application of XGBoost and decision tree-based algorithms on diabetes datasets [Master’s thesis]. Mimar Sinan Güzel Sanatlar University.
Yeşilyurt, S. N., & Dalkılıç, H. Y. (2021). XGBoost ve Gradient Boost Machine ile günlük nehir akımı tahmini [Daily river flow prediction using XGBoost and Gradient Boost Machine]. In Proceedings of the 3rd International Symposium on Civil Engineering and Earth Sciences, 36–44.
Örgerim, A., Tunç Abubakar, T., & Tokmak, M. (2025). Performance comparison of neural networks: A case of data scientists’ job change prediction. Osmaniye Korkut Ata University Journal of the Institute of Science and Technology, 8(3), 1100–1119. https://doi.org/10.47495/okufbed.1481893
Downloads
Published
How to Cite
Issue
Section
License
Copyright (c) 2025 ARCENG (INTERNATIONAL JOURNAL OF ARCHITECTURE AND ENGINEERING) ISSN: 2822-6895
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.
The journal is licensed under a 