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.

Is this a bug or an error?

Erik has posted really an interesting question on Laurent’s blog. It caught my attention and I thought of discussing it in greater detail over here.

What happens when you divide any number by a zero? You will end up with an error.

SQL> select 1/0 from dual;
select 1/0 from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

That’s true! The SQL query ends up with ORA-01476 error. Now, what happens when I run this query?

“select 1 from dual where exists (select 1/0 from dual)”

Well, even this should result in an error. Ok, let’s run this query:

SQL> select 1 from dual where exists (select 1/0 from dual);

         1
----------
         1

SQL>

Stupid! Isn’t it?

The query executes successfully instead of returning an error. That should be a bug. Wait a minute! Before arriving at any conclusion let’s test it again, but this time in a different way.

I will create a function which prints a message on console and returns a value.

SQL> create or replace function myfunc return number is
2 begin
3 dbms_output.put_line('test function');
4 return(99);
5 end;
6 /

Function created.

SQL> set serveroutput on
SQL> select myfunc from dual;

FUNC
----------
99

test function

When I use the function in a query, it returns the value and also prints the message “test function”.

Now, let’s put the function in the subquery’s Where clause and see what happens.

SQL> select 1 from dual where exists (select * from dual where myfunc = 99);

1
----------
1

test function

The function gets execute and the query returns the desired output.

Now, let’s place the function in the subquery’s Select list instead of “1/0” and test the query.

SQL> select 1 from dual where exists (select myfunc from dual);

1
----------
1
SQL>

As seen the query executes successfully but doesn’t print me the message “test function”. Why?

The answer is again hidden in Oracle documentation. The documentation for EXISTS is as below:

"An EXISTS condition tests for existence of rows in a subquery. It returns TRUE if a subquery returns at least one row.”

It’s clear that Oracle simply tests for the existence of rows in a subquery and will simply ignore the select list. However, the select list goes through syntax and semantics checks. Oracle never fetches the columns listed in the select list and it’s logical also. The EXISTS condition has nothing to do with the select list rather its interest lies in existence of rows.

This is the reason why “select 1 from dual where exists (select 1/0 from dual);” executes successfully.

Thanks for reading ;-)

Oracle 11g Hits the Stands

Dear readers,

Oracle has released the Oracle Database 11g Release 1 (11.1.0.6.0)Standard Edition, Standard Edition One, and Enterprise Edition.

Documentation for Oracle 11g can be downloaded from here.

Regards

25 Ways to Distinguish Yourself

Hello,

Lately, I have come across this beautiful document by Rajesh Shetty. The document is titled “25 ways to distinguish yourself”. In this document, Mr. Shetty discusses why and how to distinguish yourself. This document is extremely well-crafted and is really a must read document for every professional.

Thanks for reading :-))