Oracle 11g: Error Logging in SQL *PLUS

One of the new features of 11G is error logging in SQL*PLUS.
When executing scripts from SQLPLUS, we will have to spool the output and then check for any errors in the spool file. But if we forget to spool, we will not be able to find out the errors.
In 11G we can simply turn on the error logging and all the errors will be stored in a special table called SPERRORLOG.

Example:
SQL> show errorlogging
errorlogging is OFF
SQL> set errorlogging on
SQL> select 1/0 from dual;
select 1/0 from dual
SQL> commit;

Then we can query from any other session and see the errors:
select * from sperrorlog;
One has to commit in order to see the data in other session

We can pin all the errors to a message:
SQL> set errorlogging on identifier ‘RELEASE 2.4.0′

One has to have 11G client in order to make use of this feature.

RESULT_CACHE Parameters

Lets look into 4 result cache parameters:

select * from v$parameter where name like 'result_cache%';

NAME VALUE
result_cache_mode MANUAL
result_cache_max_size 21495808
result_cache_max_result 5
result_cache_remote_expiration 0

Lets examine each value.
1. result_cache_mode : If this is MANUAL then we need to explicitly user "result_cache" key word to cache the results in the cache. If it is set to FORCE all the queries are cached if it valid and fits into the cache.
2. result_cache_max_size : This is maximum size of result_cache memory can hold, This is part of SGA and cannot hold more than 75% size of SGA
3. result_cache_max_result : This tells us what percentage of result cache any single query set can occupy. In this case only 5% of result_cache memory can be occupied by a single SQL query result set.
4. result_cache_remote_expiration : specifies the number of minutes the cached result set that access a remote object will remain valid. In this case the remote objects result set are not cached at all.

So thats about the 4 parameters of result_cache of 11G.

Result_Cache Statistics

After enabling the result cache you would want to know whether it is really helping improve performance. This can be done with help of result cache views.
One such view is v$result_cache_statistics;
select name, value from v$result_cache_statistics;

NAME VALUE
Block Size(Bytes) 1024
Block Count Maximum 3036
Block Count Current 80
Result Size Maximum blocks 240
Create Count Success 10
Create Count Failure 80
Find Count 4
Invalidation Count 3
Delete Count Invalid 0
Delete Count Valid 80

If we examine the above output Create count failure is too high, which might not help in optimized performance.
Create count value tells us how many cache results that were failed to create.

Find Count value must be as high as possible for best performance. In this case its low. Find count depicts the number of cache results that were successfully found.

Delete count valid depicts the number of valid cache results deleted. This value should be relatively low to make system full use of server result caching.
block count current gives us the value of how much memory is used to store cached data.
This is some information about v$result_cache_statistics.

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.

Quotes & Escape character

If we want to insert a string with quotes then it would be confusing to prefix many escape characters.

I want to insert 'RAGHU' with quotes; We would do as below:

INSERT INTO EMP_TT VALUES (99,'''RAGHU''','A',NULL,NULL,NULL,NULL,NULL);

In 10G and above there is a new way to do:

Just write the normal single quote and then prefix q'| and suffix |' , wherever single quotes is there it will be retained.

INSERT INTO EMP_TT VALUES (99,q'|'RAGHU'|','A',NULL,NULL,NULL,NULL,NULL);

This can be really useful when you are inserting large string with many quotes.

TO_CHAR Issues

We use TO_CHAR to get month from any date. When we use this to compare, we need to be careful.Did you know TO_CHAR appends white spaces at the end till it is 9 characters long. (Only when used with DATE)

Please see the below demo:

DECLARE
VMON VARCHAR2(20);
BEGIN
select UPPER(to_char(sysdate,'month')) INTO VMON from dual;
DBMS_OUTPUT.PUT_LINE(VMON);
IF VMON = 'JULY' THEN
dbms_output.put_line('Equal');
else
dbms_output.put_line('Not Equal but WHY???');
end if;
IF trim(VMON) = 'JULY' THEN
dbms_output.put_line('Its Equal after TRIM');
else
dbms_output.put_line('Not Equal');
end if;
END;

Workspace Manager

A common requirement in any project would be to have auditing columns in a table.
This is to know who updated/Inserted/Deleted the records and at what time.

Generally programmers, have to write custom update statements, to update name and time of the records before it is being modified.

Did you know oracle has a in-built package to handle this requirement.
Just one line of code is sufficient to fulfill the above requirements.

begin
dbms_wm.enableversioning('EMP_TT','VIEW_WO_OVERWRITE');
END;
Where EMP_TT is the table you want to have versions of old records.

Thats it.

Now anyone who updates or inserts new records, all the changes will be stored in a different view, named as EMP_TT_HIST. This has columns Username and created time, which helps us identifying the source of change.
Even if the complete table is deleted, old records will be in this view.

But one drawback of this is the performance of DMLs might slow down, one needs to test for performance before being implemented in PRODUCTION.

SPLIT function

Often developers have to split the string concatenated with "," (comma).
v_str := 'a,b,c,d,e,f,g,h';
Generally developers use combination of SUBSTR and INSTR to get each value.
Did you know Oracle had a in-built package which would do this for us.
DBMS_UTILITY.comma_to_table is the procedure, which would split the string for us.
No need to loop over to get the next index position etc..

DECLARE
PROCEDURE PARSE(string_in IN varchar2)
is
l_array DBMS_UTILITY.uncl_array;
l_count PLS_INTEGER;
BEGIN
DBMS_UTILITY.comma_to_table(string_in,l_count,l_array);
for indx in 1..l_count
loop
DBMS_OUTPUT.put_line(l_array(indx));
end loop;
exception
when others then
DBMS_OUTPUT.put_line(sqlcode || 'Bad List !');
end;
begin
parse('a,b,c');
end;

Oracle-History of releases

In my first blog, I would like go past the memory lane and note the key keatures each oracle release in the history has to offer:

Summary of key features of prior releases:

Version 6 : SQL Trace, v$Views
7: Replication, stored procedures and triggers
7.1 : Parallel capabilities
7.3 : Bitmap indexes, contexts.
8o : Partitioning and other terabyte necessary features.
8i : Internet capability such as java in databases
9i : Huge advancements in data gaurd availability
10g : Managebility in a grid
11g R1 : Real Application Testing
11g2 : Edition based redefinition (Ability to upgrade plsql objects online, without any downtime)

Surely oracle has come a long way and each release is built on capabilities of past release and it keeps getting better and better.