Optimization of bulk operation performances within oracle database

Abstract

The Oracle database enables use of bulk operation in order to reduce time needed for commands execution over a large amount of data. Operations are called using PL/SQL programming language. Advantage of these operations is that they transfer a large amount of data in a single step to SQL engine where they are executed. Bulk operation may also be used with a limitation to maximal number of lines transferred in a single step between PL/SQL engine and SQL engine. This limitation is specified by the LIMIT parameter. In this paper, performances of inquiry are compared to different values of LIMIT parameter. In this way, we have shown which amount of data is optimal for transfer in a single step, and how further increase influences performances. It is also described what influences optimal amount of data that may be transferred in a single step. Different configurations of memory structures were tested within the Oracle itself (SGA and PGA areas) as well as how their changes influence performances. Influence of data types on optimal amount of data was compared, as well as a role of a processor. The method used for verification of optimal values was arithmetical means of execution times for certain inquiries over database. Measurements were done on the same data in several occasions in order to obtain highly precise value and to eliminate possible background processes that are executed in the computer. ©2010 IEEE.