I have a Java web-application running under Glassfish. App accesses Oracle using JPA (eclipselink).

Last 2 days I'm observing some perfomance issues: some messages about long running queries (10 to 100 sec) appears in Glassfish logs.

I asked Oracle DBA to provide Oracle AWR-log for 2 hours. There's SQL*Net message from dblink in top 5 Timed foreground events. It's seems suspicious for me, am I right, that in normal case there shouldn't be SQL*Net message from dblink in top 5 Timed foreground events? Is it a perfomance issue with database on the dblink other side?

enter image description here

  • "SQL*Net message from dblink" means your or other sessions captured during that time are fetching over DB link from remote database. You can try to minimize or avoid that. Based on statements involved, you could force more sql execution to happen on remote site using hints. Less data you access, faster your code will be. 30% or any% is just an observation, there are no fixed percentages. – Raj May 6 '14 at 11:34
  • Please note there is a difference between wait even "SQLnet message from DBLink" vs. "SQLNet more data from DBLink". This question is about SQLNet message from DBLink which in theory is very similar to SQLNet message from client. The latter is an idle wait event meaning Oracle database is waiting for another request. Is it the same for SQLNet message from DBLink..ie a session that came into the DB via a DBLink is now quiet and DB is waiting for another request from that session? – Kent Faulkner Aug 9 '17 at 14:36
  • I would begin by comparing this snapshot with one taken in a period where you were not observing this problem. – David Aldridge Aug 9 '17 at 16:55

Most likely means that the the shadow process(es) that support the dblink connection(s) are idle. Well they are definitely idle. The only question is are these idle periods between cursors or within an executing cursor. If they are within an executing cursor then whatever is driving the cursor execution, the fetching, is slow. But odds are it's simply an idle dblink connection. See http://kylehailey.com/oracle-sqlnet-wait-events/

| improve this answer | |

Wait on dblink is not an idle wait. If your code pull one row after the other, the whole TCP stack is involved. Probably no one changed sqlnet ( see Session data unit SDU) During the long run the remote database is forced to keep track for changed blocks due read consistency ( writes undo). Check if a data replication using materialized view ( MV) in local DB, guided with an MV_LOG on remote site might help. Or use bulk mode data transfer ( data pump) . DB link is not designed to bail out design /management errors. Is not the most performant part. You can see on larg DBs with heavy duty data transfer (ab)using db_link even 95% of runtime in wait on dblink.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.