puzzling results of using bequeath

Subject: puzzling results

Platform: Oracle 8.0.5 SE; NT 4 sp6

The server in question in hosting multiple databases. One particular database is the target of links defined in several of the others. The connect string in the link definitions specifies a TNS entry that defines a TCP/IP connection. These databases show a high percentage of their wait event time going to SQLNet Message from dblink. It occurred to me that we should be able to reduce/eliminate this wait time by setting up a TNS entry for a bequeath connection and redefine the link to use this connection.

Let's call the database with the link APPL and the target of the link CORE. For a test I did the following:
- In APPL, SELECTed from v$sqlarea, v$sqltext_with_newlines to
get some application queries; identified three that exercised the dblink in question and saved them to a .SQL file.
- Wrote a batch file to repeatedly (300 times) log on to APPL
and execute the saved queries
- Run utilbstat against both APPL and CORE

• Execute the batch file, causing the queries to be executed 300 times.
• Run utilestat against both APPL and CORE
• Change the link definition in APPL to use the bequeath connection
• Run utilbstat against both APPL and CORE
• Execute the batch file, causing the queries to be executed 300 times.
• Run utilestat against both APPL and CORE

In CORE, the SQLNet Message to Client event went from 50% of foreground session wait time (at 2/100 sec/transaction) to 10% (at 1/100 sec/trans).

In APPL, the SQLNet Message from DBLink event went from 6.93% of foreground session wait time (at 280/100 sec/trans) to 7.04% (at 293/100 sec/trans).

The timing intervals varied from 324 seconds to 350 seconds, which Oraperf says is too small to be meaningful. So I repeated the test cycle, executing the group of queries 300 times for each trial. On these trials, the SQLNet Message to Client event in CORE went from 53.85% of foreground session wait time to none. SQLNet Message from DBLink in APPL went from 6.53% (788/100 sec) to 4.64% (562/100 sec).

I really expected to see these events drop to insignificant levels, if not disappear completely. I'm particularly puzzled by the fact that SQLNet Message to Client did react as expected in the "linked to" database, but there was no significant change in SQLNet Message from DBLink in the calling database. So . . . I'm either in need of an education in how to interpret these results, or in how to conduct a valid test.

FWIW,

• Ed Stevens

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)