Performance Analysis of Oracle Database 23ai on SELECT Queries and VIEW Statements Under Intensive Iterations

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.