Abstract
To utilize the vast number of resources like multi-processors and large amount of memory that are available with the new age computers, Oracle has come out with Oracle Parallel Execution that enables to enhance the performance of queries by reducing the execution time and cost of execution. In this project, we will explain the types of queries that are eligible to be executed in a parallel order and analyze the enhancement in the performance of running these queries as compared to running them in a serial order. We will be using Oracle 11g RDBMS and associated tools to gather and analyze data. We will be using Oracle's Explain Plan utility to gather data about the execution plan used during the execution of the query and TKPROF to calculate the execution time, count the reads and writes, paging and the other resources used during the execution. In this project we will also see how tuning certain system parameters can further enhance the performance of parallel query execution. To estimate the performance of Oracle’s Parallel Execution feature, we will compare the performance of executing certain SQL statements using parallel execution versus the same statements run in serial order. By optimizing query construction and by tuning the oracle database to facilitate for maximum utilization of the available resources, we can achieve a high degree performance enhancement both in execution time and in cost of execution.