dcsimg
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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


advertisement
 

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


advertisement

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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
Thanks for your registration, follow us on our social networks to keep up-to-date