Hi,

Prod DB is using SUN storage and UFS filesystem with kaio. (i think this is not recommended disk config for Sun/Oracle right?)

Dev DB is just a local disk on notebook.

Data is the same on Prod and Dev DB. I imported them from the same source.
From the source DB, the query only takes 2.5mins having same explain plan.
Although the source DB is also SUN box(less powerful in terms of CPU and memory) and UFS filesystem, but I hear from my sysadmin that it is using Veritas Volume Manager to manage the source storage.

I have been running the AWR report a few times with the same query execution.
I noticed that there is significant "control file parallel write".
When the query is running, iostat also shows that the disk where controlfiles and redo logfiles reside is having more I/O than the other disks with datafiles.

I am suspecting it this performance issue is due to Sun async I/O on the filesystem.

Can someone confirm that my suspicion is correct?

Cheers
Orac