Performance Analysis of Oracle Database 23ai on SELECT Queries and VIEW Statements Under Intensive Iterations
DOI:
https://doi.org/10.35870/ijsecs.v6i3.8351Keywords:
Oracle 23ai, SELECT, VIEW, Database Performance, Response TimeAbstract
The rapid growth of data volume demands efficient data management techniques, where appropriate query writing strategies become a crucial factor in maintaining system performance. This study aimed to analyze the performance of SELECT and VIEW commands on Oracle Database 23ai, specifically under intensive benchmarking involving thousands of iterations and massive workloads. An experimental quantitative method was implemented using a Java-based application utilizing the Operating System and Hardware Information (OSHI) library to monitor system resource usage in real time. Testing was conducted on a simulation dataset generating 40,000 rows of joined query results derived from a 40,000-row orders table and a 10,000-row customers table (~10.44 MB), with workload variations ranging from 10 to 10,000 iterations. Performance metrics evaluated included response time as well as CPU, RAM, and disk utilization. The results demonstrated that the SELECT command achieved superior performance at low iteration scales (10–100 iterations) with faster response times. Conversely, the VIEW command demonstrated more optimal execution at high iteration scales (1,000–10,000 iterations), yielding lower average execution times than SELECT. In terms of hardware utilization, VIEW consistently maintained more efficient RAM management across all iteration levels. The performance stability of both commands was governed by the database buffer cache mechanism and the operational transition from hard parse to soft parse after the initial execution phase. This study concludes that selecting between SELECT and VIEW must strictly account for the execution scale: SELECT is recommended for low-scale queries requiring immediate response, whereas VIEW provides a more sustainable solution for enterprise systems executing repetitive, large-scale workloads.
Downloads
References
Abdi, M. F., Susanto, A., & Kusnawi, K. (2021). Perbandingan kecepatan pencarian data SQL dan NoSQL. Jurnal Teknologi Informasi, 5(1), 7–11. https://doi.org/10.36294/jurti.v5i1.1696
Ahmed, R., Bello, R., Witkowski, A., & Kumar, P. (2020). Automated generation of materialized views in Oracle. Proceedings of the VLDB Endowment, 13(12), 3046–3058. https://doi.org/10.14778/3415478.3415533
Andriadi, A. A., & Faqih, A. F. M. (2026). Penerapan teknik database tuning pada MySQL untuk mengoptimalkan sistem reservasi online berbasis web. Journal of Integrated Engineering and Applied Technology (JIEAT), 1(1), 19–26. https://doi.org/10.65487/jieat.v1i1.30
Aulia, C. P., Pratama, M. Y., & Dewi, H. L. (2023). Perbandingan performa query SELECT dasar, VIEW, dan stored procedure pada database MySQL. Prosiding Seminar Nasional Teknologi Dan Sistem Informasi, 3(1), 456–464.
Gadupudi, P., & Saha, S. (2025). Evolution of buffer management in database systems: From classical algorithms to machine learning and disaggregated memory. arXiv preprint. https://doi.org/10.48550/arXiv.2512.22995
Huang, K., Zhou, J., Zhao, Z., Xie, D., & Wang, T. (2025). Low-latency transaction scheduling via userspace interrupts: Why wait or yield when you can preempt? Proceedings of the ACM on Management of Data, 3(3), Article 182. https://doi.org/10.1145/3725319
Karri, N., & Muntala, P. S. R. P. (2023). Query optimization using machine learning. International Journal of Emerging Trends in Computer Science and Information Technology, 4(4), 109–117. https://doi.org/10.55524/ijetcsit.2023.4.4.21
Kvet, M., & Papan, J. (2024). Enhancing analytical select statements using reference aliases. IEEE Access, 12, 28315–28330. https://doi.org/10.1109/ACCESS.2024.3366455
Noviyanti, P., Deolika, A., Hartinah, S., Haris, C. A., Maryana, T., & Sari, N. D. (2018). Perbandingan query response time pada model query VIEW dan cross product. E-Jurnal JUSITI (Jurnal Sistem Informasi Dan Teknologi Informasi), 7(2), 131–141. https://doi.org/10.36774/jusiti.v7i2.285
Nugraha, F. A., & Susetyo, Y. A. (2023). Analisis perbandingan performa database DuckDB dan SQLite pada pengolahan big data. JIPI (Jurnal Ilmiah Penelitian dan Pembelajaran Informatika), 8(3), 1052–1060. https://doi.org/10.29100/jipi.v8i3.3986
Oracle. (2025). Oracle AI database: Database performance tuning guide (Release 26ai) (Part No. G43584-01). Oracle Help Center.
Putra, A. E., & Samidi. (2025). Perbandingan kinerja teknik index bitmap dan B-tree dalam optimasi query pada database Oracle. Jurnal Sistem dan Teknologi Informasi (JUSTIN), 13(2), 245–252. https://doi.org/10.26418/justin.v13i2.81234
Putra, Y. Y., Purwaningrum, O., & Winata, R. H. (2022). Perbandingan performa respon waktu kueri MySQL, PostgreSQL, dan MongoDB. Jurnal Sistem Informasi dan Bisnis Cerdas, 15(1), 39–48. https://doi.org/10.33005/sibc.v15i1.2789
Thoib, B., Candra, P., Sururi, N., & Nugraha, D. S. (2024). Perbandingan performa pencarian data berbasis teks dengan dan tanpa full-text index pada basis data MySQL. INSOLOGI: Jurnal Sains dan Teknologi, 3(6), 663–673. https://doi.org/10.55123/insologi.v3i6.4385
Wagner, J., Nissan, M. I., & Rasin, A. (2023). Database memory forensics: Identifying cache patterns for log verification. Forensic Science International: Digital Investigation, 45, Article 301567. https://doi.org/10.1016/j.fsidi.2023.301567
Wathani, M. R., Wijaya, E. S., Zaenuddin, Z., & Abidin, M. Z. (2025). Efektivitas indeks dalam meningkatkan performa query join di sistem basis data relasional. Technologia: Jurnal Ilmiah, 16(2), 361–369. https://doi.org/10.31602/tji.v16i2.15284
Yang, S., Reichelt, D. G., Jung, R., Hansson, M., & Hasselbring, W. (2025). The Kieker observability framework version 2. In Companion of the 16th ACM/SPEC International Conference on Performance Engineering (ICPE Companion '25) (pp. 1–5). Association for Computing Machinery. https://doi.org/10.1145/3710714.3713020
Downloads
Published
Issue
Section
License
Copyright (c) 2026 Yosafat Stephen Suryadarma, Dian Widiyanto Chandra
This work is licensed under a Creative Commons Attribution 4.0 International License.
Authors who publish with this journal agree to the following terms:
1. Copyright Retention and Open Access License
Authors retain copyright of their work and grant the journal non-exclusive right of first publication under the Creative Commons Attribution 4.0 International License (CC BY 4.0).
This license allows unrestricted use, distribution, and reproduction in any medium, provided the original work is properly cited.
2. Rights Granted Under CC BY 4.0
Under this license, readers are free to:
- Share — copy and redistribute the material in any medium or format
- Adapt — remix, transform, and build upon the material for any purpose, including commercial use
- No additional restrictions — the licensor cannot revoke these freedoms as long as license terms are followed
3. Attribution Requirements
All uses must include:
- Proper citation of the original work
- Link to the Creative Commons license
- Indication if changes were made to the original work
- No suggestion that the licensor endorses the user or their use
4. Additional Distribution Rights
Authors may:
- Deposit the published version in institutional repositories
- Share through academic social networks
- Include in books, monographs, or other publications
- Post on personal or institutional websites
Requirement: All additional distributions must maintain the CC BY 4.0 license and proper attribution.
5. Self-Archiving and Pre-Print Sharing
Authors are encouraged to:
- Share pre-prints and post-prints online
- Deposit in subject-specific repositories (e.g., arXiv, bioRxiv)
- Engage in scholarly communication throughout the publication process
6. Open Access Commitment
This journal provides immediate open access to all content, supporting the global exchange of knowledge without financial, legal, or technical barriers.
