Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Optimize the Massive DELETE Operation in Oracle, Part 1-3 : Page 3


advertisement
Now, repeat the same steps for the table in NOLOGGING mode (Scenario 2):
  1. Obtain performance results before load:

    select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size'; NAME VALUE ----------------- ----------- redo size 93019496 insert /*+ APPEND */ into TestData_Nologging select * from TestData; 547875 rows created. Elapsed: 00:00:05.39

  2. Obtain performance results after load:

    select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size'; NAME VALUE ----------------- ----------- redo size 93019404 select used_ublk from v$transaction; USED_UBLK ---------- 1



Populating the TestData_Nologging table with 547,875 rows in NOLOGGING mode took 5.39 seconds and generated only 0.01MB (!) (93,120,212 – 93,019,496= 100,716) of redo and 1 undo block.

Table 1 shows the performance numbers for both scenarios.

Elapsed Time (sec.) Redo Size (MB) Undo Blocks
Scenario 1: Regular INSERT, LOGGING mode 8.28 50.69 238
Scenario 2: Direct-path INSERT, NOLOGGING mode 5.39 0.01 1
Table 1. Performance Results for Scenarios 1 and 2

However, if you add just a primary key constraint to the TestData_Nologging table (Scenario 3), the redo size skyrockets even though both the table and the primary key index are in NOLOGGING mode:

drop table TestData_Nologging; create table TestData_Nologging nologging as select * from TestData where 1 = 0; alter table TestData_Nologging add constraint TestData_Nologging_PK primary key ( objectId, runId ); alter index TestData_Nologging_PK nologging;

Obtain performance results before load:

select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size'; NAME VALUE ----------------- ----------- redo size 93198748 insert /*+ APPEND */ into TestData_Nologging select * from TestData; 547875 rows created. Elapsed: 00:00:15.61

Obtain performance results after load:

select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size'; NAME VALUE ----------------- ----------- redo size 131033428 select used_ublk from v$transaction; USED_UBLK ---------- 817 commit;

As you can see, adding index led to an elapsed time increase from 5.39 to 15.61 seconds, a redo size increase from 0.01MB to 36MB (131,033,428 – 93,198,748 = 37,834,680), and an undo blocks increase from 1 to 817 (!). The reason is the database did heavy logging for the primary key index. Putting a primary key index in NOLOGGING mode did not help because NOLOGGING applies only to a limited number of operations. Here is a list of such operations from Oracle9i SQL Reference Release 2 (9.2):

DML:

  • Direct-path INSERT (serial or parallel)
  • Direct Loader (SQL*Loader)

DDL:

  • CREATE TABLE ... AS SELECT
  • ALTER TABLE ... MOVE
  • ALTER TABLE ... [all partition operations that involve data movement]
    • ALTER TABLE ... ADD PARTITION (hash partition only)
    • ALTER TABLE ... MERGE PARTITIONS
    • ALTER TABLE ... SPLIT PARTITION
    • ALTER TABLE ... MOVE PARTITION
    • ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION
    • ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION
    • ALTER TABLE ... MODIFY PARTITION ... REBUILD UNUSABLE INDEXES
  • CREATE INDEX
  • ALTER INDEX ... REBUILD
  • ALTER INDEX ... REBUILD PARTITION
  • ALTER INDEX ... SPLIT PARTITION

See Oracle9i SQL Reference Release 2 (9.2) at for more details on NOLOGGING clause (login required).

The above list shows that you can take advantage of the CREATE INDEX operation by rearranging your steps:

  1. Load the data into a table with no indexes.
  2. Create indexes in NOLOGGING PARALLEL mode.
  3. Create constraints.

The following series of commands for the TestData_Nologging table implements the above technique (Scenario 4):

drop table TestData_Nologging; create table TestData_Nologging nologging as select * from TestData where 1 = 0; insert /*+ APPEND */ into TestData_Nologging select * from TestData; create unique index TestData_Nologging_PK on TestData_Nologging ( objectId, runId ) nologging parallel; alter table TestData_Nologging add constraint TestData_Nologging_PK primary key ( objectId, runId );

Scenario 4 turns out to be more efficient than Scenario 3: redo size went down from 36.08MB to 0.17 and undo blocks from 817 to just 2.

Table 2 shows the performance numbers for both scenarios 3 and 4.

Elapsed Time (sec.) Redo Size (MB) Undo Blocks
Scenario 3: Direct-path INSERT into table with existing primary key 15.61 36.08 817
Scenario 4: Direct-path INSERT into table with no primary key, creating primary index in NOLOGGING PARALLEL mode, then adding primary key 12.34 0.17 2
Table 2. Performance Results for Scenarios 3 and 4

The numbers clearly show that Scenario 4 is the way to go. Now it's time to talk about implementing DELETEs as bulk INSERTs.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap