Thanks again to everybody. The problem is solved.

I checked all the initialization parameter and did not find any difference.
Fo a small table which has 200 rows oracle optimizer was picking parallel
execution plan. The table definition had DEGREE 2.

But when I ran the querry individually execution plan was perfect.

As soon as I changed the degrre to 1 the execution time came down from 4hrs to
2min.

I appreciate all of your help.