Function RESULT_CACHE

One of the new feature in Oracle 11G is Function result cache. This might provide huge performance benifits for applications.

If you create a function with RESULT_CACHE keyword, then oracle caches the result of the function in SGA.

When you call the function next time with same parameters, then function will not get executed at all, instead result will be returned from cache.

This saves us lot of time and improves the performance of overall process.

When ever any changes are made to underlying tables the cache is invalidated.
Hence the best results are got when you use this against tables which has more selects than inserts and updates.

Managing Function Result Cache:
1. RESULT_CACHE_MAX_SIZE parameter: Maximum amount of SGA memory that function result cache can use.
2. DBMS_RESULT_CACHE Package: Supplied package offers a set of subprograms to manage the contents of the cache.
3. Dyanamic performance views : V$RESULT_CACHE_STATISTICS, V$RESULT_CACHE_MEMORY,V$RESULT_CACHE_OBJECTS,V$RESULT_CACHE_DEPENDENCY.

DML Error Logging

By default when a DML statement fails all the records will be rolled back.
When you are loading millions of records and if there is one value which is unique then all the records which were inserted will be rolled back.
Finding out that one single record can be a daunting task.
Did you know, We can make use of DML error logging feature (introduced after Oracle 10G and above) to find out the record which is causing the error.

CREATE TABLE DML_TABLE (
id NUMBER(10) NOT NULL,
description VARCHAR2(50),
CONSTRAINT DML_TABLE_PK1 PRIMARY KEY (id)
);

INSERT INTO DML_TABLE VALUES (1,'FIRST VALUE');

INSERT INTO DML_TABLE VALUES (2,'SECOND VALUE');

INSERT INTO DML_TABLE VALUES (1,'SAME VALUE');

ORA-000001:Unique constraint Voilated

Now to avoid this and continue with the processing of records (when there are more than one record)

First we need to configure a table to hold errored out values:

BEGIN
DBMS_ERRLOG.create_error_log ('DML_TABLE','DML_TABLE_ERR');
END;

Suffix LOG ERRORS statement to the DML statement

INSERT INTO DML_TABLE VALUES (1,'SAME VALUE')
LOG ERRORS INTO DML_TABLE_ERR('INSERT') REJECT LIMIT UNLIMITED;

Now you wont get the error and the value will be inserted into ERR table.

select * from DML_TABLE_ERR;
This feature can be used for all the DML statements (INSERT,DELETE,UPDATE,MERGE)

Restriction:
When updating a record, if there is a unique constraint error then it cannot be logged.