I have query running on production DB(Version 9i).
The query is fetching data over the dblink.
Its taking around 14 minutes and total number of rows fetched are around 2 million.
Is there any way I can tune the query? I have used driving site hist but that didn't helped.
Do some math and tell us if there is a way you can tune this.
We have no idea what "2 million rows" means in terms of size. That could be 1GB of data, it could be 1PB of data. Figure out how much data you're trying to move (size wise), then figure out roughly how fast your network can pipe the data (again, we have no idea if you have a fiber line and this is an internal network between machines, or if you're transporting the data 1/2 way around the globe over a dial up connection).
When you have all that figured out you'll know if the 14 minutes is / or is not an acceptable amount of time.