I have a cursored SELECT statement in a stored procedure in database LOCAL that selects a bunch of columns from a single table on database REMOTE via a public DB link. The cursor loops and runs the SELECT 1000 times. If you run this procedure locally on the REMOTE database, it fetches 8,000 rows in 30 seconds over 1000 executes - not bad. However, when run over the DB link, it takes 330 seconds to fetch the same rows with the same executes - not good at all. I have tried several solutions, but have not brought the remote fetch time down. When the procedure is running, however, using a monitoring tool I see alot of system waits under the "SQL*Net Message from dblink" and "SQL*Net Messages to dblink" categories. Does anyone have any suggestions as to what may be slowing this query down, or further items to check on?
On your database server, usually the biggest bottleneck is I/O. Your I/O's get executed at about 60-80 MB / sec on a typical SCSI drive.
With DB links, your biggest bottleneck is the network. Your I/O's happen at 12.5 MB / sec on a 100BaseT network. The most critical part of a distributed solution is gobs and gobs of bandwidth (Gigabit network).
you're right about that, marist89...however, I failed to mention that these 2 databases are on the same UNIX server. Oracle Support is saying that networking is not an issue because of that. I'm unclear as to why that would take networking out of the picture...is it still not resolving the dblink name against the tnsnames.ora and then sending a request to the listener, whether the entire process is done on the same server or not?
Depends on how your tnsnames.ora defines the remote alias. If the remote is an IPC connection, then yes, I could see their point. If it is setup like any other tnsnames.ora alias, I would suspect it's going over the network. I believe your db link waits kinda support this theory. I would either have your network guys run a trace on the network or run some netstats from the server to confirm.
I understand what you are saying. The update statement is;
set Meter_Charge =
from NFTSSEE.QUOTE_LINE@LNK_NFTSSEE ql,
qv, NFTSSEE.QUOTE_HDR@LNK_NFTSSEE qh
where qh.qb_ref_no =:NFTS_QUOTE_ID
and qh.qb_id = ql.qb_id
and ql.qb_id = qv.qb_id
and ql.qbl_seq = qv.qbl_seq
and ql.CHARGE_CATEGORY = 'METER'
and qv.qbv_end_date = (select max(qbv_end_date)
where qb_id = ql.qb_id
and qbl_seq = ql.qbl_seq))
where NFTS_QUOTE_ID = :NFTS_QUOTE_ID
(sorry about the formatting).
What would make this statement behave differently. In one environment, 4k is dragged across the link, in the other 56Mb is dragged across the link.