Working with VARRAYs in Oracle Part III - Page 2

April 1, 2004




Supporting Data




INSERT...SELECT




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
EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED 
------------------------------ ----------- -------------- ----------- 
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
EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED    
------------------------------ ----------- -------------- ----------- 
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
EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED    
------------------------------ ----------- -------------- ----------- 
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

Conslusion

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

CREATE OR REPLACE PACKAGE Gas_Driver AS
  PROCEDURE cycle (cycle IN NUMBER, message IN VARCHAR2);
  PROCEDURE select_insert;
END Gas_Driver;
/

CREATE OR REPLACE PACKAGE BODY Gas_Driver AS

cycle_times   NUMBER;

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

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

BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
END Gas_Driver;
/

» See All Articles by Columnist James Koopmann








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers