1

Chapter 14DML Tuning

2

DML Performance Fundamentals

DML Performance is affected by:– Efficiency of WHERE clause– Amount of index maintenance– Referential integrity overhead– The overhead of using triggers

3

Index Overhead

Indexes help SELECT performance Indexes affect DML performance

– Indexes are updated with DML operations– Important to keep only needed and used indexes– Use care creating indexes on frequently updated

columns– DELETE overhead on indexes usually higher than

the delete of the data itself

4

Trigger Overhead

Triggers are PL/SQL blocks that execute when specified DML operations occur

Example of trigger affecting DML on the SALES table:– Used to derive a column value– On INSERT or UPDATE statement, calculates UNIT_PRICE as part

of DML operation:CREATE TRIGGER sales_iu_trg BEFORE INSERT OR UPDATE ON SALES FOR EACH ROW WHEN (new.quantity_sold > 0)BEGIN :new.unit_price := :new.amount_sold/:new.quantity_cold;END;

In Oracle 11g, virtual columns can perform same functionality without trigger overhead

5

Referential Integrity

Ensures data integrity Performance impacts with INSERTs and

UPDATEs Slows down DML processing

– Checks needed to ensure data integrity is maintained

– Foreign keys created between tables to ensure this integrity

6

INSERT Optimization

Array Processing– Especially valuable for INSERT statements– Improves bulk insert performance

Direct Path Inserts– Conventional Path

uses existing blocks with space for new rows Blocks processed in the buffer cache

– Direct Path Writes new rows using new blocks directly to database files on disk Bypasses buffer cache Minimal redo log entries generated Reduces overhead of buffer cache management Invoked by APPEND hint and with SQL Loader If disk IO is system bottleneck, can actually be slower

7

Multi-Table Inserts

Efficient if source table contains data to be applied to multiple target tables

INSERT ALL

WHEN region = ‘EMEA’ THEN INTO sales_emea

(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,

QUANTITY_SOLD, AMOUNT_SOLD)

VALUES (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,

QUANTITY_SOLD, AMOUNT_SOLD)

WHEN region = ‘US’ THEN INTO sales_us

(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,

QUANTITY_SOLD, AMOUNT_SOLD)

VALUES (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,

QUANTITY_SOLD, AMOUNT_SOLD)

SELECT * FROM sales_updates;

8

Delete Operations

Delete is single most expensive operation– Needs to find row to be deleted– Removed row from data block– Update all indexes that reference the row– Checking referential integrity constraints– Processing related triggers– Creating UNDO entries for all of the above

9

Truncate

Used to efficiently delete all rows from a table Resets table high water mark Improves subsequent full table scans Is a DDL operation, not a DML operation Cannot be rolled back Should be used over DELETE when removing all

rows from a table

10

Partitions

Can perform table-level operations at the partition-level– Can drop partitions to delete data– Can truncate partitions to delete data

Use range or interval partitioning on date columns for data to be purged

11

Create Table as Select

Can be used instead of delete if large number of rows need deletion

Especially effective with the following clauses– NOLOGGING– PARALLEL

Can be done using direct-path insert mode

12

Update and Merge Operations

UPDATE changed values to existing rows– Correlated updates

Use Join technique over correlated updates

MERGE– Updates rows if they exist– Inserts new rows where there is no match– More efficient that separate UPDATE and INSERT

statements

13

COMMIT Optimization

COMMIT involves disk IO– More frequent COMMIT means more disk IO

COMMIT– Usually driven by application design– Not by performance considerations– Data integrity more important than performance

For bulk updates, COMMIT infrequently

14

Batch and NOWAIT COMMIT

By default, COMMIT– Writes redo log entries to disk– Ensures recoverability and ACID qualities– Returns control to application after redo IO complete

This can be modified for performance– COMMIT NOWAIT returns control to application

immediately– Redo IO occurs in background– Raises possibility that Redo IO could fail unnoticed

15

NOLOGGING

Reduces most redo log overhead incurred by DML Compromises recoverability Use NOLOGGING for tables that contain

– Short term data– Non-critical data

Use NOLOGGING in these operations– Create table as select– Create index– Direct path insert

Append hint SQL Loader

16

Batch and NOWAIT COMMIT (cont.)

This can be modified for performance (cont.)– COMMIT BATCH means

Redo IO occurs “later” Reduced redo IO rate Opens chance that committed transaction not written to disk

– Oracle 11 parameters can be set for all transactions COMMIT_LOGGING=BATCH COMMIT_WAIT=NOWAIT

– Oracle 11g has single parameter COMMIT_WRITE={WAIT,NOWAIT,BATCH,IMMEDIATE}

– See table 14-1 p. 455