Working with VARRAYs in Oracle Part III

Thursday Apr 1st 2004 by James Koopmann

The third and final installment of this series examines the performance implications of using VARRAYS.

This article is the third and last in the three part series, that has explored the use of VARRAYs in Oracle. This part will look at the performance implications if you should choose to use VARRAYS.

In Part I&II of this series, we learned how to create the abstract data types and use them to our advantage for modeling purposes. They were pretty slick and could handle some interesting scenarios when moving data around. We also learned that we could easily hide the complexity of the abstract structures from our end users to make the abstract data types very easy to use. However, in this article we turn our focus to how these structures will behave when we actually use them. Will they improve the performance or bring our systems to their knees. Let's walk through a few different scenarios and show the effects of using VARRAYs.

The Players

1.      The table called GAS_LOG, defined with and without a VARRAY. See the first and second part of this series if need be.

2.      A non-unique b-tree index on GAS_LOG table for VIN number.

3.      A source table of 50,000 rows where each row is a unique VIN number. This table will be cycled through 20 times to simulate 20 different gas logs added for each VIN defined. This will amount to a 1 million-row table.

The Simple Test

For our test, we will be determining the response time, wait time, and a breakout of the CPU used. Table 1 shows the three test scenarios we will use and what they entail. The last two require a PL/SQL procedure which can be found in Listing 1.

Table 1
Test Scenarios




This is a simple insert into...select from statement and will be used as a baseline because it requires no processing and will produce the quickest response since there is not translation processing to be done.

PL/SQL Cursor

on Regular Table

This will open a cursor (20 times) on the source table and execute individual insert statements for a GAS_LOG table that does not have a VARRAY defined on it.

PL/SQL Cursor

on Table with VARRAY

This will open a cursor (20 times) on the source table and execute individual insert statements for a GAS_LOG table that DOES HAVE a VARRAY defined on it.

Definition Level Set

Before we go any further in this, I have provided a few quick definitions in Table 2 to give you insight into what we will be measuring. I have zeroed in only on these items because at a high level they are what matters to the end user and directly impact what the end user will see as response from the system. Please note that response time is the summation of the CPU times plus the Wait time. Also, note that parse time and recursive time are components of the total CPU used.

Table 2
Important Response Time Statistics

Response Time

The amount of time it took the application to finish. This is the summation of CPU Used and Wait Time.

CPU used by this session

Total amount of CPU Used for the application.

parse time CPU

Total amount of CPU used to parse the SQL Statements.

recursive CPU usage

Total amount of CPU used to update internal tables for the processing of the SQL Statements. This could be stuff like space allocation of updates to the data dictionary.

Wait Time

Total amount of time that resources were in a wait state.

Comparing Inserting Data

In the following scenarios, you will see the top level wait events along with the amount of CPU that was used to produce the inserts into the tables. While there were other wait events with some time associated to them, they were not very much and did not impact the results that I saw. Table 3 shows you a quick overview of the statistics and how the different scenarios compare to one another. A few gleaming insights can be noted:

1.      If it were not for the PL/SQL code doing recursive calls the cursor and inserting into the table without the VARRAY would almost be as quick as the straight inserts.

2.      For the VARRY object, all the times were much more than any operation on a normal table. Remember, if you want to look back at Part I&II of this series, that there is more PL/SQL code to handle the inserting into the VARRAY table and we must read the VARRAY into memory before we can change it and update it.

Table 3
Overview of Statistics Gathered



Response Time

CPU Used

Parse Time CPU

Recursive CPU Usage

Time Waited







PL/SQL Cursor on Regular Table






PL/SQL Cursor on Table with VARRAY






While performing the inserts, 50,000 at a time for 20 cycles, I decided to take a look at the segregation of the insert rate. As you can see from Table 4 there was not really any derogation for the straight insert or PL/SQL cursor methods. Unfortunately, the more entries we put in the VARRAY in our table, the more time it took. As you can also see, the very first insert we did had the best response time but still was not even close to what the other methods provided. Moreover, it just went downhill (upslope) from there.

Table 4
Graph of Response Time for each set of Inserts over time

Supporting Data


This test is by far going to be the fastest and simplest way to get the 1 million rows into our GAS_LOG table. We will do a straight insert into and selecting from our source table with the following DML.

insert into GAS_LOG 
  (select * from GAS_LOG_SOURCE);

The only overhead that we will incur in this approach will be the balancing of the b-tree index. As you can see by the following lines of output, it took just 2.41 minutes from start to finish.

Major Events
------------------------------ ----------- -------------- ----------- 
control file parallel write             53              0         260
db file sequential read                777              0         554
log file parallel write                760            743        1383
db file parallel write                 402            194        1608
db file scattered read               31620              0       10537
CPU used by this session                                         1749 
                                                                16091 = 2:41 minutes
CPU Statistics
CPU used by this session                        1749
parse time cpu                                     2
recursive cpu usage                                7

PL/SQL Cursor on Regular Table

This scenario proved to be just as fast for inserting as the straight inserts except for the procedural code. This is good to know since most all applications have code and we do not want to have our database be loaded down just because we are inserting through an application. Obviously, the trick is to reduce the amount of overhead that is required to execute the code and the DML is not really a concern.

Major Events
------------------------------ ----------- -------------- ----------- 
control file sequential read           157              0         639
log buffer space                        67              0        1125
log file parallel write               2584           2493        2252
db file parallel write                 976            476        4532
db file scattered read               31888              0       11189
CPU used by this session                                        22933
                                                                42670 = 7:06 minutes
CPU Statistics
CPU used by this session                       22933
parse time cpu                                  2797
recursive cpu usage                            20364

PL/SQL Cursor on Table with VARRAY

I am very disappointed in this method. I had great hopes before I started running these tests but really have to suggest not using this method unless you are not concerned with performance or you are not handling massive amounts of data. It just takes way too long and is too resource intensive to read an array into memory, manage the array for new data, and then update that VARRAY row back into the table.

Major Events
------------------------------ ----------- -------------- ----------- 
control file sequential read           487              0         964
log file parallel write               6450           6351        2599
db file scattered read               33040              0       10705
db file parallel write                4656           2321       15593
db file sequential read             243481              0       48065
CPU used by this session                                       141384
                                                               219310 = 36:33 minutes
CPU Statistics
CPU used by this session                      141384
parse time cpu                                 10312
recursive cpu usage                           137015


I do not think there is much else to say here and I am certain that you have reached the same conclusion I have. Maybe in the next release we might have a few easier methods to manipulate VARRAYs. I can only hope that we could manipulate individual elements without having to message the full VARRAY in latter versions of the engine.

Listing 1
Procedure for Inserts

  PROCEDURE cycle (cycle IN NUMBER, message IN VARCHAR2);
  PROCEDURE select_insert;
END Gas_Driver;


cycle_times   NUMBER;

PROCEDURE cycle (cycle IN NUMBER, message IN VARCHAR2) AS
FOR cycle_times IN 1..cycle LOOP
END cycle;

PROCEDURE select_insert AS
CURSOR cur0 IS SELECT a.* FROM gas_log_source a;
  FOR r0 IN cur0 LOOP
            'INSERT INTO gas_log '||
            ' VALUES (:1,:2,:3,:4)'
      USING r0.vin,r0.gallons,r0.fillup_date,r0.gas_station;
END select_insert;

END Gas_Driver;

» See All Articles by Columnist James Koopmann

Mobile Site | Full Site