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 have a similar experience with a dblink to a remote machine.
The same update statement runs in milli seconds in our pre-production environment, and runs in multiples of hours in our production environment.
The update statement looks sonething like this;
set = (select
from @, @, @
and = <>
The update statement with a hard-coded value for field2 runs in sub second times. The select statement by itself, using the dblink, runs in sub second times.
Our DBA's tell me that both environments have been setup the same - same Oracle version, same init.ora settings, same network.
Is there something in the Oracle environment that could be causing this hige difference in speed?
same server use IPC
also you understand that queries over db links the remote table is brought to local database?
None of our DBA's suggested that a remote table is brought to the local db.
If that is the case, can you explain this - when the select query is run by itself, it runs in sub second times.
Also, in one environment, the DBA's report that 4k is tranferred across the db link, whereas in the other environment, 56Mb is transferred.
of coz the table is brought to local database
if I run
from emp, dept@dblink
where emp.deptno = dept.deptno
where do you think the join takes place? do you think emp is sent to remote database and joined there then data is sent back?
if you look the SQL run in remote database you will see
select * from dept
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.
...because the join actually is taking place on the link in one case and brought local in the other.
Try using the driving_site hint.