Published December 20, 2025 | Version v1
Journal article Open

Oracle PL/SQL Sorgularının Yapay Zekâ Tabanlı Performans Optimizasyonu

Authors/Creators

Description

Bu çalışma, Oracle PL/SQL ve Forms tabanlı uygulamalarda yavaş çalışan sorguları analiz ederek performansı artırmayı amaçlayan yapay zekâ destekli bir optimizasyon sistemi geliştirmeyi hedeflemektedir. Araştırmada, Oracle veri tabanında çalıştırılan sorguların performans verileri SQL_TRACE ve EXPLAIN PLAN kullanılarak toplanmış, Python ortamında analiz edilmiştir. Çalışma süresi, mantıksal okuma ve I/O işlemleri gibi metriklere dayalı özellik seçimiyle veri seti oluşturulmuş; sorgu yavaşlığının nedenlerini belirlemek için Random Forest ve XGBoost algoritmaları uygulanmıştır. Tarihsel performans kayıtlarıyla eğitilen modellerin doğruluğu çeşitli metriklerle değerlendirilmiş, sistem gerçek ortamdan alınan yeni sorgularla test edilerek öneri yeteneği geliştirilmiştir. Sonuçlar, önerilen sistemin sorgu yürütme süresini %82,4, mantıksal okuma oranını %84,8, fiziksel okuma oranını %90,9 ve toplam Oracle maliyetini %97 oranında iyileştirdiğini göstermektedir. Model karşılaştırmasında XGBoost, %96,1 doğruluk ve F1 skoru ile daha yüksek sınıflandırma başarısı sergilerken, Random Forest daha hızlı tahmin süreleri sağlamıştır. Bu çalışma, Oracle PL/SQL performans sorunlarını yapay zekâ ile analiz eden özgün bir sistem sunarak veri tabanı yöneticilerine karar desteği sağlamaktadır.

Abstract (English)

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.

Files

384-399.pdf

Files (616.2 kB)

Name Size Download all
md5:ff71c950b5969ffa98896a561283c375
616.2 kB Preview Download

Additional details

Additional titles

Translated title (English)
Artificial Intelligence-Based Performance Optimization of Oracle PL/SQL Queries

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