Installing Oracle Database 11g on Oracle Enterprise Linux 5

Dear readers,

Yesterday, I successfully installed Oracle Enterprise Linux 5 and Oracle Database 11g. The installation was very smooth without any problems. You may download Oracle Enterprise Linux 5 from here and Oracle Database 11g from here.

You may find this document to be very helpful while installing Oracle Enterprise Linux 5. Although, “welcome.html” file is very handy in guiding you through the database installation but this guide lists all the instructions specifically required to install Oracle Database 11g on Oracle Enterprise Linux 5.

Well, there are many new features which I would like to test.

Regards

SQL Functions Revisited

I was reading “SQL Reference” manual and came across some interesting SQL functions which I thought of discussing here.

COALESCE

COALESCE function accepts a varying length list of arguments and returns the first non-NULL value/expression in the list. Oracle uses short-circuit evaluation, i.e., it starts scanning from the left for the first not null value and immediately terminates upon finding the first not-null occurrence instead of scanning all the expressions is the list.

You can use COALESCE as a variety of the CASE expression. For example,

COALESCE( expr1, expr2)

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

SQL> select coalesce(null, null, null, null,15) from dual;

COALESCE(NULL,NULL,NULL,NULL,15)
--------------------------------
                              15

SQL> select coalesce(null, 20, null, 1/0, null) from dual;

COALESCE(NULL,20,NULL,1/0,NULL)
-------------------------------
                             20

Instead of running into error (ORA-01476: divisor is equal to zero) for the above SQL, the query returns 20 as the output. This is because Oracle immediately stops searching the list when it finds the first non-null value.

EXTRACT

EXTRACT function returns the value of a specified datetime field from a datetime expression.

SQL> select extract(month from sysdate) from dual;

EXTRACT(MONTHFROMSYSDATE)
-------------------------
                        8

SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2007

NULLIF

This function takes two parameters and compares the first parameter with the second parameter. If they are equal, then the function returns null otherwise it returns the first parameter.

SQL> select nullif(1, 2) from dual;

NULLIF(1,2)
-----------
          1

SQL> select nullif(1, 1) from dual;

NULLIF(1,1)
-----------

REMAINDER

This function takes two numeric arguments (non-numeric values are implicitly converted to numeric values) and returns the remainder of expr2 divided by expr2.

SQL> select remainder(5624,54) from dual;

REMAINDER(5624,54)
------------------
                 8

Thanks for reading :-)

To cache or not to cache an Oracle Sequence?

Last week, when I was reviewing AWR report of a busy OLTP database, I came across a dictionary related “Update” statement. This update statement was listed in top 10 SQL’s under “SQL ordered by Parse Calls” and “SQL ordered by Executions” sections of the AWR report and was parsed and executed 698 times during 30 minutes (A 30 minutes AWR report).

Here is the complete SQL Statement:

“update seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1”

Immediately, I queried Oracle data dictionary for a list of sequences of one of our most important schema. Following is the query and its output:

SQL> select sequence_name, cache_size, last_number from dba_sequences where sequence_owner = 'PISYS';

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ_AUDIT_DETAILS                   0     6728991
SEQ_TRANS_LOG                       0           1
SEQ_BATCH_LOG                      20         991

Sequence “SEQ_AUDIT_DETAILS” seems to be a culprit here, it has been used for more than 6 Million times and is not cached. Upon further investigation, it was revealed that this sequence is used by an audit detail table to generate unique serial.

I altered the sequence definition to cache 1000 values. Caching instructs Oracle to pre-allocate (in my case 1000) values in the memory for faster access.

SQL> alter sequence pisys.seq_audit_details cache 1000;

The above ALTER command did a magic and the UPDATE statement vanished from the AWR reports.

We need to cache sequences whenever possible to avoid extra physical I/O.

Thanks for reading and comments are welcome.