Welcome, Guest
Username: Password: Remember me

TOPIC: Forward attribution of SQL*Net message events

Re:Forward attribution of SQL*Net message events 2 years 3 months ago #345

Hi Jeff,

Thank you so much by your replies. The "trick" to me is the concept to "backward" the attribution for SNMFC when the slot is ZERO.

One more question: should we consider CLOSE calls as any other call to backward/forward SNMFC of cursor ZERO?

For example:

...
CLOSE #46912539545592:c=0,e=5,...
WAIT #0: nam='SQL*Net message to client' ...
WAIT #0: nam='SQL*Net message from client' ...
PARSE #46912532597640:c=0,e=17,...
...

In this case, who will be blamed by SNMFC events? Cursor #46912539545592 or cursor #46912532597640? This trace fragment is in the trace we're using
The administrator has disabled public write access.

Re:Forward attribution of SQL*Net message events 2 years 3 months ago #346

Hi Jeff,

In fact, it's very inefficient. It's a very complicated system in terms of performance.

BTW, are those memories good or bad? Pls share with us
The administrator has disabled public write access.

Re:Forward attribution of SQL*Net message events 2 years 3 months ago #347

  • Jeff Holt
  • Jeff Holt's Avatar
  • OFFLINE
  • Posts: 105
  • Thank you received: 2
Fond memories, they were. Truly.

A few years ago, one of our customers sent a few of their DBAs to one of our performance diagnosis courses. At the time, Cary and I were teaching together. That's primarily why I fondly recall it.

One of the students presented us with a trace file of one of their Powerbuilder application executions while it was traced. I don't know much about Powerbuilder but I know a lot about Oracle Forms and its predecessor SQL*Forms. The trace file showed every SQL statement begin parsed twice.

We later found out that there was a compilation option that would make the application parse all statements twice. That feature, I was told, was put there so that if there were a parse error, the error would be thrown during the second parse making it easier for the diagnostician to identify the offending statement.

Without that second parse, the first parse being deferred until execution (or cursor open) would make it more difficult (although why I have no idea) for the diagnostician to identify the offending statement.

As for the one-row-per-fetch problem, lots of Oracle applications have that potential problem. Even Java applications can suffer from it. But a Java developer can add code to increase the rows-per-fetch or, better yet, if there is a resource file that contains the connection attributes, then that file could be modified without having to edit the source.

I'm guessing that a well-written application or at least a well-designed application framework would make this fix pretty easy to implement.
The administrator has disabled public write access.

Re:Forward attribution of SQL*Net message events 2 years 3 months ago #348

The trace we're talking about has some os these "features". Its based on the old BDE (Borland Database Engine): between other things, double parsing and a kind of AUTOCOMMIT that follows almost every SQL statement.

We spent the last weeks trying to convince the developers about these problems but, the system is really complex, too big, and the vendor is confortable to disable these "features" now.

Jeff, the "forward attribution" and sometimes "backward" is clear to me now. I have just two more questions:

The first one, is regarding CLOSE # calls. In the context of "forward" or backward the SNMFC events, should we consider CLOSE calls as any other DB CALL? For example:

...
CLOSE #4444:c=0,e=5,...
WAIT #0: nam='SQL*Net message to client' ...
WAIT #0: nam='SQL*Net message from client' ...
PARSE #9999:c=0,e=17,...
...

In this case, who will be blamed by the the WAITS #0? Cursor #4444 or cursor #9999? And, what about the case below:

...
CLOSE #4444:c=0,e=5,...
WAIT #9999: nam='SQL*Net message to client' ...
WAIT #9999: nam='SQL*Net message from client' ...
PARSE #9999:c=0,e=17,...
...

The second question is not exactly related to SQL*Net message from client, but it came to me when I did some "greps", "cuts" and "awks" in my trace.

I got 26.390 close calls for cursors related to 2p09241t2na0g, but Profiler gaves 13.195. Probably I did something wrong but, just to be sure, is there any "conditions" regarding CLOSE counts and attribution to any cursor?
The administrator has disabled public write access.

Re:Forward attribution of SQL*Net message events 2 years 3 months ago #349

  • Jeff Holt
  • Jeff Holt's Avatar
  • OFFLINE
  • Posts: 105
  • Thank you received: 2
In older releases of the Oracle RDBMS, STAT lines, which reveal execution plans, were emitted only when a cursor was closed. Long ago, we made the mistake of equating STAT lines with a cursor close instead of just the execution plan.

Then, when Server Technology added CLOSE lines they also allowed STAT lines to be emitted at times other than cursor close.

Therefore, we were faced with a difficulty of adding support for CLOSE. This difficulty in how to support old and new RDBMS releases slowed us down a bit in supporting CLOSE lines.

The next release of our Profiler will support CLOSE lines as dbcalls similar to PARSE, EXEC, and FETCH.

So, when you start using that version of the Profiler, you should see the behavior you desire.
The administrator has disabled public write access.

Re:Forward attribution of SQL*Net message events 2 years 3 months ago #350

OK.
Jeff, thank you again, by all your explanations.
The administrator has disabled public write access.